SSRS show a default value of 0 for a date that returns no rows

Question

I was hoping you can help me with a problem with SSRS reporting's line graph I'm trying to create. So right now, I'm querying our database to get a card counts made for ever day. Each card is a separate row in the database. Right now, my chart is using the sum of cardCounts as the Y axis and for the X axis it is using the date as the category groups. Now the problem is that for a couple days, there's no data at all because we didn't create cards on that day. So rather than the line graph include that day as a 0 value, it's skipping that day altogether (in the pic below, the day of 7/16/2016 is missing completely). I tried doing it at the data level, but when I do a "isnull" it still doesn't work. Screenshot of issue

Here is the query I'm using:

SELECT dbo.ProductionSet.PSId,Convert(date,MIN(PSCreateTime)) AS CreateTime,isnull(MAX(dbo.ProductionSet.PSCardsInSet),0) AS CardCount,MAX(PrintJobMaster.PJMBatchid) AS BatchId,
isnull(DATEDIFF(SECOND,MIN(PaperPrintStart.PSTimeOf),MIN(PaperPrintDone.PSTimeOf)),0) AS PaperPrintSecs,
          isnull(nullif(DATEDIFF(Minute,MIN(ProductionSet.PSCreateTime),MIN(PaperCopyDone.PSTimeOf)),0),0) AS SecsToPaperOnPress
    FROM dbo.ProductionSet WITH (NOLOCK)
          LEFT OUTER JOIN PSStats PaperPrintStart WITH (NOLOCK) ON ProductionSet.PSId = PaperPrintStart.PSID AND PaperPrintStart.PSStatTo='1-1' AND PaperPrintStart.PJMID IS NOT NULL
          LEFT OUTER JOIN PSStats PaperPrintDone WITH (NOLOCK) ON ProductionSet.PSId = PaperPrintDone.PSID AND PaperPrintDone.PSStatTo='3-3' AND PaperPrintDone.PJMID IS NOT NULL
          LEFT OUTER JOIN PSStats PaperCopyStart WITH (NOLOCK) ON ProductionSet.PSId = PaperCopyStart.PSID AND PaperCopyStart.PSStatFrom='PA-D'
          LEFT OUTER JOIN PSStats PaperCopyDone WITH (NOLOCK) ON ProductionSet.PSId = PaperCopyDone.PSID AND PaperCopyDone.PSStatTo='PAD-D'
          LEFT OUTER JOIN PSStats EncodingStart WITH (NOLOCK) ON ProductionSet.PSId = EncodingStart.PSID AND EncodingStart.PSStatTo='R'
          LEFT OUTER JOIN PSStats EncodingDone WITH (NOLOCK) ON ProductionSet.PSId = EncodingDone.PSID AND EncodingDone.PSStatTo='M'
          LEFT OUTER JOIN dbo.PrintJobMaster WITH (NOLOCK) ON dbo.PrintJobMaster.PJMPSID=productionset.PSId 
WHERE ProductionSet.PSCreateTime >= '7/16/2016' and ProductionSet.PSCreateTime < '7/17/2016'  and PaperCopyDone.PSTimeOf is not null
       GROUP BY ProductionSet.PSID
       ORDER BY MIN(ProductionSet.PSId)

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-08-11T20:50:47

Try using this expression in the Values of your chart.

=Sum(
IIF(ISNOTHING(Fields!CardCount.Value),0,Fields!CardCount.Value)
)

It will produce 0 for the dates in x-axis with null y-axis values.

Note that you have two Values series so you have to use the same expression for both fields.

Let me know if this helps.

SSRS show a default value of 0 for a date that returns no rows

1 Answers