D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Wednesday, 30 June 2010

Display dataset query in the report - SSRS

To display a dataset query in the report, simply place the following code in a textbox expression:

=datasets!datasetname.commandtext

11 comments:

Josh said...

Nice tip. Do you happen to know how to display the Parameters chosen in the Query also?

Dom Horton said...

Hi Josh,

thanks for the comments, this post should help with displaying parameter values

Josh said...

Thanks Dom. I'm using that method now, but was curious if there was a way to display it in the query, and without having to specifically reference each parameter. It would be nice to have a single expression I could use as a template for any report.

Anonymous said...

Doesn't work for me in SSRS 2008 using BIDS 2008. Just comes out blank for me. I'm using this expression
[code]
="SQL Query: " & DataSets!DataSet1.CommandText
[/code]
And yes my dataset is called DataSet1.

Dom Horton said...

Josh, this may help:

http://www.sqldev.org/sql-server-reporting-services/displaying-dataset-query-in-report-53077.shtml

Anon, I'm using 2005 but not sure whether that has a bearing on things

Josh said...

Thanks Dom. I'll look into the RewrittenCommandText.

Anonymous: I'm also using SSRS 2008 and the query I'm using is shown on the preview:

="Query: " & vbcrlf & datasets!AutoGL.CommandText

Maybe it has something to do with placement, or using the Data set in the Report.

Gaston said...

Thanks for the replies!

I used
=datasets!DataSet1.CommandText

In the expression editor the CommandText property is red underlined, but is seems to work in the report anyway.

Best regards,
Gaston

Dom Horton said...

Gaston,
glad it's been of use

James said...

Thanks for posting this. Was struggling with debugging a query. My error was obvious once I was able to see the query.

Dom Horton said...

No worries James. Glad it was of use. Not been blogging much lately...the birth of my first son has taken precedence!

Pavithra said...

It does not display the values of parameters - Can it be done?