Sol Web Solutions Blog

Sol Web Solutions Blog is aimed to help, inform, educate and enable our site visitors.

Series Chart Line Start @ 0

Posted by Sol Web Solutions Blogger
Sol Web Solutions Blogger
Sol Web Solutions Blogger
User is currently offline
on Monday, 28 February 2011
in General Web 0 Comments

SQL Server Reporting ServicesGetting 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.

Hits: 555
0 votes
Trackback URL for this blog entry

Comments

No comments made yet. Be the first to submit a comment

Leave your comment

Guest Friday, 18 May 2012

Facebook Invite

Please log in through Facebook to invite your friends to this site!

Facebook LiveStream

Newest Customer

Buffalo Tours

Tour the community and peek at Los Alamos National Laboratory with a native Los Alamos guide. Our comfortable van tours are rich in history, archaeology, geology, and beauty. New Mexico is our home.
Buffalo Tours in Los Alamos :: Tour the community and peek at Los Alamos National Laboratory with a native Los Alamos guide

Constant Contact

Facebook Login

Login With Facebook

Like Sol Web

HostGator Hosting

Seussical The Musical

Los Alamos Little Theatre Seussical