Date/Time Stamp: date parameter issue

hi all

We are a multi site / multi language company so do not want to hardcode the date format - these reports will be on intranet

Using MS Sql - Reporting Services 2005

Using no formatting on fields = yyyy-mm-dd - is how it is displayed.

I added a @.startdate and an @.enddate parameter

I read up and saw this information:

Report properties set

'Language' setting to '=User!Language' to detect user
culture automatically

My regional settings are set to English (New Zealand)

but whatever I do I receive no data - (data is definately there eg when run without parameters) so it is obviously the dates incorrect.

Can someone give me some advice on what else to check.

hi there all

I have not solved this problem but maybe if I explain a little more, someone may be able to help

I am using the BI Interface and adding the parameters like this"

Report properties set

'Language' setting to '=User!Language' to detect user
culture automatically

My regional settings are set to English (New Zealand)

on Data Tab

SELECT CallID, CompanyName, Application, RecvdDate, ClosedBy, ClosedDate, CallStatus
FROM CallLog
WHERE ('WHERE RecvdDate' BETWEEN @.StartDate AND @.EndDate) AND (CallStatus = 'closed')
ORDER BY CallID DESC

so my code on right clicking my report - portion of date parameters reads like this:

<ReportParameters>
<ReportParameter Name="StartDate">
<DataType>DateTime</DataType>
<Prompt>StartDate</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate">
<DataType>DateTime</DataType>
<Prompt>EndDate</Prompt>
</ReportParameter>
</ReportParameters>

so I need to verify what my parameter settings on the report should be:

my Data type = DateTime
what should my available values be:
what should my default values be:

cheers

jewel

|||

hi there

this is the next thing I have tried

can someone let me know if I am on the right track

SELECT CallID, CompanyName, Application, RecvdDate, ClosedBy, ClosedDate, CallStatus
FROM CallLog
WHERE (CallStatus = 'closed') AND (RecvdDate BETWEEN CONVERT(VARCHAR(10), @.StartDate, 23) AND CONVERT(VARCHAR(10), @.EndDate, 23))
ORDER BY CallID DESC

this works fine in the Data Tab

My report Parameters are set in the Parameter window as datetime

as I am under the impression I am converting from character to date

But when I run the preview

error = 'the value provided for the report paramet EndDate is not valid for it's type'

thanks

jewel

|||

SQL :

SELECT .... FROM .... WHERE (MONTH(DATATABLE.[Start Date]) <= @.MONTHCHECK) AND (YEAR(DATATABLE.[Start Date]) <= @.YEARCHECK)

=====================================

Report Parameter (Just example.):

MONTHCHECK=iif(month(now())=1,12,month(now())-1)

YEARCHECK==iif(month(now())=1,year(now())-1,year(now()))

=====================================

I think it's not good way to solve ur problem but it helped me when i use date value in report parameter.

|||

thanks psychosisking but this did not solve my problem.

does anyone have any ideas?

|||

Have you tried the following...?

SELECT CallID,
CompanyName,
Application,
RecvdDate,
ClosedBy,
ClosedDate,
CallStatus
FROM CallLog
WHERE (RecvdDate >= @.StartDate AND RecvdDate <= @.EndDate)
AND (CallStatus = 'closed')
ORDER BY CallID DESC

When I used BETWEEN in an SQL Statement, I got no rows, but using the above works Ok.

|||

Is that reporting agasinst a HEAT database? If so, I've found (at least in our version) that the received date/time is actually stored as a string. I believe RS passes the date through as a string. The result is a string comparison.

We had to use date part functions, string functions, and convert (to datetime) to get our filters to work against this (string) database field.

Let me know if that helps.

Regards,

Dan

|||

thanks Jon - yes I can get data if I key the date in like you mentioned (can even get data if I use the between) but that doesn't really solve what I am trying to do. But thanks heaps for the input

Dan - absolutely correct it is a Heat Database.

I particular want the customer to use the calendar control to choose their from and to dates if possible - are you able to give me some more info on the functions you used?

so I think I should be converting the string somehow - want to try and keep this simple lol

cheers

Dianne

|||

Dianne,

If you are using 2005 (I see above you are), just make the parameter type (Report Menu - > Report Parameters) datetime and the calendar will appear.

If your date is in the same format as our HEAT database, it is like 2002-12-19 (Dec 19, 2002). I was able to use a convert(datetime, recvd_date) to filter the records. This forces the dbms to do a date comparison against the startdate and end date you pass to the it. Try the following:

SELECT CallID, CompanyName, Application, convert(datetime, RecvdDate), ClosedBy, ClosedDate, CallStatus
FROM CallLog
WHERE convert(datetime, RecvdDate) BETWEEN @.StartDate AND @.EndDate) AND (CallStatus = 'closed')
ORDER BY CallID DESC

Other variations of the queries above will also probably work (the convert(datetime, recvd_date) >= @.start and convert(datetime, recvd_date) <= @.start), but you still have to add the conversion of recvd_date to do a date comparison.

The above may not actually work if it is unrelated to the HEAT data problem we had. It could be the language & date format, but i figured I would let you know about my experience.

Let me know...

Dan

|||

thanks Dan

I think it's probably a combination lol

our dates definately are a string and come in the format you mentioned 2006-10-01

in the Data tab things are getting greyed out with the

error in Where clause near 'CONVERT'

Unable to parse query text.

so with your one - are the users using the calendar control (not just keying in the dates)?

cheers for your help

Dianne

|||

There could be some data issues. if not all of the date values in that string field are a valid date format, you may have a problem converting).

I would suggest try getting the query to work in a sql client (query analyzer/Management Studio) to determine the syntax issues first.

If you can't figure it out, you can paste the full xml of the report into here and I will run against my heat database.

As far as the calendars go, yes, through the report manager web page, the calendar displays when the parameter type is datetime.

Regards,

Dan

|||

thanks for your help Dan

If I use the calendar displays and choose the dates I get the error

report parameter 'EndDate' is not valid for its type

If I type in it works ok only if I type 2006-10-01 it in like this

so I will post the xml like you said because I am clutching at straws a little to understand this.

thanks

<?xml version="1.0" encoding="utf-8"?>

<DataSourceReference>HeatTest</DataSourceReference>

<rd:DataSourceID>4a16dd31-779c-4773-b653-92a1c7a31be6</rd:DataSourceID>

</DataSource>

</DataSources>

<DataType>DateTime</DataType>

<Prompt>StartDate</Prompt>

</ReportParameter>

<DataType>DateTime</DataType>

<Prompt>EndDate</Prompt>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Position>RightCenter</Position>

</Legend>

<Subtype>Plain</Subtype>

<Axis>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

</MajorGridLines>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

</MinorGridLines>

<MajorTickMarks>Outside</MajorTickMarks>

</Axis>

</CategoryAxis>

<Shading>Simple</Shading>

</ThreeDProperties>

<DataSetName>Company</DataSetName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightGrey</BackgroundColor>

</Style>

</PlotArea>

<Axis>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

</MajorGridLines>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

</MinorGridLines>

<MajorTickMarks>Outside</MajorTickMarks>

<Max>=Max(Fields!CallID.Value, "Company")</Max>

</Axis>

</ValueAxis>

<Type>Column</Type>

<GroupExpression>=Fields!Application.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<Label>=Fields!Application.Value</Label>

</DynamicCategories>

</CategoryGrouping>

</CategoryGroupings>

<Palette>Default</Palette>

<Value>=Count(Fields!CallID.Value)</Value>

</DataValue>

</DataValues>

</DataPoint>

</DataPoints>

</ChartSeries>

</ChartData>

<Style>

<BackgroundColor>White</BackgroundColor>

</Style>

</Chart>

<Sizing>AutoSize</Sizing>

<Source>External</Source>

<Value>atlaslogo.gif</Value>

</Image>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

<TextAlign>Center</TextAlign>

</Style>

<Value>Closed Calls by Company by Application</Value>

</Textbox>

<rd:DefaultName>textbox10</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox11</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>Total</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox12</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>=Count(Fields!CallID.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

<rd:DefaultName>textbox6</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox7</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox8</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<DataSetName>Company</DataSetName>

<rd:DefaultName>CompanyName</rd:DefaultName>

<Style>

<Default>Ridge</Default>

</BorderStyle>

</Style>

<Value>=Fields!CompanyName.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>Application</rd:DefaultName>

<Style>

<Default>Ridge</Default>

</BorderStyle>

</Style>

<Value>=Fields!Application.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox4</rd:DefaultName>

<Style>

<Default>Ridge</Default>

</BorderStyle>

</Style>

<Value>=Count(Fields!CallID.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<SortExpression>=Fields!CompanyName.Value</SortExpression>

<Direction>Descending</Direction>

</SortBy>

</Sorting>

<Label>=Fields!CompanyName.Value</Label>

<GroupExpression>=Fields!CompanyName.Value</GroupExpression>

<GroupExpression>=Fields!Application.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</TableGroup>

</TableGroups>

<rd:DefaultName>textbox2</rd:DefaultName>

<Style>

<TextAlign>Center</TextAlign>

<BackgroundColor>LightSteelBlue</BackgroundColor>

</Style>

<Value>Company</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

<TextAlign>Center</TextAlign>

<BackgroundColor>LightSteelBlue</BackgroundColor>

</Style>

<Value>Application</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox5</rd:DefaultName>

<Style>

<Default>Ridge</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<BackgroundColor>LightSteelBlue</BackgroundColor>

</Style>

<Value>No of Calls</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

</Body>

<rd:ReportID>2dc52e67-b495-4d63-95f7-15d55ab64e33</rd:ReportID>

<Query>

<CommandText>SELECT CallID, CompanyName, Application, RecvdDate, ClosedBy, ClosedDate, CallStatus

FROM CallLog

WHERE (CallStatus = 'closed') AND (RecvdDate BETWEEN @.StartDate AND @.EndDate)

ORDER BY CallID DESC</CommandText>

<Value>=Parameters!StartDate.Value</Value>

</QueryParameter>

<Value>=Parameters!EndDate.Value</Value>

</QueryParameter>

</QueryParameters>

<DataSourceName>HeatTest</DataSourceName>

</Query>

<rd:TypeName>System.String</rd:TypeName>

<DataField>CallID</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>CompanyName</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>Application</DataField>

</Field>

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>RecvdDate</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>ClosedBy</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>ClosedDate</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>CallStatus</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Language>=User!Language</Language>

</PageFooter>

</Report>

|||

Hi,

It's just a thought but may be helpful. Recently I struggled with the various date format as well (unfortunately it was SQL RS 2000, but I think it may apply to SQL RS 2005 as well). The thing that solved my problem was client browser settings.

My server has English - United states locale settings which is a standard in my company. Unfortunately (as I work for an international company) different users have their own local settings with different date format. The solution to my problem occured to be very simple.

Every client that connects to my report server had to modify its internet browser settings: In IE, go to 'Internet Options' -> 'General' Tab -> 'Languages' button. In the dialog that , make sure that English - United States settings were set at the top of the list. Then the reporting services stopped generating invalid date error.

I think the problem is that the client date format needs to be the same as server's.

I'm not sure if it applies to your case, if not then just ignore it. Hope you will be able to resolve your issue

Cheers,
Wojtek

|||

Looks like you have (had) two problems.

Problem 1: Your HEAT database is storing dates as a string, therefore, your where clause isn't working correctly. This should be corrected by the suggestion above (of converting the string date to a datetime). I did this in your report (pasted below).

Problem 2: As Wojtek stated, this error you're getting now is related to culture. I took your report, deployed it to our server (against our data source), changed my IE settings to be English New Zealand (en-nz), and I couldn't replicate your problem. Unfortunately, I've not worked with culture problems up to this point.

Maybe, if you make the report's language be something static (like en-nz), maybe your calendar control will know what format to pass in without having users change IE (unless there's a specific reason you want to use that language setting). When my browser was set to en-nz, i was able to type the date in two ways (2006-1-1 or 1/1/2006) and either worked. I was also able to use the calendar to pick dates.

See of either Wojtek's solution or changing the report language helps any.

Dan

<?xml version="1.0" encoding="utf-8"?>

<DataSourceReference>SYBARIS_HEAT</DataSourceReference>

<rd:DataSourceID>24753e48-d98c-47c2-bbe9-4aa245698594</rd:DataSourceID>

</DataSource>

</DataSources>

<DataType>DateTime</DataType>

<Prompt>StartDate</Prompt>

</ReportParameter>

<DataType>DateTime</DataType>

<Prompt>EndDate</Prompt>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Position>RightCenter</Position>

</Legend>

<Subtype>Plain</Subtype>

<Axis>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

</MajorGridLines>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

</MinorGridLines>

<MajorTickMarks>Outside</MajorTickMarks>

</Axis>

</CategoryAxis>

<Shading>Simple</Shading>

</ThreeDProperties>

<DataSetName>Company</DataSetName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightGrey</BackgroundColor>

</Style>

</PlotArea>

<Axis>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

</MajorGridLines>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

</MinorGridLines>

<MajorTickMarks>Outside</MajorTickMarks>

<Max>=Max(Fields!CallID.Value, "Company")</Max>

</Axis>

</ValueAxis>

<Type>Column</Type>

<GroupExpression>=Fields!Application.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<Label>=Fields!Application.Value</Label>

</DynamicCategories>

</CategoryGrouping>

</CategoryGroupings>

<Palette>Default</Palette>

<Value>=Count(Fields!CallID.Value)</Value>

</DataValue>

</DataValues>

</DataPoint>

</DataPoints>

</ChartSeries>

</ChartData>

<Style>

<BackgroundColor>White</BackgroundColor>

</Style>

</Chart>

<Sizing>AutoSize</Sizing>

<Source>External</Source>

<Value>atlaslogo.gif</Value>

</Image>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

<TextAlign>Center</TextAlign>

</Style>

<Value>Closed Calls by Company by Application</Value>

</Textbox>

<rd:DefaultName>textbox10</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox11</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>Total</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox12</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>=Count(Fields!CallID.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

<rd:DefaultName>textbox6</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox7</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox8</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<DataSetName>Company</DataSetName>

<rd:DefaultName>CompanyName</rd:DefaultName>

<Style>

<Default>Ridge</Default>

</BorderStyle>

</Style>

<Value>=Fields!CompanyName.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>Application</rd:DefaultName>

<Style>

<Default>Ridge</Default>

</BorderStyle>

</Style>

<Value>=Fields!Application.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox4</rd:DefaultName>

<Style>

<Default>Ridge</Default>

</BorderStyle>

</Style>

<Value>=Count(Fields!CallID.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<SortExpression>=Fields!CompanyName.Value</SortExpression>

<Direction>Descending</Direction>

</SortBy>

</Sorting>

<Label>=Fields!CompanyName.Value</Label>

<GroupExpression>=Fields!CompanyName.Value</GroupExpression>

<GroupExpression>=Fields!Application.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</TableGroup>

</TableGroups>

<rd:DefaultName>textbox2</rd:DefaultName>

<Style>

<TextAlign>Center</TextAlign>

<BackgroundColor>LightSteelBlue</BackgroundColor>

</Style>

<Value>Company</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

<TextAlign>Center</TextAlign>

<BackgroundColor>LightSteelBlue</BackgroundColor>

</Style>

<Value>Application</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox5</rd:DefaultName>

<Style>

<Default>Ridge</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<BackgroundColor>LightSteelBlue</BackgroundColor>

</Style>

<Value>No of Calls</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

</Body>

<rd:ReportID>2dc52e67-b495-4d63-95f7-15d55ab64e33</rd:ReportID>

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>SELECT CallID,CompanyName, Application, RecvdDate, ClosedBy, ClosedDate, CallStatus

FROM dbo.CallLog

WHERE (CallStatus = 'closed') AND (CONVERT(datetime, RecvdDate) BETWEEN @.StartDate AND @.EndDate)

ORDER BY CallID DESC</CommandText>

<DataSourceName>SYBARIS_HEAT</DataSourceName>

</Query>

<rd:TypeName>System.String</rd:TypeName>

<DataField>CallID</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>CompanyName</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>Application</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>RecvdDate</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>ClosedBy</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>ClosedDate</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>CallStatus</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Language>=User!Language</Language>

</PageFooter>

</Report>

|||

thanks guys for your help

yes I have two issues - which are still the same but I will address separately I think

I will leave the issue of the Internationalisation until later.so have changed the language

on my report to English nz and will address this at a later stage.

1st part is to get the string converted to a date - 2nd part is re the parameter calendar

as per your xml Dan - If I copy this into Query Designer

Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

SELECT CallID,CompanyName, Application, RecvdDate, ClosedBy, ClosedDate, CallStatus

FROM dbo.CallLog

WHERE (CallStatus = 'closed') AND (CONVERT(datetime, RecvdDate) BETWEEN @.StartDate AND @.EndDate)

ORDER BY CallID DESC

my error is:

Msg 137, Level 15, State 2, Line 5 Must declare the scalar variable "@.StartDate".

I get this error when running from Management Studio - just design a query.

so once I can get this sorted

in the Report Parameters - these are date/time - should I have anything in the available values and the default values?

cheers for your help

Dianne

cheers

sql

Thursday, March 22, 2012

date parameter issue

No comments:

Post a Comment

Date/Time Stamp

Blog Archive

About Me