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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sonu619
Posting Yak Master

202 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
52326 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  
 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