Trying to use parameters 1

[pk400i]

I set up parameters for dates on the

where SOP10100.DOCDATE >= @Startdate and SOP10100.DOCDATE <= @Enddate

but am getting an error message: Must declare the scalar varibale @Startdate

 

[bburnell]

In SQL, you must declare any variables you are using like so:
DECLARE @StartDate NVARCHAR(12)

and if I'm not mistaken, if it's declared as a variable in the report, the format is:
FormatDateTime(Parameters!Start_Date.Value, DateFormat.ShortDate)

or something like that. I pass all my parameters into stored procedures and pass the data back that way so I can change the SQL without changing the actual report.

Hope that helps!
Brett

--------------------------
Web/.net Programmer & DBA
Central PA

 

[pk400i]

I put this simple declare and comparison but it doesn't work

DECLARE @StartDate NVARCHAR(15)
SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' as source


FROM ((((PBS.dbo.SOP10200
SOP10200 INNER JOIN PBS.dbo.IV00101 IV00101 ON
SOP10200.ITEMNMBR=IV00101.ITEMNMBR)

INNER JOIN
PBS.dbo.SOP10100 SOP10100 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE))

INNER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)

INNER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)

INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP10100.CUSTNMBR=RM00101.CUSTNMBR

where SOP10100.DOCDATE >= @StartDate

 

[pk400i]

Sorry strike that last one there was something else that i had not noticed.

 

[bburnell]

Is @StartDate getting set to anything?

To set it to a value, say today's date, you would do the following:

DECLARE @StartDate DATETIME
SET @StartDate = GETDATE()

You also need to make sure that SOP10100.DOCDATE is DATETIME or StartDate is the same type.

--------------------------
Web/.net Programmer & DBA
Central PA

 

[RiverGuy]

Did you use the correct data access provider? Using SQL Server as a source should allow you to add parameters which you will be prompted for when you execute the query and which will automatically map to your parameters if you've used the wizard. There should be no need to use the DECLARE statement. My guess is that you just don't have the parameters defined in your report itself.

 

[pk400i]

I defined then using the Report tab where it says "Report Parameters" but previously I had done the parameters a different way because I did not have this issue. (Unfortunately I was cleaning up and deleted the report that had been working ok)

 

[pk400i]

it's still not actually doing any filtering.

 

[RiverGuy]

Does the name of the "report parameter" match the name of the "query parameter"?

You can also use "report parameters" with a query without parameters, utilizing the report parameters to filter a table or matrix or chart, but of course, that isn't usually recommended.

 

[pk400i]

I don't see where "query parametes" are located, that might be the problem.

 

[RiverGuy]

They aren't explicit. The items in bold define your query parameters:

where SOP10100.DOCDATE >= [b]@Startdate[/b] and SOP10100.DOCDATE <= [b]@Enddate[/b]

 

[bburnell]

In Visual Studio, they are under the "Report" --> "Report Parameters" tab.

--------------------------
Web/.net Programmer & DBA
Central PA

 

[pk400i]

I have them defined in that REport Parameter tab. Its set as 'non queried'

 

[RiverGuy]

If you have a parameter in your query named @Startdate, and a parameter in your report parameters names Startdate, it should map it correctly. If your report parameter is named Sdate, it won't work.