THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

SQL Server Reporting Services: Avoid T-SQL in Reports

This blog has moved! You can find this content at the following new location:

http://greglow.com/index.php/2008/03/12/sql-server-reporting-services-avoid-t-sql-in-reports/

Published Tuesday, March 11, 2008 9:32 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Rod Colledge said:

-- "Fortunately, the reports are typically located in a single location"

... unless you have users keen on using Report Builder to create and save custom reports on their desktop :-) To make the refactoring even more interesting, they base these reports on a report model whose definition needs to change.

Argh...

March 11, 2008 6:50 AM
 

Saggi Neumann said:

Multi-valued parameters are annoying when used with stored procedures.

Any chance SSRS 2008 will be able to use table value parameters?

March 11, 2008 7:22 AM
 

Greg Low said:

Agreed. I tend to use comma-delimited lists for these. No idea on support for TVPs in SSRS 2k8 but that's a good question. I'll ask.

March 11, 2008 7:39 AM
 

jeff m said:

Thank you for the post.

March 11, 2008 9:08 AM
 

jerryhung said:

Totally agree, I'd love to use SP for SSRS if it wasn't for the multi-valued parameter, and the frequent #temp table error during the report creation using wizard

If you use #temp inside the SP, the wizard won't let you run it, so I constantly to have put in a dummy query or the SP SELECT into the wizard, to generate the report first, then switch data source to SP after

March 11, 2008 9:29 AM
 

Brian H said:

jerryhung,

Although I don't use the wizard, I've had problems with stored procedures using temporary tables as sources for reports, until I found on a blog somewhere

SET FMTONLY OFF

Put that at the top of your procedure and temporary tables can even be the means of output.

On the subject of multi-valued parameters, I pass the demited list to a table-valued function that outputs it as a table. This can then be used as a table within the stored procedure, typically in a 'where variable in (select * from fncConvertMultiList(@DelimitedList))' clause.

March 16, 2008 7:02 AM
 

Greg Low said:

Great tip Brian !

March 16, 2008 3:42 PM
 

Sean Cooperman said:

Can we use a stored procedure in the report model? If we can do you recommend doing this or not?

Thanks

April 17, 2008 10:59 AM
 

Greg Low said:

Hi Sean,

Not in any way I know of as yet. The report model requires a dataview source and that is built over tables or views. I think it's a signficant limitation that it can't at least do non-parameterised sprocs.

A view is the closest you can currently do, even though it's not the same.

Regards,

Greg

April 22, 2008 5:47 AM
 

Smily said:

just refresh the dataset

July 14, 2008 9:10 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement