Passing parameters with SSRS


I'm trying to figure out the mechanics of an existing report created some time ago.

The report contains two datasets.  One provides the user with a single batch ID to select as the parameter.  The other dataset has a query that hits the relevant database tables but the query doesn't contain ANY parameter evaluation in the WHERE clause.

How is it that SSRS is combining the batch ID parameter with other query?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Its possible that the parameter is being used a filter in the actual report itself. Check the filter tab on any tables/graphs you have in your report and see it its being used. 

Megan BrooksSQL Server ConsultantCommented:
Sometimes it is helpful to view the XML report definition, where you can see everything as a flat text file and search for strings. Just right-click the report name in Solution Explorer and select "View code."
(By the way, editing the XML is also a way to do bulk search/replace and other operations that the designer does not support, if you are very careful to preserve the syntax.)
John500Author Commented:
>>  Its possible that the parameter is being used a filter in the actual report itself

I checked all possible places a filter could be used such as:

1)  The two dataset properties
2)  Individual dataset fields

>>  Sometimes it is helpful to view the XML report definition,

Yes, I would think this would be the 'teller'.  However, this doesn't seem to show anything either.  I looked through the code and saw that it is setup like this:

Dataset1 fields
Dataset1 query
The one Dataset2 field  (batch ID)
Table format

If the parameter was being passed off as the result of the SSRS capabilities I would think it would show up in the WHERE clause of the code, right?

It's not there.......
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Megan BrooksSQL Server ConsultantCommented:
If the 2nd DS were used as a filter in another DS or other object then there would be no 'where'. Do you see any references in the XML to either the 2nd DS name or to its fields? Any hidden DSs?
John500Author Commented:
>>  Do you see any references in the XML to either the 2nd DS name or to its fields?

Yes.  As I say, the 2nd DS which produces the batch ID is declared just as the first one is.  The only other reference to it is in the <ReportParameters> section as such:

    <ReportParameter Name="batchid">
      <Prompt>Select Batch ID</Prompt>

>>  Any hidden DSs?
I did a search throughout the code on the word DataSet and there was only the two already mentioned.  All I see in this code is referrence to two different datasets but nothing that brings the two together - no link!

Megan BrooksSQL Server ConsultantCommented:
OK, remember it is OK to break things as long as you save a copy! If you haven't already, remove the 2nd dataset (the one that is not the table dataset) and see if it actually affects the output. If it does, more digging is in order. Otherwise, the result set is being determined some other way.
The "batch ID" dataset is not the dataset used by the report table and the report doesn't use nested tables?
Search for the name of the second dataset, and find out everywhere that it is referenced.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John500Author Commented:
>>   and see if it actually affects the output.

Yep.  It's like I said, this 2nd dataset is used to populate the report parameter drop-down.  The user selects the batch ID provided by this 2nd dataset and the report uses this value to generate the final results.  The final results are based upon the query of the first dataset.

However, the 1st dataset or main report table query, does not contain any clause that takes the parameter into consideration.  As I say, I find this odd because for reports I've created, I have to make sure I name the report parameter the same as what I'm using in the query so that the query runs without error.

How is it then this report is incorporating the parameter?  We've already considered that no filter is being used.....

John500Author Commented:
>>   and see if it actually affects the output.

I probably didn't make it clear in my last post with my 'Yep' -  the report  will not run without the batchID dataset......
Megan BrooksSQL Server ConsultantCommented:
Well if the report definition doesn't contain a reference to the batch ID parameter then it doesn't affect the output. So it seems like there must be some kind of reference somewhere. You can have as many parameters as you want that aren't referenced, and while they may affect report initialization through parameter constraints and parameter dataset interdependencies, they don't affect the final report output.
I know this is turning repetitious, but are you sure that the batch ID parameter name "batchid" (from your RDL sample above) doesn't appear outside the parameter definition?
Have you reviewed the query parameters (as opposed to report parameters) assigned to the main dataset? (I know, there is no "where" clause query parameter reference, but are there any query parameters defined for the dataset, whether or not they appear to be referenced in the T-SQL?)
And of course filtering can be performed in T-SQL in ON clauses and subqueries, as well as WHERE. I am just trying to think of anything at all.
John500Author Commented:
Well I got to the bottom of it.  The reason this report was working was because the main table query returned everything the report needed regardless of the parameter query.  The parameter query used the same table as the main query.  Thus, the parameter wasn't even needed.

You got me to think and that's what I needed.  How about checking this one out if you get the chance:

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.