Search code examples
reporting-servicesssrs-2008ssrs-2008-r2

Grouping by data based on month-year via expression in ssrs2008

I have a matrix in ssrs 2008 like

Date                    Data
<<Expr>>                [Fields!Data.Value]

where expr is:

MonthName(Month(Fields!StartPeriod.Value), True) + " " + CStr(Year(Fields!StartPeriod.Value)) + " - " + MonthName(Month(Fields!EndPeriod.Value), True) + " " + CStr(Year(Fields!EndPeriod.Value))

In this case, I have the output like:

Date                    Data
March2014-April,2014    10
March2014-April,2014    20 
March2014-April,2014    30
March2014-April,2014    40
April,2014-May2014      ...
April,2014-May2014
April,2014-May2014
April,2014-May2014
May2014-June2014
May2014-June2014
May2014-June2014
May2014-June2014
June2014-July2014
June2014-July2014
June2014-July2014
June2014-July2014

However, I want it grouped like:

Date                    Data
March2014-April,2014    100
April,2014-May2014      ...
May2014-June2014        ...
June2014-July2014       ...

I tried to add a groupby endtime to the related row grout but it is not working since endtime is coming from the query weekly (4 days for 4 weeks in a month, so 4 row for one month). When I write the same expression in the group by filter, it is not working as well. I couldnt find how to provide this. Any help would be appreciated.

Solution

  • You have to create a group using the expression for Date, once the group exists you can aggregate your Data field.

    Go to Rows Groups and create a Parent Group. Use the expression you have to produce Date.

    enter image description here

    To calculate the sum of Data use:

    =Sum(Fields!Data.Value)

    Let me know if this helps.