Hi!!

hers the solution which seems to be working for me

Scenario- I had the stored procedure with IF ELSE in it which returned multiple result set depending on Input parameter.

i had returning four colunms for each result set for (daily,monthly,weekly,quaterly,yearly)

eg: DAILY....ACTIVE...CLOSED... SUSPENDED colunms for daily parameter grouped by daily

Problem-the dataset returnrd only for the first IF condition in SSRS designer eg-DAILY(excluded the the other but the results were showed along with BLANK values for 1 st colunm)

SOlution- create query in Desiner SSRS which returns table variable

the table variable will contain the results by excecuting main stored proc.

eg-

DECLARE @DATE TABLE (

PERIOD NVARCHAR(20),

ACTIVE int,

CLOSED int,

SUSPENDED int

)

if(@Frequency='DAILY')

begin

INSERT @DATE (PERIOD, ACTIVE, CLOSED,SUSPENDED)

exec sp_MAF_Report @Firstdate,@Lastdate,@Frequency

select PERIOD, ACTIVE, CLOSED,SUSPENDED from @DATE

end

else if(@Frequency='MONTHLY')

begin

INSERT @DATE (PERIOD, ACTIVE, CLOSED,SUSPENDED)

exec sp_MAF_Report @Firstdate,@Lastdate,@Frequency

select PERIOD, ACTIVE, CLOSED,SUSPENDED from @DATE

end

else if(@Frequency='WEEKLY')

begin

INSERT @DATE (PERIOD, ACTIVE, CLOSED,SUSPENDED)

exec sp_MAF_Report @Firstdate,@Lastdate,@Frequency

select PERIOD, ACTIVE, CLOSED,SUSPENDED from @DATE

end

this one works for me perfectly fine

Thanks & regards

Satyajit