Calculate Min and Max for aggregation in SSRS report

0
votes

My SSRS report have a dataset that will return the following result. The result set for some reason may not be changed. Note that Category - SubCategory pair might not be distinct.

Category Sub-Category Value
-----------------------------
A        A1           100
A        A2           120
A        A2           60
B        B1           80
B        B2           90
B        B2           70

I want to show the max value and main value for each of the SUM(category, subCategory) in report matrix, as exactly the format as follows (except the comment in bracket):

Max |  180 (two A-A2 rows)
Min |  80  (B-B1)

How can I define the matrix and write the expression? If make a group on these two columns, The matrix will show four rows regardless of what expression I set.

3
reporting-servicesssrs-2008-r2
can you clarify your question? What is a "Main" value? you have a max of 180 in your desired output.. yet it looks like a sum of just the A2 subcategory.. in you are grouping by category and then by sub category... you should simply be able to add a column inside the subcategory group to get you min , max and sum of that sub category.. - Harry
when inside the group.. you can simply say min(fields!Value.value) or max(fields!Value.value) or sum(fields!Value.value) - Harry

3 Answers

0
votes

I tried to run your use case on my local SSRS.

On Left hand side I have original Data and on Right hand side I have the desired result you expect.

enter image description here

What you need is grouping as below

enter image description here

Expression for sum as below

enter image description here

0
votes

Put a tablix into your report. Then at Row Groups (bottom) click on the (Details) and chose Add Group > Parent Group. Click the Add group header and chose your Sub-Category. Do the same with your Category. Your Row Grouping hierarchy should be now Category > Sub-Category > Details.

Now you see the brackets on the left in your tablix, they indicate the level. If you use now the following expression with their group name on the specific level, you will get what you want.

'At the Category group level header
=Sum(Fields!Value, "CategoryGroupName")

'At the Sub-Category group level header
=Sum(Fields!Value, "SubCategoryGroupName")
0
votes

I got the way to make it. The solution is as follows:

  1. Make a row parent group called row. Let the group group by a constant.
  2. Make a child group category under the row group which is grouped by Category.
  3. In matrix cell which is inside the group, Add this expression: =Min(Sum(Fields!Value.value), "Category"), "row"). that's the reason why I make a constant group, because I want to make the nested aggregate function legal.
  4. This expression will return all values identical within the Category group. Now add another row outside of these row groups. Pick a cell and enter =ReportItems!ThatTextBox.Value.
  5. Hide the row which consists your groups.
  6. Do the same for MAX value (Start from adding an adjacent group, grouping by constant)