logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


Welcome Guest ! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
mista852003  
#1 Posted : Friday, February 21, 2014 9:50:46 AM(UTC)
mista852003

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.
epf  
#2 Posted : Sunday, February 23, 2014 12:28:36 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 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

Users browsing this topic
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.