Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Community Member

CUIC Date/Time Filter

Hello,

When I create a new report in CUIC, I am using "anonymous block" and setting parameters like :start_date and :end_date

Those parameters appear in the Filter and need to be manually filled

From Date : mm/dd/yyyy hh:mm:ss 

End Date: mm/dd/yyyy hh:mm:ss

Is there a way to replace this filter by the "Relative Date and Time Range" which is found in WebView?

Any advice is appreciated.

Thanks,

Justine.

Everyone's tags (3)
17 REPLIES
Community Member

CUIC Date/Time Filter

Justine,

yes, just add the following block of code at the begining of a report definition.  This will give you both the Relative Dates and the start_date and end_date.

a couple of things you need to do:

1. In YOUR query, rename start_date and end_date to "@BeginDate" and "@EndDate" (without the quotes)

2. In the parameters tab in the report definition, uncheck "Required" and check "

*********************************code below*************************

BEGIN

DECLARE @RelativeDate int

, @AbsBegin varchar(30)

, @AbsEnd varchar(30)

, @BeginDate varchar(30)

, @EndDate varchar(30)

SELECT  @AbsBegin = :start_date

, @AbsEnd = :end_date

, @RelativeDate = :RelativeDate

SELECT @BeginDate = CASE @RelativeDate

WHEN 1 THEN CONVERT(DATETIME,(CONVERT(Varchar(15),GETDATE(),112)))

WHEN 2 THEN CONVERT(DATETIME,(CONVERT(Varchar(15),DATEADD(d, -1,GETDATE()),112)))

WHEN 3 THEN CONVERT(Varchar(30),DATEADD(dd,-6,DATEADD(wk,(DATEPART(ww, GETDATE())-1),CONVERT(char(4),DATEPART(yyyy, GETDATE())))))

WHEN 4 THEN CONVERT(Varchar(30),DATEADD(dd,-6,DATEADD(wk,(DATEPART(ww, GETDATE())-2),CONVERT(char(4),DATEPART(yyyy, GETDATE())))))

WHEN 5 THEN Convert(Varchar(2), DATEPART(m,GETDATE())) + '/01/' + Convert(Char(4), DATEPART(YY,GETDATE()))

WHEN 6 THEN Convert(Varchar(2), DATEPART(m,DATEADD(mm,-1,GETDATE()))) + '/01/' + Convert(Char(4), DATEPART(YY,DATEADD(MM,-1,GETDATE())))

WHEN 7 THEN '01/01/' + Convert(Char(4), DATEPART(yy,GETDATE()))

WHEN 8 THEN '01/01/' + Convert(Char(4), DATEPART(yy,DATEADD(yy,-1,GETDATE())))

ELSE @AbsBegin

END

SELECT @EndDate = CASE @RelativeDate

WHEN 1 THEN CONVERT(Varchar(30), GETDATE(),112) + ' 23:59:59'

WHEN 2 THEN CONVERT(Varchar(30), DATEADD(d, -1,GETDATE()),112) + ' 23:59:59'

WHEN 3 THEN CONVERT(Varchar(30),DATEADD(dd,-0,DATEADD(wk,(DATEPART(ww, GETDATE())-1),CONVERT(char(4),DATEPART(yyyy, GETDATE())))) + '23:59:00')

WHEN 4 THEN CONVERT(Varchar(30),DATEADD(dd,-0,DATEADD(wk,(DATEPART(ww, GETDATE())-2),CONVERT(char(4),DATEPART(yyyy, GETDATE())))) + '23:59:00')

WHEN 5 THEN DATEADD(d,-1, CONVERT(Varchar(30), DATEPART(m,DATEADD(mm, 1, GETDATE()))) + '/01/' + CONVERT(Char(4), DATEPART(YY,GETDATE())) + ' 23:59:00')

WHEN 6 THEN DATEADD(d,-1, CONVERT(Varchar(30), DATEPART(m,DATEADD(mm,0, GETDATE()))) + '/01/' + CONVERT(Char(4), DATEPART(YY,DATEADD(MM,-1,GETDATE()))) + ' 23:59:59')

WHEN 7 THEN '12/31/' + CONVERT(Char(4), DATEPART(yy,GETDATE()))+ ' 23:59:59'

WHEN 8 THEN '12/31/' + CONVERT(Char(4), DATEPART(yy,DATEADD(yy,-1,GETDATE())))+ ' 23:59:59'

ELSE @AbsEnd

END

--continue with you query here

Community Member

CUIC Date/Time Filter

Thanks a lot Luis for the answer I wouldn't have guessed all this for sure

After adding the block of code, I tried to create the parameters (@RelativeDate - @agent_list - @end_date - @start_date) and I have specified DECIMAL for @agent_list and I have tried DATE and DATETIME for the rest of the parameters but I am still getting the same error:

Create the parameters or correct the query syntax and recreate the parameters. Incorrect syntax near '0'.

Is this related to the parameters type? I have edited a report that was working.

Thanks,

Justine.

Community Member

CUIC Date/Time Filter

Any time.

Could be; the @RelativeDate parameter should be DECIMAL. The @end_date and @start_date should be DATETIME.

L

Community Member

CUIC Date/Time Filter

Hi Luis,

I have tried to change the types as you suggested earlier but still no luck

I have also tried to choose "Database Query" instead of "Anonymous block" but this didn't solve the issue.

I am unable to create the fields in order to reach step 2 (uncheck "Required" and check "Pass NULL for empty string" for all the date parameters).

Do you have any additional advice? Can you double check if there's a typo in the code you pasted please?

Thanks,

Justine.

Community Member

Re: CUIC Date/Time Filter

Justine,

     Attached is one of my production reports which uses the Relative Dates.  Import it and check its definition -- this should work.

let me know

CUIC Date/Time Filter

Justine, did you figure out what you were doing wrong when adding the block of code Luis suggested?  I am getting the same error you're getting for a call type historical report and I can't get to create the parameters, any help would be appreciated.

david

CUIC Date/Time Filter

David,

Take a look at my post and give it a try.

https://supportforums.cisco.com/message/3683671#3683671

Amer

Community Member

Hello All, I am using

Hello All, I am using relative date range in custom report

when I login as Administrator in CUIC  and run the particular report , I get all filters like , Today , yesterday , Last week etc  in relative range and all works fine. 

But when I login as supervisor and try to run exact same report, all the filters under relative date are not displayed.

 Any idea what can cause this issue,

 

Attached are Prtsc.

 

Thanks,

bhawani

Bhawani,Check the permissions

Bhawani,

Check the permissions on your relative dates value list. You need to make sure all users have access to execute it.

-Jameson

-Jameson
Community Member

Janeson, Thanks this was

Jameson, Thanks this was permission issue . I now assigned execute to all users.

Community Member

CUIC Date/Time Filter

I'm running against the same issue;

Create the parameters or correct the query syntax and recreate the parameters. Incorrect syntax near '0'.


Went through all the details, parameter type's, no luck.

Any help appreciated !

Hans

Community Member

CUIC Date/Time Filter

OK, I found the issue;

added the block of code, statement by statement and found out that one more END statement is missing

Hans

Community Member

Re: CUIC Date/Time Filter

Thanks Luis,

I’ve made a few changes to improve;

  • Changed last week and this week in last full workweek and this week to date.
  • First day of week is Monday (to match ISO week number).
  • This Month failed in December.

SELECT @BeginDate = CASE @RelativeDate

WHEN 1 THEN CONVERT(DATETIME,(CONVERT(Varchar(15),GETDATE(),112)))

WHEN 2 THEN CONVERT(DATETIME,(CONVERT(Varchar(15),DATEADD(d, -1,GETDATE()),112)))

WHEN 3 THEN DATEADD(dd,- (DATEPART(dw,GETDATE())-2),CONVERT(CHAR,GETDATE(),112))

WHEN 4 THEN DATEADD(dd,- (DATEPART(dw,GETDATE())+5),CONVERT(CHAR,GETDATE(),112))

WHEN 5 THEN Convert(Varchar(2), DATEPART(m,GETDATE())) + '/01/' + Convert(Char(4), DATEPART(YY,GETDATE()))

WHEN 6 THEN Convert(Varchar(2), DATEPART(m,DATEADD(mm,-1,GETDATE()))) + '/01/' + Convert(Char(4), DATEPART(YY,DATEADD(MM,-1,GETDATE())))

WHEN 7 THEN '01/01/' + Convert(Char(4), DATEPART(yy,GETDATE()))

WHEN 8 THEN '01/01/' + Convert(Char(4), DATEPART(yy,DATEADD(yy,-1,GETDATE())))

ELSE @AbsBegin

END

SELECT @EndDate = CASE @RelativeDate

WHEN 1 THEN CONVERT(Varchar(30), GETDATE(),112) + ' 23:59:59'

WHEN 2 THEN CONVERT(Varchar(30), DATEADD(d, -1,GETDATE()),112) + ' 23:59:59'

WHEN 3 THEN DATEADD(dd,- (DATEPART(dw,GETDATE())-8),CONVERT(CHAR,GETDATE(),112))

WHEN 4 THEN DATEADD(dd,- (DATEPART(dw,GETDATE())-1),CONVERT(CHAR,GETDATE(),112)) + '23:59:00'

WHEN 5 THEN DATEADD(d,-1,CONVERT(Varchar(30),DATEPART(m,DATEADD(mm,1,GETDATE()))) + '/01/' + CONVERT(Char(4),DATEPART(YY,DATEADD(d,1,GETDATE())))) + ' 23:59:59'

WHEN 6 THEN DATEADD(d,-1, CONVERT(Varchar(30), DATEPART(m,DATEADD(mm,0, GETDATE()))) + '/01/' + CONVERT(Char(4), DATEPART(YY,DATEADD(MM,-1,GETDATE()))) + ' 23:59:59')

WHEN 7 THEN '12/31/' + CONVERT(Char(4), DATEPART(yy,GETDATE()))+ ' 23:59:59'

WHEN 8 THEN '12/31/' + CONVERT(Char(4), DATEPART(yy,DATEADD(yy,-1,GETDATE())))+ ' 23:59:59'

ELSE @AbsEnd

END

Kind regards,

Hans

Community Member

Re: CUIC Date/Time Filter

Cool.

Community Member

CUIC Date/Time Filter

I am trying this and having an issue with the last month section (case 6).

The begin date is working as 12/01/2013

but the end date is calculating as 12/31/2012

I  am thinking that is because it is looking at January 01 to try to find  the end of the month of Dec. It must be subtracting it twice somehow.

Has anyone else run into this?

Community Member

CUIC Date/Time Filter

Hi Brandy,

After fixing ‘This Month’ for December, I had another issue running ‘Last Month’ in January.

The latest changes I made are;

WHEN 1 THEN CONVERT(DATETIME,(CONVERT(Varchar(15),GETDATE(),112)))

WHEN 2 THEN CONVERT(DATETIME,(CONVERT(Varchar(15),DATEADD(d, -1,GETDATE()),112)))

WHEN 3 THEN DATEADD(dd,- (DATEPART(dw,GETDATE())-2),CONVERT(CHAR,GETDATE(),112))

WHEN 4 THEN DATEADD(dd,- (DATEPART(dw,GETDATE())+5),CONVERT(CHAR,GETDATE(),112))

WHEN 5 THEN Convert(Varchar(2), DATEPART(m,GETDATE())) + '/01/' + Convert(Char(4), DATEPART(YY,GETDATE()))

WHEN 6 THEN Convert(Varchar(2), DATEPART(m,DATEADD(mm,-1,GETDATE()))) + '/01/' + Convert(Char(4), DATEPART(YY,DATEADD(MM,-1,GETDATE())))

WHEN 7 THEN '01/01/' + Convert(Char(4), DATEPART(yy,GETDATE()))

WHEN 8 THEN '01/01/' + Convert(Char(4), DATEPART(yy,DATEADD(yy,-1,GETDATE())))

ELSE @AbsBegin

END

SELECT @EndDate = CASE @RelativeDate

WHEN 1 THEN CONVERT(Varchar(30), GETDATE(),112) + ' 23:59:59'

WHEN 2 THEN CONVERT(Varchar(30), DATEADD(d, -1,GETDATE()),112) + ' 23:59:59'

WHEN 3 THEN DATEADD(dd,- (DATEPART(dw,GETDATE())-8),CONVERT(CHAR,GETDATE(),112))

WHEN 4 THEN DATEADD(dd,- (DATEPART(dw,GETDATE())-1),CONVERT(CHAR,GETDATE(),112)) + '23:59:00'

WHEN 5 THEN DATEADD(d,-1,CONVERT(Varchar(30),DATEPART(m,DATEADD(mm,1,GETDATE()))) + '/01/' + CONVERT(Char(4),DATEPART(YY,DATEADD(d,1,GETDATE())))) + ' 23:59:59'

WHEN 6 THEN DATEADD(d,-1, CONVERT(Varchar(30), DATEPART(m,GETDATE())) + '/01/' + CONVERT(Char(4), DATEPART(YY,DATEADD(d,1, GETDATE())))) + ' 23:59:59'

WHEN 7 THEN '12/31/' + CONVERT(Char(4), DATEPART(yy,GETDATE()))+ ' 23:59:59'

WHEN 8 THEN '12/31/' + CONVERT(Char(4), DATEPART(yy,DATEADD(yy,-1,GETDATE())))+ ' 23:59:59'

Hope this helps,

Hans

Community Member

CUIC Date/Time Filter

Thanks!

2773
Views
5
Helpful
17
Replies
CreatePlease to create content