July 14, 2010 at 1:44 pm
I don't write a lot of SSRS reports, but I am trying to create a fairly simple report, but I am having a bit of trouble with thinking through the parameter(s).
Basically what it comes down to is that I want allow the user to pick whether to show the reports that have a lab number or those that don't. (ie, @lab_number = '' or @lab_number <> '')
So my problem that I am running into is that I am not sure how to configure the report to "flip" my operator from equal to not equal. There may be some other way that I am not thinking of and I am open to suggestions. But I am trying to not have to publish two different reports that are identical except for the operator in my dataset.
Sherri
July 15, 2010 at 4:46 am
This was removed by the editor as SPAM
July 15, 2010 at 7:25 am
Thank you for the suggestion, I will give that a try.
July 15, 2010 at 7:41 am
Sherri Barkley (7/14/2010)
I don't write a lot of SSRS reports, but I am trying to create a fairly simple report, but I am having a bit of trouble with thinking through the parameter(s).Basically what it comes down to is that I want allow the user to pick whether to show the reports that have a lab number or those that don't. (ie, @lab_number = '' or @lab_number <> '')
Sherri
It is still possible to do this within your dataset.
You basically create a new field which will group the labnumbers into those with labnumbers and those without and then you filter the data based on this field using the parameter.
When you run the query in designer enter the parameter 'no labnumber' and this should return all the data where no labnumbers have been entered.
select * from (
select
case when lab_number = '' then 'no labnumber'
else 'has labnumber' end as Grouplabnumber, other fields . . .
from yourtable) as derived
where Grouplabnumber = @labnumber
July 16, 2010 at 12:04 pm
As always, there are more options: rather than using a regular SQL query to populate your Dataset, you can use an expression as a query. For example, assuming you have created a report parameter, (type boolean) named "ReportParameter1, you can define the expression as:
=IIF(Parameters!ReportParameter1.Value,"SELECT * FROM yourTable WHERE lab_number <>'',"SELECT * FROM yourTable WHERE lab_number = '' ") (the quotes after "=" and "<>" are single quotes, the others are double quotes).
In such a simple case, there's no advantage compared to the solutions already proposed. However, if necessary you can build complex expressions that are not possible/very hard to create with SQL only.
The main disadvantage of this method is that you will have to manually create the fields that will be returned by your "expression query". This can be solved by writing a "normal" query first, including all the fields you need for the report and then, once the fields are shown as part of your Dataset, replace the "normal" query with the expression, where you can reuse most of the SQL query.
July 23, 2010 at 2:34 pm
Thank you all for the suggestions. I decided to go with "Abs-225476" suggestion and it worked perfectly.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply