How to allow user to run report by Vendor or Client 1

[dianemarie]

Hello, I have to create a report with a parameter (@RunBy) that allows the user to run the report by Vendor or by Client. Could someone give me a general direction as to how you would then select the data according to the parameter pick? I have thought maybe a table variable (I've worked with them a bit, with help) but I'm not sure how to allow for the two fields, VendorID, ClientID. I've also been playing with CASE statements in the Where clause, but having trouble. My report right now simply says

Where VendorID in (@VendorID).

I need to modify the report to pull records by VendorID if the @RunBy parameter is selected as Vendor, or run by ClientID if the @RunBy parameter is Client. I guess to do this, I'd have to have the @VendorID parameter display vendor or client ID's depending on the @RunBy parameter. What I'm looking for is the best way to attack this...should I focus on getting the WHERE statement to work using a CASE, or should I start thinking about how to make a table variable work, or would some other solution work better? Thanks so much.

 

[RiverGuy]

Well, as far as your report is concerned, the first thing that comes to mind would be to create two parameters: the user would first select the Type, choosing either "Vendor" or "Client." This would then activate the "ID" parameter and provide a list of either VendorID's or ClientID's. You might have to base "ID" parameter's dataset off of a query such as follows:

SELECT ClientID AS [ID] FROM Clients WHERE (@Type = 'Client')
UNION ALL
SELECT VendorID AS [ID] FROM Vendors WHERE (@Type = 'Vendor')

So then, you will have a single id parameter to pass into your SQL code. If you were working with just one selected value, then I would suggest the following query:

SELECT * FROM YourTable
WHERE (@Type = 'Client' AND ClientID = @ID)
OR (@Type = 'Vendor' AND VendorID = @ID)

But, since you're probably working with multivalue parameters, you could try the following:

SELECT * FROM YourTable
WHERE (@Type = 'Client' AND ClientID IN (@ID))
OR (@Type = 'Vendor' AND VendorID IN (@ID))

Now I'm not 100% sure that SSRS will rewrite that query like it will do with simple selects with multivalue parameters. If you remember, when you have a WHERE FieldName IN (@Parameter) query, SSRS rewrites this for you in the background to something more like: WHERE FieldName IN ('a','b','c').

So, if the query-rewrite does not work properly, you can go the pass-a-delimeted-string-to-your-query-and-use-a-string-split-function method. If you need to go that route, Google for SQL Split Function and you should get lots of examples.

 

[dianemarie]

This is excellent RG. I was sort of dead in the water with the report I'm working on but now I can move forward and start working on your suggestions. (Learning the in's and out's of SSRS, to me, is sort of like being stuck in traffic. As long as I'm moving forward, even slowly, it doesn't seem totally discouraging.) And by the way, every time my boss asks me the status of the report conversion project, I always start with "well thanks to RG on Tek-Tips...." Really. A fantastic service, is Tek-Tips and I appreciate it more than I can say. I'll re-post once I work this all out. Thanks much.

 

[RiverGuy]

Good luck with your project, be sure to post back if you have any questions or need anything clarified.

 

[dianemarie]

Worked like a charm and it was easy! You got me through a crisis of confidence. Have I told you? You rock.

Many many thanks and oh yeah, I'll be back. I'd like to be riding the big bike but I'm still wearing the training wheels.