question

Goldie50 avatar image
Goldie50 asked

Create a dynamic Where clause based on Parms from report services

What is the most efficient way to do this......

I have an invoice table that the users need to query in about any combanation under the sun!

example filter based Invoice# or *all,Customer # or *all, salesrep# or *all, region# or *all, etc now these filters can be actual value or all, in any combo, example all invoice# for region a, or 123(customer) in a. I would also like to give the user the parm for subtotal selection(groupBY) I will alway be summing on the invoice amount. Following the above example 123 invoices in reagion A, subtotaled @ date.(I'm already passing date range as a parm)

I was thinking I could pass the user selected values to a stored procedure for the where clause, and order by, but not sure how efficient that would be, and need a code example for the *all situation. Any help would be greatly appreciated. Thanks in advance.

Goldie50

stored-proceduresparameters
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
TG avatar image
TG answered

For filtering you can default your parametes to NULL and don't include the parameter when "All" is selected (or pass an explicit NULL). Then in the WHERE clause you code: Where (@customerNo is NULL OR [customer#] = @customerNo) for all the filtering parametes.

for subgrouping, assuming you want to show the details and organize them in groups with sub-totals, you need to do that in the report writer. If you use a GROUP BY in the SP then all you return is the aggregate totals and your grouped columns, you can't show the details.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.