sql server reporting services: trouble with access query -- sql server reporting services

You're in the

sql server reporting services

group:

trouble with access query

ana9
12/29/2006 5:14:52 PM

sql server reporting services: I have the following query in an access database that works just fine:

SELECT [SALES ORDER].ACCOUNTMANAGER, [SALES ORDER].ORDERAMOUNT, Sum([SALES
ORDER]![PRODREVENUE]+[SALES ORDER]![SVCREVENUE]+[SALES ORDER]![TRREVENUE]) AS
TTLREV, Sum([SALES ORDER]![PRODMARGIN]+[SALES ORDER]![SVCMARGIN]+[SALES ORDER]
![TRMARGIN]) AS TTLMARG, [SALES ORDER].[SO#]
FROM [SALES ORDER]
GROUP BY [SALES ORDER].ACCOUNTMANAGER, [SALES ORDER].ORDERAMOUNT, [SALES
ORDER].[SO#], [SALES ORDER].ACCOUNTMANAGER, [SALES ORDER].DATE
HAVING ((([SALES ORDER].DATE) Between [Forms]![REPORTS MENU]![Report Start
Date] And [Forms]![REPORTS MENU]![Report End Date]))
ORDER BY [SALES ORDER].ACCOUNTMANAGER;

I need to port this over to reporting services however, if I create two
parameters for the [Report Start Date] and [Report End Date] called
@startdate and @enddate reporting services cannot parse the query text. I
can't keep the original parameters because there is no form for the user to
fill out. Is there any way to refer to parameters that I create in the
report?

I am trying to do something like
WHERE ([DATE] BETWEEN @startdate AND @enddate)

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200612/1

Re: trouble with access query

Bruce L-C [MVP]
12/29/2006 9:11:13 PM

I assume you are using ODBC to get to Access? You cannot used named
parameters (for instance @enddate). You have to use unnamed parameters. Each
place you put a parameter below put a question mark. RS will automatically
create report parameters for you. I typically rename them (in layout go to
menu Report-> Report Parameter ... or something like that). Rename them.
Then go back to the dataset and click on the ... and go to the parameters
tab and remap them. Note that this list is positional so you need to keep
track of which question mark goes with which parameter.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: trouble with access query

ana9 via SQLMonster.com
1/2/2007 1:54:47 PM

First off, thank you for your help. I am not exactly sure what you mean by
using a question mark. I tried replacing the @ with ?, and just leaving ?'s
in for all the parameters, to no avail. What is the syntax I should be using?

And yes, I am connecting with ODBC.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200701/1

Re: trouble with access query

ana9 via SQLMonster.com
1/2/2007 3:50:46 PM

I got it. You can't use BETWEEN. I had to use >= ? and < ? in the criteria
pane.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200701/1

Re: trouble with access query

Bruce L-C [MVP]
1/2/2007 10:11:02 PM

When you use ODBC you use unnamed parameters. You query should look like
this:

select * from sometable where somefield >= ? and somefield < ?

The first question mark would be your start date and the second question
mark should be your end date. RS will automatically create report parameters
for these. In layout mode you will want to go to the menu Report-> Report
parameters and click on each parameter and change the data type to datetime.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: trouble with access query

Bruce L-C [MVP]
1/2/2007 11:18:11 PM

I am pretty sure that if you go to generic query designer (the button to
switch to this mode is to the right of the ...) that you can use between.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

merge 2 datasets in reposrting services

debi
2/15/2007 3:00:39 AM

how can i do to inform to first dataset that the parameter is a column in second dataset?

EggHeadCafe.com - .NET Developer Portal of Choice

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn