CODEWITHSUNDEEP
sqlreportbuilder
dbawannabe
dbawannabe

Reputation: 11

How to call Stored Procedure using Report Builder 1.0?

I'm using SQL Report Builder and I would like to call the stored procedures that were already built. SSRS allows me to do this but since the company's requirement is to build a report model to allow users to do their ad-hoc reports, I need to find a way to include these stored procs written to populate some of the tables.

Can anyone please help me with this?

Thanks.

Upvotes: 1

Views: 3663

Answers (1)

Davos
Davos

Reputation: 5415

When you use SSRS to create a Report Model project, you create a data source and then a Data Source view (DSV) which is a restricted list of tables & views available for the report model.

The idea is to create a limited set of views for the Report Model so that report creation is simple and unambiguous for end users. It's not recommended if end users are not going to be the ones creating reports. In practice, Report Builder is not powerful enough for power users, and other users are not going to get a lot of value from it that they couldn't already get from connecting Excel to the datasource and creating pivot tables.

Unfortunately with Report Builder you are limited to tables & views. This immediately means that complex reports should not use Report Builder. Anything fancy is not going to be easy to reproduce in a view because they don't take parameters.

If it definitely required, then you need to somehow create a view from those existing Stored procs. One way is to convert them to use table valued functions (TVF's ) . That is not an easy process because you still then need to incorporate the TVF into a view and it still won't be able to take parameters.

Teo Lachev's book 'Applied Microsoft SQL Server 2008 Reporting Services' lists another workaround on page 312. You can use OPENROWSET to create a named query. This relies on you having already enabled SQL Server for ad hoc distributed queries (server option). The example he gives:

SELECT a.* FROM OPENROWSET('SQLNCLI', 'Trusted_Connection=yes',
'[AdventureWorks].[dbo].uspGetManagerEmployees 16') AS a

That actually seems like the least amount of work for you.

The best option is to just do the reports in SSRS and reference the stored proc as is.

Upvotes: 1

Related Questions