I'm using Kentico 7.0.23.
I created a report with custom parameters to specify the date range.
It's working correctly when accessing it from the page CMSDesk -> Tools -> Reporting; in the View tab I'm able to change the default values, too.
When the marketer adds it to the Online marketing dashboard, he gets an exception because of a wrong datatype conversion. Same exception is thrown if I subscribe to the report.
Where am I wrong?
Thanks
Regards
Emanuele Firmani
Message: [DataConnection.HandleError]: Query: SET @FromDate = dbo.Func_Analytics_DateTrim(@FromDate,'day');
SET @ToDate = dbo.Func_Analytics_EndDateTrim(@ToDate,'day');
SELECT F.FormName, x.StartTime,
(
SELECT COUNT(1)
FROM [dbo].[View_OM_Contact_Activity] ca
WHERE CA.ActivityItemID = F.FormID
AND ActivityType = 'bizformsubmit'
AND ca.ActivityCreated BETWEEN X.StartTime AND DATEADD(day, 1, X.StartTime)
) AS 'NrSubmissions'
INTO #T
FROM (
SELECT FormID, REPLACE(FormDisplayName, 'aduno.form.', '') AS 'FormName'
FROM dbo.CMS_Form
) F
CROSS JOIN (
SELECT [Date] AS StartTime
FROM dbo.Func_Analytics_EnsureDates (@FromDate,@ToDate,'day')
) X
ORDER BY x.StartTime
DECLARE @CNames VARCHAR(MAX)
DECLARE @CName VARCHAR(MAX)
DECLARE @ColumnNames TABLE(FormName VARCHAR(900))
SET @CNames = ''
INSERT INTO @ColumnNames
SELECT DISTINCT FormName
FROM #T
ORDER BY FormName
SELECT TOP 1 @CName = FormName
FROM @ColumnNames
ORDER BY FormName
WHILE @@ROWCOUNT > 0 BEGIN
SET @CNames = @CNames + ', [' + @CName + ']'
SELECT TOP 1 @CName = FormName
FROM @ColumnNames
WHERE FormName > @CName
ORDER BY FormName
END
SET @CNames = SUBSTRING(@CNames, 3, 100000)
DECLARE @SQL VARCHAR(MAX)
SET @SQL =
'SELECT ' +
' CONVERT(VARCHAR(10), StartTime, 104) AS ''Day'', ' + @CNames +
' FROM ' +
' #T' +
' PIVOT' +
' (' +
' SUM(NrSubmissions)' +
' FOR FormName IN (' + @CNames + ')' +
' ) AS [P]'
--print(@sql)
EXEC(@SQL)
drop table #T: caused exception: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Invalid object name '#T'.
The statement has been terminated.
Stack Trace:
at CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, SqlException ex)
at CMS.DataProviderSQL.DataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction)
at CMS.SettingsProvider.SqlHelperClass.ExecuteQuery(QueryParameters query)
at CMS.Reporting.AbstractReportControl.LoadData()
at CMSModules_Reporting_Controls_ReportTable.ReloadData(Boolean forceLoad) in e:\Farm\Aduno2Test\CMSModules\Reporting\Controls\ReportTable.ascx.cs:line 341
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Invalid object name '#T'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at CMS.DataProviderSQL.DataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction)