• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2977
  • Last Modified:

Report Builder 3.0 parameter value to select ALL


I'm working in SQL Report Builder 3.0, and I'm attempting to create an expression that will essentially say to return ALL values.

When the report is run there will be multiple values for the parameter, which will return the rows for based on the values selected.

I wanted to create a value called "ALL" that will return all values when selected.

Attached is a screenshot is what I'm looking at:

  • 2
3 Solutions
Nico BontenbalCommented:
When you use 'Specify values' you need to specify all the values for the parameter. So not field names like 'Field1' but values like 'Product 1', 'Product 2', etc.

Does this answer your question:

If not can you give some more details about the parameter and the report.
ValentinoVBI ConsultantCommented:
As shown in the solution through the link provided by nicobo, you should give 'ALL' a value which is not in your database, such as -1 when you know that all possible values are greater than zero.

Then you can take that into account in your query, again as shown through that link:

SELECT some_fields
FROM some_table
WHERE filtered_field = your_ALL_value or filtered_field = @your_parameter
delmarvamonkeyAuthor Commented:
Okay, thanks.

The CAML query is already selecting the field from the appropriate table (sharepoint list).

With the parameter created, I figured I could just enter code in the expression to specific the values.

If I use one value, for example "Test" it returns all rows with the value for contract as "test."

But what if I say ="Test" & "Test2"?

You would think it would return all records with both values, correct?
ValentinoVBI ConsultantCommented:
No, that will not work.  If you have a string parameter, you need to enter each possible value on a separate line, or retrieve the values from the database (more interesting for maintenance).

You'd also need to ensure that your parameter is multi-valued.  Have a read through the following article, I believe it will provide you with some useful background info: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_2002-Reporting-On-Data-From-Stored-Procedures-part-2.html
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now