CODEWITHSUNDEEP
reporting-services
OBL
OBL

Reputation: 1357

SSRS display report with sum from different months

Here is what I am trying to achieve. I have a stored proc that is returning a data-set, needs no parameters. I need to display month and year in the header and need to show total amount for each month at the end of information related to that month. I have no idea how to achieve this.

Any guidance will be highly appreciated. This is against ssrs 2005.

Thanks!

Upvotes: 1

Views: 1547

Answers (1)

Tim
Tim

Reputation: 105

Assuming you've got a Datetime (lets call it "dts") field in the data returned from the stored procedure, and assuming you want a table with the data in it you could do the following.

  • Create your table and add all the columns you want to it
  • Insert a group into you table (right click on the far right of the row where you would to insert a new row)

  • You want to group by year and month so in the "Expression" field enter something like

    =DatePart("m", Fields!dts.Value).ToString & "/" & DatePart("yyyy", Fields!dts.Value).ToString

  • Use that expression to sort by if you need to as well ("Sorting" tab)

  • Add that same expression into the group header row that you've just created

  • In either the group header or footer you can then add an expression for the sum of whatever data you interested in:

    =count(Fields!sales.Value)

Hope that helps, not sure if my explanation was completely clear but it might point you in the right direction.

Cheers

EDIT: actually you could do that expression much better to group by a Datetime for the start of the month, rather than for a string indicating the start of the month as I just showed, that would be better for sorting by etc. Let me know if you need an expression that will do that, in SQL you do something like

DATEADD(dd, 0, DATEDIFF(dd, 0, dts))

I'm sure you could do similar in an SSRS expression.

Upvotes: 2

Related Questions