Post reply

Multivalue parameter - default select All

  • October 22, 2010 at 5:59 am

    #388075

    Hi,

    I've got strange problem with SQL 2005 report. Tried to find the answer in forum history, but didn't succeed, sorry if I'll duplicate issue.

    Well, i got multivalued parameter (Order Type) in the report and i need it to has selected all items as default. Available values and Default values got very same dataset. It's list of all Order Types codes used in any order. (there is no reference table to that, just two char long string column).

    Here's the funny part - it works when dataset got two columns (code and some random id), but it does not in case dataset got only one column (code). All values are available as expected, but none is selected. But i can't use any ID, because there is no table containing such values and i can't change DB structure just because of report.

    Hope it's clear.

    Any idea how to solve it? Thx in advance

  • October 22, 2010 at 7:47 am

    #1240920

    This isnt really a direct answer to your question but more of a suggested workaround -

    Typically when I've come across this issue, I'll move some of that work off to the underlying query.

    I'll first mark the parameter as being nullable, and make it null by default. Then in the query itself I'll change the where clause to read something like

    AND ( value IN (@value) OR @value IS NULL)

    I'm not sure about the 1-column vs 2-column behavior you have been experiencing, but I'd be able to provide better assistance if you can elaborate a little more on the query and the parameter in question, maybe posting some example code.

  • October 22, 2010 at 8:56 am

    #1240982

    Hi, thanks for your response, but since multivalued parameter can't be null-able, this workaround probably won't solve my issue.

    I'll try to be more specific.

    1. Create simple report with one dataset SAP

    2. Specify the query, for example

    select SAPDespatchCode from Orders

    3. Create multivalued parameter, set

    - Available values: SAP

    Value Field: SAPDespatchCode

    Label Field: SAPDespatchCode

    - Default values: SAP

    Value Field: SAPDespatchCode

    4. Run report - default values don't work

  • October 22, 2010 at 8:57 am

    #1240983

    Yeah, I've just solved the issue 😀 You advice about NULL pointed me to the solution. It's works perfect if there is no null value is query result. How simple 🙂 So, thank you very much 😎

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply