Forum Discussion

mmoizk's avatar
mmoizk
Icon for Helper III rankHelper III
10 years ago
Solved

Reporting on Many to many

I have 3 datasets DS1, DS2 & DS3. 

 

Example 

DS1

DS1ID          Name                 DS2ID

1                   T1                         1

1                   T1                         2

1                   T1                         3

2                   T2                         4

2                   T2                         3

2                   T2                         5

3                   T3                         1

3                   T3                         6

 

DS2

DS2ID          DS2Name             

1                   S1

2                   S2

3                   S3

4                   S4

5                   S5

6                   S6

 

 

DS3

DS2ID          PName

1                   p1

1                   p2

2                   p3

2                   p4

3                   p1

3                   p3

4                   p5

4                   p6

5                   p2

5                   p3

 

Now the report need to count all Trans in DS1 for a Giver PName

 

PName           Count of DS1.Name

p1                         3

p2                         1

..                          ...

  • mmoizk Which visual are you using ? When you pick a field from third table have you specified you want the count instead of Don't summarize (if you're using table).

     

7 Replies

  • ankitpatira's avatar
    ankitpatira
    Icon for Community Champion rankCommunity Champion

    mmoizk It is simple. You should have your relationships that looks like below. In any visual select name from TAB1 and Count of PName from TAB3.

     

     

    • Sean's avatar
      Sean
      Icon for Community Champion rankCommunity Champion

      mmoizk can you explain how you get your totals? I get other totals with your setup???

       

      • mmoizk's avatar
        mmoizk
        Icon for Helper III rankHelper III

        Hi Sean, Sorry i didn't mention this earlier but count are actually Distict count of DS1ID .

    • mmoizk's avatar
      mmoizk
      Icon for Helper III rankHelper III

      Thanks Ankit Patira, I wish it was that easy . I did extactly that but when i pic something from the 3rd table i get an error. 

      • ankitpatira's avatar
        ankitpatira
        Icon for Community Champion rankCommunity Champion

        mmoizk Which visual are you using ? When you pick a field from third table have you specified you want the count instead of Don't summarize (if you're using table).