Forum Discussion
HierarchyID display
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.
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
Dude, I fail to see why it is so difficult to copy and paste a value out of a cell, or type a value from a cell into this little edit box versus a bunch of SQL code that assumes someone has the AdventureWorks database installed along with something like SQL Server Management Studio and the like, which means that the people trying to help you have to jump through hoops and since we are all volunteers on here, time is precious.
But, what you want is to replace your slashes (/) with piples (|) and then use PATH and PATHITEM. See my Quick Measure here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279
Also, I suggest you read my blog post here on how to get your question answered quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
You do not need AdvW - choose whatever databse you wish
Dont need Manager - can be run in Vis Studio or at a command prompt.
The idea is to get a table that could be used - as is requested frequently on Stackoverflow.
Finally your answer does not meet my criterial. The hierarchyid is helds as hex - you see it as / delimited here as the PowerBI is automatically doing something like
Convert(varchar(25), level) as lev
on the hex.If you choose to run my code you would see how the data is held in teh table.
Your code is manipulating a text string, which does not exist unless explicity created in a select statement.
The aim was not to write code as I don't want people to do anything - just drag data into a grid or something better.
I'll stick to the code which I posted in the first post - it works in Excel very well
Interestingly if one searchs for hierarchyid and PowerBi in the search engine of your choice, this is the only reasonably relevant entry that appears.
I'm guessing my requirement has not been approached by others.
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 OrganizationNodeI get rows like this:
1 Ken Sánchez NULL NULL NULL
2 Terri Duffy 0x58 /1/ 1
3 Roberto Tamburello 0x5AC0 /1/1/ 2Which, 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 OrganizationNodeI 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--- 4So, 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