How to use a Parameter to switch between two tables?

[javedi]

Hello

In SSRS 2008, I have two different tables and would like a parameter to switch between them. Does anyone know how to do this?

Thanks,
Javedi

 

[IanWaterman]

You will have to use a stored procedure and use the parameter to change SQL

Ian

 

[dhulbert]

2 Simple ways to do this either

1) Have 2 scripts 1 for each table then use the parameter to decide which script to run.
2) Use dynamic SQL to create and execute the SQL statement with the appropriate table name included.

Option 1 is the easiest to set up but ongoing there could be issues ensuring the 2 scripts remain in sync, if you have more then 2/3 tables this method starts to become very messy.
Option 2 is more difficult to set up but is much easier to maintain and adding extra tables or making changes is much easier.



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[javedi]

Thanks Dhulbert,

Can you elaborate on Option 1 please?

I'm using one database table for each of the two queries each with grid tables in ssrs. I don't understand how to get the parameter to switch between the grid tables, so only displaying one. So the parameter should show "OPEN" and "CLOSED" options and show appropriate grid table.

Can you provide further clarification/steps?

Thanks,
Javedi

 

[SQLScholar]

Something like this (not syntax checked)

Create procedure myproc @querytype varchar(10) as

IF @Querytype = 'A'
Begin
SELECT * from table1 
END 
ELSE
BEGIN
Select * from table2
END

So in this case - if you pass A to the proc, it will run the sql getting info from table1. Else it will get it from table2

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[jtb]

Look up "visibility"... add the fx...

Like:

If @platform.value = "Windows" UseDriveLetters
Else UseMountPoints

You can do it in Report Builder.

JTB
Have Certs, Will Travel
"A knight without armour in a [cyber] land."