Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Parameters in URL querystring don't make it to report Expand / Collapse
Author
Message
Posted Tuesday, May 13, 2008 4:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:40 AM
Points: 148, Visits: 606
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



Post #500040
Posted Wednesday, May 14, 2008 10:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:52 AM
Points: 438, Visits: 909
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
Post #500709
Posted Wednesday, May 14, 2008 11:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:40 AM
Points: 148, Visits: 606
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



Post #500787
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse