Forum Discussion
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 using code such as
SELECT
CAST(REPLICATE (@tab , b.Hid.GetLevel()) + b.Component as nvarchar(100)) +
' , ' +b.[Description] + ' qty' + Convert(varchar(20), b.QtyPer) as listing
I would like to use the table directly in PowerBI and have indedented columns by simply adding the hierarchyID column. I've found nothing to do this.
Has PowerBI not kept up with SQL data types or am I missing something?
Thanks in advance for any help
15 Replies
Not sure what a "standard Microsoft hierarchyID column positioning parts" means. Can you show some example/sample data?
I am likely way off, but Power BI has PATH, PATHITEM, etc. functions to deal with parent-child values.
OK.
Rather than use my daya - I'll use teh data from the HumanResources.Employee table in SQL 2016
SELECT E.BusinessEntityID, P.FirstName + ' ' + P.LastName as 'Name',
OrganizationNode, OrganizationNode.ToString() as 'HierarchyID.ToString()',
OrganizationLevel
FROM HumanResources.Employee E
JOIN Person.Person P
ON E.BusinessEntityID = P.BusinessEntityID
order by OrganizationNodeThat will produce a listing of employees sorted by OrganizationalNode You can see the relationship between lines through the ToString() column. I would like to display the data in the correct hierarchy order, with the hierarchys offset dependant on level.
Does this help?
Not entirely, what would an actual row of data look like? I don't need your data, just a representation of a data row.
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.
Can't answer your question without knowing what a sample row of data actually looks like.
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
