06-19-2012 04:40 AM - edited 03-14-2019 10:04 AM
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.
06-19-2012 06:04 AM
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
06-19-2012 11:39 PM
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.
06-20-2012 11:02 AM
Any time.
Could be; the @RelativeDate parameter should be DECIMAL. The @end_date and @start_date should be DATETIME.
L
06-21-2012 03:58 AM
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.
06-21-2012 04:25 AM
08-03-2012 06:38 AM
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
08-03-2012 01:36 PM
David,
Take a look at my post and give it a try.
https://supportforums.cisco.com/message/3683671#3683671
Amer
11-11-2014 01:23 PM
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
11-11-2014 01:32 PM
Bhawani,
Check the permissions on your relative dates value list. You need to make sure all users have access to execute it.
-Jameson
11-11-2014 01:48 PM
Jameson, Thanks this was permission issue . I now assigned execute to all users.
10-28-2013 08:34 AM
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
10-29-2013 03:16 AM
OK, I found the issue;
added the block of code, statement by statement and found out that one more END statement is missing
Hans
01-06-2014 06:44 AM
Thanks Luis,
I’ve made a few changes to improve;
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
01-06-2014 07:00 AM
Cool.
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide