Did you miss watching Friday’s DBAs@Midnight live last week? Shame, really…
Watch now: Sean’s Pretty Hands, the MCM spectacularrrrrrrrrrrrrrrrrrrr! (Some NSFW language.)
SSRS Tip: Hide a tablix if no rows returned
I have a tablix in an SSRS report, populated by a query that might or might not return any rows. What I want to do is make that tablix invisible if there are no rows returned.
First off, to set conditional visibility.
- Right-click the tablix and select Properties.
- Click Visibility.
- Select Show or hide based on an expression.
- Click the Function button.
The formula you place in here should evaluate to TRUE (meaning, hide the tablix) or FALSE. Examples of True/False expressions here include:
= 1=1 ‘(true)
= 2=1 ‘(false)
=Globals!ExecutionTime > “1/1/1998″ ‘(true)
But we want something a little more specialized – something that checks if any rows were returned. But there’s no ReturnedRows function in VB, so we have to attach this another way. The question you want to ask is, does the first row from this data set actually have data?
Start with the first row part:
- Under Category, click DataSets.
- Under Item, click the data set you want.
- Under Values, click a field that really shouldn’t be NULL (if the data set DID return data).
For example, I use First(ID). This gives you the formula =First(Fields!ID.Value, “DataSet1″), which clearly doesn’t evaluate to True or False. All you have to do now is slap the IsNothing function around it, and you’re golden!
=IsNothing(First(Fields!ID.Value, “DataSet1″))
If you like alternative wording in code, here’s another way to say the same thing:
=IIF(First(Fields!ID.Value, “DataSet1″) Is Nothing, True, False)
This performs the exact same test; it’s just more spelled out via the IIf and explicit True and False return values. This wording gives you a bit more flexibility for other uses; for the specific problem at hand, I’ll personally use the tighter IsNothing formula.
Wait a minute…
Why couldn’t we have simply used Fields!ID.Value, instead of First(Fields!ID.Value, “DataSet1″)? Well, IsNothing won’t work against an expected rowset, even if the rowset returned no rows; that’s a different kind of object. But it will work against a single value.
For more on SSRS expressions, see MSDN’s Expression Examples (Report Builder 3.0 and SSRS) article.
Happy days,
Jen McCown
MidnightDBA.com/Jen
Posted in sqlserverpedia-syndication, SSC, SSRS, Tips
2 Comments
Answer the Question Asked
“Bill, are you running SQL R2 on your desktop?”
This seems like a simple enough question, yet I usually cannot get an answer to a question like this on the first try. Why? The simple answer is that people don’t hear the question you ask. People hear a question through filters. What this means to me is that I’m perpetually asking the same question multiple times to the same person, just to convince them that it’s what I really want to know.
Would you like an example? Well, let’s consider Bill. Bill may have some or all of these mental filters going on at the moment my question hits his eardrums (or his email inbox):
- There’s something SQL-related Bill was supposed to do this week, but hasn’t yet. He’s feeling nervous about that.
- He imagines something has happened, or some code has been written, in an R2 environment, and he’s anxious to assure me he hasn’t changed code or messed with production.
- He thinks there’s something I need to test on R2, and so he’d like to jump straight to recommending an R2 box he’d rather I use, thus saving time within the conversation.
- He thinks I’m looking into licensing, and that I need the edition (rather than the version).
Any of these might cause Bill to answer me indirectly, or not at all:
Bill, are you running SQL R2 on your desktop?
[Bill:nervous] I was just getting to that PRM script.
What?Bill, are you running SQL R2 on your desktop?
[Bill:anxious] Why do you ask? I haven’t opened SSMS today.
What?Bill, are you running SQL R2 on your desktop?
[Bill:helpful] I think Dev09 has R2 installed.
What?Bill, are you running SQL R2 on your desktop?
[Bill:super helpful] I’m running Developer edition.
….??
Now, I like to head off concerns at the pass and shorten conversations as much as the next DBA. But I have been well trained to answer the question asked. If I then feel the need to add more information (or ask questions), I will.
Jen, are you running SQL R2 on your desktop?
[Jen:responsive] Yes I am, Developer Edition. What’s up?
Sure, there’s a lot going on, all the time. We’re busy, multitasking, intelligent people. That shouldn’t prevent you from communicating clearly – especially at work (or in an interview!) A simple answer often cuts straight through the chatter.
Happy days,
Jen McCown
MidnightDBA.com/Jen
Thanks to Grant Fritchey for the link to this excellent article about how to ask questions, which partially inspired this blog. Thanks to Sean McCown, who has said it 1,000 times if he’s said it once: “ANSWER the QUESTION I ASKED.”
Edit: I’m currently reading the appendix of Denny Cherry’s (web, Twitter) excellent book “Securing SQL Server“, which has this pertinent note (emphasis mine):
When working with an auditor, always answer the question being asked, and only the question being asked. … If an auditor asks a question that is very broad, there is nothing wrong with asking for clarification of thq uestions. Don’t assume that you know what question the auditor is asking becuase if the audit is asking one thing, and you assume the opposite, your wrong assumption could cause the company to fail the audit.
Yes, just another real-life example of the criticality of proper responses. -J
Posted in Random helpfulness, sqlserverpedia-syndication, SSC
Tagged Excellent communication skills, Ranty rant
4 Comments
