Adding All to SSRS report parameters giving out of memory exception

SSRS report is failing while selecting the All parameter

Now let me begin by giving a case statement whereby I have added 'ALL' to my SSRS report parameters dropdown. But whenever I select the ALL option I run into the out of memory exception because the report is executing in a never ending loop. Here are samples of the issue @ hand and how I went about fixing the same For example==> Let me say that I have a Country dropdown for the following SSRS report (using Adventureworks). The SSRS dataset Report Query is as follows:
SELECT adr.AddressLine1, adr.AddressLine2, adr.PostalCode, adr.City, sp.Name, cr.Name AS Country FROM Person.Address AS adr INNER JOIN Person.StateProvince AS sp ON adr.StateProvinceID = sp.StateProvinceID INNER JOIN Person.CountryRegion AS cr ON sp.CountryRegionCode = cr.CountryRegionCode and (cr.CountryRegionCode = @country)
As you notice the parameter I am using is

@country.



The @country parameter is populated initially by using the following query:
SELECT CountryRegionCode, Name AS Country FROM Person.CountryRegion order by Country Desc

The SSRS report works perfectly fine....... with all the parameters. Now let us add an ALL clause to the SQL statement for the parameter Country by modifying the above query as follows:



SELECT 'ALL' AS CountryRegionCode, 'ALL' AS Country UNION SELECT CountryRegionCode, Name AS Country FROM Person.CountryRegion order by Country Desc

Now we have to modify the SSRS dataset query to take this additional parameter into account and we do this by:

SELECT adr.AddressLine1, adr.AddressLine2, adr.PostalCode, adr.City, sp.Name, cr.Name AS Country FROM Person.Address AS adr INNER JOIN Person.StateProvince AS sp ON adr.StateProvinceID = sp.StateProvinceID INNER JOIN Person.CountryRegion AS cr ON sp.CountryRegionCode = cr.CountryRegionCode and (cr.CountryRegionCode = @country) OR (@country='ALL')


This should work perfectly fine except in my case where the report constantly errored out with an out of memory exception when the parameter ALL was selected. Bewildered I tried a couple of times to run the report by changing the order of the where clauses but to no avail. Decided to Bing a solution to this problem or if it had occured to anyone earlier (knowing that would have been a slight relief) but no answer to this issue. Finally my last solace was that I modified the above dataset query in the following manner:

SELECT adr.AddressLine1, adr.AddressLine2, adr.PostalCode, adr.City, sp.Name, cr.Name AS Country FROM Person.Address AS adr INNER JOIN Person.StateProvince AS sp ON adr.StateProvinceID = sp.StateProvinceID INNER JOIN Person.CountryRegion AS cr ON sp.CountryRegionCode = cr.CountryRegionCode and (cr.CountryRegionCode = @country) OR (cr.CountryRegionCode = sp.CountryRegionCode and @country='ALL')

and yippie-kai-yay as McClane would say resolved the friggin issue. What was the huge difference? In fact the second modification put an additional performance hit by performing the same where clause condition again. Might have to ask another guru (barring myself of course) to help me figure this one out........

Comments

Unknown said…
Use dynamic sql dont put the param in the where clause directly. Something like..

Declare @sql as nvarchar(max)
set @sql = 'select stateid, statename from Person.State where StateId = @stateid '

if(@country = 'ALL')
begin
set @sql = @sql + 'and CountryId in (@country) '
end
exec @sql
Unknown said…
its @country <> 'ALL' ... you get the point
Ishwar said…
Suhas,
This is an SSRS report. I do not want to use dynamic SQL to fetch results since I already know the query that needs to be executed.

Let me say that I put an SP and created the same as a dynamic SQL--> Would it work?

Let me try..
Ishwar said…
Same error :(

Popular posts from this blog

Load Data into Azure DW using C# in an SSIS script task

Branding your SharePoint site in a super fast way - Emgage

Power BI To Embed Or Not To Embed