October 16, 2007 at 8:03 am
I'm new to Reporting services and am try to convert some reports from Crystal. I have a user interface front end that the user selects criteria for the report to generate from and then the where clause in the report will be modified to create the appropriate "Where". Below is an example:
Select Date, Office, Name from Info_table
Where Date >= '01/01/2007' and
Date <= '02/01/2007'
and Office = 'x'
The user selects "Office_A" from the front end and the SQL above will be replaced with:
Select Date, Office, Name from Info_table
Where Date >= '01/01/2007' and
Date <= '02/01/2007'
and Office = 'Office_A'
If no office is selected the SQL will be replaced as follows:
Select Date, Office, Name from Info_table
Where Date >= '01/01/2007' and
Date <= '02/01/2007'
Effectively the "and" for office has been remove from the where because I don't need to eliminate any offices from the results when none have been selected.
Can this same thing be done in a Reporting service report?
October 16, 2007 at 8:13 am
Set a default value for the (@office) office parameter to something like "N/A" and then change your query to...
Select Date, Office, Name from Info_table
Where Date >= '01/01/2007' and
Date <= '02/01/2007'
and (Office = @office OR @office = 'N/A')
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 16, 2007 at 8:27 am
Ok. That works, although I'm not sure I understand how. What if I have another senerio where there are multiple entries for office like:
Where Date >= '01/01/2007' and
Date <= '02/01/2007'
and Office in ('Office_A','Office_B')
How do I eleminate the "and Office in() " when none are selected by the users. BTW thanks for you previous answer.
October 16, 2007 at 8:29 am
I'm not sure with the multi-select parameter feature in RS 2005.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply