Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Cody
Starting Member
24 Posts |
 Posted - 2009-01-29 : 05:00:15
|
| I am doing a reporting services project. The input dataset looks like:year month total period_id2008 7 $1.00 12008 8 $1.00 22008 9 $1.00 32008 10 $1.00 42008 11 $1.00 52008 12 $1.00 62008 13 $1.00 7As you can see, in Australia financial years often start in July and so are called Period 1, August is Period 2, etc. I created a chart:Values are "=sum(Fields!total.value)"Category Groups is "=Fields!period_id.value"Series Groups is ="Fields!year.value"This results in the chart I want, where I can see the figure totals per period, and compare across years, and it starts at period 1 (July) and goes to period 12 (December).The problem is that the colours are wrong. Because the colours for the bars are based on the "year" group, and so are being coloured as period's 7-6, instead of being set for periods 1-12.For example, one colour will be used for Jan 08-Dec 08, another for Jan 09-Dec 09. This looks wrong because the chart is using fiscal years where one colour should be Jul 08 - Jun 09, another for Jul 07-Jun 08.I'm totally at a loss about how I could possibly group to fix the line colours. How could I "translate" a year + period_id combination into a proper group value?Any ideas? |
|
|
Cody
Starting Member
24 Posts |
Posted - 2009-01-29 : 05:53:53
|
| After an hour afk I figured it out. I added another column to the data, a year_id_fiscal, which I calculate based on whether the month value is greater than the period id, in which case I keep the year as is, otherwise decrease it by 1.Then I change all the chart calculations to use this new year_id_fiscal and it all sorts correctly.Phew. |
 |
|
|
|
|
|
|
|
Grouping by fiscal year