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;