none
SSRS 2008R2 Parameter Help

    Question

  • I have a union query which uses a column which does not exist in the data, for example

    Select 'Account1' as account, ID From Database1.Table1

    union

    Select 'Account2' as account, ID from Database2.Table1

    union

    Select 'Account3' as account, ID from Database3.Table1

    I need to give an option for the person running the report to choose the parameter for account, either Select all, Account1, Account2, Account3

    I'm having trouble making these choices go to the dataset with the value of column that does not exist in the data.  How do I use Specify Values and have them match these values, or put this parameter into the query itself?

    Tuesday, August 20, 2013 10:46 PM

Answers

  • Hi dorindam,

    Above query work for single select paramater for multiselect parameter, please try something like this:

    select *
      from( 
        Select 'Account1' as account, ID From Database1.Table1
        union
        select 'Account2' as account, ID from Database2.Table1
        union
        Select 'Account3' as account, ID from Database3.Table1
        ) Temp where account in (@Param)


    Regards Harsh

    • Marked as answer by dorindam1 Thursday, August 22, 2013 1:51 PM
    Thursday, August 22, 2013 4:18 AM

All replies

  • I'm having trouble making these choices go to the dataset with the value of column that does not exist in the data. 

    Hello,

    I am sorry, but I don't understand what you mean, especially "not exists in the data"?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 21, 2013 6:01 AM
  • Hi,

    Build your query with if else, then you will able to filter the data. Try something like this:

    If @Param = 'Account1'
     Select 'Account1' as account, ID From Database1.Table1
    
    else if @Param = 'Account2'
     select 'Account2' as account, ID from Database2.Table1
    
    else if @Param = 'Account3'
      Select 'Account3' as account, ID from Database3.Table1
    else
        Select 'Account1' as account, ID From Database1.Table1
        union
        select 'Account2' as account, ID from Database2.Table1
        union
        Select 'Account3' as account, ID from Database3.Table1
    

     


    Regards Harsh

    Wednesday, August 21, 2013 6:24 AM
  • Hi,

    Build your query with if else, then you will able to filter the data. Try something like this:

     


    Regards Harsh


    That works perfect for the option of each seperate account, however when trying to select all or select multiple accouts I get an error.  "Query execution failed for dataset.  An expression of non-boolean type specified in a context where a condition is expected, near ','. Incorrect syntax near the keyword 'Else'. " It runs perfectly in the Query Designer when I leave the parameter selection blank, so I must have something set wrong with the parameter or with the query. 
    Wednesday, August 21, 2013 3:48 PM
  • Hi dorindam,

    Above query work for single select paramater for multiselect parameter, please try something like this:

    select *
      from( 
        Select 'Account1' as account, ID From Database1.Table1
        union
        select 'Account2' as account, ID from Database2.Table1
        union
        Select 'Account3' as account, ID from Database3.Table1
        ) Temp where account in (@Param)


    Regards Harsh

    • Marked as answer by dorindam1 Thursday, August 22, 2013 1:51 PM
    Thursday, August 22, 2013 4:18 AM
  • Thank you Harsh, that works perfectly.  Its good to know since I'll be doing several reports like this in the near future.
    Thursday, August 22, 2013 1:51 PM