Freigeben über

Please do not ask me again 'does the query include.....'

  • Artikel

As we all know the slightest change in the query underneath a report can drastically change the report results. The consumers of your report may be full of lots of questions..and constantly…

'Are you including sales from the previous month too?'
'Is that only for balloon parties for kids < 8?'
For some cases, it even relevant to ask 'Is your search of customer Last Name case sensitive?'

Well, for users not versed in query syntax you may still have to answer all the questions, publish some descriptive guide, or just buy them a cup of Joe and tell them to trust you.

However, for those not ready for the leap of faith and in a situation where the users know query syntax you can empower them to answer their own questions. Allow them to see the syntax of your dataset on the report. Then they know exactly what the report is based on.

To expose a query, you can use the ‘Datasets’ global collection. Simply create an expression that refers to the commandtext of the data set so, I create a textbox (say textbox5) and its value is “=datasets!mydatasetname.commandtext”

Then what I like to do is hide it, as it can be very large, but have another text box (say textbox10) whose value is “View Query Text”. I make its color a blue so its more obvious to users its some sort of action and calls to them ‘click me’. Then I make textbox10 the visibility toggleitem property of textbox5. So a user clicks “View query text” and textbox10 appears showing the query.

This is particularly handy during the first iterations of building a report and you may need to collaborate with co-workers on the exact goal/make up of the query results. Even after your done, I leave it there and hidden in case I need it.

For more information on the Global collections, please see this SQL Server Books Online topic.” Using Global Collections in Expressions (Reporting Services)”
Note the following comment “Not available in local preview.”

Comments

  • Anonymous
    August 09, 2007
    Is there a way to make this work when the dataset is generated using a SPROC?

  • Anonymous
    August 15, 2008
    I found a nice little feature in SQL Server Reporting Services (SSRS) today. Through the expression "=dataset!&lt;dataset