December 8, 2008 at 4:57 pm
I am only a newbie to SQL and have been trying various reports. I have writtern in SSRS the following datasets. I am trying to allow the user to select the dates and amounts and accounts that they want data on. It works fine if they only select one account from the Multi-value drop down box but gets an error if they select 2 or more.
The errors message is
An error has occurred during report processing.
Query execution failed for data set 'General_Ledger_Reports'.
Incorrect syntax near ','.
General_Ledger_Reports (dataset)
select * , convert(varchar(10),DATE,103) as TranDate
from Vwgldailytransactions
where (date >= @StartDate) and (date = @MinimumAmt) and (amount <= @MaximumAmt)
and [ac number] = @ac_number
General_Ledger_Number (dataset)
select *
from dbo.VwGLBalances
where [GL Balance] is not null
order by [GL Number]
The screen dump of the report parameters and error message are attached.
Thanking you in advance.
December 8, 2008 at 8:12 pm
When you have a parameter that allows multiple values to be selected, Reporting Services actually returns the selected values as comma separated list. Hence, the error you are getting when using the parameter with multiple value selected.
Try the following instead ...
select * , convert(varchar(10),DATE,103) as TranDate
from Vwgldailytransactions
where (date >= @StartDate) and (date = @MinimumAmt) and (amount <= @MaximumAmt)
and [ac number] in (@ac_number)
December 8, 2008 at 8:15 pm
There are a few problems with your datasets. First, you should never use '*' in production code - it will cause issues for you as soon as someone modifies the view. It is better to specify the columns that you need - and only the columns that you need for the report.
Second, you really should get used to schema qualifying your objects. As you move into SQL Server 2008 and beyond, it is going to be even more important.
And finally, to address the issue you are having - you need to use the IN operator for the multi-value parameter. This might work directly in SSRS, but will not work if you move the code into a stored procedure. If you move the code to a stored procedure, you will need to modify your code to convert the delimited list of parameters to a table and then join to the table.
For now, you can try modifying your dataset to the following:
Select ... -- specify your columns here
,convert(varchar(10),DATE,103) As TranDate
From dbo.Vwgldailytransactions -- schema qualified object
Where [date] >= @StartDate
And [date] = @MinimumAmt -- is this correct? Not sure that is what you want
And amount <= @MaximumAmt
And [ac number] IN (@ac_number) -- Use IN operator here...
Also, you should not use [date] as a column name. That is a reserved word...
If that does not work, you are going to have to use a function to split the parameter into a table. You can search this site for different techniques for working with 'arrays' in SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 8, 2008 at 9:12 pm
To Happycat
Thanks for the advice. Worked a treat.
December 8, 2008 at 9:13 pm
Jeffrey
Manny thans for the advice. Being a newbie these peices of advice are greatly appreciated.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply