I am creating a report using a query where the data source is an Oracle 8i database.
I'm selecting data that is between 2 dates:
"Select * from table where mydate is between :startdate and :stopdate".
To get all of the data I need to use :stopdate + 1 to add a day for oracle SSRS uses dateadd to get the date that oracle requires. SSRS does not like the (+1) and Oracle does not recognize the dateadd function.
How do I solve this problem?
P.S. This is my first SSRS report.
The bottom of this posting contains a simple report that shows how to do this based on a SQL Server data source.
Overall there are several options:
* you can keep your current query commandtext and perform the calculation in the report parameter as shown in the RS 2005 sample below, where the second report parameter is marked as "hidden" and its value is determined based on the first report parameter using an expression: =CDate(Parameters!StartDate.Value).AddDays(1)
* perform the datetime calculation in the commandtext. However in that case you *must* use the Oracle date functions to perform the calculation (lookup your Oracle SQL documentation)
* write a Oracle stored procedure (e.g. in PL/SQL) and call the stored procedure from Reporting Services. The stored procedure has to return the data through one OUT REF cursor (see also this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356162&SiteID=1)
-- Robert
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>95e33366-62c0-4eea-87f0-94d2534377bd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="StartDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=Today</Value>
</Values>
</DefaultValue>
<Prompt>StartDate</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=CDate(Parameters!StartDate.Value).AddDays(1)</Value>
</Values>
</DefaultValue>
<Prompt>EndDate</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox2">
<Left>0.25in</Left>
<Top>0.5in</Top>
<ZIndex>1</ZIndex>
<Width>2.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Parameters!EndDate.Value</Value>
</Textbox>
<Textbox Name="textbox1">
<Left>0.25in</Left>
<Top>0.125in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>2.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Parameters!StartDate.Value</Value>
</Textbox>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>e15b81e3-ab03-4e68-a6b8-0de6d6880ab5</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from orders where OrderDate between @.StartDate and @.EndDate</CommandText>
<QueryParameters>
<QueryParameter Name="@.StartDate">
<Value>=Parameters!StartDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@.EndDate">
<Value>=Parameters!EndDate.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="OrderID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>OrderID</DataField>
</Field>
<Field Name="CustomerID">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CustomerID</DataField>
</Field>
<Field Name="EmployeeID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>EmployeeID</DataField>
</Field>
<Field Name="OrderDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>OrderDate</DataField>
</Field>
<Field Name="RequiredDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>RequiredDate</DataField>
</Field>
<Field Name="ShippedDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>ShippedDate</DataField>
</Field>
<Field Name="ShipVia">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ShipVia</DataField>
</Field>
<Field Name="Freight">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>Freight</DataField>
</Field>
<Field Name="ShipName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipName</DataField>
</Field>
<Field Name="ShipAddress">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipAddress</DataField>
</Field>
<Field Name="ShipCity">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipCity</DataField>
</Field>
<Field Name="ShipRegion">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipRegion</DataField>
</Field>
<Field Name="ShipPostalCode">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipPostalCode</DataField>
</Field>
<Field Name="ShipCountry">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipCountry</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Robert,
Thanks for your response. Options 1 & 2 are feasible since we can only 'read' from the Oracle database.
No comments:
Post a Comment