Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trying to use parameters 1

Status
Not open for further replies.

pk400i

MIS
Feb 10, 2009
102
US
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
 
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
 
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
 
Sorry strike that last one there was something else that i had not noticed.
 
Is @StartDate getting set to anything?

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

Code:
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
 
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.
 
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)
 
it's still not actually doing any filtering.
 
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.
 
I don't see where "query parametes" are located, that might be the problem.
 
They aren't explicit. The items in bold define your query parameters:
Code:
where SOP10100.DOCDATE >= [b]@Startdate[/b] and SOP10100.DOCDATE <= [b]@Enddate[/b]
 
In Visual Studio, they are under the "Report" --> "Report Parameters" tab.

--------------------------
Web/.net Programmer & DBA
Central PA
 
I have them defined in that REport Parameter tab. Its set as 'non queried'
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top