SSRS Chart to group by months based on fiscal year instead of calendar year

0
votes

I've created a SSRS chart to group by months. I'm able to get the months to be displayed on the chart based on fiscal year (starting in October and end in September). However, the counts & values are still based on calendar year. How do I get the SSRS bar chart to group based on fiscal year?

For example.. On the attach print screen, you can see the months is in fiscal year order but it's group by calendar year with the new color (green) on Jan, Feb, March, & April. If it's grouped by fiscal year, Oct, Nov & Dec should also be in green.

Anybody have any ideas how I can set my SSRS chart to group by fiscal year?

Please Help! Thanks!

Chart showing correct month order but group by calendar year

Chart showing correct month order but group by calendar year

Here's an example of my data. Example of Data

Chart setting with the expression I use for grouping

1
reporting-servicesreportdynamics-crmssrs-2012ssrs-tablix
You'll need to post a sample of data showing your structure and probably show your chart design with series and category properties visible. Just looking at a chart doens't help much I'm afraid. – Alan Schofield
I'm guessing this will be something that needs to be handled in your query. You'll likely want a column indicating which values are related to which fiscal years and group the chart based on that column. – Steve-o169

1 Answers

0
votes

I've actually found the answer. On my series Group, I grouped the receivedon.Value date by Year but if the receivedon.Value month is Oct, Nov or Dec, add 1 year to the receivedon.Value date.

=iif(Month(Fields!receivedon.Value)10,Year(Fields!receivedon.Value),Year(DateAdd(DateInterval.Year,1, Fields!receivedon.Value))))