iFinity

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

Hi Bruce,
thanks for the nice scripts. Just the "Link Clicks" is exactly the "File Downloads" with a different Label. Can you have a look at this?
your's
felix

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

Hi Bruce,

Can we also track number of user visits.

Gyanendra

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

Is this DNN5 compatible?

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

Hi, I'm getting this error when I set the type report as: File Downloads and then click on display.
Can you tell me why this is happening?
Thanks,
Javier


Error: Site Log is currently unavailable.
DotNetNuke.Services.Exceptions.ModuleLoadException: Arithmetic overflow error converting expression to data type int. ---> System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type int. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at System.Data.Common.DbEnumerator.MoveNext() at System.Web.UI.WebControls.DataGrid.CreateAutoGeneratedColumns(PagedDataSource dataSource) at System.Web.UI.WebControls.DataGrid.CreateColumnSet(PagedDataSource dataSource, Boolean useDataSource) at System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource) at System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) at System.Web.UI.WebControls.BaseDataList.DataBind() at DotNetNuke.Modules.Admin.SiteLog.SiteLog.BindData() at DotNetNuke.Modules.Admin.SiteLog.SiteLog.cmdDisplay_Click(Object sender, EventArgs e) --- End of inner exception stack trace ---

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

Hi This looks like a good module, however i does not seem to want to instal on DNN 05.01.01

Are there any Plans for a DNN 5x version of this module

thanks,

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

Bruce,

Any chance you could post the code for your report on the link click tracking. If you could update it would be much appreciated. As mentioned above it is a duplicate report of the download report.

These reports work fine for me in DNN 5.

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

Bruce,

Love the scripts and all the resources you share. When executing the link clicks report get the following output. I also get the same error on the site log page as javier above. Any suggestions?

Secondly the user visit detail is only showing the super user account, none of the test users I've created (that are showing up i the detailed report)

System.Data.SqlClient.SqlException: Cannot drop the procedure 'dbo.GetSiteLog22', because it does not exist or you do not have permission. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String ConnectionString, String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String ConnectionString, String Script) /* drop the proc if it already exists */ IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'dbo.GetSiteLog21') AND type in (N'P')) DROP PROCEDURE dbo.[GetSiteLog22] System.Data.SqlClient.SqlException: Invalid column name 'MOduleTitle'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String ConnectionString, String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String ConnectionString, String Script) /* create the report procedure*/ CReaTE procedure dbo.[GetSiteLog22] @PortalId int, @PortalAlias nvarchar(50), @StartDate datetime, @EndDate datetime as /* Created : Bruce Chapman 6 March 2008 Modifications */ BEGIN /* work out the time difference between the portal site timezone and the installed server timezone*/ declare @timeDiff int /* the time difference is the difference between the portal site timezone, and the time zone of the sql server*/ /* the timezone of the sql server can be worked out by the difference between the current date and the utc date */ select @timeDiff = timezoneOffset - dateDiff(n,getutcDate(),getDate()) from Portals where portalID = @portalID /* the filtering start and end date should be assumed to be given in the local portal time so we go back to the sql server timezone by subtracting the difference between the portal timezone and the sql server timezone */ select @StartDate = DateAdd(mi, -@timeDiff, @StartDate) ,@EndDate = DateAdd(mi,- @timeDiff, @EndDate) select case when isnumeric(tr.Url) = 1 then replace(t.TabPath, '//','/') else tr.Url END as [Link Destination] , tr.Clicks as [Number Clicks] , Convert(varChar(12),LastClick ,107) As [Last Click Date] , Convert(varChar(12),CreatedDate ,107) As [Link Created Date] ,m.MOduleTitle as [Link Module Title] , coalesce(replace(rt.TabPath, '//','/'), '[Removed]') as [Link Module Page] ,case when coalesce(DateDiff(d, CreatedDate, LastClick ),0) > 0 then str(Clicks * 1.0 / (DateDiff(d, CreatedDate, LastClick ) * 1.0),10,2) else '-' end as [Clicks per Day] from UrlTracking tr left join Modules m on tr.ModuleId = m.ModuleId left join TabMOdules tm on m.ModuleId = tm.ModuleId left join Tabs rt on rt.TabId = tm.TabId left join Tabs t on convert(varchar(4),t.TabId) = tr.Url where urlType in ('T','U') and tr.PortalID = @portalId order by tr.clicks desc END

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

@Ben I have updated the files with the fix for the drop error you were getting.

To fix the arithmetic overflow you'll need to comment out the columns one by one to see where the problem is, and try and work out if it is because of a divide by zero, or because the value is too large to go into an INT. It's impossible to debug without having the data that causes the problem at hand so you'll need to do some simple debugging of the stored proc in a query manager.

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

Hi Bruce,

Thank you for posting up these scripts. My client is not very happy that the "track the number of times this link is clicked" in the HTML module doesn't offer a report (even though the data is in the database).

So, I've run your SQL script to add "Link Clicks" to the "Site Log". It's perfect with one exception...

How can I get rid of [Removed] links? Better yet, is there a way I can regenerate all outbound links? I originally tried to create null pages with redirects to get the stats, but that didn't work. I ran your SQL, and noticed all those links that were outbound, are now [Removed]. ~ Giff

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

Hi Bruce, these reports are great but wondered if the Downloads report can be updated to include the start and end date as the links we're using has the "track date and time" option selected so each click is recorded in the UrlLog DNN table. Is it easy enough to update to query to include the start and end date?
Thanks.

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

Getting the following Error when trying to add the Link Clicks Report SQL in DNN 5.6.2 Community.

System.Data.SqlClient.SqlException: Invalid column name 'MOduleTitle'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String ConnectionString, String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String ConnectionString, String Script) /* create the report procedure*/ Create procedure dbo.[GetSiteLog22] @PortalId int, @PortalAlias nvarchar(50), @StartDate datetime, @EndDate datetime as /* Created : Bruce Chapman 6 March 2008 Modifications */ BEGIN /* work out the time difference between the portal site timezone and the installed server timezone*/ declare @timeDiff int /* the time difference is the difference between the portal site timezone, and the time zone of the sql server*/ /* the timezone of the sql server can be worked out by the difference between the current date and the utc date */ select @timeDiff = timezoneOffset - dateDiff(n,getutcDate(),getDate()) from Portals where portalID = @portalID /* the filtering start and end date should be assumed to be given in the local portal time so we go back to the sql server timezone by subtracting the difference between the portal timezone and the sql server timezone */ select @StartDate = DateAdd(mi, -@timeDiff, @StartDate) ,@EndDate = DateAdd(mi,- @timeDiff, @EndDate) select case when isnumeric(tr.Url) = 1 then replace(t.TabPath, '//','/') else tr.Url END as [Link Destination] , tr.Clicks as [Number Clicks] , Convert(varChar(12),LastClick ,107) As [Last Click Date] , Convert(varChar(12),CreatedDate ,107) As [Link Created Date] ,m.MOduleTitle as [Link Module Title] , coalesce(replace(rt.TabPath, '//','/'), '[Removed]') as [Link Module Page] ,case when coalesce(DateDiff(d, CreatedDate, LastClick ),0) > 0 then str(Clicks * 1.0 / (DateDiff(d, CreatedDate, LastClick ) * 1.0),10,2) else '-' end as [Clicks per Day] from UrlTracking tr left join Modules m on tr.ModuleId = m.ModuleId left join TabMOdules tm on m.ModuleId = tm.ModuleId left join Tabs rt on rt.TabId = tm.TabId left join Tabs t on convert(varchar(4),t.TabId) = tr.Url where urlType in ('T','U') and tr.PortalID = @portalId order by tr.clicks desc END

Am I doing something wrong, or does the code need to be updated for this version of DNN.

Thank you.

A

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

Smae Problem with Linkklick

MOduleTitle not found.

?????

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

I'm getting the following error for the User Visit Detail...followed the steps as listed.

Error: Site Log is currently unavailable.
DotNetNuke.Services.Exceptions.ModuleLoadException: Parameter count does not match Parameter Value count. ---> System.ArgumentException: Parameter count does not match Parameter Value count. at Microsoft.ApplicationBlocks.Data.SqlHelper.AssignParameterValues(SqlParameter[] commandParameters, Object[] parameterValues) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, String spName, Object[] parameterValues) at DotNetNuke.Data.SqlDataProvider.GetSiteLog(Int32 PortalId, String PortalAlias, String ReportName, DateTime StartDate, DateTime EndDate) at DotNetNuke.Services.Log.SiteLog.SiteLogController.GetSiteLog(Int32 PortalId, String PortalAlias, Int32 ReportType, DateTime StartDate, DateTime EndDate) at DotNetNuke.Modules.Admin.SiteLog.SiteLog.BindData() at DotNetNuke.Modules.Admin.SiteLog.SiteLog.cmdDisplay_Click(Object sender, EventArgs e) --- End of inner exception stack trace ---

Re: Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

Edit: Nevermind, I reran the script and it worked the 2nd time..go figure :)