sql server reporting services: Hi, I'm new to Reporting Services:


1) First a question: what's the difference between Views and Stored
Procedures? I am rewriting an Access Database to take it into RS....in
there, I have 4 tables, 1 query and a report that depends on that
query.


2) I need to make a new project in RS. The report wizard has asked me
to Design the Query. Since I am rewriting my Access Database, I already

have the following query from Microsoft Access. But it doesn't work in
RS. I get errors. The errors are the following:
1) There's an error in the query. Line 2. Incorrect Syntax near '=3D'
2) Line 39. Incorrect syntax near 'DateConvert'


The query is below. Please kindly help!!!


SELECT Departments.Department, DESKS.Desk_ID, DESKS.Description,
TRACE.TicketNumber, TRACE.Ticker, TRACE.Cusip, TRACE.Trace,
TRACE.BuySell,
IIf([Amount_Ind]=3D0,[Amount],
IIf([Amount_Ind]=3D1,([Amount]/1=AD0),
IIf([Amount_Ind]=3D2,([Amount]/1=AD00),
IIf([Amount_Ind]=3D3,([Amount]/1=AD000),
IIf([Amount_Ind]=3D4,([Amount]/1=AD0000),
IIf([Amount_Ind]=3D5,([Amount]/1=AD00000),
IIf([Amount_Ind]=3D6,([Amount]/1=AD000000),"PLEASE CHECK")))))))
AS Quantity,


IIf([Price_Ind]=3D0,[Price],
IIf([Price_Ind]=3D1,([Price]/10)=AD,
IIf([Price_Ind]=3D2,([Price]/100=AD),
IIf([Price_Ind]=3D3,([Price]/100=AD0),
IIf([Price_Ind]=3D4,([Price]/100=AD00),
IIf([Price_Ind]=3D5,([Price]/100=AD000),
IIf([Price_Ind]=3D6,([Price]/100=AD0000),
IIf([Price_Ind]=3D7,([Price]/100=AD00000),
IIf([Price_Ind]=3D8,([Price]/100=AD00000),99999)))))))))
AS Price2,
DateConvert([Date]) AS Date2,
TRACE.Time, TRACE.AsOfTime,
DateConvert([AsOfDate]) AS AsOfDate2,
TRACE.CustomerAccount, TRACE.DFA_BrokerCode, TRACE.Status,
TRACE.ConfirmNumber, TRACE.RejectMessage, TRACE.RecType,
TRADE_TYPE.Description,
IIf(TRACE.RecType=3D"XTT",1.5,0) AS [Canceled Trade Fee],
IIf(TRACE.RecType=3D"CTT",1.5,0) AS Corrections,
IIf(TRACE.RecType=3D"PXT",3,
IIf(TRACE.RecType=3D"PCT",3,
IIf(TRACE.Date>TRACE.AsOfDate,=AD3,0)))
AS [Late As Of Rpt Fee],


IIf([Quantity]<201000,0.475,
IIf([Quantity]<1000000,[Quanti=ADty]*0.000002375,2.375))
AS [Trade Report Fee],


[Canceled Trade Fee]+[Corrections]+[Late As Of Rpt Fee]+[Trade Report
Fee] AS [Total Trace Fee]


FROM TRADE_TYPE INNER JOIN ((Departments RIGHT JOIN DESKS ON
Departments.Department =3D DESKS.Department) RIGHT JOIN TRACE ON
DESKS.Bloomber_Desk_ID =3D TRACE.Trader) ON TRADE_TYPE.RecType =3D
TRACE.RecType
WHERE (((DateConvert([Date]))>#2/28/=AD2005# And
(DateConvert([Date]))<#4/1/200=AD5#) AND ((TRACE.ConfirmNumber) Is Not
Null))=20
WITH OWNERACCESS OPTION;

Hi

SQL Books online will help you when trying to convert from Access.
There is no IIF in SQL so you have to convert the Access quer
statement to TSQL that SQL server will understand. The mai
difference between View and Stored Procedure is that a Store
Procedure excepts parameters. Start by simplifying your query an
then get each piece to work, then move on to the next hardest piece.
I have converted and upgraded many Access databases to SQL. You nee
to create a stored proc and find what is causing the error. There i
no IIF in SQL so you need to replace that with something like this

CASE Amount_In
WHEN 0 THEN [Amount
WHEN 1 THEN [Amount]/1
..

EN
AS Quantit

Also the function DateConvert() I believe is a custom function create
in your database. You need to figure out how to convert this to
Stored Procedure or User Defined Function in SQL