Help in SSRS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Help in SSRS

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2013-01-08 : 18:20:44

Hi guys,

Here is the SP that i am using in SSRS.

""""

Set @SDate = '1/1/2012'
Set @EDate = '3/31/2012'

SELECT
F.FFacility,
YEAR(DService) Years,
CASE
WHEN MONTH(DService) IN (1,2,3) THEN 'Q1'
WHEN MONTH(DService) IN (4,5,6) THEN 'Q2'

END Qtr,
ISNULL(E.Ilified,0) IsQV,
COUNT(EId) Ecount,
3 TestQualify
INTO #temp1
FROM counters E
JOIN dbo.Facility F ON E.FUniqueId = F.FUniqueId
WHERE E.StatusId<>7
AND E.DService>=@StartDate
AND E.DService<=@EndDate
GROUP BY F.FAbbr, YEAR(DService),
CASE
WHEN MONTH(DService) IN (1,2,3) THEN 'Q1'
WHEN MONTH(DService) IN (4,5,6) THEN 'Q2'

END,
ISNULL(E.Ised,0)

If @Isfy = 1
Begin

SELECT
Fac,
YEARs,
Qtr,
CASE WHEN IsQV= 1 THEN 'Qfied' ELSE 'N-Qv' END IsQV,
Encount
FROM #temp1
WHERE IsQV = @Isfy

End
Else
If @Isfy = 0
Begin
SELECT
Fac,
YEARs,
Qtr,
CASE WHEN IsQV= 1 THEN 'Qfied' ELSE 'N-Qv' END IsQV,
Encount
FROM #temp1
WHERE IsQV = @Isfy

-- I just added this One, to get all Records.
End
if @Isfy = 3
Begin
SELECT
Fac,
YEARs,
Qtr,
CASE WHEN IsQV=1 THEN 'Qfied' ELSE 'N-Qv' END IsQV,
Encount
FROM #temp1
WHERE TestQfy = 3
or @IsQy = 3

END

Drop table #temp1
"

In above store procedure i am using if @Isqy = 0,1 or 3) execute that select statement. When i execute SP " EXEC MySp @SDate,@EDate,@IsQy (0,1) Working fine but when i want to see all to use @IsQY = 3 not showing any data in SSRS Reports but SP is working fine in SSMS. A ny advise would be great appreciate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 00:07:57

whats the datatype of @IsQY?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tm
Posting Yak Master

160 Posts

Posted - 2013-01-29 : 16:00:08

Just wondering if you are passing or referencing the correct variable.

In the script there is @Isfy in "if" statement but you mention @IsQy variable.

In your script you have same select for @Isfy = 1 and @Isfy = 0

quote:

If @Isfy = 1
Begin

SELECT
Fac,
YEARs,
Qtr,
CASE WHEN IsQV= 1 THEN 'Qfied' ELSE 'N-Qv' END IsQV,
Encount
FROM #temp1
WHERE IsQV = @Isfy

End
Else
If @Isfy = 0
Begin
SELECT
Fac,
YEARs,
Qtr,
CASE WHEN IsQV= 1 THEN 'Qfied' ELSE 'N-Qv' END IsQV,
Encount
FROM #temp1
WHERE IsQV = @Isfy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources