whitz11 whitz11 - 2 months ago 10
SQL Question

When 'Select All' Parameter ticked all data is not showing

I've edited this post as I don't think I was explaining myself very clearly

This is a glimpse of what my report look likes, a series of charts, where you can select a country and a date range of either 12 months rolling or 36 months rolling.

The report is working ok in regards to selecting one country and seeing the different data within the 12 or 36 months date range.

enter image description here

The problem comes when I 'Select All' countries. What I want is the totals of all the countries to be represented on the graphs. As you see below, with all countries selected the data on the graph has not changed from the image above.

enter image description here

This is my stored procedure

ALTER PROCEDURE [name]



@Country varchar(max),
@DateRange varchar(max)


AS BEGIN


CREATE TABLE #Tmptable1
( country varchar (max)
,[yyyy-mm] varchar (max)
,[12Months] varchar (1)
,[36Months] varchar (1)
,[Population] int
,[Employed] int

)

INSERT INTO #Tmptable1

select * from


(
select


country
,[yyyy-mm]
,[12Months]
,[36Months]
,[Population]
,[Employed]


from

(




select



case [countryID] when '800002' then 'NSDG'
when '800003' then 'ESDG'
when '800004' then 'WSDG'
else 'N/A'
end as country


,Convert(char(7),[DateTimeOfCall] , 121) "yyyy-mm"

,case when datetimeofcall between Dateadd(Month, Datediff(Month, 0, DATEADD(m, -12, current_timestamp)), 0) and Dateadd(Month, Datediff(Month, 0, DATEADD(m, 0, current_timestamp)), 0)then 'y' else 'n' end as [12Months]

,case when datetimeofcall between Dateadd(Month, Datediff(Month, 0, DATEADD(m, -36, current_timestamp)), 0) and Dateadd(Month, Datediff(Month, 0, DATEADD(m, 0, current_timestamp)), 0)then 'y' else 'n' end as [36Months]

,sum(case when [CategoryID] = '180003'
and [CauseID] IN ('700002', '700003')
AND [TypeID] = '100002'
AND [PopTypeID] BETWEEN '170002' AND '170019' then 1 else 0 end) Population


,sum(case when [EmpID] = '210002' then 1 else 0 end) as Employed


FROM [dbo].[country]

WHERE status = '1'

group by [countryID], Convert(char(7),[DateTimeOfCall] , 121),case when datetimeofcall between Dateadd(Month, Datediff(Month, 0, DATEADD(m, -12, current_timestamp)), 0) and Dateadd(Month, Datediff(Month, 0, DATEADD(m, 0, current_timestamp)), 0)then 'y' else 'n' end ,case when datetimeofcall between Dateadd(Month, Datediff(Month, 0, DATEADD(m, -36, current_timestamp)), 0) and Dateadd(Month, Datediff(Month, 0, DATEADD(m, 0, current_timestamp)), 0)then 'y' else 'n' end )a


)c

;

select

country
,[yyyy-mm]
,[12Months]
,[36Months]
,[Population]
,[Employed]



from #Tmptable1


where country = @country and ((@DateRange = 12 and [12Months] = 'Y') or (@DateRange = 36 and [36Months] = 'Y'))





drop table #Tmptable1

END


And this is what the output is

country yyyy-mm Population Employed 12months 36months

uk 2016-06 56 43 y y
france 2016-06 40 22 y y
Germany 2016-06 73 32 y y
uk 2015-06 45 10 n y
france 2015-06 30 11 n y
Germany 2015-06 76 56 n y
AND SO ON......


All help appreciated, thank you.

Answer

Based on what you've described I think something like this will work for you

You are correct that you need two parameters: one for the country, the other is the period. For this second parameter specify two entries in the report designer. Give them the labels '12 months' and '36 months' and values or 12 and 36 respectively. Now change your dataset query as shown in the example below (obviously my table/column names won't be the same as yours)

select country, [yyyy-mm], Total
from @datatable
where country = @country
and ((@period = 12 and [12months] = 'Y') or (@period = 36 and [36months] = 'Y'));

The last line is where the magic happens. By testing the value of the @period parameter in the where clause we can make parts of the clause conditional.