Example of Link 3 Reports

Examples of reports that might be relevant for Tenants.

Each report includes a description of the fields required to create the report in Link 3 UI.

Create, edit, delete or schedule a report

Reports

Failed Documents Last X Days

Name:

Failed Documents Last X Days

Description:

Show failed and ignored documents last X days with exception information.

Query:

SQL

DECLARE
	@lStatusTypesIdFailed INT,
	@lStatusTypesIdIgnored INT;

SELECT @lStatusTypesIdFailed = st.StatusTypesId FROM [[:ConfigurationDB:]].Tracking.tStatusTypes st WHERE st.StatusTypeName = 'Failed';
SELECT @lStatusTypesIdIgnored = st.StatusTypesId FROM [[:ConfigurationDB:]].Tracking.tStatusTypes st WHERE st.StatusTypeName = 'Ignored';

;WITH FailedDocuments AS (
	SELECT 
		d.DocumentsId,
		d.CreatedDate,
		st.StatusTypeName AS [DocumentStatus]
	FROM 
		[[:TransactionalDB:]].[Tracking].[tDocuments] d
		JOIN [[:ConfigurationDB:]].[Tracking].tStatusTypes st ON d.StatusTypesId_LatestDocumentStatus = st.StatusTypesId
	WHERE 
		(st.StatusTypesId = @lStatusTypesIdFailed OR st.StatusTypesId = @lStatusTypesIdIgnored)
		AND  d.CreatedDate > GETUTCDATE()-@iDaysBack
)
SELECT 
	d.DocumentsId,
	d.CreatedDate,
	d.DocumentStatus,
	et.Area,
	ec.ExceptionCategoryName,
	et.ErrorCode,
	et.ExceptionCause,
	ei.ExceptionMessage,
	et.ExceptionSolution
FROM 
	FailedDocuments d
	LEFT JOIN [[:TransactionalDB:]].ErrorHandling.tExceptionInstances ei ON ei.DocumentsId = d.DocumentsId
	LEFT JOIN [[:ConfigurationDB:]].ErrorHandling.tExceptionTypes et ON ei.ExceptionTypesId = et.ExceptionTypesId
	LEFT JOIN [[:ConfigurationDB:]].[ErrorHandling].[tExceptionCategories] ec on ec.ExceptionCategoriesId = et.ExceptionCategoriesId
ORDER BY
	d.CreatedDate;

Parameters:

Name	DisplayName	Type	Default Value
@iDaysBack	DaysBack	INT	7

Document Count Per Month - With An Option To See Totals Per Document Types

This report can be used if the count of documents is really big. Then this report might be faster.

Name:

Documents per Month per Document Type

Description:

Documents per Month per DocumentType - with an option to see totals per months.

[DocumentCount]: Total count of documents

[DocumentTypeName]: Name of DocumentType

Query:

SQL

/*
	2026-01-14 - (HCE).
	Count documents per month per DocumentType with respect to UTC date and DST (Daylight Saving Time).
	It will include documents x month back in time, up until today.
*/

/* Query is used in Link reports, and these variables are input values */
DECLARE
	@iMonthBack INT = 3,
	@iPerDocumentType BIT = 0;

SET @iPerDocumentType = ISNULL(@iPerDocumentType, 0)
SET @iMonthBack = ISNULL(@iMonthBack, 3);

DECLARE
	@lLocalTz SYSNAME = N'Central European Standard Time',
	@lStartDateUtc DATETIME,
	@lStartDateTemp DATETIME;

/* Subtract (MonthBack - 1) from todays UTC date as current month also counts. */
SET @lStartDateTemp = DATEADD(MONTH, -(@iMonthBack - 1), GETUTCDATE());
/* Make a new start-date that starts on the day 1 to get the whole months of data */
SET @lStartDateTemp = DATEFROMPARTS(YEAR(@lStartDateTemp), MONTH(@lStartDateTemp), 1);
/* Set time zone to UTC - ex. this start date '2025-12-01' will become this DATETIME value '2025-11-30 23:00:00' */
SET @lStartDateUtc = @lStartDateTemp AT TIME ZONE @lLocalTz AT TIME ZONE 'UTC';

;WITH Bounds AS
(
    -- Find the local-month span that covers your data (or a chosen date range)
    SELECT
        MinUtc = MIN(d.CreatedDate),
        MaxUtc = MAX(d.CreatedDate)
    FROM
		[[:TransactionalDB:]].Tracking.tDocuments d
    WHERE
		d.CreatedDate > @lStartDateUtc
),
MonthSpan AS
(
    -- Convert min/max once to local month starts
    SELECT
		StartLocalMonth = DATEFROMPARTS(
			YEAR((CAST(MinUtc AS DATETIME2(0)) AT TIME ZONE 'UTC' AT TIME ZONE @lLocalTz)),
			MONTH((CAST(MinUtc AS DATETIME2(0)) AT TIME ZONE 'UTC' AT TIME ZONE @lLocalTz)),
			1 /* First day of month */
        ),
        EndLocalMonth = DATEFROMPARTS(
            YEAR((CAST(MaxUtc AS DATETIME2(0)) AT TIME ZONE 'UTC' AT TIME ZONE @lLocalTz)),
            MONTH((CAST(MaxUtc AS DATETIME2(0)) AT TIME ZONE 'UTC' AT TIME ZONE @lLocalTz)),
            1 /* First day of month */
        )
    FROM Bounds
),
Months AS
(
    -- Generate local month starts
    SELECT
		ms.StartLocalMonth AS MonthStartLocal
    FROM
		MonthSpan ms
    UNION ALL
    SELECT
		DATEADD(MONTH, 1, m.MonthStartLocal)
    FROM
		Months m
		CROSS JOIN MonthSpan ms
    WHERE
		m.MonthStartLocal < ms.EndLocalMonth
),
MonthUtc AS
(
    -- Convert each local month boundary to UTC ONCE (DST-safe)
    SELECT
        m.MonthStartLocal,
        MonthStartUtc = CAST((CAST(m.MonthStartLocal AS DATETIME2(0)) AT TIME ZONE @lLocalTz AT TIME ZONE 'UTC') AS DATETIME2(0)),
        NextMonthStartUtc = CAST((CAST(DATEADD(MONTH, 1, m.MonthStartLocal) AS DATETIME2(0)) AT TIME ZONE @lLocalTz AT TIME ZONE 'UTC') AS DATETIME2(0))
    FROM
		Months m
),
Agg AS
(
    -- Aggregate
    SELECT
		mu.MonthStartLocal AS [Month],
		d.DocumentTypesId,
		COUNT_BIG(*) AS DocumentCount
    FROM
		MonthUtc mu
		JOIN [[:TransactionalDB:]].Tracking.tDocuments d ON d.CreatedDate >= mu.MonthStartUtc AND d.CreatedDate < mu.NextMonthStartUtc
    GROUP BY
		mu.MonthStartLocal,
		d.DocumentTypesId
)
SELECT
	a.[Month],
	ISNULL(dt.DocumentTypeName, 'Unknown') AS DocumentTypeName,
	a.DocumentCount AS [Document Count]	
INTO
	#docs
FROM
	Agg a
	LEFT JOIN [[:ConfigurationDB:]].DistributionModel.tDocumentTypes dt ON dt.DocumentTypesId = a.DocumentTypesId
OPTION (MAXRECURSION 1000);

IF @iPerDocumentType = 1
BEGIN
	SELECT
		FORMAT(d.[Month], 'yyyy-MMMM', 'da-dk') AS [Year-Month],
		d.DocumentTypeName,
		d.[Document Count]
	FROM
		#docs d
	ORDER BY
		d.[Month],
		d.DocumentTypeName;
END
ELSE
BEGIN
	SELECT
		FORMAT(d.[Month], 'yyyy-MMMM', 'da-dk') AS [Year-Month],
		SUM(d.[Document Count]) AS [Document Count]
	FROM
		#docs d
	GROUP BY
		d.[Month]
	ORDER BY
		d.[Month];
END

DROP TABLE IF EXISTS #docs;

Parameters:

Name	DisplayName	Type	Default Value
@iMonthBack	MonthBack	INT	3
@iPerDocumentType	Pr. DocumentType	BOOL	0

Document Count Per Month - Optional Per Document Types and Partners

Displays the number of documents, optionally broken down by document type or partner. If the report execution time is long, consider narrowing the start and end date range.

Name:

Document Count Per Month

Description:

Document count per month, optional per document type or partners

[Document Count]: Total count of documents

Query:

SQL

/*
	Input parameters:
	
	DECLARE
		@iStartDate DATETIME,
		@iEndDate DATETIME,
		@iPerDocumentTypes BIT = 0,
		@iPerPartners BIT = 0;
*/

IF @iStartDate IS NULL
BEGIN
	SET @iStartDate = DATEADD(MONTH, -3, GETUTCDATE());
END

IF @iEndDate IS NULL
BEGIN
	SET @iEndDate = GETUTCDATE();
END

SELECT
	CAST(DATEADD(MONTH, DATEDIFF(MONTH, '2020-01-01', d.CreatedDate), '2020-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS DATE) AS DateTimeMonth,
	dt.DocumentTypeName,
	p_in.PartnerName AS FromPartnerName,
	p_out.PartnerName AS ToPartnerName,
	COUNT(*) AS DocumentCount
INTO
	#docs
FROM
	[[:TransactionalDB:]].Tracking.tDocuments d
	JOIN [[:ConfigurationDB:]].DistributionModel.tDocumentTypes dt ON dt.DocumentTypesId = d.DocumentTypesId
	JOIN [[:ConfigurationDB:]].partner.tPartners p_in ON p_in.PartnersId = d.PartnersId_In
	JOIN [[:ConfigurationDB:]].partner.tPartners p_out ON p_out.PartnersId = d.PartnersId_Out
WHERE
	d.CreatedDate > @iStartDate
	AND d.CreatedDate <= @iEndDate
GROUP BY
	dt.DocumentTypeName,
	p_in.PartnerName,
	p_out.PartnerName,
	DATEDIFF(MONTH, '2020-01-01', d.CreatedDate)
ORDER BY
	DATEDIFF(MONTH, '2020-01-01', d.CreatedDate),
	dt.DocumentTypeName,
	p_in.PartnerName,
	p_out.PartnerName;

IF @iPerDocumentTypes = 1
BEGIN
	IF @iPerPartners = 1
	BEGIN
		SELECT
			d.DateTimeMonth,
			d.DocumentTypeName,
			d.FromPartnerName,
			d.ToPartnerName,
			SUM(d.DocumentCount) AS DocumentCount
		FROM
			#docs d
		GROUP BY
			d.DateTimeMonth,
			d.DocumentTypeName,
			d.FromPartnerName,
			d.ToPartnerName
		ORDER BY
			d.DateTimeMonth,
			d.DocumentTypeName,
			d.FromPartnerName,
			d.ToPartnerName
	END
	ELSE
	BEGIN
		SELECT
			d.DateTimeMonth,
			d.DocumentTypeName,
			SUM(d.DocumentCount) AS DocumentCount
		FROM
			#docs d
		GROUP BY
			d.DateTimeMonth,
			d.DocumentTypeName
		ORDER BY
			d.DateTimeMonth,
			d.DocumentTypeName;
	END
END
ELSE
BEGIN
	IF @iPerPartners = 1
	BEGIN
		SELECT
			d.DateTimeMonth,
			d.FromPartnerName,
			d.ToPartnerName,
			SUM(d.DocumentCount) AS DocumentCount
		FROM
			#docs d
		GROUP BY
			d.DateTimeMonth,
			d.FromPartnerName,
			d.ToPartnerName
		ORDER BY
			d.DateTimeMonth,
			d.FromPartnerName,
			d.ToPartnerName
	END
	ELSE
	BEGIN
		SELECT
			d.DateTimeMonth,
			SUM(d.DocumentCount) AS DocumentCount
		FROM
			#docs d
		GROUP BY
			d.DateTimeMonth
		ORDER BY
			d.DateTimeMonth
	END
END

DROP TABLE IF EXISTS #docs;

Parameters:

Name	DisplayName	Type	Default Value
@iStartDate	StartDate	Datetime	2025-01-01
@iEndDate	EndDate	Datetime	GETUTCDATE()
@iPerDocumentTypes	PerDocumentTypes	BIT	0
@iPerPartners	PerPartners	BIT	0

Document Count Per Month - Incl. High Transaction Distribution

Name:

Document Count Per Month - Incl. High Transactional Distribution

Description:

Document count per month - including documents where High-Transaction is enabled on the distributions.

[Document Count]: Total count of documents
[High-Transaction Document Count]: Count of documents where distribution is set to High-Transaction
[Standard Document Count]: Documents without a distribution or where distribution is not set to High-Transaction

Query:

SQL

/*
DECLARE
	@iStartDate DATETIME2(0);

SET @iStartDate = DATEADD(MONTH, -12, GETUTCDATE());
*/

;WITH HighTransactionDistribution AS (
	SELECT
		ck.ConfigurationKeysId,
		ck.KeyName,
		cku.KeyUsageName,
		dc.DistributionsId,
		dc.Value AS HighTransactionDistribution
	FROM
		[[:ConfigurationDB:]].Presentation.tConfigurationKeys ck
		JOIN [[:ConfigurationDB:]].Presentation.tConfigurationKeyUsages cku ON cku.ConfigurationKeyUsagesId = ck.ConfigurationKeyUsagesId
		JOIN [[:ConfigurationDB:]].DistributionModel.tDistributionConfigurations dc ON dc.ConfigurationKeysId = ck.ConfigurationKeysId
	WHERE
		cku.KeyUsageName = 'Distribution_Advanced'
		AND ck.KeyName = 'BBLINK_HighTransactionDistribution'
		AND dc.DistributionsId IS NOT NULL
)
SELECT
	CAST(DATEADD(MONTH, DATEDIFF(MONTH, '2020-01-01', d.CreatedDate), '2020-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS DATE) AS [Month],
	FORMAT(COUNT(*), 'N0', 'da-dk') AS [Document Count], /* Total number of documents created by MONTH */
	FORMAT(SUM (
		CASE
			WHEN (hi.DistributionsId IS NULL OR hi.HighTransactionDistribution = 'False') THEN 1
			ELSE 0
		END
	), 'N0', 'da-dk') AS [Standard Document Count],
	FORMAT(SUM (
		CASE
			WHEN hi.DistributionsId IS NOT NULL AND hi.HighTransactionDistribution = 'True' THEN 1
			ELSE 0
		END
	), 'N0', 'da-dk') AS [High-Transaction Document Count]
FROM
	[[:TransactionalDB:]].Tracking.tDocuments d
	LEFT JOIN HighTransactionDistribution hi ON hi.DistributionsId = d.DistributionsId
WHERE
	d.CreatedDate > @iStartDate
GROUP BY
	DATEDIFF(MONTH, '2020-01-01', d.CreatedDate)
ORDER BY
	DATEDIFF(MONTH, '2020-01-01', d.CreatedDate);

Parameters:

Name	DisplayName	Type	Default Value
@iStartDate	StartDate	Datetime	2025-01-01

User Audit and Login Overview

Name:

User Audit and Login Overview

Description:

Count changes (insert/update/delete) per user and last successfully login date.

Query:

SQL

--- Count changes per user, and include last login date.
;WITH LastLogins AS (
    SELECT
        CAST(CreatedDate AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS DATETIME) AS CreatedDate,
        JSON_VALUE(JsonLog, '$.Username') AS Username,
        JSON_VALUE(JsonLog, '$.Message') AS Message,
        ROW_NUMBER() OVER (PARTITION BY JSON_VALUE(JsonLog, '$.Username') ORDER BY CreatedDate DESC) AS RowNumber
        ,ale.UsersId
    FROM
		[[:TransactionalDB:]].Logging.tAuditLogEntries ale
	WHERE
		[Action] = 'user login'
		AND ale.UsersId IS NOT NULL
)
SELECT
    u.DisplayName,
    u.EmailAddress,
    ISNULL(cl.LogAction, 'User Login') AS [Action],
    COUNT(*) AS [ActionCount],
    MIN(ll.CreatedDate) AS LastLogin,
    MIN(ll.Message) AS LoginMessage
FROM
	LastLogins ll
    JOIN [[:ConfigurationDB:]].Users.tUsers u ON u.UsersId = ll.UsersId
    LEFT JOIN [[:ConfigurationDB:]].Logging.tChangeLogs cl ON cl.CreatedBy = ll.UsersId
WHERE
    1 = 1
    AND ll.RowNumber = 1 /* To get last login only */
GROUP BY
    u.DisplayName,
    u.EmailAddress,
    cl.LogAction
ORDER BY
	u.DisplayName,
    cl.LogAction;

Parameters:

None.