locked
how to get distinct counts in ssrs. RRS feed

  • Question

  • I have a sales resultset from which would like to know how many products sold to which company on the sales summary report?


    For instance: Sales Person A might have sold 50 products for a month 10 each for 5 different companies. But distinct products might be 4 to Company A, 5 to Company B, 3 to Company C and so on. I do not want to do this in my SQL Query is there a way I could get this accomplished using Reporting Services features.

    Display:

    Sales            Company        Products
    Sales Person A    Company A    4
    Sales Person A    Company B    5
    Sales Person A    Company C    3

    Thanks...

    Tuesday, May 31, 2011 9:31 PM

Answers

  • Hi,

     

         Coudl you please let us know if you are using any groupings in the matrix or table? if yes then use countdistinct(Fields!product.value,"<Groupname>"). Let us know if it helps you.

     

    Regards,

    Bharath R S

    • Marked as answer by Elvis Long Sunday, June 5, 2011 4:37 PM
    Wednesday, June 1, 2011 4:39 AM

All replies

  • Hi

    There is CountDistinct aggregation function , it should help you

    Check documentation for details 

     


    Please mark this as answer if it helps.
    Microsoft Certified Trainer
    Microsoft Certified Professional Developer
    BizTalk blog: http://www.EnterpriseApplicationsDevelopment.com/
    Tuesday, May 31, 2011 10:40 PM
  • Well, I am using the CountDistinct aggregation function but that gives me the actual count and not the distinct count.

     

    Thanks..

    Tuesday, May 31, 2011 10:53 PM
  • Can You Show Post the ho are you fetching Your Data Value in the dataset ?? So, that we can Look into it and find any solution ,,..

     


    << Mark it as Answered if found useful >> Rakesh M J
    Wednesday, June 1, 2011 4:04 AM
  • Hi,

     

         Coudl you please let us know if you are using any groupings in the matrix or table? if yes then use countdistinct(Fields!product.value,"<Groupname>"). Let us know if it helps you.

     

    Regards,

    Bharath R S

    • Marked as answer by Elvis Long Sunday, June 5, 2011 4:37 PM
    Wednesday, June 1, 2011 4:39 AM
  • Thanks for your response...your inputs helped.

     

    Regards.....

    Wednesday, June 1, 2011 8:18 PM