Post reply

Parameters will not Default

  • May 16, 2006 at 9:30 am

    #168296

    Greetings

    I am using SQL 2005 Reporting Services and I have a problem with parameters. If I leave 3 of the 4 values as non queried in the Available values section and mark them as a Default Values: of NULL, then the report works fine. However if I set the Available values to a query I have written and leave the Default values as NULL, when I view the report in either preview mode or on the reports server, it wants me to specify a value from the boxes and the tick box for NULL has disappeared. Allow NULL value is ticked inthe first section, Properties.

    Anybody got any suggestions?

  • May 16, 2006 at 3:39 pm

    #638294

    I *believe* when you choose to have the Available Values loaded from a query, then that parameter becomes required.

  • May 16, 2006 at 10:52 pm

    #638344

    OK, well if that is the case I suppose the only option is to make an entry in the query result a NULL so they can specify that as an option which would be the same effect I guess.

    I will give that a go and see how I get on.

    Thanks Author

     

  • May 17, 2006 at 3:24 pm

    #638579

    I wouldn't use NULL as a valid query parameter, it can get confusing. I'm assuming that when the user selects NULL they mean ALL?

    Why not set up your WHERE clause to ignore the a param value of ALL. eg

    WHERE (Myfield = @P1 OR 'ALL' = @P1)

    So if @P1 = 'ALL' then in the above statement 'ALL' = "ALL' (1 = 1) which is always true so the filter on Myfield (which would return zero records) is superceeded.

    Repeat for all of your params and set the default for them to ALL. When the report runs it will run for All records.

    Hope that helps.

    Kindest Regards,

    Martin

  • May 17, 2006 at 10:18 pm

    #638619

    I think you have similar problem with mine.

    I guess you can try Martin's suggestion or try below

    This is what I do

    Edit your dataset

    SELECT     NULL AS Code, 'None' AS Description

    UNION

    SELECT     Code, Description

    FROM         tablename

    in your report parameter - tick on 'allow null value' and default value to null

    cheers,

    Susan

  • May 17, 2006 at 11:20 pm

    #638630

    Hi

    Both valid suggestions and I thank you for making them to me.

    Regards

     

Viewing 6 posts - 1 through 5 (of 5 total)

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