SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Help in SSRS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sonu619
Posting Yak Master

195 Posts

Posted - 01/08/2013 :  18:20:44  Show Profile  Reply with Quote
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
52317 Posts

Posted - 01/09/2013 :  00:07:57  Show Profile  Reply with Quote
whats the datatype of @IsQY?

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

Go to Top of Page

tm
Posting Yak Master

160 Posts

Posted - 01/29/2013 :  16:00:08  Show Profile  Reply with Quote
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


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000