Sol Web Solutions Blog
Sol Web Solutions Blog is aimed to help, inform, educate and enable our site visitors.
Series Chart Line Start @ 0
Getting SQL Server 2008 Reporting Services series chart line to start @ 0 (trying to mimic another chart in another application that does the same thing) was a bit of a challenge. The line chart series is being used for accounting and starts at the January amount, say $10,000 for the first month data value.
Looking at the first three months:
January @ $10,000
February @ $15,000
March @ $12,000
...a sample query would pull back 12 rows (1 row for each month, Jan - December), and the columns would show the dollar amounts for separate buckets of money allocations. The category fields (on the horizontal) are the months, and the data fields (what builds the vertical axis) are the costs. Initially, the chart line began @ $10,000 and the request was for it to start @ 0 then go to $10,000 for the month span of January.
How did it get resolved? ...with the help of a "Periods" look-up table that I created.
The main table, which showed the 12 monthly periods, had one column which was a long date-time field which was used to get the monthname().
With this new look-up child table, an int column ("PKID") from 0-12 (...yes, 13 values/rows) was defined to get the ordering needed along with a second column of the month name. Int values 0 and 1 were applied this to the first month, so January now had two rows.
For the series lines, an expression was used that said if the int value of PKID in the look-up table was 0, then the value for the line was 0...hence, the lines to start at x,y of 0.
From the customers point of view, the January series line looked like it started from 01/31 in the beginning when the report was first developed WITHOUT this PKID field...and they wanted it to really start @ 01/01. For the category (horizontal) axis, the label was turned off if PKID = 0.





Leave your comment