0 Replies Latest reply: Jun 15, 2011 10:01 AM by juliiian RSS

    Data about a user's employee in a chart

      Hi there,

       

      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 : 

       

      schema.png

       

      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:

       

       

      IdUser      IdUserChildIdDailyActivityIdActivityContact
      211

      13

      14

      15

      29

      11
      5
      11
      6
      3
      7
      3

      55
      4

      56

       

      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.

       

      Julian