Forum Discussion

MikekSSL's avatar
MikekSSL
Frequent Visitor
8 years ago

HierarchyID display

Hello I have a table with a standard Microsoft hierarchyID column positioning parts with a product Bill of Materials. I can display the BoM parts in Excel tabbomng across and back for each level us...
Need Help
MikekSSL's avatar
MikekSSL
Frequent Visitor
8 years ago

Well there is a glitch in the matrix.

 

I connected PowerBI to Adventureworks2014 and entered the simple string

SELECT * FROM [HumanResources].[Employee] in the advanced  options/sql statement

and get a messgae - incompatible data type OrganisationNode.

 

 

Doing the same with my tables - no error - hierarchyID comes through as if GetString() applied

 

 

 

PowerBI - the software that is almost good.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

Can't answer your question without knowing what a sample row of data actually looks like.

 

  • MikekSSL's avatar
    MikekSSL
    Frequent Visitor
    8 years ago

    I appreciate your attemp to help, but as I have said and implied in each post its to do with the standard (since 2008) hierarchicalID data type.  The rest of the data in the row is irrelevant

     

    And as said in my penultimate post:

    "The raw data is exactly as you'll see if you do a select * from AdventureWorks2014.HumanResources.Employee"

     

    Just look at a line in the  AdventureWorks2014.HumanResources.Employee table and you will see what I see.  My own columns are different headings etc but the hierarchical data is exactly the same.

     

     

    If someone can tell me how to get that Employee table into a PowerBI Visualisation so the OrganizationNode controls the indentents I'd be happy

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      8 years ago

      OK, that's great, but to do that, I have to go out and find the AdventureWorks database in question, download it, potentially install SQL Server Management Studio and do a bunch of other investigation. Versus, you just post example data from that row or column and viola, you might get an answer to your question.

      • MikekSSL's avatar
        MikekSSL
        Frequent Visitor
        8 years ago

        OK - try this

        Use adventureworks2014;
        Go


        CREATE TABLE SimpleDemo
        (Level hierarchyid NOT NULL,
        Location nvarchar(30) NOT NULL,
        LocationType nvarchar(9) NULL);
        Go

        --- sample dataInsert

        INSERT SimpleDemo
        VALUES
        ('/1/', 'Europe', 'Continent'),
        ('/2/', 'South America', 'Continent'),
        ('/1/1/', 'France', 'Country'),
        ('/1/1/1/', 'Paris', 'City'),
        ('/1/2/1/', 'Madrid', 'City'),
        ('/1/2/', 'Spain', 'Country'),
        ('/0/', 'World', 'Planet'),
        ('/3/', 'Antarctica', 'Continent'),
        ('/2/1/', 'Brazil', 'Country'),
        ('/2/1/1/', 'Brasilia', 'City'),
        ('/2/1/2/', 'Bahia', 'State'),
        ('/2/1/2/1/', 'Salvador', 'City'),
        ('/3/1/', 'McMurdo Station', 'City');
        Go

         

         

        I'd like the grid visual to show the the levels indented by their relative hier value

        Ie world not indented, continents indented by 1, then countries indented under their continent.  The icing on the cake would be for vertical lines to indicate the 'leading edges' of each hierarchy level.  Other informational fileds just hang off the end of the row.

         

        I can do all of this in SQL and in Excel but I want a super simple one touch solution - preferably in PowerBI