INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to use a Parameter to switch between two tables?

How to use a Parameter to switch between two tables?

(OP)
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

RE: How to use a Parameter to switch between two tables?

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

Ian

RE: How to use a Parameter to switch between two tables?


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)

RE: How to use a Parameter to switch between two tables?

(OP)
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

RE: How to use a Parameter to switch between two tables?

Something like this (not syntax checked)

CODE --> SQL

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

----------------------------------------
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
----------------------------------------

RE: How to use a Parameter to switch between two tables?

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."

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close