Graphs question

[azzazzello]

I have dataset which calls a stored procedure, which returns, say DATE (a date),NAME (a string),SALES (an integer)

I want to graph this as a simple line graph such that DATE is on X-axis, NAME is a series, and SALES is the data (on Y-axis). So I have as many lines as there are NAMEs, showing me SALES over time. Ok, simple enough.
Now, there is a nuance. There are cases when I have no entry in the database for a given NAME, for a given DATE. Consider the following dataset

2007-08-01,JACK,10
2007-08-01,MARY,5
2007-08-02,JACK,20
2007-08-02,MARY,15
2007-08-03,JACK,30
2007-08-04,JACK,25
2007-08-05,JACK,50
2007-08-05,MARY,35
2007-08-06,JACK,60
2007-08-06,MARY,45

If I were to graph this, Jack would be fine, however for Mary, I would have a line connecting the values of dates 2007-08-02 and 2007-08-05, instead of going to 0 for the dates where data is missing. This is very, very misleading. I know one of the solutions is to populate the dataset with 0-entries for missing dates, but considering the actual SQL is dynamically generated, and can contain many other fields, this is a very undesirable pathway. What can I do on Report Server side, perhaps within the graph object itself, to deal with this?

 

[xlbo]

Can you use the ISNULL function and group by NAME ?

All it would require is wrapping the value field in an isnull(value,0) function in the SQL...





Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[azzazzello]

xlbo,

I...might be misunderstanding what you mean, but the problem is not that the value for a given DATE,NAME is NULL. It is that it does not exist. Given exact data in my original inquiry, I am not sure what you are suggesting, because when I do:

SELECT DATE,NAME,sum(ISNULL(SALES,0)) 
FROM TEST_SALES
GROUP BY DATE,NAME

I am getting the same number of rows...am I misunderstanding something?

 

[xlbo]

sorry - my bad - sounds like you need a cross join then

To re-iterate, AFAIK if you have blanks in the data set, that is how the line chart will plot. I don't know of any property in RS that plots for blank data...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question