Accepted Solutions
GO
CREATE procedure [dbo].[Test222]
@From datetime = null,
@To Datetime = null,
@DayWeek nvarchar(200) = null,
@TimeZone nvarchar(100) = null
as
select @From as FromDate,@to as ToDate,@DayWeek as dw ,@TimeZone TZ
GO
Create Report Definition.
Configure the Parameters
Create report for the report definition.
Select the parameters
Expected results
DW = "Mon:Tue:Wed:Thu:Fri", TZ="UTC" , From="10/12/16". To="10/12/16"
Real Results in error.
{call [Test222]('2016-10-12 00:00:00','2016-10-12 23:59:59','UTC','TEST')}
Per your suggestion reordering Parameters in Parameters tab.
Run Report same parameters.
{call [Test222]('UTC','2016-10-12 00:00:00','TEST','2016-10-12 23:59:59')}
Report execution fails SQL error
Msg 8114, Level 16, State 5, Procedure Test222, Line 0
Error converting data type varchar to datetime.
Now do you understand what I have been trying to explain?
Also Anonymous Block does not support Automatic parameters "Current User Timezone and Current Username"
So I cannot use Anonymous block as the mapping of the Parameters to call the SP.
declare
@From Datetime =:From,
@To datetime =:To,
@DayOfWeek nvarchar(200) =:DayWeek,
@TimeZone nvarchar(100) =:TimeZone
exec Test222 @From,@To,@DayOfWeek,@TimeZone
Here are the steps I used and I didn't face any issues for the same stored procedure:
(By the way, what version of CUIC are you using? I am looking at 11.5(1) - yours look like 11.0(1) or older).
01 - Create the fields:
02 - Fields List (second tab on Report Definitions):
03 - Parameters List (I made sure that TimeZone is the last parameter - because the SP has it as the last one):
04 - Created a report and then ran it - Choosing Filters (Notice the timezone being automatically picked):
05 - Show SQL on the executed report (TimeZone is the last parameter):
