posted 10/6/2010 by ChrisAlbrektson - Views: [1423]
This blog is for all the report developers out there that have been accused of a report not working correctly, which is probably 99.9% of us. What I am about to show you will help defer the blame as well as help potentially save you any research time. I started doing this myself a couple of years ago after getting annoyed by the end users saying that a report wasn’t working. I later realized that the report was working it was just the parameters they picked had no data associated with it at that time. What I did was to add a text box in the page header that says something like, ‘No data available for selected parameters’ and then created an expression to show or hide the text box. See the instructions and example below.
Step 1. Create a text box in the page header or you could add a row at the bottom on the table and build the Show/Hide expression based off that.
Step 2. Next create a new column in the table with a default value of 1 and hide that column, or base it off and existing column.
Step 3. Create an expression formula to Hide/Show the text box like this, =Sum(Fields!data.Value, "SalesbyRegion") > 1
Here are 2 examples of each
In the page header:
In the table itself:
Believe it or not I still will get an end user asking why the report is blank and even send me the export with the text box clearly visible in it. Anyways I am sure we could do this many different ways so please post your examples. I hope this helps.
Chris, this is great. I have used something similar many times for the same reason. There is also a Tablix property called "NoRowsMessage" where you can put an expression that will do pretty much the same thing but without the overhead of adding a column, etc., to your dataset. One additional advantage of using this property is that you can make it part of a report template so it is done even before you start.
Hey Daniel, thanks for the tip.
I submitted the note above a little prematurely. I forgot to mention that although the NoRowsMessage can be put into your template, and it doesn't require dataset modifications, it is not as flexible in terms of formatting size, position, or color, so it may be a little too understated for some users to notice.
One other note, when using your method (or something similar) I also find it useful to include the parameters in the message because sometimes the parameter was chosen in error and the note helps the user figure that out.
I would suggest sometimes adding the selected parameters to the heading of the report, so that anyone viewing the report will know what combiantion of selected parameters caused the "No data" scenario to occur. I would even add the execution date, because some data may be time sensitive. Running a report today for tomorrow's data would of course get no results, since I can see the execution data of today. But running the report with the same criteria three weeks from now will get values using the same selected parameters, but of course, the execution date is three weeks later.
For execution time:
="Report Generated: "+Globals!ExecutionTime
And for rendering time:
="Render Duration: " +IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", (IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", "")))
="Render Duration: " +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds",
(
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))
)
Just another two cents!
You can check out this link as well. It has magically appeared after I posted my blog. It’s a little better documented than mine.
http://www.bidn.com/articles/ssrs-development/182/where-are-my-records-using-the-norowsmessage-property
Quick note, the Render Time calculation is from briankmcdonald. Apologies for the lack of credit there.
Execution time, however, is all mine! Bwahahaha *evil maniacal laughter*
Deuces!