September 12, 2011 at 7:52 am
Hi my ssrs report isn't returning any data when I select a null value or --all languages-- for primary language skill, where as it works for --all users-- . I have 4 report parameters : date start, date end, primary language skill and user ID. I have used null and union for an optional all languages or all users etc. the dates are just entered by the user in ssrs. If I try executing the code in SSMS and leave those params null it works fine??? any ideas? It must be something to do with the language code block...
select null as PrimaryLanguageSkill
, N' --All Languages-- ' as Language
union all
select distinct PrimaryLanguageSkill, PrimaryLanguageSkill
from dbo.[User]
where PrimaryLanguageSkill IS NOT NULL
order by PrimaryLanguageSkill
select null as UserID
, N' --All Users-- ' as UserName
union all
select UserID
, Username
from dbo.[User] US INNER JOIN dbo.UserGroup UG ON US.UserGroupID = UG.UserGroupID
where US.Active = '1' AND UG.UserGroupID NOT IN ('0','20','21')
order by Username
maybe something to do with my where clause? obviously i want the user to be able to select all languages or all users, or both...
AND US.UserID = CASE WHEN @user-id IS NULL THEN US.UserID
ELSE @user-id
END
AND US.PrimaryLanguageSkill = CASE WHEN @PrimaryLanguageSkill IS NULL THEN US.PrimaryLanguageSkill
ELSE @PrimaryLanguageSkill
END
September 12, 2011 at 8:18 am
Run the report with profiler on.
See if the query sent via SSRS really sends what you think it sends (you may get a big surprise there).
September 12, 2011 at 3:37 pm
Make sure the params in SSRS are set to allow null and blank values, make sure your data types match. It looks like variables were declared as nVarchar - make sure the data type for the value you are comparing has the same type and size.
And I'd do the where something like this:
AND US.UserID = isNULL(@UserID, US.UserID)
AND US.PrimaryLanguageSkill = isNULL(@PrimaryLanguageSkill,US.PrimaryLanguageSkill)
OR
AND ((@UserID=NULL) OR (US.UserID = @user-id))
AND ((@PrimaryLanguageSkill IS NULL) OR (US.PrimaryLanguageSkill = @PrimaryLanguageSkill))
The second method will give you more control with multiple parameters.
November 17, 2011 at 1:42 pm
As for the where clause, I agree with Burninator.
Remember, if using a parameter (say Area), and you want users to select 5 out of 10 areas (multivalue parameter), then you can't use multivalue parameter and allow for <null> as well, either the one or the other.
Create Area dataset -
select
Area as LABEL,
AreaId as VALUE
from table
union
select
' ALL' as LABEL,
NULL as VALUE.
---
Select Area Parameter (in report properties) - select Null.
for values (not default values) select Area dataset,
select VALUE under Value, select LABEL under Label.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply