Help with query field parameters

Hi everyone,

within one of my reports I would like to take an input parameter, feed this into a dataset which is then used to populate the dataset of the query used in the main body of the report. It won't allow me todo this, could you guys offer any advice on how this may be possible?

E.g

Sub Query:

@.personIds = SELECT personid FROM people WHERE name = @.name;

Main Report Query

SELECT * FROM orders WHERE personId IN (@.personIds)

I cannot change the "main report query" as this is actually a stored procedure from an external application vendor. Please help.

Kind regards

Taz

Is the @.Name the parameter in your report?

If so, then set up the population of that parameter up with the SQL:

Select PersonID, Name from People

with the value = PersonID and the label = Name

and then in your main query, the personid is returned as the parameter value.

Hope that helps.

BobP

|||

Hi Bobp, thanks for the response.

The problem I have is that the SQL (SELECT * FROM people) will return 100s of rows. I dont want to populate the drop down with all of these, instead I want the drop down to have generic options (i.e. "David", "James") which when selected perform the query to get ther relvant IDs and then populate the query that the main report is based on.

Does that clarify at all?

Thanks again for your suggestion, do you have any further ideas?

Kind regards
Taz

|||

Yes, actually... one more idea...

Create parameter named @.Name. This is a string, user type in.

Then create another parameter named @.personIDs. This should be a string, with hidden and multi value selected.

Create a dataset, using the following SQL:

Select personid from people where name like '%' + @.name + '%'

Use this new data set as the Default Values Query for the @.PersonIDs parameter.

Then you can pass personid to your main query like this:

SELECT * FROM orders WHERE personId IN (@.personIds)

Make sure that in the parameter list, the @.Name parameter is first in the list.

Let me know if that works for you. I have tested, and am using that approach in several reports where the select list is too long.

Another way to do this would be to make the @.PersonIDs NOT hidden, and also use the new dataset to populate the available values of it, and allow the user to select an individual. This way, if the user types in DAVID, a second parameters asks the user to select from a list of '%David%'

BobP

|||

Bobp,

fantastic, this worked perfectly! many thanks for your help here.

One further question in regards to thw SQL command IN. i.e.

SELECT * FROM people WHERE peopleid IN(1,2,3,4);

is it possible to easily negate the IN within reporting services filters?

I have something like

=Fields!Name.value IN =Parameters!Names.value

How can I negate this?

Any help appreciated, however what you have done so far is fantastic enough! :)

Kind regards
Taz

|||

No problem at all...

I am not sure what you are trying to do with the filter... Could you give some more detail?

by default, the names that come back should be like the names parameter.

Thanks

BobP

|||

BobP - BIM wrote:

No problem at all...

I am not sure what you are trying to do with the filter... Could you give some more detail?

by default, the names that come back should be like the names parameter.

Thanks

BobP

Well, I am finding that I am overloading the input parameter for my stored procedure. It has a limit of 4000 characters, and my dynamic SQL is along the region of 5200 characters.

I can generate the SQL for the stored proc in 2 ways, either get the clients who have bought something (small list) or get the clients who haven't bought something (very long list). The former works fine (small list) however when i try to send in the big list it exceeds the limit of the stored proc and thus falls over.

Thus, I thought maybe I could instead return everything and then create a filter on my dataset where I do something like

Expression:
=Fields!ClientName.Value

Operator:
IN

Value:

=Parameters!ResultsOfTheSQLQueryWeCreatedBefore.Value

This works, i.e. show all the rows where the ClientName appears in the Parameter list. I was wonder if there was a simple way of making it show all the rows where the ClientName does NOT appear in the Parameter list without loading the huge list instad (as this would be slow).

Man this is a difficult one to explain. I hope I was succesful. Thanks for your time Bob!

Taz