May 13, 2008 at 4:02 pm
I am passing in two parameters through a URL to my report. They limit the selections in one dataset that is used to populate a drop down list of report parameters (in this case 'Regions'). The url parameters are Region and Role. If the role is a manager, they can see a larger selection of regions in the dropdown list, if they are not a manager, they see only their region as an option in the dropdown. Please don't be confused by the fact that the dropdown is showing a selection of regions, while at the same time the URL is passing in a 'Region' parameter -- there is a reason for this but it doesn't relate to this issue.
The URL parameters are used in a report dataset (running a stored procedure) that expects the two parameters. The parameters are used nowhere else in the report. Under the report parameters definition tab, I have the url parameters at the top of the list (as I understand it, this forces the report to evaluate them first). When I run the stored procedure in SSMS, it functions as expected -- both when I pass in 'Manager' or 'SalesRep' as role. When I pass in 'Manager' in the url[/u], it functions as expected (that is, the drop down shows all the expected selections). When I pass in 'SalesRep' in the url[/u], the drop down list is empty -- which to me suggests that the sproc is not returning the filtered dataset.
Again, running the stored procedure in SSMS with both versions of parameters returns the results I expect. Only when they are sent in via the url do I get 'wrong' results.
When I preview the report in BIDS, manually typing in various values for the parameters that would otherwise be passed in in the URL, the report runs as I expect. Also, as I mentioned above, in SMSS the sproc runs fine.
Any thoughts anyone? many thanks. David
May 14, 2008 at 10:26 am
create a log table, and have the proc write the parameters it receives to the log. you're probably not getting what you expect.
also, if you only have 2 roles, use 'Manager' as a boolean comparison
if (role = 'Manager')
do this
else
do that
rather than a case
if (role = 'Manager')
do this
if (role = 'SalesRep')
do that
May 14, 2008 at 11:47 am
Thanks for the suggestions -- it hadn't occurred to me to use a parameter tracking table like that. It's a useful tool. As regards the logic suggestion, my sproc was using something like that already, but thank you anyway.
The problem was in an unexpected place. The end result of the sproc was to return the following result set:
IF @intmg>0
BEGIN
SELECT 'All' AS RegionID
,'All Regions' AS Region
UNION ALL
SELECT DISTINCT Regionshort
,RegionShort
FROM dimdistributors
END
ELSE
BEGIN
SELECT DISTINCT Regionshort
,RegionShort
FROM dimdistributors
WHERE RegionShort=@strRegion
END
But the query in the ELSE statement (the one that 'wasn't working') did not alias the columns, so the dataset in the report wasn't being populated. When I added aliases as below, everything got cleaned up.
IF @intmg>0
BEGIN
SELECT 'All' AS RegionID
,'All Regions' AS Region
UNION ALL
SELECT DISTINCT Regionshort
,RegionShort
FROM dimdistributors
END
ELSE
BEGIN
SELECT DISTINCT Regionshort AS RegionID
,RegionShort AS Region
FROM dimdistributors
WHERE RegionShort=@strRegion
END
Thanks again! David
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply