CUIC Date/Time Filter

[justine.joubran]

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.

[Luis Yrigoyen]

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 "Pass NULL for empty string" for all the date parameters (i.e. @RelativeDate, @start_date, @end_date)

*********************************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

[justine.joubran]

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.

[Luis Yrigoyen]

Any time.

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

L

[justine.joubran]

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.

[Luis Yrigoyen]

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

[david.macias]

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

[Amer rajai Sha'er]

David,

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

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

Amer

[Bhawani Singh]

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

[Jameson Gagnepain]

Bhawani,

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

-Jameson

[Bhawani Singh]

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

[halblas]

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

[halblas]

OK, I found the issue;

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

Hans

[halblas]

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

[Luis Yrigoyen]

Cool.