| Author |
 Topic  |
|
|
Sonu619
Posting Yak Master
193 Posts |
 Posted - 01/08/2013 : 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
India
47023 Posts |
Posted - 01/09/2013 : 00:07:57
|
whats the datatype of @IsQY?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
tm
Posting Yak Master
137 Posts |
Posted - 01/29/2013 : 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
|
|
|
| |
Topic |
|
|
|
Help in SSRS
