CODEWITHSUNDEEP
ssrs-2012reporting-services-2012
BoDiggy
BoDiggy

Reputation: 23

SSRS Display 0 if count is Null

I have an report in SSRS that counts items used every month but some months are missing because no items were used that month.

=count(Fields!Drug.Value)

ie Jan=2, Mar=1, Apr=3, June=4

I would like the month grouping to appear and the count to display a '0' if there is no data for a particular month.

ie Jan=2, Feb=0, Mar=1, Apr=3, May=0, June=4

If have tried an IsNothing expression but it still doesn't display '0' for months with no data.

=IIF(IsNothing(count(Fields!Drug.Value)),0, count(Fields!Drug.Value))

'SELECT Distinct case when month(BagDoseStartDateTime) between 7 and 12 then cast(right(year(BagDoseStartDateTime),2) as varchar(10)) + '/' + cast(right(year(BagDoseStartDateTime) + 1,2) as varchar(10)) else cast(right(year(BagDoseStartDateTime) - 1,2) as varchar(10)) + '/' + cast(right(year(BagDoseStartDateTime),2) as varchar(10)) end as FinancialYear ,year(BagDoseStartDateTime) as YearAdministered ,month(BagDoseStartDateTime) as MonthAdministered ,D.[Drug] ,D.[BagDoseStartDateTime]

FROM CAB_Reporting.dbo.CAB_V_Doses_Bags D --Inner join CAB_Reporting.dbo.CAB_V_Dose_Actions DA on DA.PatientID = D.PatientID

Where ((year(BagDoseStartDateTime) = 2016 and month(bagDoseStartDateTime) >6) or (year(BagDoseStartDateTime) = 2017)) And ((D.Drug Like 'Red Blood Cells (1 Unit)' And D.Status Like 'Administered') Or (D.Drug Like 'Fresh Frozen Plasma (FFP 1 Unit)' And D.Status Like 'Administered') Or (D.Drug Like'Cryoprecipitate (Multiple Units)' And D.Status Like 'Administered') Or (D.Drug Like 'Platelets (1 Unit)' And D.Status Like 'Administered')) Order by year(BagDoseStartDateTime) ,month(BagDoseStartDateTime)'

Upvotes: 1

Views: 781

Answers (1)

Lucky
Lucky

Reputation: 4493

You will need to make sure you are returning a column or row in the DataSet for the value that would be NULL or 0. If there is no field in your DataSet for that month, SSRS won't know it exists. I'm not sure what your query looks like but you could probably implement COALESCE in there to set 0 where there is NULL values for your month.

As a less desirable result you can use LOOKUP in the value field for each month.

LOOKUP("MonthName", Fields!Month.Value, Fields!Drug.Value, "DataSetName")

This will look for months matching the first parameter and return the first found value of that Drug field. It seems you have multiple fields though so you'd have to use LOOKUPSET and then sum the contents of the array in that case.

Hope that helps.

Upvotes: 1

Related Questions