November 2, 2009 at 4:10 am
Hi,
I need to write some t-sql to use for reports in SSRS 2005. The reports will be called from a front end application. In the front end users will select options (start date, end date, customer address in this or that country, all the usual kind of stuff) and a user's selections will be passed at runtime as report parameter values.
So here's my question: does anyone have any tips or tricks for limiting the number of separate rdl files I have to create and upload to the report server? I can see for example that if I write a parameterised stored procedure which executes one query if the param value is x and another if the value is y, then call this procedure in a report, the report designer shows me only the first possible set of column headings to use in the report. But I don't want to create a separate report for every possible set of user selections.
Thx in advance,
Louise
November 3, 2009 at 6:35 am
Not sure if this will help, but...
One way I have used for a few years now is to create
a set up parameters that the user can control HOW they filter.
I.e. some want to select by a level in the Customer hierarchy,
some want from Product hierarchy, some want both, some want
to see everything.
So, my parms are like this(values in parentheses):
Filter Type (Enterprise, Customer, Product)
Filter Cateogry (if they picked Customer, then Account, Chain, Customer etc)
Filter Value (list of available values for the preceding parm)
This way they are in essence building their own query...and I simply
pass em all to my 1 stored proc which handles everything.
It takes a bit of user training to get some folks to understand this,
but once they do they love it because they can generate their
own ad-hoc reports and I love it for the same reason 🙂
November 17, 2009 at 7:36 am
Hi,
Thanks for your reply, so I've taken so long to respond. That's the kind of interface I give my users. In your stored proc, how do you handle the user's selections? I've pasted below an illustration of what I have at the moment, but I can't believe it's the slickest sql for the job.
Thanks for your help,
Louise
ALTER PROCEDURE spContactReport1(@org bit, @addr bit, @email bit)
BEGIN
-- 1. Names only, no org, no address, no email
if @org = 0 and @addr = 0 and @email = 0
select c.ContactID, c.Title, c.Forenames, c.Surname, c.Salutation from dbo.tblContacts as c where (c.Deactivated = 0)
-- 2. Names, org, no address, no email
if @org = 1 and @addr = 0 and @email = 0
SELECT
c.ContactID,
c.Title,
c.Forenames,
c.Surname,
c.Salutation,
c.OrganisationID, o.OrganisationName
FROMdbo.tblContacts AS c INNER JOIN
dbo.tblOrgs AS o ON c.OrganisationID = o.OrganisationID
WHERE(c.Deactivated = 0)
-- 3. if @org = 1 and @addr = 1 and @email = 0
etc etc
November 17, 2009 at 9:33 am
Ahh yes, I have been there.
What I do is similar to this:
lets say my parms are like this:
FilterCategory1 (they pick the word Customer, Account, Division, etc)
FilterValue1 (popultaes based on what they picked for the previous parm, they now sleect Customer #111 or whatever)
then I duplicate the set of parms so they can add a second filter.
create procedure Sp_test
@FilterCategory1 varchar(255), --if they picked 'ALL' make the parm return 'ALL' to the proc (same for all parms)..if they picked something, say the word Customer, have the parm return the word CustomerNumber
@FilterValue1 varchar(max),
@FilterCategory2 varchar(255),
@FilterValue2 varchar(max)
declare @sql varchar(max)
IF FilterCategory1 = 'ALL' and FilterCategory2 = 'ALL' --they want to see everything
select * from mytable
---otherwise, they picked something somewhere
set @sql = 'select * from mytable where ' + '''' + @FilterCategory1+ '''' + ' IN(' + @FilterValue1 + ') AND ' + '''' + @FilterCategory2 + '''' + ' IN(' + @FilterValue2 + ')'
exec(@sql)
---of course the trick is to have mytable have all the values populated that they coudl possibly select by
--i usually make a temp table and populate it up, and then select from it
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply