Line chart - show nulls (no data) as 0 1

[dianemarie]

Hello, I've seen other posts about showing 0 as null, but I want to (I think) show no data as 0, so as to get a data point on the chart equal to 0. I have a simple line chart, by month (X - category), year (series) and order quantity (Y - data field). If there is no order quantity for a given month (ex: April 2008), the line chart goes from March to May in one unbroken line for that year. I would like it to (and by "I" I mean the user, I could live with it...) go from March to 0 for April & then from 0 to May. The month field is calculated as is the year:

=month(Fields!OrdDate.Value)

I have tried doing a calculated field and pulling it onto the data field but it doesn't work:

=iif(IsNothing(Fields!ordrqty.Value),0,
(Fields!ordrqty.Value)) I've tried several versions of this, including doing the IsNothing part on the Month calculation.

Is it possible to do this? Thank you for any help.

 

[RiverGuy]

I would do this in the source query. Use an OUTER JOIN to join a list of dates (or months) against your transactional data. Quite often, you'll have a Date table in your database -- this is especially true for data warehouses. Here's a pseudo example:

SELECT a.*,
ISNULL(b.OrderQty, 0) AS OrderQty

FROM

(SELECT TheMonth
 FROM DateTable
 WHERE TheMonth BETWEEN @StartDate AND @EndDate) a

LEFT OUTER JOIN TransactionTable b
  ON a.DateColumn = b.DateColumn

If you don't have a Date table, I suggest you create one--it will come in handy for many reasons in the future. If you can't create one, you can always create a temporary table in code (assuming SQL Server as the database). For example:

DECLARE @DateTable TABLE (DateKey DATETIME, TheMonth DATETIME)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT @StartDate = '20090101'
SELECT @EndDate = '20091231'

WHILE @StartDate <= @EndDate 
BEGIN
  INSERT INTO @DateTable SELECT @StartDate, DATEADD(day, -1 * (DATEPART(day, @StartDate)-1), @StartDate)
  SELECT @StartDate = DATEADD(day, 1, @StartDate)
END

SELECT * FROM @DateTable

 

[dianemarie]

Sounds promising RG. It's going to take me a bit to work it out. I'll let you know how it goes. Thank you.

 

[dianemarie]

Hi RG, I'm just now starting to try and get this to work. I have to create a temp table in my SSRS query. Temp tables are new to me. (I get the concept, but have not worked with them other than some code I copied from Tek-Tips for another report - I think it might have been yours.) I'm doing a simple line graph on Order Quantity per year. There will be three parameters. Owner, and Start and End Dates, which I haven't incorporated yet - just trying to get the temp table working. This is what I have so far for the query. There are a few extra fields I'm pulling from the ordhist table for testing purposes. I'm getting an error "DateKey not a valid column". I'm a little lost. Any help on correcting the query would be greatly appreciated. Thanks again.

DECLARE @DateTable TABLE (DateKey DATETIME, TheMonth DATETIME)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT @StartDate = '20090101'
SELECT @EndDate = '20091231'

WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO @DateTable SELECT @StartDate, DATEADD(day, -1 * (DATEPART(day, @StartDate)-1), @StartDate)
SELECT @StartDate = DATEADD(day, 1, @StartDate)
END

select oh.enduser,oh.endusename,oh.ordrqty, oh.shipqty,oh.createdate, oh.ordrdate, oh.maildate,
isnull(oh.ordrqty, 0) as OrdQty0
from
(select TheMonth
from @DateTable
where TheMonth between @StartDate and @EndDate) a
left outer join nm_ordhistall oh
on oh.createdate = a.datekey

 

[RiverGuy]

You forgot to add the DateKey column in the select list of your derived table a.

(select [b][COLOR=#ff0000]DateKey,[/color][/b] TheMonth
from @DateTable
where TheMonth between @StartDate and @EndDate) a

By the way, you're using what are referred to as "table variables." "Temp tables" are usually considered to be those table objects your declare with ## or # (not @).

 

[dianemarie]

Hi RG, thank you for the info, re: temp vs. variable tables. I did some research on them. I think I'll need them again. OK, so back to my original issue (showing null data as a zero on a line chart). I now have a table variable that returns date & corresponding month. I joined the table to my transactional table, as you showed above. But when I run my query, the data looks pretty much the same to me. I'm not sure how to use the results of the table variable to get to displaying no data as zero. I saw another posting somewhere about using a union join somewhere. (By the way, it looks like maybe SSRS 2008 allows for displaying empty data points, if I understood it correctly.) I'm on 2005. Thanks for any help.

 

[RiverGuy]

You can use the ISNULL function to return a zero instead of a NULL.

For illustration purposes, let's say you have a table variable with 5 dates -- Jan. 1, 2009 through Jan. 5, 2009. And let's say you have transactions for Jan. 1 through Jan. 4 only. See the following queries:

SELECT
a.DateColumn,
b.TransactionAmount
FROM @TableVariable a
LEFT OUTER JOIN Transactions b
ON a.DateColumn = b.DateColumn

What this does is returns a NULL for you when you don't have a transaction to match up to the date table based on the transaction date. The result set would look like this:

1/1/2009  100
1/2/2009  150
1/3/2009  200
1/4/2009  100
1/5/2009  NULL

Don't confuse that resultset with your original problem. I think in your original query you would simply not be returning Jan. 5 at all -- not even a null value because that date would not exist in your resultset. This is a little better because at least all needed dates are returned.

Now to make it perfect, you use the ISNULL function in your query

SELECT
a.DateColumn,
ISNULL(b.TransactionAmount, 0) AS TransactionAmount
FROM @TableVariable a
LEFT OUTER JOIN Transactions b
ON a.DateColumn = b.DateColumn

your resultset will look like the following:

1/1/2009  100
1/2/2009  150
1/3/2009  200
1/4/2009  100
1/5/2009    0

So in the end, you will get a 0 when you have no transactions for a specific date. This makes it a lot easier to work with in SSRS.

 

[dianemarie]

Hi RG, I'm getting there (I think). I got this to work exactly as you said it would, until I add criteria from the main transactional table for a specific client (enduser). This doesn't make sense to me because it's a left outer. The most recent version of the query is below (with specific enduser commented out - only way I can get it to pull in the nulls by date right now). Do you see anything obvious as to what I'm doing wrong? Thank you for your ongoing help.

DECLARE @DateTable TABLE (DateKey DATETIME, TheMonth DATETIME)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT @StartDate = '20060101'
SELECT @EndDate = '20071231'

WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO @DateTable SELECT @StartDate, DATEADD(day, -1 * (DATEPART(day, @StartDate)-1), @StartDate)
SELECT @StartDate = DATEADD(day, 1, @StartDate)
END

select
d.datekey,
oh.orders,oh.ordrqty, oh.createdate, oh.maildate,oh.enduser,oh.endusename,
isnull(oh.ordrqty, 0) as OrdQtyNulls
from
@DateTable d
left outer join nm_ordhistall oh
on d.datekey = oh.createdate
where d.datekey between '1/1/06' and '12/31/07'
--and oh.enduser = '38450'

order by d.datekey

 

[RiverGuy]

When you add the oh.enduser criteria, you are effectively turning the OUTER JOIN into an INNER JOIN. One way to code it would be the following:

select
d.datekey,
oh.orders,oh.ordrqty, oh.createdate, oh.maildate,oh.enduser,oh.endusename,
isnull(oh.ordrqty, 0) as OrdQtyNulls
from
@DateTable d
left outer join 
  [b](SELECT * FROM nm_ordhistall WHERE enduser = '38450')[/b] oh
on d.datekey = oh.createdate
where d.datekey between '1/1/06' and '12/31/07'

In the query above, the enduser criteria applies only to the nm_ordhistall table. In your previous query, you were applying the criteria to the entire resultset.

 

[dianemarie]

OK, so I change it, all excited, feeling like victory is in my grasp, and I return nothing. Deflated!! Then I realize I keyed the enduser number wrong. Sweet success!! My chart now puts a datapoint on 0 when there is no data for a given month. Thanks so so much.

One more question? <sheepish grin> Is there a nice easy, Excel-like way to change the chart to show percentage of total for each month's data point as opposed to quantity?

 

[RiverGuy]

One more question? <sheepish grin> Is there a nice easy, Excel-like way to change the chart to show percentage of total for each month's data point as opposed to quantity?

I'm not sure off the top of my head, although there could very well be as I'm not versed in all of the functionality in SSRS. This is especially true for SSRS 2008 as many improvements and added functionality have arrived. I actually couldn't even find the functionality in Excel either.

 

[dianemarie]

Hi RG, yes I found that out when I went into Excel. Pie charts allow you to select to show percentage rather than quantities but line charts do not. It's still easy enough in Excel however, as I would simply calculate the percentages in the spreadsheet and then create the chart from that. Not sure how to translate that to SSRS. But thanks for all your help on this report. I've deployed it for now and will try working on the percentage more later. I'm moving on to a Top 20 I'm almost done with, except for when there are more than or less than 20 records (grouped) returned. (Common issue apparently and there are lots of posts but I'm just not getting it.) I'll probably be posting about it shortly.