Thursday, March 29, 2012
Date Range Question..
What I need it to do is to give the data that ends at @.To and that starts at
the first day of the month specified in the parameter @.To.
So I have constructed this query
WHERE (CONVERT(char(11), Date, 3) BETWEEN '1/@.TO/@.TO' AND @.TO)
However this doesn't work :(
So I do it this way:
WHERE (CONVERT(char(11), Date) BETWEEN '@.TO 1 @.TO' AND @.TO)
This does work, however the parameter must be entered in the following
format Apr 16 2004 (which is useless to me)
Can anyone out there help?
AshYou might want to use an expression-based command text, where you write a
VB.NET expression which evaluates into a SQL query commandtext. However, you
have to be careful to not mix up SQL and VB.NET syntax. You might want to
checkout this walkthrough in BOL:
http://msdn.microsoft.com/library/en-us/RSAMPLES/htm/rss_tutorials_v1_7qr7.asp
Using an expression-based command text you could then use VB functions like
CDate(), etc. to dynamically construct the dates you need in the query.
There are many options for your case. Assuming you have have two DateTime
report parameters:
="SELECT ... BETWEEN CAST('" & CDate(Parameters!StartDate.Value) & "' as
smalldatetime) AND CAST('" & CDate(Parameters!EndDate.Value) & "' as
smalldatatime))"
You could then initialize these two report parameters with an
expression-based default value that automatically calculates the first and
the last day of the current month:
StartDate:
=DateValue(Month(Today) & " 1," & Year(Today))
EndDate:
=DateValue(Month(Today) & " 1," &
Year(Today)).AddDays(System.DateTime.DaysInMonth(Year(Today),
Month(Today))-1)
Information about DateTime / conversion functions is available on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateAdd.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatediff.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatepart.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AshVsAOD" <.> wrote in message
news:eAxsg90VEHA.2564@.TK2MSFTNGP11.phx.gbl...
> I have a parameter called @.To that utilizes the date field in my
datasource,
> What I need it to do is to give the data that ends at @.To and that starts
at
> the first day of the month specified in the parameter @.To.
> So I have constructed this query
> WHERE (CONVERT(char(11), Date, 3) BETWEEN '1/@.TO/@.TO' AND @.TO)
> However this doesn't work :(
> So I do it this way:
> WHERE (CONVERT(char(11), Date) BETWEEN '@.TO 1 @.TO' AND @.TO)
> This does work, however the parameter must be entered in the following
> format Apr 16 2004 (which is useless to me)
> Can anyone out there help?
> Ash
>|||Thanks for the quick reply!!!
I do not want to use two parameters only one. And sadly I just don't have
the knowledge of VB to construct these expressions. I am basically there
using SQL only code and will continue the battle.
Thanks for your help.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O6lS%23b1VEHA.3428@.TK2MSFTNGP12.phx.gbl...
> You might want to use an expression-based command text, where you write a
> VB.NET expression which evaluates into a SQL query commandtext. However,
you
> have to be careful to not mix up SQL and VB.NET syntax. You might want to
> checkout this walkthrough in BOL:
>
http://msdn.microsoft.com/library/en-us/RSAMPLES/htm/rss_tutorials_v1_7qr7.asp
> Using an expression-based command text you could then use VB functions
like
> CDate(), etc. to dynamically construct the dates you need in the query.
> There are many options for your case. Assuming you have have two DateTime
> report parameters:
> ="SELECT ... BETWEEN CAST('" & CDate(Parameters!StartDate.Value) & "' as
> smalldatetime) AND CAST('" & CDate(Parameters!EndDate.Value) & "' as
> smalldatatime))"
> You could then initialize these two report parameters with an
> expression-based default value that automatically calculates the first and
> the last day of the current month:
> StartDate:
> =DateValue(Month(Today) & " 1," & Year(Today))
> EndDate:
> =DateValue(Month(Today) & " 1," &
> Year(Today)).AddDays(System.DateTime.DaysInMonth(Year(Today),
> Month(Today))-1)
>
> Information about DateTime / conversion functions is available on MSDN:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateAdd.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatediff.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatepart.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "AshVsAOD" <.> wrote in message
> news:eAxsg90VEHA.2564@.TK2MSFTNGP11.phx.gbl...
> > I have a parameter called @.To that utilizes the date field in my
> datasource,
> > What I need it to do is to give the data that ends at @.To and that
starts
> at
> > the first day of the month specified in the parameter @.To.
> >
> > So I have constructed this query
> >
> > WHERE (CONVERT(char(11), Date, 3) BETWEEN '1/@.TO/@.TO' AND @.TO)
> >
> > However this doesn't work :(
> >
> > So I do it this way:
> > WHERE (CONVERT(char(11), Date) BETWEEN '@.TO 1 @.TO' AND @.TO)
> >
> > This does work, however the parameter must be entered in the following
> > format Apr 16 2004 (which is useless to me)
> >
> > Can anyone out there help?
> >
> > Ash
> >
> >
>
Date Range Parameter Help
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
date range parameter
where the user can select today, 1 week, 1 month, 60, 90, etc. days between
current date and need by date. i've created parameters before, but this
one's giving me trouble. any ideas?Hi,
I think I would create 2 parameters like to work with that kind of
requirements
- Datevalue: would hold the value (Number)
- DateType: would hold the parameter type (DropDown) filled with value
like (Days,Weeks,Months,...)
After that i would do the calculation at the stored procedure level
using the T-SQL DATEADD function..
HTH,
Eric|||Create parameters using non-queried and put in the following (as example)
Label Value
Today 0
1 Week 7
30 Days 30
60 Days 60
90 Days 90
Then in your query do this:
select * from whatever where mydatefield < dateadd(dd,?, getdate())
Anyway, gives you an idea of how to do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> i have a need by date in my report. i would like to create a date
parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||In your where clause, you could have a statement such as:
WHERE
MyDate >= DATEADD(DAY, -@.DaysBack, GETDATE())
Then, in your parameter dropdown, you'll specify the number of days that
correlate to each value in the dropdown, e.g. 1 week - 7, etc.
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
>i have a need by date in my report. i would like to create a date
>parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||Thanks for your help, but when I tried to do this I got an error message,
"argument data type datetime is invalid for argument 2 of dateadd function"
My where statement reads:
WHERE (mydate <= DATEDIFF(dd, @.date , GETDATE()))
@.date being the name of my parameter.
"Bruce L-C [MVP]" wrote:
> Create parameters using non-queried and put in the following (as example)
> Label Value
> Today 0
> 1 Week 7
> 30 Days 30
> 60 Days 60
> 90 Days 90
> Then in your query do this:
> select * from whatever where mydatefield < dateadd(dd,?, getdate())
> Anyway, gives you an idea of how to do this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "jmann" <jmann@.discussions.microsoft.com> wrote in message
> news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> > i have a need by date in my report. i would like to create a date
> parameter
> > where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> > current date and need by date. i've created parameters before, but this
> > one's giving me trouble. any ideas?
>
>|||Hi,
After giving it a little more tought, Here is what i would do:
Create 2 parameters
- DateValue (Integer) holding the value
- DateType (Dropdown) holding the parameter type
Label Value
Days 0
Weeks 1
Months 2
...
After that i would do the calculation at the query of stored procedure
level with something like this:
SELECT *
FROM Table1
WHERE Table1.DateField >= CASE
WHEN @.DateType = 0 THEN
DATEADD(dd,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 1 THEN
DATEADD(wk,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 2 THEN
DATEADD(mm,CAST(@.DateNumber AS
INTEGER),GETDATE())
END
HTH,
Eric|||WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))|||This will work, or, just set the parameter type as integer instead of
string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aiwa" <eric__brochu@.hotmail.com> wrote in message
news:1105996187.872921.265900@.f14g2000cwb.googlegroups.com...
> WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))
>
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 !
Sunday, March 25, 2012
Date problem
Hi,
I enter a date in my parameter in the browser like '30/01/2001'. When I press on 'view report' then I get an error. 'An error occurred during local report processing. The value provided for the report parameter 'datum' is not valid for its type.
I think he sees the '30' as a month, so it gives an error. BUT in my SQL-statement i write this : CONVERT(DateTime, @.datum, 103). What's the problem?
Thx
We do validation on parameter values. If you specify the type of this parameter is datetime, it needs to be valid datetime value based on the current culture. You can try changing the parameter type to be string if you don't want the validation.|||Hi
Does any one know how we can validate the input criteria in reporting server 2005? By saying this I mean to say; let say we have to dates “Start date” and “End date” as criteria to one report. How can I check that the Start date must be lower or equal to the End date?
Regards;
Rakesh
Date Problem
I have a problem with date parameter. I have selected data type as date time. I would like the date to be in British format. When I select a date from Calendar control, for example 25 December, then I get a error>
The value provided for the report prameter Startdate is not valid for its type.
Can anyone please help me on this?
regards
Josh
Hello JoshKer.
In Reporting Servies you can define the default Language of the reports, this Language is used for the Date Culture...
To edit him : In Layout Tab / Click on the Yello Part (out of the report) / Properties or F4 / then you accessed the Report Properties...
Alter the Language Propertie, to English (United KingDown)
If this not correct your error, then you can make the CAST on the Date Parameter on the Query....
I Hope Have helped you !
|||See if the problem is dedicated to this entry, if yes, vote for it.https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=271928
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for your reply. But it gives the same problem with the date picker.
Date picker parameter with MDX...
I am sure others have ran into this issue.
But I need to have date parameters using the datetime type and I need to be able to pass the correct value to the MDX.
For those of you that are interested you merely need to format the parameter values coming into your dataset your passing the report params to.
Ex.
Code Snippet
="[COE Date].[Date].&["&Format(Parameters!FromCOEDateDate.Value,"yyyy-MM-dd")+"T00:00:00]"The above example shows the FromCOEDateDate parameter used in the report
Date Picker on Report Parameters
parameter. The report parameter ("From Date") is defined as a date.
Thanks
--
Mike RottmannNo date picker in RS 2000, there is a date picker in RS 2005
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike" <Mrottmann@.micorsoft.com> wrote in message
news:D6D9528D-7CB5-4CBB-8DA8-9C1ACE6EA069@.microsoft.com...
> Is there a way to configure a Date Picker option on a report date type
> parameter. The report parameter ("From Date") is defined as a date.
> Thanks
> --
> Mike Rottmann|||Thanks.
Mike Rottmann
"Bruce L-C [MVP]" wrote:
> No date picker in RS 2000, there is a date picker in RS 2005
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike" <Mrottmann@.micorsoft.com> wrote in message
> news:D6D9528D-7CB5-4CBB-8DA8-9C1ACE6EA069@.microsoft.com...
> > Is there a way to configure a Date Picker option on a report date type
> > parameter. The report parameter ("From Date") is defined as a date.
> >
> > Thanks
> > --
> > Mike Rottmann
>
>
Date Picker formatting
"The value for the report parameter XXX is not valid for its type"
I have a report (RS 2005) that has 2 date parameters, "start" and "end".
My SQL in not very complicated at all, I have a simple WHERE date between @.start and @.end
But when I go to view the report in VS2005, I get the above error.
I am yet to find a decent fix for this, what is going on? the date I am trying is:
"26/02/2007" now, obviously its trying to us the en-US formatting, but im in Australia, so I want en-AU.
I have changed my report Language setting to be en-AU, my local settings in Region Setting is English (Australia). (BTW, I had to change the Language setting in my report through the XML, is there a better way to do this?)
How do I fix this? I have searched an searched but no-one seems to be able to give a clear answer as to what is going on....
Hi,
I think the date/time picker sends the datetime parameter to the SQL query on the server using the regional settings of the client PC, and not the server settings. Then, the server will try to interpret this date using the server Regional Options. Can you specify both Regional Options of client and Reporting Server? If what I'm saying doesn't make sense, please let me know... Because I have users with different local settings, I have not been able to use the datetime picker, because I don't know in advance in what format I will receive it in my query...
Regards, Jeroen
|||I do know what you are saying. Thanks for that, it is what I assumed. I wonder why Microsoft did it this way? Surely it should be on a report by report basis...I did however install the SP2 for SQL 2005 last night and it seemed to fix my problem with my regional settings. Now the datepicker works like it should. But I dont know if it was simply because I had to restart my PC for the update, or not.
Who knows, but either way I am going to have to manipulate the Reporting Servers regional settings. Unless they are already correct (which I am hoping they are).
Thanks for the tips
Date Picker formatting
"The value for the report parameter XXX is not valid for its type"
I have a report (RS 2005) that has 2 date parameters, "start" and "end".
My SQL in not very complicated at all, I have a simple WHERE date between @.start and @.end
But when I go to view the report in VS2005, I get the above error.
I am yet to find a decent fix for this, what is going on? the date I am trying is:
"26/02/2007" now, obviously its trying to us the en-US formatting, but im in Australia, so I want en-AU.
I have changed my report Language setting to be en-AU, my local settings in Region Setting is English (Australia). (BTW, I had to change the Language setting in my report through the XML, is there a better way to do this?)
How do I fix this? I have searched an searched but no-one seems to be able to give a clear answer as to what is going on....
Hi,
I think the date/time picker sends the datetime parameter to the SQL query on the server using the regional settings of the client PC, and not the server settings. Then, the server will try to interpret this date using the server Regional Options. Can you specify both Regional Options of client and Reporting Server? If what I'm saying doesn't make sense, please let me know... Because I have users with different local settings, I have not been able to use the datetime picker, because I don't know in advance in what format I will receive it in my query...
Regards, Jeroen
|||I do know what you are saying. Thanks for that, it is what I assumed. I wonder why Microsoft did it this way? Surely it should be on a report by report basis...I did however install the SP2 for SQL 2005 last night and it seemed to fix my problem with my regional settings. Now the datepicker works like it should. But I dont know if it was simply because I had to restart my PC for the update, or not.
Who knows, but either way I am going to have to manipulate the Reporting Servers regional settings. Unless they are already correct (which I am hoping they are).
Thanks for the tipssql
Thursday, March 22, 2012
Date parameters with annoying and unwanted Timestamp.
report.
But when I enter same date in both date parameter text boxes it appends
the timestamp (12:00:00 AM).
Example: When I enter 07/24/2004 and 07/24/2004 it converts that into
07/24/2004 12:00:00 AM and 07/24/2004 12:00:00 AM.
If I'm generating a report with those parameters I shouldn't get any
data back if I can't take care of that datetime in my stored proc. But
for how many end users do I need to explain and ask them for not to
worry about it when it shows in the front end?
Isn't it really annoying?
Is there anyway to get rid of 12:00:00 AM?The only way is to change the report parameter from date to string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RK" <am_i_dotnet@.hotmail.com> wrote in message
news:1122317815.803912.144810@.o13g2000cwo.googlegroups.com...
>I have two date parameters which will be used as data range in my
> report.
> But when I enter same date in both date parameter text boxes it appends
> the timestamp (12:00:00 AM).
> Example: When I enter 07/24/2004 and 07/24/2004 it converts that into
> 07/24/2004 12:00:00 AM and 07/24/2004 12:00:00 AM.
> If I'm generating a report with those parameters I shouldn't get any
> data back if I can't take care of that datetime in my stored proc. But
> for how many end users do I need to explain and ask them for not to
> worry about it when it shows in the front end?
> Isn't it really annoying?
> Is there anyway to get rid of 12:00:00 AM?
>
Date parameters in ="select.......where creationdate="&...."
I'm looking for the correct syntax for using datetime parameters in the SQL
statement.
The parameter is called ReceiveDate and defaults to =Today()
="select ...... where creationdate = "&.....
Thanks
LudoWhere you are writing the statemnt? In report or in stored procedure ?
If it is in report itself you can do as given belo:
If you declare ReceiveDate as DateTime Type. you can write the sql statement
directly .
Select ...... Where creationdate = @.ReceiveDate
If you Declare it as String then you need to use date conversion function
Select ...... Where creationdate = CDate(@.ReceiveDate)|||I think the issue here is that you are using an expression which is really a
mistake unless there is no other way. If you use the generic query designer
(two pane) then just do this:
select ... where creationdate = @.ReceiveDate
You don't have to do anything special. RS takes care of everything. If you
use an expression then you have to do a lot more (for instance, embed single
quotes).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm looking for the correct syntax for using datetime parameters in the
> SQL statement.
> The parameter is called ReceiveDate and defaults to =Today()
> ="select ...... where creationdate = "&.....
> Thanks
> Ludo
>|||Thanks,
it is in the report (RS 2005). It works when I put the parameter to string
and use query:
select part_number,serial_number from UNIT_STATUS_V where creation_time
between @.StartDate and @.EndDate
creation_time is a DateTime field.
But I'd rather use the parameter as a DateTime as well since then I get a
calender pick control.
I tried using CDate but with no result so far...
Ludo
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>I think the issue here is that you are using an expression which is really
>a mistake unless there is no other way. If you use the generic query
>designer (two pane) then just do this:
> select ... where creationdate = @.ReceiveDate
> You don't have to do anything special. RS takes care of everything. If you
> use an expression then you have to do a lot more (for instance, embed
> single quotes).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>|||Thanks,
it is in the report (RS 2005). It works when I put the parameter to string
and use query:
select part_number,serial_number from UNIT_STATUS_V where creation_time
between @.StartDate and @.EndDate
creation_time is a DateTime field.
But I'd rather use the parameter as a DateTime as well since then I get a
calender pick control.
I tried using CDate but with no result so far...
Ludo
"Rama Prasad" <RamaPrasad@.discussions.microsoft.com> wrote in message
news:827DCC76-9E60-40EF-9065-EBB1D9B4A335@.microsoft.com...
> Where you are writing the statemnt? In report or in stored procedure ?
> If it is in report itself you can do as given belo:
> If you declare ReceiveDate as DateTime Type. you can write the sql
> statement
> directly .
> Select ...... Where creationdate = @.ReceiveDate
> If you Declare it as String then you need to use date conversion function
> Select ...... Where creationdate = CDate(@.ReceiveDate)
>|||This is very odd. RS should be handling this. There is no reason for you to
have to use CDate or anything like that. Try changing your SQL to be like
this:
select part_number,serial_number from UNIT_STATUS_V where creation_time >=@.StartDate and creation_time <= @.EndDate
You definitely should be able to have the parameter be a datetime.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
news:ONBhiqI1FHA.3780@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> it is in the report (RS 2005). It works when I put the parameter to string
> and use query:
> select part_number,serial_number from UNIT_STATUS_V where creation_time
> between @.StartDate and @.EndDate
> creation_time is a DateTime field.
> But I'd rather use the parameter as a DateTime as well since then I get a
> calender pick control.
> I tried using CDate but with no result so far...
> Ludo
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>>I think the issue here is that you are using an expression which is really
>>a mistake unless there is no other way. If you use the generic query
>>designer (two pane) then just do this:
>> select ... where creationdate = @.ReceiveDate
>> You don't have to do anything special. RS takes care of everything. If
>> you use an expression then you have to do a lot more (for instance, embed
>> single quotes).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
>> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>>
>|||Hi again....
thanks for your help so far, I found the problem and I'll try to explain.
My date settings are set to british "dd/MM/yyyy". When I do the preview of
the report the EndDate parameter defaults to =DateAdd("d",-1,Today()) and
the StartDate to =Today(). The readable fields read 19/10/2005 and
20/10/2005 which look fine. When I click on the preview tab the report runs
but when I click on view report the system returns an erro on the date
parameter. Now...when I select a date in the calender picker the readable
fields shows the date correctly like dd/MM/yyyy but the report returns the
same error. But when I select fi 08/08/2005 till 09/08/2005 and I click
view report the report runs and the 2 visible fields previously filled by
the calender picker change to 08/08/2005 till 08/09/2005 so the reports runs
from august 8th till september 8th ...I don't know the reason but when I
deploy the report it seems to run correctly, so the problem only occurs in
the preview....
Could this be a bug in the RS 2005 or do you know some settings I need to
check ?
Anyway many thanks for your help.
Ludo Van Dun
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eMgscQL1FHA.2540@.TK2MSFTNGP09.phx.gbl...
> This is very odd. RS should be handling this. There is no reason for you
> to have to use CDate or anything like that. Try changing your SQL to be
> like this:
> select part_number,serial_number from UNIT_STATUS_V where creation_time >=> @.StartDate and creation_time <= @.EndDate
> You definitely should be able to have the parameter be a datetime.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
> news:ONBhiqI1FHA.3780@.TK2MSFTNGP12.phx.gbl...
>> Thanks,
>> it is in the report (RS 2005). It works when I put the parameter to
>> string and use query:
>> select part_number,serial_number from UNIT_STATUS_V where creation_time
>> between @.StartDate and @.EndDate
>> creation_time is a DateTime field.
>> But I'd rather use the parameter as a DateTime as well since then I get a
>> calender pick control.
>> I tried using CDate but with no result so far...
>> Ludo
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>>I think the issue here is that you are using an expression which is
>>really a mistake unless there is no other way. If you use the generic
>>query designer (two pane) then just do this:
>> select ... where creationdate = @.ReceiveDate
>> You don't have to do anything special. RS takes care of everything. If
>> you use an expression then you have to do a lot more (for instance,
>> embed single quotes).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
>> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>>
>>
>
date parameters
current week
last week
current month
current quarter
I have a dataset with today, monday of current week, current quarter number
and current month number, but i'm at a loss as to how to tie this in with my
report and how to put it into a parameter...any suggestions or referrals
would be most appreciated.Hi,
It is possible to ty values to a labels for a parameter through a query. All
you have to do is to create the dataset with two columns. One with you're
desired options and the other column with the corresponding number. In this
way it is possible to show the user the list of the four option, but use in
your report your own variables.
Jan Pieter Posthuma
"jmann" wrote:
> I need a parameter with the following options:
> current week
> last week
> current month
> current quarter
> I have a dataset with today, monday of current week, current quarter number
> and current month number, but i'm at a loss as to how to tie this in with my
> report and how to put it into a parameter...any suggestions or referrals
> would be most appreciated.|||This logic probably isn't right for your data but the basic idea should be
the same. For simplicity, I'm figuring that current week/month/quarter means
one week/month/quarter ago through today. You'll likely need to figure out
when each period begins using your data and make adjustments.
SELECT * FROM YourTables
WHERE OrderDate BETWEEN
CASE
WHEN @.Period = 'Current Week' THEN DATEADD(Week, -1, GETDATE())
WHEN @.Period = 'Last Week' THEN DATEADD(Week, -2, GETDATE())
WHEN @.Period = 'Current Month' THEN CAST(CAST(MONTH(GETDATE()) AS
VarChar(2)) + '-1-' + CAST(YEAR(GETDATE()) AS VarChar(4)) AS DateTime)
WHEN @.Period = 'Current Quarter' THEN DATEADD(Quarter, -1, GETDATE())
END
AND
CASE
WHEN @.Period = 'Last Week' THEN DATEADD(Week, -1, GETDATE())
ELSE GETDATE()
END
Paul Turley
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:E474B451-652E-405F-B698-D0D1642D907D@.microsoft.com...
>I need a parameter with the following options:
> current week
> last week
> current month
> current quarter
> I have a dataset with today, monday of current week, current quarter
> number
> and current month number, but i'm at a loss as to how to tie this in with
> my
> report and how to put it into a parameter...any suggestions or referrals
> would be most appreciated.sql
Date parameter works in report designer but not when deployed.
called 'Start':
=IIF(DATEPART("d",today()) >= "12",DateSerial(Year(today()),
Month(today()), 1) ,DateSerial(Year(today()), Month(now)-1, 1))
The query then uses the parameter to filter the data so I only get the
transactions I want depending on what day of the month it is 'today',
i.e., 'Date >= @.start'
This works fine in Report Designer in VS. However, it doesn't work once
the report is deployed and I run it through Report Manager - I get all
the transactions.
Anyone know why this is happening? I'm still using SQL2000I see two problems with your expression:
1) DatePart returns an integer not a string
2) With DateSerial(Year(today()), Month(now)-1, 1) what happens when now is
January? What Month is January minus 1?
I suggest you use the following expression:
=Iif( DatePart(DateInterval.Day, Today()>=12, DateSerial(Year(Today()),
Month(Today()),1), DateAdd(DateInterval.Month, -1, DateSerial(Year(Today()),
Month(Today()),1)))
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"paddydog" wrote:
> I use the following expression as the default parameter for a report
> called 'Start':
> =IIF(DATEPART("d",today()) >= "12",DateSerial(Year(today()),
> Month(today()), 1) ,DateSerial(Year(today()), Month(now)-1, 1))
> The query then uses the parameter to filter the data so I only get the
> transactions I want depending on what day of the month it is 'today',
> i.e., 'Date >= @.start'
> This works fine in Report Designer in VS. However, it doesn't work once
> the report is deployed and I run it through Report Manager - I get all
> the transactions.
> Anyone know why this is happening? I'm still using SQL2000
>
Date Parameter without time
Data Type to Datetime.
When I run report I get 22/2/2005 12:00:00 AM in parameter box on
top of report (parameters section of report), which is correct.
But I want to format this to appear and make input like 22/2/2005
ie.(dd/MM/yyyy) and without time part.
Thanks in advanceI use a string for all of my dates and it works fine. In my dataset or
SP that is called, I simply format the date and add 00:00:00 to the
start date, and 23:59:59 to the end date.|||Or you could use a custom code to return a short date string for that
field.
call Code.FormatDate(Fields!data.Value)
Function FormatDate(ByVal date as Object) as String
If IsDate(data) Then
Return CDate(date).ToShortDateString()
Else
Return String.Empty
End If
End Funtion
Date Parameter with ReportViewer
Hi All,
I have the following code which works using the ReportViewer component. However how do I pass in the current date i.e Now?
When I use DateTime.Today.ToString I get the following error: The value provided for the report parameter 'SnapshotDate' is not valid for its type. SnapshoteDate is defined as DateTime.
If Not IsPostBack Then
ReportViewer1.ServerUrl = "http://devnet2/ReportServer"
ReportViewer1.ReportPath = "%2fCorVuReports/LAA/Pie&SnapshotDate=06/01/2004"
ReportViewer1.Toolbar = Microsoft.Samples.ReportingServices.ReportViewer.multiState.False
ReportViewer1.Zoom = "75"
End If
I managed to solve this...
cheers
|||Could you please post your resolution, so others (like us) who encounter the problem could benefit from your wisdom.
Much appreciated.
Date Parameter used in filter
solution to this and it is repetition.
I am trying to set a filter for my table that compares a date from my data
to a date parameter.
I have no problem getting the date parameter with the new funky date picker,
but when I select a date it returns in US format mm/dd/yyyy but my data is in
dd/mm/yyyy. This then causes the report to fall over "value for the report
parameter is not valid".
I would like to maintain the date in the format dd/mm/yyyy for the users
benefit (UK users) but would also like to keep the date picker.
When I pick a date that is the same month and date eg 10/10/2005 all works
fine.
Thanks for the HelpHi,
did you get any resolution for this? I'm having the same issue
thanks
Matt
"Are friends electric?" wrote:
> Hi sorry if trolling through the other postings that I have not found a
> solution to this and it is repetition.
> I am trying to set a filter for my table that compares a date from my data
> to a date parameter.
> I have no problem getting the date parameter with the new funky date picker,
> but when I select a date it returns in US format mm/dd/yyyy but my data is in
> dd/mm/yyyy. This then causes the report to fall over "value for the report
> parameter is not valid".
> I would like to maintain the date in the format dd/mm/yyyy for the users
> benefit (UK users) but would also like to keep the date picker.
> When I pick a date that is the same month and date eg 10/10/2005 all works
> fine.
> Thanks for the Help|||Hi Matt
I did not find a solution on the report designer but when I deploy the
report and run it in explorer it works fine. I suppose this is a solution,
just a pain when trying to work on the design of the report.
Not that I am a fundi in any shape or form but if you need help on this I
can let you know how I set it up.
Steve
"BlackDuck" wrote:
> Hi,
> did you get any resolution for this? I'm having the same issue
> thanks
> Matt
> "Are friends electric?" wrote:
> > Hi sorry if trolling through the other postings that I have not found a
> > solution to this and it is repetition.
> >
> > I am trying to set a filter for my table that compares a date from my data
> > to a date parameter.
> >
> > I have no problem getting the date parameter with the new funky date picker,
> > but when I select a date it returns in US format mm/dd/yyyy but my data is in
> > dd/mm/yyyy. This then causes the report to fall over "value for the report
> > parameter is not valid".
> >
> > I would like to maintain the date in the format dd/mm/yyyy for the users
> > benefit (UK users) but would also like to keep the date picker.
> >
> > When I pick a date that is the same month and date eg 10/10/2005 all works
> > fine.
> >
> > Thanks for the Help|||Yep, I found the same thing. It's a pain. It seems odd that the date picker
obeys your regional settings but the target textbox doesn't.
"Are friends electric?" wrote:
> Hi Matt
> I did not find a solution on the report designer but when I deploy the
> report and run it in explorer it works fine. I suppose this is a solution,
> just a pain when trying to work on the design of the report.
> Not that I am a fundi in any shape or form but if you need help on this I
> can let you know how I set it up.
> Steve
> "BlackDuck" wrote:
> > Hi,
> >
> > did you get any resolution for this? I'm having the same issue
> >
> > thanks
> >
> > Matt
> >
> > "Are friends electric?" wrote:
> >
> > > Hi sorry if trolling through the other postings that I have not found a
> > > solution to this and it is repetition.
> > >
> > > I am trying to set a filter for my table that compares a date from my data
> > > to a date parameter.
> > >
> > > I have no problem getting the date parameter with the new funky date picker,
> > > but when I select a date it returns in US format mm/dd/yyyy but my data is in
> > > dd/mm/yyyy. This then causes the report to fall over "value for the report
> > > parameter is not valid".
> > >
> > > I would like to maintain the date in the format dd/mm/yyyy for the users
> > > benefit (UK users) but would also like to keep the date picker.
> > >
> > > When I pick a date that is the same month and date eg 10/10/2005 all works
> > > fine.
> > >
> > > Thanks for the Helpsql
Date Parameter Question
I am still kind of new at this...
I am trying to have a date parameter that the user selects a month and it
brings back all the data for that month. Say the users picks August, the
report comes back for all the data for the month of August.
I hope I explained it well enough.
Any help would be great.
Thanks in advance,
KerrieConsider creating a parameter (called @.Month for example) and enter in the
"Available Values" (Report -> Report Parameters) the following:
Jan 1
Feb 2
Mar 3
etc, for each month. Then in your query use the following on your date
column:
SELECT
*
FROM
tblData
WHERE
DATEPART(mm, dateStart) = @.Month
to explain:
DATEPART(mm, <date>) returns the month of a date, e.g. for the 16th of July
it would return "7" for July.
Hope that helps,
-Geoff R G Williams
Primal Blaze Ltd.
"KS" <KS@.discussions.microsoft.com> wrote in message
news:303428AC-348D-45C1-BBCD-9EC5C032AEAE@.microsoft.com...
> Hi all,
> I am still kind of new at this...
> I am trying to have a date parameter that the user selects a month and it
> brings back all the data for that month. Say the users picks August, the
> report comes back for all the data for the month of August.
> I hope I explained it well enough.
> Any help would be great.
> Thanks in advance,
> Kerrie
>
>
Date Parameter Procedure
here is what I think the syntax should look like, but it is wrong;
"Create Procedure procAccrual Aging
as
Select Distinct Receiptdate
from pop30310
where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
As you seen, I am trying to create a beginning and ending range parameter.
Thank you,
RyanOn Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> I am trying to create a Parameter for entering a Date Range for a report,
> here is what I think the syntax should look like, but it is wrong;
> "Create Procedure procAccrual Aging
> as
> Select Distinct Receiptdate
> from pop30310
> where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> As you seen, I am trying to create a beginning and ending range parameter.
> Thank you,
> Ryan
If I'm understanding you correctly, you will want to create your
stored procedure for your report like this:
Create Procedure procAccrual Aging
@.BeginDate DATETIME,
@.EndDate DATETIME
as
Select Distinct Receiptdate
from pop30310
where (pop30310.receiptdate between @.BeginDate and @.EndDate)
Then have the @.BeginDate and @.EndDate report parameters based on a
standard calendar datetime control (default for datetime parameters).
Then in the Data view, where you define the dataset for the stored
procedure procAccrual Aging then in the Parameters tab of the Edit
Dataset [...] section, select the report parameters. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Ed,
The procedure works now, but when I preview the report and enter a range,
the data still spits out the same. Is there some linking that needs to be
done?
Thanks,
Ryan
"EMartinez" wrote:
> On Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> wrote:
> > I am trying to create a Parameter for entering a Date Range for a report,
> > here is what I think the syntax should look like, but it is wrong;
> >
> > "Create Procedure procAccrual Aging
> > as
> > Select Distinct Receiptdate
> > from pop30310
> > where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> >
> > As you seen, I am trying to create a beginning and ending range parameter.
> >
> > Thank you,
> >
> > Ryan
>
> If I'm understanding you correctly, you will want to create your
> stored procedure for your report like this:
> Create Procedure procAccrual Aging
> @.BeginDate DATETIME,
> @.EndDate DATETIME
> as
> Select Distinct Receiptdate
> from pop30310
> where (pop30310.receiptdate between @.BeginDate and @.EndDate)
> Then have the @.BeginDate and @.EndDate report parameters based on a
> standard calendar datetime control (default for datetime parameters).
> Then in the Data view, where you define the dataset for the stored
> procedure procAccrual Aging then in the Parameters tab of the Edit
> Dataset [...] section, select the report parameters. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 18, 10:00 am, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> Ed,
> The procedure works now, but when I preview the report and enter a range,
> the data still spits out the same. Is there some linking that needs to be
> done?
> Thanks,
> Ryan
> "EMartinez" wrote:
> > On Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> > wrote:
> > > I am trying to create a Parameter for entering a Date Range for a report,
> > > here is what I think the syntax should look like, but it is wrong;
> > > "Create Procedure procAccrual Aging
> > > as
> > > Select Distinct Receiptdate
> > > from pop30310
> > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> > > As you seen, I am trying to create a beginning and ending range parameter.
> > > Thank you,
> > > Ryan
> > If I'm understanding you correctly, you will want to create your
> > stored procedure for your report like this:
> > Create Procedure procAccrual Aging
> > @.BeginDate DATETIME,
> > @.EndDate DATETIME
> > as
> > Select Distinct Receiptdate
> > from pop30310
> > where (pop30310.receiptdate between @.BeginDate and @.EndDate)
> > Then have the @.BeginDate and @.EndDate report parameters based on a
> > standard calendar datetime control (default for datetime parameters).
> > Then in the Data view, where you define the dataset for the stored
> > procedure procAccrual Aging then in the Parameters tab of the Edit
> > Dataset [...] section, select the report parameters. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
I'm assuming you are addressing me. Have you linked the parameters
that the stored procedure is expecting in the Data tab view with the
report parameters? You would check this via selecting the dataset that
references the stored procedure in the Data view, select the [...]
button to the right of the dataset, for Edit dataset, then select the
Parameters tab and set/verify that @.BeginDate, @.EndDate have the
correct Report parameters associated with them. The values should be
whatever your report parameters are named (i.e., Parameters!
BeginDate.Value and Parameters!EndDate.Value). Hope this clarifies
things for you.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
Thanks for all of your help? What is your address? I am going to have to
send you a bottle of Scotch my friend!
Ryan
"EMartinez" wrote:
> On Jun 18, 10:00 am, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> wrote:
> > Ed,
> > The procedure works now, but when I preview the report and enter a range,
> > the data still spits out the same. Is there some linking that needs to be
> > done?
> >
> > Thanks,
> > Ryan
> >
> > "EMartinez" wrote:
> > > On Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> > > wrote:
> > > > I am trying to create a Parameter for entering a Date Range for a report,
> > > > here is what I think the syntax should look like, but it is wrong;
> >
> > > > "Create Procedure procAccrual Aging
> > > > as
> > > > Select Distinct Receiptdate
> > > > from pop30310
> > > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> >
> > > > As you seen, I am trying to create a beginning and ending range parameter.
> >
> > > > Thank you,
> >
> > > > Ryan
> >
> > > If I'm understanding you correctly, you will want to create your
> > > stored procedure for your report like this:
> > > Create Procedure procAccrual Aging
> > > @.BeginDate DATETIME,
> > > @.EndDate DATETIME
> > > as
> >
> > > Select Distinct Receiptdate
> > > from pop30310
> > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)
> >
> > > Then have the @.BeginDate and @.EndDate report parameters based on a
> > > standard calendar datetime control (default for datetime parameters).
> > > Then in the Data view, where you define the dataset for the stored
> > > procedure procAccrual Aging then in the Parameters tab of the Edit
> > > Dataset [...] section, select the report parameters. Hope this helps.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. Software Consultant
>
> I'm assuming you are addressing me. Have you linked the parameters
> that the stored procedure is expecting in the Data tab view with the
> report parameters? You would check this via selecting the dataset that
> references the stored procedure in the Data view, select the [...]
> button to the right of the dataset, for Edit dataset, then select the
> Parameters tab and set/verify that @.BeginDate, @.EndDate have the
> correct Report parameters associated with them. The values should be
> whatever your report parameters are named (i.e., Parameters!
> BeginDate.Value and Parameters!EndDate.Value). Hope this clarifies
> things for you.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 18, 1:34 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> Enrique,
> Thanks for all of your help? What is your address? I am going to have to
> send you a bottle of Scotch my friend!
> Ryan
> "EMartinez" wrote:
> > On Jun 18, 10:00 am, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> > wrote:
> > > Ed,
> > > The procedure works now, but when I preview the report and enter a range,
> > > the data still spits out the same. Is there some linking that needs to be
> > > done?
> > > Thanks,
> > > Ryan
> > > "EMartinez" wrote:
> > > > On Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> > > > wrote:
> > > > > I am trying to create a Parameter for entering a Date Range for a report,
> > > > > here is what I think the syntax should look like, but it is wrong;
> > > > > "Create Procedure procAccrual Aging
> > > > > as
> > > > > Select Distinct Receiptdate
> > > > > from pop30310
> > > > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> > > > > As you seen, I am trying to create a beginning and ending range parameter.
> > > > > Thank you,
> > > > > Ryan
> > > > If I'm understanding you correctly, you will want to create your
> > > > stored procedure for your report like this:
> > > > Create Procedure procAccrual Aging
> > > > @.BeginDate DATETIME,
> > > > @.EndDate DATETIME
> > > > as
> > > > Select Distinct Receiptdate
> > > > from pop30310
> > > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)
> > > > Then have the @.BeginDate and @.EndDate report parameters based on a
> > > > standard calendar datetime control (default for datetime parameters).
> > > > Then in the Data view, where you define the dataset for the stored
> > > > procedure procAccrual Aging then in the Parameters tab of the Edit
> > > > Dataset [...] section, select the report parameters. Hope this helps.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. Software Consultant
> > I'm assuming you are addressing me. Have you linked the parameters
> > that the stored procedure is expecting in the Data tab view with the
> > report parameters? You would check this via selecting the dataset that
> > references the stored procedure in the Data view, select the [...]
> > button to the right of the dataset, for Edit dataset, then select the
> > Parameters tab and set/verify that @.BeginDate, @.EndDate have the
> > correct Report parameters associated with them. The values should be
> > whatever your report parameters are named (i.e., Parameters!
> > BeginDate.Value and Parameters!EndDate.Value). Hope this clarifies
> > things for you.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
Glad I could be of assistance. Thanks for the offer, however, I'm not
much into drinking.
Best Regards,
Enrique
date parameter problems
Hello Im having a date problem
I have a parameter called @.Param_Daterange with the following values
Last 30 days =Today.AddDays(-30)
Last 60 days =Today.AddDays(-60)
My SQL has a chart which uses the following SQL
SELECT Sum(dbo.tbl_OMDQ_Aggregates.Value) AS SumOfValue, dbo.tbl_OMDQ_Aggregates.status_desc, dbo.tbl_OMDQ_Aggregates.DateRun
FROM dbo.tbl_OMDQ_Aggregates
WHERE (((dbo.tbl_OMDQ_Aggregates.SaleMonth) IN (@.Param_SaleMonth))and (status_desc IN (@.Param_ChurnType))) and dbo.tbl_OMDQ_Aggregates.DateRun > @.Param_DateRange
GROUP BY dbo.tbl_OMDQ_Aggregates.status_desc, dbo.tbl_OMDQ_Aggregates.DateRun;
In visual studio the chart is working fine, however in IE the data getting returned is only a small subset off the entire data, ...my IE language setting is at English Aus, I also created a textbox so i can see what value the daterange parameter is passing and it seems to be in the correct format...the graph is also in the right day/time format of dd/MM/yyyy. However i have no idea which date is getting passed incorrectly, somewhere the date is getting switched to American format however what is outputting on the screen is in the right format...just not the correct records are returning
can anyone please help
Fixed it !!! it wasnt the notorious Amercian format problem after all but rather the daterange parameter had turned into a string datatype for some reason..changed it back and all is well !!!