Post reply

Graph not displaying the values for a year when there is no rows retrieve from the query output

  • May 14, 2014 at 4:01 am

    #293296

    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

    #1713368

    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

    #1714044

    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