1

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)'

BoDiggy
  • 23
  • 3
  • 2
    Are you returning null values for February and May or no records at all? Probably, the easiest approach would be to do this work in the dataset but without seeing what your dataset query and output look like it's hard to tell. Can you post the dataset query and sample output. – Alan Schofield Apr 27 '17 at 10:32
  • The dataset has no results for Feb or May. – BoDiggy Apr 27 '17 at 21:59
  • FY Year Month ProductType ChartTime 16/17 2017 1 Red Blood Cells 2/01/2017 10:25 – BoDiggy Apr 27 '17 at 22:01
  • Can you edit your question to show the SQL script in your dataset or the stored proc if it's calling a Proc. – Alan Schofield Apr 27 '17 at 22:52

1 Answers1

1

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.

Lucky
  • 4,443
  • 2
  • 8
  • 18