I am trying to create a chart displaying data about a user and of their employees. The hierarchy is defined through the "Fact" table (according to the star schema, its a brand new thing to me, and I hope that I understood it well.) I transformed my source SQL database to the following :
The tables User and UserChild are actually the same.. (FirstNameChild = FirstName, etc.), it's the only way I found in order to associate the IdUserChild to the associated FirstName and LastName. What I would like to do now is to create a chart that counts the rows in DailyActivity and Activity_Contact associated with each IdUserChild (well.. IdUser in fact.. That's my problem.) The hierarchy is defined as follow in the Facts table:
I have a variable $(userid), and I would like all their child to be displayed in a chart with their associated number of IdDailyActivity and IdActivity_Contact. My problem is that my dimension in the chart is associated with IdUser, and not IdUserChild. That means that every columns will be about the IdUser pointed by my variable $(userid), and not the actual employees of my user.. And nothing is associated with IdUserChild in my schema, but the User table. I hope I made myself clear.
My dimension is defined as follows : =if(IdUser=userid, IdUserChild) (this way, I am able to display the user's employee in a chart, and using FirstNameChild and LastNameChild I'm able to display their name, but I don't want to import my data twice in order to associate everything with IdUserChild too)
Any help is much appreciated.