CODEWITHSUNDEEP
reporting-services
Mike
Mike

Reputation: 57

SSRS Groups, Aggregated Group after detailed ones

in SSRS, I try to create a group structure as in the example below (months have only 5 days here for simplicity). The tricky issue is the last column "Sales(monthly)": It should be a Row Group, grouping by month, after the more detailed columns "Day" and "Sales(daily)". It would be no problem to have this column as the second one between "Month" and "Day" column.

But after being already on the "detail level" with "Day" and "Sales(daily)", I cannot create another more aggregated group. And if I just set Group Properties, Group On to Month for the 4th column, then "Day" and "Sales(daily)" also appear aggregated with only 1 row per month.

Does anybody know how to solve this? Or is this just not possible in SSRS?

Month  Day   Sales(daily)  Sales(monthly)
Feb    1     100           615
       2     150
       3     130    
       4     125    
       5     110    
March  1     100           685
       2     150    
       3     200    
       4     125    
       5     110    
April  1     100           560
       2     150    
       3     75 
       4     125    
       5     110

Many thanks Mike

Upvotes: 1

Views: 1327

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

There's not a native way to do this in SSRS.

You would need use LookupSet to get the daily sales for the month and then use a custom function (SumLookup) to get the total.

Then you need to use an IIF so that you only display it on DAY 1.

=IIF(Fields!DAY.VALUE <> 1, NOTHING, 
Code.SumLookup(LookupSet(Fields!MONTH.Value, Fields!MONTH.Value, Fields!SALES.Value, "Dataset1")))

Function code:

Function SumLookup(ByVal items As Object()) As Decimal

If items Is Nothing Then Return Nothing

Dim suma As Decimal = New Decimal()
Dim ct as Integer = New Integer()

suma = 0
ct = 0

For Each item As Object In items

  suma += Convert.ToDecimal(item)
  ct += 1

Next

If (ct = 0) Then return 0 else return suma 

End Function

SSRS:

Use LookupSet to retrieve a set of values from the specified dataset for a name-value pair where there is a 1-to-many relationship. For example, for a customer identifier in a table, you can use LookupSet to retrieve all the associated phone numbers for that customer from a dataset that is not bound to the data region.

Upvotes: 2

Related Questions