Date/Time Stamp: reports

Showing posts with label reports. Show all posts

Thursday, March 29, 2012

Date Range Parameter Help

Crystal Reports XI-

I have entered a date range parameter into my report to pick a range for Appointment Dates. When I choose a start date of 01/01/2007 and an end date of 01/31/2007, my report has all of January's appointments, but also has the last appointment of the previous month (in this case December). The same happens if I pick a date range from Feb 1st to the 28th. I get all of Feb appointments, but also the last day in January.

Am I missing something? Or has this happened to anyone else?Are you wanting the dates between your 2 date paramenters, and if so did you reference your date ranges in the record selection?

{table.appointmentdate} In {?StartDate} To {EndDate}

This will give you everything between the 2 dates.

GJ

Tuesday, March 27, 2012

Date Query Parameter

Hi There,

I've only just starting working with RSS2005. I've started designing basic reports that pulls info from our HEAT DB (incident/change logging system).

One of the report requirements is to be able to pull reports for calls that were logged for specific day/date, using the calllog.recvddate field in the HEAT DB.

What i would like to know is how do i do this ? i.e. how do i setup the date query parameter so that the user can select a date from the calender picker that returns all calls logged for the selected date ? How do i link the date parameter to the calllog.recdvddate field ?

Thanks,

RSS2005 NOOB

Hello,

Here's what you need to do.

Select 'Report Parameters' from the Report menu. Click 'Add', give the parameter a name (ex. DateParam), change the data type to 'DateTime', and hit OK. Go to your query from the Data view, and in your where clause, add calllog.recvddate = @.DateParam. If your recvddate has the time stored as well, you'll need to add this instead: convert(varchar, calllog.recvddate, 101) = @.DateParam.

Now when you preview your report, you will see a parameter section at the top of your report, pick your date and select 'View Report'.

Hope this helps.

Jarret

|||

Thanks so much for the Reply Jarret, i tried what u said and it apprears to work. The call totals correspond to those in the DB .... UBBER YAY !!!!!

Can't believe how close i was yet so far at the same time !

Date problem in Selection formula

Hi,
I am using Crystal Reports 9 and i am facing some problem in creating a selection formula to select some records in between a range of given dates.

MY DATE PARAMETER IS A STRING AND I NEED TO CONVERT IT TO DATE TYPE

Actually I don't know how to make to needed selection formula. What i really needed is to select record sets in between a range of dates which is given as parameters to the reports

Pls help
JOBINuse record selection formula

urfield >=dateparameter1 and urfield<=dateparameter2

Sunday, March 25, 2012

Date picker controls - anyway to limit date range

Have seen other questions here about modifying date pickers supplied by reports created in BIDS. The answer is usually NO. But this does not involve a format change, simply want to limit say to a specific year.

Any ideas?

I do not know of a way as how we can limit to a particular daterange other than forcing the user with a messge after they run the report.

It will be good to know if there is a way to do this.

|||

Seems to me not a big deal for MS SSRS team to add this in the parameter manager. After all, you can set defaults, why not a default range?

Add to wishlist?

|||

DevDells wrote:

Have seen other questions here about modifying date pickers supplied by reports created in BIDS. The answer is usually NO. But this does not involve a format change, simply want to limit say to a specific year.

Any ideas?

If you develop a front end for the reports using Visual Studio 2005, this is not very difficult.

date parmeter converts into drop down

I am working in SSRS 2005. I have three parameters on the reports

Parmeter 1 is Date filter which is drop down and values are MTD,QTD,YTD...

2nd and 3rd parameters are fromdate and todate which are datetime parameters.

When user select let's say MTD from parameter1 then i have Stored procedure which populate the fromdate paramter with 1st date of the month and, todate populate the todays's date. The problem i am facing is the moment these date parameters get populated it converts into drop down. i want these date parameters should be still datetime so user can select the date. The value i am passing to these parameters are datetime type( Now()) .Still the date parameters controls are showing as dropdown. I don't know how to handle it. Please help me if you have faced this kind of problem.

Thanks,

Ranjay

Dropdown appears because parameter's "available values" option is set. In this case, it is not possible to enter value. Try to set default value instead of available values.|||Lev,thanks for your reply. I know that it will work for default but i want to populate the date parameters based on some other parameters selcted in report. Default value just shows one time. it doesn't change the value next time when i change the other parameter.|||Default value can be query based.|||

That's true but problem with default value is it's not getting refreshed when my other parameter change. Default value is set only the first time when it initialize. I don't know if i was able to explain the problem properly

|||

Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097549&SiteID=1

date parmeter converts into drop down

I am working in SSRS 2005. I have three parameters on the reports

Parmeter 1 is Date filter which is drop down and values are MTD,QTD,YTD...

2nd and 3rd parameters are fromdate and todate which are datetime parameters.

Thanks,

Ranjay

|||

Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097549&SiteID=1

sql

Thursday, March 22, 2012

Date Parameters

Hello,
I have some Reporting Services users who want to setup subscriptions for
reports and enter the current month, trailing 7 days, this week, etc. in the
start and end date parameter fields. Can they do this with code (i.e.
currentdate(-7)) or does this have to be setup as a schedule on the server?
Any assistance is appreciated
Thank you
David Mahler
dmahler@.superwarehouse.comDear David,
I am not sure if I understand your question well, but if you need to
address last week, you may use sql like
select ...
from ...
where date between dateadd(day,-7,getdate()) and getdate().
getdate() will give you current date-time, and dateadd will shift back
7 days.
See dateadd() for more help.
Best Regards,
leecz
On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
Mah...@.discussions.microsoft.com> wrote:
> Hello,
> I have some Reporting Services users who want to setup subscriptions for
> reports and enter the current month, trailing 7 days, this week, etc. in t=he
> start and end date parameter fields. Can they do this with code (i.e.
> currentdate(-7)) or does this have to be setup as a schedule on the server=?
> Any assistance is appreciated
> Thank you
> David Mahler
> dmah...@.superwarehouse.com|||give an exampl as how you would like to see the subscription..
On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> Dear David,
> I am not sure if I understand your question well, but if you need to
> address last week, you may use sql like
> select ...
> from ...
> where date between dateadd(day,-7,getdate()) and getdate().
> getdate() will give you current date-time, and dateadd will shift back
> 7 days.
> See dateadd() for more help.
> Best Regards,
> leecz
> On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
>
> Mah...@.discussions.microsoft.com> wrote:
> > Hello,
> > I have some Reporting Services users who want to setup subscriptions for=
> > reports and enter the current month, trailing 7 days, this week, etc. in= the
> > start and end date parameter fields. Can they do this with code (i.e.
> > currentdate(-7)) or does this have to be setup as a schedule on the serv=er?
> > Any assistance is appreciated
> > Thank you
> > David Mahler
> > dmah...@.superwarehouse.com- Hide quoted text -
> - Show quoted text -|||Thanks for your replies. I apologize for not being clear in my description.
I am referrring to the front end web interface where users can setup their
own subscriptions.
The user creates a new subscription for a report. The select the delivery
method, how often the report runs, and other parrameters. When they enter
the Start Date and End Date (might be with the calendar control), could they
enter a getdate(-7) or something like that to run the report for the current
month, trailing week, etc.
My thought is that this may have to be done on the back end. I would hope
that the user can set the report to run every week and get the current week's
data on an automated basis.
Thanks for your help
David
"Sridar K" wrote:
> give an exampl as how you would like to see the subscription..
> On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> > Dear David,
> > I am not sure if I understand your question well, but if you need to
> > address last week, you may use sql like
> > select ...
> > from ...
> > where date between dateadd(day,-7,getdate()) and getdate().
> > getdate() will give you current date-time, and dateadd will shift back
> > 7 days.
> > See dateadd() for more help.
> >
> > Best Regards,
> > leecz
> >
> > On 1æ'4æ?¥, ä¸?å'1æ?¶53å', David Mahler <David
> >
> >
> >
> > Mah...@.discussions.microsoft.com> wrote:
> > > Hello,
> >
> > > I have some Reporting Services users who want to setup subscriptions for
> > > reports and enter the current month, trailing 7 days, this week, etc. in the
> > > start and end date parameter fields. Can they do this with code (i.e.
> > > currentdate(-7)) or does this have to be setup as a schedule on the server?
> >
> > > Any assistance is appreciated
> >
> > > Thank you
> >
> > > David Mahler
> > > dmah...@.superwarehouse.com- Hide quoted text -
> >
> > - Show quoted text -
>

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

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

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

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

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...?

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

|||

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.

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,

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.

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 29, 2012

Date Range Parameter Help

Tuesday, March 27, 2012

Date Query Parameter

Date problem in Selection formula

Sunday, March 25, 2012

Date picker controls - anyway to limit date range

date parmeter converts into drop down

date parmeter converts into drop down

Thursday, March 22, 2012

Date Parameters

date parameter issue

date parameter issue

Date/Time Stamp

Blog Archive

About Me