Report Builder 3.0 parameter value to select ALL

Posted on 2012-08-28
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
    LVL 22

    Assisted Solution

    by:Nico Bontenbal
    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

    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

    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

    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:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
    It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now