Search code examples
t-sqlreporting-servicesssrs-2008-r2

SSRS Query gets scalar variable error instead of using parameter in Visual Studio


I am having trouble with SSRS not using the already declared parameter inside a report. Within the report I already have two queries which are both using @Period to determine which month the user is requesting. However on the third report it keeps bringing up a Scalar Variable error and wants me to declare the @Period, which of course unless I specify the int value brings up a completely incorrect figure as it is totalling everything rather than the set period.

I have gone over the code even copy pasting the @Period from the working code to see if there was a hidden space or something which was preventing it from using the correct parameter but it still cant see it. Below is a screenshot of the reporting data window in visual studio and the code which I am using. I just cant understand why it cant use the @Period.

The SSRS Report Data

Declare @Year As int
--Declare @Period As int  = 9

--Sets up the year for this financial year
If @Period Between 5 and 12
Begin
Set @Year = Year(Getdate()) + 1
End
Else 
Set @Year = YEAR(Getdate())
--Sets the Declared Period to the correct number for Clients
if @Period = 5 
Begin
Set @Period = 1
End
ELSE if @Period = 6
Begin
Set @Period = 2
End
Else if @Period = 7
Begin
Set @Period = 3
End
Else if @Period = 8 
Begin
Set @Period = 4
End
Else if @Period = 9
Begin
Set @Period = 5
End
Else if @Period = 10
Begin
Set @Period = 6
End
Else if @Period = 11
Begin
Set @Period = 7
End
Else if @Period = 12
Begin
Set @Period = 8
End
Else if @Period = 1
Begin
Set @Period = 9
End
Else if @Period = 2
Begin
Set @Period = 10
End
Else if @Period = 3
Begin
Set @Period = 11
End
Else
Set @Period = 12

--This Sorts all the clients into their sectors
SELECT A.GROUP_NAME,B.CLIENT_DIWOR 
INTO #TMPCLIENT
FROM CLIENT_GROUP A
JOIN CLIENT_GRP_MEMBER B on A.DIWOR = B.CLIENT_GRP_DIWOR
WHERE GROUP_TYPE = 'THEME'
GROUP BY A.GROUP_NAME,B.CLIENT_DIWOR 

--This sets the figures according to Current year and period range
SELECT A.MATTER_ALPHA_NAME,B.FEES_PRD As Fees,A.CLIENT_DIWOR,D.GROUP_NAME
INTO #Fees
FROM MATTER A 
JOIN ON_MAT_FE_PERF B on A.MATTER_DIWOR = B.MATTER_DIWOR
JOIN PM_YEAR C on B.FINYEARDIWOR = C.DIWOR
LEFT JOIN #TMPCLIENT D on D.CLIENT_DIWOR = A.CLIENT_DIWOR
WHERE  C.YEARDESC = @Year and B.PERIOD between 1 and @Period  and B.FEES_PRD <> 0 and D.GROUP_NAME 
is 
   not NULL
   order by MATTER_ALPHA_NAME

--This sets Previous year and period
Set @Year = SUM(@Year-1)

SELECT A.MATTER_ALPHA_NAME,B.FEES_PRD As Fees,A.CLIENT_DIWOR,D.GROUP_NAME
INTO #PFees
FROM MATTER A 
JOIN ON_MAT_FE_PERF B on A.MATTER_DIWOR = B.MATTER_DIWOR
JOIN PM_YEAR C on B.FINYEARDIWOR = C.DIWOR
LEFT JOIN #TMPCLIENT D on D.CLIENT_DIWOR = A.CLIENT_DIWOR
WHERE  C.YEARDESC = @Year and B.PERIOD between 1 and @Period  and B.FEES_PRD <> 0 and D.GROUP_NAME 
is not NULL
order by MATTER_ALPHA_NAME

Select TOP 20
MATTER_ALPHA_NAME,SUM(Fees) As Fees,GROUP_NAME
Into #Charities
FROM #Fees
WHERE GROUP_NAME = 'Charities (Sector)' 
GROUP By MATTER_ALPHA_NAME,GROUP_NAME
ORDER BY Fees Desc

Select TOP 20
MATTER_ALPHA_NAME,SUM(Fees) As Fees,GROUP_NAME
Into #PCharities
FROM #PFees
WHERE GROUP_NAME = 'Charities (Sector)'
GROUP By MATTER_ALPHA_NAME,GROUP_NAME
ORDER BY Fees Desc

SELECT ROUND(SUM(Fees),-3) As Fees
Into #TmpCharC
FROM #Charities 

SELECT ROUND(SUM(Fees),-3) As Fees
Into #TmpCharP
FROM #PCharities

Create Table #Main (Charity int,PCharity int)

insert into #Main (Charity,PCharity)
Select A.Fees,B.Fees 
from #TmpCharC A
CROSS JOIN #TmpCharP B

Select * From #Main

Solution

  • So after a bit more digging around I found the issue.

    Inside Dataset Properties and Parameters the Parameter Name as per the image had not been included for some random reason. Adding this fixed the issue.

    Quick note thanks to Zoher Peled for the tip on Modular arithmetic

    enter image description here