0

I've built an SSRS report that is supposed to look at data from one of several tables with similar names - i.e., table001, table010, table011, etc. When I was building the report, I was only including three of the tables, out of more than a dozen. Everything worked fine until I added all the rest of the tables to the query, using multiple SELECT statements UNIONed together; it was trying to sort through so much data that it was taking almost half an hour to render the report. That's not going to work.

Is there a way to pass an SSRS parameter to the SQL query, specifying which table to access?

Ben C.
  • 1,761
  • 5
  • 15
  • 24
  • If the query is slow, the report will be slow. The issue isn't with passing the parameters from SSRS to the database, but your query. You need to find out how to solve your problem of unioning 12 tables together, and your report issues will solve themselves – Tom Aug 01 '13 at 20:08
  • @Tom - That's why I'm trying to figure out how to use the variable for the table name. If I could do that, I could query just the one table instead of all of them - just pass in the report parameter to the variable and it would query based on that. I just don't have any idea how to do that. – Ben C. Aug 01 '13 at 20:12
  • Sorry, I see now, I didn't understand the question first. So, based on some parameter, you will select from some table? For example, @Param = 'Something' then select from table008? You can define that in an `if/else` or `case` statement in your query. – Tom Aug 01 '13 at 20:16
  • @Tom - Yeah, something like that. If you could expand on that a bit, that'd be great. I'm pretty new to SQL still. Most likely there would be a param whose value would be the string '001'. I've tried just doing something like `FROM @Param`, but then it complained about static variables. – Ben C. Aug 01 '13 at 20:19

2 Answers2

1

Based on your comments, you could do the following

DECLARE @Parameter NVARCHAR(15) = 'Foo'

SELECT CASE 
WHEN @Parameter IN ('Foo', 'Bar')
    THEN (
            SELECT *
            FROM table001
         ) 
WHEN @Parameter IN ('Foobar')
    THEN (
            SELECT *
            FROM table002
         ) 
ELSE
         (
            SELECT *
            FROM table003
         )
END


Alternately

SELECT CASE @Parameter 
WHEN 'Foo'
    THEN (
            SELECT *
            FROM table001
         ) 
WHEN 'Bar'
    THEN (
            SELECT *
            FROM table002
         ) 
WHEN 'Foobar'
    THEN (
            SELECT *
            FROM table003
         ) 
ELSE
    (
        SELECT *
        FROM @table004
    )
END
Tom
  • 2,180
  • 7
  • 30
  • 48
  • In the first example, I don't see how the value of the report parameter is getting to the query. It looks like you're just declaring a SQL variable and immediately assigning it a value. – Ben C. Aug 01 '13 at 20:30
  • Also, I tried the second example. It gave me three errors - `Must declare the scalar variable @Param`, `Incorrect syntax near the keyword ELSE`, and `Incorrect syntax near the keyword SET` – Ben C. Aug 01 '13 at 20:36
  • Well, right, that was for demonstration purposes. Maybe I don't understand the question -- is the entirety of your question simply how do you pass a parameter in SSRS? I guess that *is* what the title says. In that case, http://msdn.microsoft.com/en-us/library/aa337432(v=sql.105).aspx is an in depth article on the subject. – Tom Aug 01 '13 at 20:38
  • I've already tried what that article says, in a few different ways, to no avail. I tried it again just now, and it's still giving me the `Must declare the scalar variable` error. – Ben C. Aug 01 '13 at 20:46
1

Have you followed the MSDN tutorial? This is good too: http://sql-bi-dev.blogspot.com/2010/07/report-parameters-in-ssrs-2008.html

Please share what you have tried and where you are having trouble. Essentially, you define a parameter in the report and include it in your query. SSRS provides the parameter value (or it is received through user input), and then the final query is passed off to the database.

David O
  • 49
  • 7