SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parameters in URL querystring don't make it to report


Parameters in URL querystring don't make it to report

Author
Message
DavidL
DavidL
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 764
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, it functions as expected (that is, the drop down shows all the expected selections). When I pass in 'SalesRep' in the url, 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



antonio.collins
antonio.collins
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 921
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
DavidL
DavidL
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 764
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search