×

Warning message

  • Cisco Support Forums is in Read Only mode while the site is being migrated.
  • Cisco Support Forums is in Read Only mode while the site is being migrated.

CUIC Date/Time Filter

Unanswered Question
Jun 19th, 2012
User Badges:

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.

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (1 ratings)
Loading.
Luis Yrigoyen Tue, 06/19/2012 - 06:04
User Badges:

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 Tue, 06/19/2012 - 23:39
User Badges:

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 Wed, 06/20/2012 - 11:02
User Badges:

Any time.


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



L

justine.joubran Thu, 06/21/2012 - 03:58
User Badges:

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.

david.macias Fri, 08/03/2012 - 06:38
User Badges:
  • Blue, 1500 points or more

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

Bhawani Singh Tue, 11/11/2014 - 13:23
User Badges:

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

Attachment: 
Jameson Gagnepain Tue, 11/11/2014 - 13:32
User Badges:
  • Silver, 250 points or more
  • Community Spotlight Award,

    Member's Choice, March 2015

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 Tue, 11/11/2014 - 13:48
User Badges:

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

halblas Mon, 10/28/2013 - 08:34
User Badges:

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 Tue, 10/29/2013 - 03:16
User Badges:

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 Mon, 01/06/2014 - 06:44
User Badges:

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

BTciscoN00b Thu, 01/30/2014 - 09:06
User Badges:

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?

halblas Sun, 02/02/2014 - 23:49
User Badges:

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

Actions

This Discussion

Related Content