none
Calcuate maximum number of rows in a given category RRS feed

  • Question

  • Hi,

    I have a dataset in SSRS report. The dataset has two columns Category and Product.

    (Note: DataSet is not attached to a table/matrix)

    Requirement: I need to calcuate the number of rows in each category and then findout the maximum number among the row count for each category.

    For Ex:

    Category

    Product

    Category1

    Prod-a

    Category1

    Prod-b

    Category2

    Prod-c

    Category3

    Prod-d

    Category2

    Prod-e

    Category1

    Prod-f

    Category3

    Prod-g

    No. of rows in Category 1: 3

    No. of rows in Category2: 2

    No. of rows in Category3: 2

    Maximum number among (3,2,2): 3

    Thanks


    NC

    Tuesday, September 18, 2012 9:06 PM

Answers

  • Hi There

    Thanks for your posting again. This could be achievable using report item collection. So for example in the above example if the maximum count textbox name is  Textbox14. Please change it with your textbox name by going to properties. Now you can hide this table as per your requirement and use the maximum count as parameter for your sub report like this.

    So your parameter value will be

    =reportitems!Textbox14.Value

    =reportitems!Textbox14.Value

    I hope this will help. I am putting screenshot for your help

    Many Thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.



    Wednesday, September 19, 2012 9:02 PM
    Moderator

All replies

  • Hi There

    Thanks for your posting. I think you just need to create a group report based on your category and just take the count at group level.

    For the maximum count at all category level please put some expression like this

    =max(count(Fields!product.Value,"category"))

    =max(count(Fields!product.Value,"category"))

    Where I am assuming that you have a group with Name ”category”

    Please change the group name with your group name.

    I am putting a screenshot for your help

    I hope this will help

    Many Thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Proposed as answer by Charlie LiaoModerator Wednesday, September 19, 2012 9:15 AM
    • Unproposed as answer by gownam Wednesday, September 19, 2012 3:36 PM
    Tuesday, September 18, 2012 10:03 PM
    Moderator
  • Hi Syed,

    Thanks a lot for the detailed reply.

    I had added a note in my question -> (Note: DataSet is not attached to a table/matrix)

    The reason i added this is because, I need to be able to use the "Maximum Count" in the other places in the report.

    If i calculate the Maximum Count in the way described above, i wont be able to use it in some scenarios:

    For Example: In a report i have two tables(refer screenshot below). One for calculating Maximum Count and another table where i am using the calculated value.

    I get an error when i try to pass the "Maximum Count", as a parameter to subreport in another table.

    (Error: An error occurred during local report processing. (processing): (fields.Length == m_count))

    So i wanted to know if there is any other way to calculate the "Maximum Count" so that i can use this value anywhere in the report.

    Thanks


    NC

    Wednesday, September 19, 2012 3:36 PM
  • Hi There

    Thanks for your posting again. This could be achievable using report item collection. So for example in the above example if the maximum count textbox name is  Textbox14. Please change it with your textbox name by going to properties. Now you can hide this table as per your requirement and use the maximum count as parameter for your sub report like this.

    So your parameter value will be

    =reportitems!Textbox14.Value

    =reportitems!Textbox14.Value

    I hope this will help. I am putting screenshot for your help

    Many Thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.



    Wednesday, September 19, 2012 9:02 PM
    Moderator