[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Report Builder 3.0 parameter value to select ALL

Posted on 2012-08-28
Medium Priority
Last Modified: 2013-02-12

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:

Question by:delmarvamonkey
  • 2
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 664 total points
ID: 38344282
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.
LVL 37

Assisted Solution

ValentinoV earned 1336 total points
ID: 38345042
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

Author Comment

ID: 38345298
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?
LVL 37

Accepted Solution

ValentinoV earned 1336 total points
ID: 38345308
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question