Uses of Stored Procedure in SSRS

Question

Uses of Stored Procedure in SSRS

indraprakash

SSChasing Mays

Points: 637
More actions
July 2, 2008 at 6:04 am

#209486

Hi,
Can we use two or more stored procedure in one SSRS, If yes then how?
Which tools have the functality of crosstab?
Thans & Regards,
Indra Prakash Tiwari

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

nigel.c.west Hall of Fame Points: 3468 More actions · Answer 1

There are several ways to use more than one stored procedure and it depends on what you want to achieve that pushes you through to one method or another.

If, as I suspect, you want more than one distinct set of data then you simply need to create more than one dataset in the data tab of SSRS. How you then implement the report again will depend on the output you want, but for starters you could simply add two tables onto the report, one for the first dataset and one for the second dataset.

For a cross-tab, use the Matrix function.

If you are using SSRS 2008 the Tablix function is also worth a look as this provides cross over functionality between a matrix (cross-tab) and a normal table.

If you need more, please provide some more specific questions/examples.

Good luck,

Nigel West
UK

indraprakash SSChasing Mays Points: 637 More actions · Answer 2

Thanks for Reply,

But actually we have two stored procedure and one column common in both SP. We want to display only those records which are common in both SP output.

nigel.c.west Hall of Fame Points: 3468 More actions · Answer 3

So the thing to do probably is to create a third stored procedure that executes an inner join on the output of the other two stored procedures......

Does this make sense

For example:

DECLARE @tmp1 TABLE

(currankrow int, prvrankrow int, acdim int, acimage varbinary(max), acname nvarchar(100), acdesc nvarchar(2000), fundname nvarchar(100), curfmv decimal(18,2), prvfmv decimal(18,2), valueadd decimal(18,2))

INSERT INTO @tmp1

EXEC usp__TpCoByVal '30 sep 2007', '30 jun 2007'

DECLARE @tmp2 TABLE

(currankrow int, prvrankrow int, acdim int, acimage varbinary(max), acname nvarchar(100), acdesc nvarchar(2000), fundname nvarchar(100), curfmv decimal(18,2), prvfmv decimal(18,2), valueadd decimal(18,2))

INSERT INTO @tmp2

EXEC usp__TpCoByVal '31 dec 2007', '30 sep 2007'

The above code is an exmaple of calling an SP and sending the output to a temp table, you could then do a final select by doing a fairly standard join on these two tables to match your own requirements.

Good luck,

Nigel West
UK

indraprakash SSChasing Mays Points: 637 More actions · Answer 4

I know this the solution

but i want to know that in SSRS join is possibe or not

Actually I am a database developer as well as Crystal Report developver recently i am start to use SSRS services.

Like Crystal Report join option is possible in SSRS or not. now I think you better understand my problem.

thanks & regards,

Indra Prakash

nigel.c.west Hall of Fame Points: 3468 More actions · Answer 5

You can use exactly the same code in the Query Text of a dataset in SSRS.

I prefer to use calls to an SP for every dataset in a report, it just makes more sense to me, but you can do whatever you want.

I don't know of a way to directly JOIN the output of the two SP's without using temp tables, but maybe someone out there does?

Regards,

Nigel West
UK