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.
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
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