Date Range Parameter - Support - RS Data Extension

Search Forum Home

Forum

Enesys RS Data ...

Support - RS Da...

Date Range Parameter

3/13/2007 6:02 PM

davids

www.airvac.com
Joined: 7/31/2006

Posts: 9

Date Range Parameter

I have a user that wants a report from a list that will allow him to choose from a date range, like month end reports for example. So far I can do a date parameter but it will only pull up records with that date. How do I set up a parameter to choose from a date range?

3/13/2007 7:32 PM

davids

www.airvac.com
Joined: 7/31/2006

Posts: 9

Re: Date Range Parameter

Modified By davids on 3/13/2007 8:34:54 PM

I found a caml example that will probably work @StartDate! @EndDate! Open

3/13/2007 8:35 PM

Frédéric LATOUR

Joined: 5/31/2006

Posts: 629

Re: Date Range Parameter

Modified By Frédéric LATOUR on 3/13/2007 9:36:22 PM

Hi,
Glad you could find a solution.
If you are reporting over an event list and would like to expand recurring events, you will find a complete sample in the latest documentation.
Regards,

Frederic LATOUR
ENESYS

7/20/2007 12:52 AM

kspollen

Joined: 7/20/2007

Posts: 3

Re: Date Range Parameter

Modified By kspollen on 7/20/2007 12:56:39 AM

I'm not sure what "@StartDate! @EndDate! Open" means or how you would apply it in the tag.. but I have a Sharepoint list that brings back a Date field. eg 10/31/2006 12:00:00 AM. I want to filter this so that Date >= @FromDate and Date <= @ToDate. Alternatively, if there is a 'Between' syntax, that woud work too. I can't get the correct syntax. My current attempt is as follows:

Where
Gt
FieldRef Name="Date"
Value Type="DateTime" @pFromDate! Value
Gt

Kevin

7/20/2007 1:42 PM

Frédéric LATOUR

Joined: 5/31/2006

Posts: 629

Re: Date Range Parameter

Hi,
If you don't have to deal with recurring events you could have the following query :
Where
And
    Gt
      FieldRef Name="Date"
      Value Type="DateTime" @pFromDate! /Value
    /Gt
    Leq
      FieldRef Name="Date"
      Value Type="DateTime" @pToDate! /Value
    /Leq
/And
/Where

Did I miss something?
Regards,

Frederic LATOUR
ENESYS

7/24/2007 4:57 PM

kspollen

Joined: 7/20/2007

Posts: 3

Re: Date Range Parameter

Frederic:
No, you didn't miss anything really. I didn't do enough homework. However, it took a while to figure out that the date format HAS to be YYYY-MM-DD HH:MM:SS, which is somewhat limiting to say the least.
Thanks,
K

7/25/2007 11:55 AM

Frédéric LATOUR

Joined: 5/31/2006

Posts: 629

Re: Date Range Parameter

Hi,
This is the format needed by SharePoint CAML Query.
I'm curious to know why it would be limiting as a report parameter of type DateTime is automatically converted into this format by ERSDE.
Regards,

Frederic LATOUR
ENESYS

7/26/2007 12:21 AM

kspollen

Joined: 7/20/2007

Posts: 3

Re: Date Range Parameter

It's limiting for me because we have a dashboard report with OLAP data sources. The date parameter for the OLAP is MM/DD/YYYY, so I have to create separate versions of the dates for each filter. Not a big deal, but more work.

Page 1 of 1