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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

I'm still not even sure of your initial question honestly. And you can't just use any database, it has to have the kind of information you are dealing with, which I'm still not sure of quite honestly.

 

But, I took the time to install SQL Server. And I installed SQL Server Management Studio (I didn't have either that or Visual Studio) and then I installed the AdventureWorks database.

 

So, then from this query:

 

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 OrganizationNode

 

I get rows like this:

1 Ken Sánchez NULL NULL NULL
2 Terri Duffy 0x58 /1/ 1
3 Roberto Tamburello 0x5AC0 /1/1/ 2

 

Which, incidently was what I was asking for in the first place.

 

Then, let's see, from this query, 

SELECT * FROM [HumanResources].[Employee]

 

I get data like this::

 

And from this query:

DECLARE @tab CHAR(2)
SET @tab = CHAR(9)
SELECT
Convert(nvarchar(100),REPLICATE(@tab , b.OrganizationNode.GetLevel()) )
+ b.LoginID + '--- '
+ Convert(nvarchar(25),b.OrganizationNode.GetLevel() )
as listing
FROM [HumanResources].[Employee] AS b
order by OrganizationNode

 

I get data like this:

  adventure-works\terri0--- 1
    adventure-works\roberto0--- 2
      adventure-works\rob0--- 3
      adventure-works\gail0--- 3
      adventure-works\jossef0--- 3
      adventure-works\dylan0--- 3
        adventure-works\diane1--- 4

 

So, the question becomes, what format is your data in (input) and what format do you want it to be in (output).

 

Also, this thread might help.

https://community.powerbi.com/t5/Desktop/Creating-a-Hierarchy-in-Power-BI/td-p/49093

 

MikekSSL's avatar
MikekSSL
Frequent Visitor
8 years ago

To explain how I am currently displaying is to use my Excel example

I use the data you created in the final table (either through a direct query or a stored proc ) but esentially comes down to looking like a csv file into PowerQry and use the colum splitter function on the single column which contains all my tab characters to push the components out to a new column compatible with their hierarchy depth

 

A snippet of my code in PowerQry

 

let
PartNo = Excel.CurrentWorkbook(){[Name="Partparam"]}[Content], <--(comment - this allows me to select which block of data we are displaying - not relevant for this discussion
Source = Sql.Database("uksqldemo", "Adventureworks2014", [Query="exec dbo.TabbedList

@Partno = " & "'" & PartNo[Sparams]{0} & "'" ]

)

,
#"Split Column by Delimiter" = Table.SplitColumn(Source, "listing", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"listing.1", "listing.2", "listing.3", "listing.4", "listing.5", "listing.6"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"listing.1", type text}, {"listing.2", type text}, {"listing.3", type text}, {"listing.4", type text}, {"listing.5", type text}, {"listing.6", type text}}),

 

 

 

The result after splitting the column is as follows.  Which is eaxctly how we need it.  Note the M or F is the gender column I have added for further modifications.

 

 

 

My ideal would be for vertical and horizontal lines joining up the parts so people can follow the levels and even better drill through the details of, in this case, the login info.

 

If we could do this on one or two drag and drops in PowerBI we would be v happy and I reckon another useful tool for PBI

 

Thanks

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Hmm, I think what you really want to do is to fill in all of your values instead of having nulls. Take a look at the table created by this query:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTVfSUXLKzMkBUsGlVZVAyqc0GUR5JSZn6ybq5uQXpCrF6hBQm5mblE9YVX5GXl6lglN+JUGlAaklqUUEVbkk5hFU40uEu1KLizNRPOlclFhVqeCRX1ScClKdkZqTAzYrNScxLxMk5FKalkaSBo/8XFQP4dDhlJ8EcSYoPGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [tier1 = _t, tier2 = _t, tier3 = _t, tier4 = _t, tier5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"tier1", type text}, {"tier2", type text}, {"tier3", type text}, {"tier4", type text}, {"tier5", type text}}),
in
    #"Changed Type"

    Once you have that, you can just build a hierarchy by draggin tier2 into tier1 and then tier3, tier4 and tier5. Then, if you use that hierarchy in a matrix, you get a really nice way to drill down and up into the hierarchy if you use the expanding drill down (branching down arrows).