0

I have a report that outputs relatively simple counts of clients. It outputs everything from a system as data parameters are not required. They want to see everything.

The thing that is throwing me is, they want months grouped by the 24th of one month to the 23th of the next month.

For example, the month of October 2016 should count all data between the 24th October 2016 TO 23rd of November 2016, the previous Septeber should group everthing between 24/09/2016 to 23/10/2016 etc etc.

In short, is it possible to group month using 24th of that month to the 23rd of the next month?

Happy to provide more information, a bit new to advanced ssrs tasks like this. I did search for solutions like this but couldnt find anything suitable. Any help greatly appreciated.

ChipChipowsky
  • 3
  • 3

1 Answers1

1

This requirements can be implemented by enhancing your query. Say f.e., you have the following:

select
  t.Date,
  t.AggregatedField
from SomeSchema.SomeTable as t

You can do next:

select
  MonthNumber = 
    case 
      when DATEPART(DAY, t.Date) >= 24 then DATEPART(MONTH, t.Date)
      else DATEPART(MONTH, t.Date) - 1
    end,
  t.AggregatableField
from SomeSchema.SomeTable as t

The output you will get will be like (month_number, field_to_aggregate). You then need to group data by that month_number, you can do that directly in SQL, or using RDL report grouping (consider that first provides the best performance). To construct month name, you will probably need to extract year number to like this:

DATEPART(YEAR, t.Date)

and then construct date using month number anhd year number.

grafgenerator
  • 679
  • 7
  • 13
  • Thank you. I had considered writing a sql function to return the month but yours is a more elegant solution. I will be sure to tell Santa about this! thanks again. – ChipChipowsky Nov 09 '16 at 19:13