May 14, 2014 at 4:01 am
Problem Statement:
Graph not displaying the values for a year when there is no rows retrieve from the query output.
Description:
I am using BIDS 2008 for my development, I have to display the values in a graph with a range of 5 years e.g. from 2009 to 2013 for which I am using the year in the graph category, when the graph is having values for all the years from 2009 to 2013 the graph/chart is coming fine, the problem occurs when there is no rows for a particular year in the range from the query output.
Here is a sample output of the query
Sample1
Value Year
25 2010
58 2011
65 2012
Sample2
Value Year
16 2009
40 2011
70 2013
Now for samples the graph doesn't show the year which is not present in the sample of the output. for sample1 the graph shows the year and values for 2010, 2011 & 2012 and for sample2 the graph shows values only for 2009, 2011 & 2013.
I have to display the year in the category of the graph and its corresponding values as 0{zero) for the year 2009 & 2013 in sample1 and 2010 & 2012 in sample2.
Any help for this is appreciated.
Thanks in Advance !!
- Ansuman
May 14, 2014 at 4:29 am
The easiest solution would be to use a date table that contains the different years.
That way you won't have any gaps. Join the date table against the table with the data with a LEFT OUTER JOIN.
If the data is NULL, replace with 0.
You can create a date table on the fly like this:
DECLARE @MinYear INT = 2009;
DECLARE @MaxYear INT = 2013;
SELECT Years = N
FROM
(
SELECT TOP 3000 ROW_NUMBER() OVER(ORDER BY t1.number) as N
FROMmaster..spt_values t1
CROSS JOINmaster..spt_values t2
) tmp
WHERE N BETWEEN @MinYear AND @MaxYear;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 15, 2014 at 3:06 pm
Hi Koen,
Thanks for you suggestions, well my approach was different but i could deduce from your suggestion.
What I did is made a union of the two tables the 1st table had the values and year as its field and in the second table I had the Value as zero for all the years and when I did a union of both the tables I was able to get the desired result after doing some grouping.
Before
Value-Year
60-2010
70-2011
80-2012
After
Value-Year
0-2009
60-2010
70-2011
80-2012
0-2013
And I was able to show the 0 values in the graph for the year 2009 and 2013.
Thanks Again ! and have a nice day
Regards
Ansuman
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy