Seal Report Forum
»
Report Edition
»
Data Source Creation
»
How to retrieve datasource from ssrs report server and add dataset from stored procedure?
Rank: Newbie
Groups: Registered
Joined: 2/21/2014(UTC)
Posts: 2
|
Is it possible to add dataset section instead of table? and this dataset should support load the meta data from stored proc.
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209  Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
If I summarize, you want to use a SQL Server stored procedure that returns a result set, and you want to use it as a normal table... Several options can be used to achieve this: First try to use OPENROWSET function ( http://msdn.microsoft.com/en-us/library/ms187569.aspx) Create a new table in your Data Source and name it: Code:OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'EXEC yourSP')
Then you should be able to get the columns and create a model from this new table (even joins should work)... Otherwise you may consider to switch your SP to a Function that returns a table: e.g.: create a new function in your database: Code:CREATE FUNCTION aSampleFunction() RETURNS TABLE
AS return select * from sys.schemas
then create a new table named aSampleFunction() in your Data Source Finally, the most complicated (but more flexible) is to create a temp table using Pre and Post SQL statements defined in the Table definition of the Data Source. Create a table in your Data Source with the following properties: Pre SQL: Code:CREATE TABLE #tmpCatalogTable
(
name nvarchar(50),
description nvarchar(500)
)
INSERT into #tmpCatalogTable
EXECUTE sp_catalogs 'localhost'
Post SQL: Code:DROP table #tmpCatalogTable
Name: #tmpCatalogTable To create the columns, create first the table without the # prefix (this creates a normal table), then switch again to the temp table. You will get several errors when checking the table, or building your model (because Pre and Post SQL are not executed in these cases) but finally the report will work... We will try to enhance this in the next release. Edited by user Thursday, February 27, 2014 12:00:06 PM(UTC)
| Reason: Not specified
|
|
|
|
|
|
Seal Report Forum
»
Report Edition
»
Data Source Creation
»
How to retrieve datasource from ssrs report server and add dataset from stored procedure?
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
