No matter how much we love to see our designed report brimming with data like a cornocopia, there are times when it's a little too much of a good thing.
Linda writes with the following question (using SQL 2005 and RDLs):
1. I have a report that all parameters have a default so the report runs when it is opened before you click View Report.
2. I don't want the report to run because it is pulling way too much data. I want the user to fill in one of the five parameters to limit the data (I don't care which one).
3. I wrote custom code that checks to see if all parameters are nothing and returns a boolean.
4. I use the Hidden property, with an expression that calls the custom code to either show a textbox saying "Please fill in one parameter" or the table that is the actual report.
5. The problem is, that even when my table (actual report) is hidden (because they did not enter a parameter), the dataset is still running (and pulling way too much data).
6. How can I get the dataset to NOT run unless they have entered one of the parameters. I have tried several things in my custom code but have not been successfull.
Well, sure. Hiding a data region in the layout doesn't affect the data-loading. From the report's point of view, hiding a data region doesn't mean that some custom code or some other data region isn't using the data in this same dataset. You have to get "back", closer to the data-pull, and further from the layout, to do this right.
Linda was really close to getting this right without help, as you may be able to tell from the steps she's taken. She just needs a little boost. And this is a problem that besets us all — so I wanted to get it out there, in at least one version, as quickly as possible. (These days, "as quickly as possible" isn't as quickly as I'd like… apologies to EVERYBODY who has been waiting in the queue.)
There are a lot of ways to do this. Some involve dynamic sql for the actual report query, and some don't. I'm going to show some basic ingredients for the ones that don't need a dynamic query, which are surprisingly simple.
1. Setup: Not important
For this walkthrough I'll use a very boring table with very boring data.
The table lists event notices that are triggered in a system.
It doesn't matter what's in it; all you have to know, really, is that the Designer preview in VS 2008 throws an "Out of Memory" exception if I run the report with no filters.
2. Consider the needs of each individual parameter
In this walkthrough I've used "start" and "end" dates for one filter, and a picklist of event notice types for another one. These are just a couple of common filter types.
But these two params won't fit every case and every data set. You need to look at each parameter in your report, and decide how to apply what I'm doing in the example.
3. To dream the impossible dream
The picklist parameter gets its available valules from a second data set. The entries come mostly from a lookup table, but I've added a UNION statement, like this:
SELECT Notice_Type,2 AS Ord FROM S2S_NoticeTypes
UNION
SELECT 'Please select' AS Notice_Type, 1 AS Ord
ORDER BY Ord, Notice_Type
For the picklist's default value, I"ve set the value 'Please select'.
So far, I've set a "known impossible" value as the default value. I now use this "impossible" default value in the actual SQL query, like this:
SELECT * FROM S2S_Notices
WHERE
Notice_Requested > @NoticeRequestedAfter AND
Notice_Requested < @NoticeRequestedUntil AND
(@NoticeType = 'Please select' OR Notice_Type = @NoticeType)
Notice the all-important parens. This is a simple trick, but one I use a lot, to say "evaluate this parameter only if it has a real value".
Meanwhile, you can see that the date parameters in the query are always evaluated. What gives?
The date parameter defaults are set a little differently:
=DateAdd("d",1,Today())
=DateAdd("d",2,Today())
These are still "impossible" defaults for this particular table, because I know for sure the event notice queue has no future dates in it.
Look: Dates are particularly tricky as parameters in Reporting Services, especially when created as empties. I don't even know if Linda has any dates in her parameter set, but I wanted to use at least one example. She may be using nulls or empties for date defaults, and we could easily handle this on the SQL end if necessary; it's on the RS end that it's tricky. Read on. I think you'll see how it will work.
You notice I used Today() instead of Now() above? I didn't want times in this case, you might in yours. There are a ton of other silly date hang-ups.
4. Pick your defaults wisely!
Maybe in Linda's case, a different strategy than "impossible" defaults shown to the user would be better.
For example, it sounds to me like she's using explicit nulls as defaults in at least one parameter's case, as an indication that this parameter is optional.
She can use default nulls, if she wants, for all the parameters! Or, she can mix and match with "Select a value" style defaults for some, and null defaults for others.
When all defaults are provided but all parameters are actually optional, as in Linda's case, I usually end up with a query that has a particularly weird-looking WHERE clause. Let's adapt my example to show exactly this case.
Notice the first part of the WHERE clause here, which will come into play if none of the parameters are chosen:
SELECT * FROM S2S_Notices
WHERE
(@NoticeRequestedAfter IS NULL AND
@NoticeRequestedUntil IS NULL AND — just add one "impossible" condition
@NoticeType IS NULL AND Notice_Type = 'XXXX' — at the end of the all-null params
)
OR — your real WHERE clause is here, whatever it happens to be:
( ( @NoticeRequestedAfter IS NULL OR Notice_Requested > @NoticeRequestedAfter) AND
( @NoticeRequestedUntil IS NULL OR Notice_Requested < @NoticeRequestedUntil) AND
( @NoticeType = 'Please select' OR Notice_Type = @NoticeType)
)
Update (10/7/2009) after talking with Linda:
The above query, written in haste with the second part (after the OR) copied verbatim from the earlier example, is probably really confusing, and I'm sorry about that.
The second part doesn't match the first part (on the top half we're using a null test for @NoticeType, and on the bottom half we're testing for 'Please select', same way we did it in the earlier example.
What's more, I left out an all-important test on the second part, to ensure that at least one parameter had been edited by the user.
A more appropriate example would look something like this:
SELECT * FROM S2S_Notices
WHERE
(@NoticeRequestedAfter IS NULL AND
@NoticeRequestedUntil IS NULL AND — just add one "impossible" condition
@NoticeType IS NULL AND Notice_Type = 'XXXX' — at the end of the all-null params
)
OR — your real WHERE clause is here, whatever it happens to be:
( (@NoticeRequestedAfter IS NOT NULL OR
@NoticeRequestedUntil IS NOT NULL OR
@NoticeType IS NOT NULL ) AND
( @NoticeRequestedAfter IS NULL OR Notice_Requested > @NoticeRequestedAfter) AND
( @NoticeRequestedUntil IS NULL OR Notice_Requested < @NoticeRequestedUntil) AND
( @NoticeType IS NULL OR Notice_Type = @NoticeType)
)
… or the two @NoticeType IS NULL clauses above could both be changed to @NoticeType = 'Please select', instead.
5. Can it be easier?
Oh, definitely.
There's a good chance Linda will just take the ball up and run with it if this is enough to get her started.
She's probably also see that she can make her life a whole lot easier if she passes this stuff to a sproc. Then she can basically say SELECT TOP (@TopN) PERCENT where she evaluates @TopN to be 0 in certain conditions and 100 in others.
You do remember that MS SQL 2005 and above have variable Top (N) syntax, right? It works with the Percent keyword.
6. The "reveal"
So, now comes the fun part: showing this in the RDL itself. Instead of Linda's conditionally-hidden textbox, I decided to use a textbox with this expression:
=IIF(CountRows("dsMain")= 0,
"Please edit the filter values to return data",
"You have selected " & CountRows("dsMain").ToString()
) & " rows."
… no particular reason, I just thought it would look nice. Of course the "false" part of the IIF could have been an empty string, too. In Linda's case, if she's using nulls for defaults, she might want to say "Please edit at least one filter value" or something like that.
I could also have chosen to use the NoRowsMessage property of the Table layout control, either instead of or alongside the specialized message textbox. This property is pretty convenient (I think all the data region controls have it), although not as flexible in the formatting department.
7. Take your bows
Obviously, if I'm talking about using NoRowsMessage property of the Table layout control, I didn't choose to hide the table when there wasn't any data. I thought the headers looked nice.
You can definitely hide the table if you want to — whether on the basis of the parameters values or CountRows("dataset scope") is up to you. Just remember that there may be times that the parameters are edited by the user but CountRows() is still 0. Do you want to show the table, or not?
And also remember that, as Linda found, hiding something in the layout is just not the end of the story if you're looking to optimize the report.
This is the end of the story. Or at least the end of my walkthrough. It's a bit rushed (Linda's apparently on a deadline), so I might have missed something important or not been explicit enough somewhere.
That's okay. In my experience, you guys know how to find me <g>.