Create Custom Reports

DBA Dash Custom Reports

Intro

This feature allows you to extend the functionality of DBA Dash, by creating your own bespoke reports. It’s really easy to get started if you are familiar with writing T-SQL code.

⚠️Note: Breaking changes will be minimized where possible, but forward compatibility is not guaranteed for any custom reports you create.

Stored procedure & parameters

To create a custom report, you need to create a stored procedure in the UserReport schema with at least one of these parameters. The parameters below are used by the app to provide context information - which instances or database the report should run for.

  • @InstanceIDs IDs

IDs is a user defined table type. The application will pass a list of InstanceIDs. If you run the report at instance level, the app will pass the InstanceID for all instances that match the selected filters. When navigating down to a specific instance this table will contain a single row. Note: For Azure DB, there is an InstanceID associated with each database.

  • @InstanceID INT

The @InstanceID parameter provides the InstanceID associated with the selected instance. Use this parameter instead of @InstanceIDs to have the report listed at instance level instead of root level

  • @DatabaseID INT

The @DatabaseID parameter provides the DatabaseID parameter associated with the selected database. This is the DBA Dash DatabaseID associated with the dbo.Databases table and it uniquely identifies a specific database (not the database_id from sys.databases). Use this parameter if you want your report to appear at database level

If your procedure has @FromDate and @ToDate parameters, the application will display the date range filter and supply these parameters to the report. The dates are supplied are UTC.

If you add additional parameters to the report, the user is able to supply them manually. If you want the report to run automatically, provide default values for the additional parameters.

DBA Dash Custom Report Parameters

Once you create the procedure, the report will be available the next time you load the application. Custom reports will also be refreshed when you click the Clear All option in the filter menu or switch repository connections.

Examples

Root & Instance level report

This basic example lists the SQL instances associated with the current context in the app. This report might not be very useful but it demonstrates the basics needed to create a report.

CREATE PROC UserReport.RootAndInstanceLevelTest(
	@InstanceIDs IDs READONLY
)
AS
SELECT I.ConnectionID 
FROM dbo.Instances I 
JOIN @InstanceIDs T ON I.InstanceID = T.ID

Instance only report

This report will just list the name of the current instance. The report will only appear at instance level as we don’t have an @InstanceIDs parameter of type IDs.

CREATE PROC UserReport.InstanceOnlyTest(
	@InstanceID INT
)
AS
SELECT I.ConnectionID 
FROM dbo.Instances I 
WHERE I.InstanceID = @InstanceID

Database only report

This report will only work at database level and it will list the name of the current instance and database.

CREATE PROC UserReport.DatabaseLevelTest(
	@DatabaseID INT
)
AS
SELECT	I.ConnectionID, 
		D.name as DB
FROM dbo.Databases D 
JOIN dbo.Instances I ON D.InstanceID = I.InstanceID
WHERE D.DatabaseID = @DatabaseID

Real world example - report to display sysadmin users

DBA Dash collects permissions and role membership by default but it doesn’t display this information anywhere in the GUI. This is a good example for a custom report…

The stored procedure below will list the logins that are members of the sysadmin role (or other roles using @FixedRole parameter which we default to sysadmin). The stored procedure returns 3 results. The second and third results pivot the data by instance and login. In addition to creating the stored procedure, the script below also inserts the customizations made in the GUI to set a description for the report and to rename the results.

CREATE OR ALTER PROC [UserReport].[SysAdminUsers](
	@InstanceIDs IDs READONLY,
	@IsDisabled BIT=NULL,
	@TypeDesc NVARCHAR(60)=NULL,
	@Type CHAR(1) = NULL,
	@FixedRole NVARCHAR(128)='sysadmin',
	@IncludeHidden BIT =0
)
AS
/* Base query into temp table */
SELECT I.InstanceDisplayName AS Instance,
		SP.name AS Login,
		SP.type AS Type,
		SP.type_desc AS [Type Description],
		SP.is_disabled AS [Is Disabled],
		SP.create_date AS [Created Date],
		SP.modify_date AS [Modified Date]
INTO #T
FROM dbo.ServerPrincipals SP
JOIN dbo.Instances I ON SP.InstanceID = I.InstanceID
WHERE EXISTS(
		SELECT 1 
		FROM dbo.ServerRoleMembers SRM
		JOIN dbo.ServerPrincipals R ON SRM.role_principal_id = R.principal_id
		WHERE SP.principal_id = SRM.member_principal_id
		AND R.type = 'R'
		AND R.name= @FixedRole
		AND R.is_fixed_role=1
		AND SRM.member_principal_id = SP.principal_id
		AND SRM.InstanceID = I.InstanceID
		AND R.InstanceID = I.InstanceID
		)
AND EXISTS(SELECT 1 
		FROM @InstanceIDs T 
		WHERE T.ID = I.InstanceID
		)
AND (SP.is_disabled = @IsDisabled OR @IsDisabled IS NULL)
AND (SP.type_desc = @TypeDesc OR @TypeDesc IS NULL)
AND (SP.type = @Type OR @Type IS NULL)
AND	(I.ShowInSummary=1 OR @IncludeHidden=1)

/* Raw data */
SELECT Instance,
       Login,
       Type,
       [Type Description],
       [Is Disabled],
       [Created Date],
       [Modified Date]
FROM #T

/* Dynamic pivot by instance */
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'
SELECT	T.Login,
		T.Type,
		T.[Type Description]
		' + (SELECT ',MAX(CASE WHEN T.Instance = ' + QUOTENAME(T.Instance,'''') + ' AND  T.[Is Disabled] =1 THEN ''D'' WHEN T.Instance = ' + QUOTENAME(T.Instance,'''') + ' AND T.[Is Disabled]=0 THEN ''A'' ELSE ''-'' END) AS ' + QUOTENAME(T.Instance) 
		FROM #T T
		GROUP BY T.Instance
		ORDER BY T.Instance
		FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') + '
FROM #T T
GROUP BY	T.Login,
			T.Type,
			T.[Type Description]
ORDER BY T.Login
OPTION(RECOMPILE)'

EXEC sp_executesql @SQL

/* Dynamic pivot by login */
SET @SQL = N'
SELECT	T.Instance
		' + (SELECT ',MAX(CASE WHEN T.Login = ' + QUOTENAME(T.Login,'''') + ' AND  T.[Is Disabled] =1 THEN ''D'' WHEN T.Login = ' + QUOTENAME(T.Login,'''') + ' AND T.[Is Disabled]=0 THEN ''A'' ELSE ''-'' END) AS ' + QUOTENAME(T.Login) 
		FROM #T T
		GROUP BY T.Login
		ORDER BY T.Login
		FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') + '
FROM #T T
GROUP BY T.Instance
ORDER BY T.Instance
OPTION(RECOMPILE)'

EXEC sp_executesql @SQL

GO
/* Optional - Add metadata. (Customizations done in the GUI) */

DELETE dbo.CustomReport 
WHERE SchemaName = 'UserReport' 
AND ProcedureName = 'SysAdminUsers'

INSERT INTO dbo.CustomReport ([SchemaName], [ProcedureName], [MetaData])
VALUES
( N'UserReport', N'SysAdminUsers', N'{
  "ReportName": "Sys Admin Users",
  "Description": "Members of the sysadmin role.  Use @FixedRole parameter to display members of a different role.",
  "CustomReportResults": {
    "0": {
      "ColumnAlias": {},
      "CellFormatString": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [],
      "ResultName": "Raw Data"
    },
    "1": {
      "ColumnAlias": {},
      "CellFormatString": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [],
      "ResultName": "Pivot by Instance"
    },
    "2": {
      "ColumnAlias": {},
      "CellFormatString": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [],
      "ResultName": "Pivot By User"
    }
  }
}' )

Viewing your report

Once the procedure is created it will be available the next time you start the application. The report will be available in the Reports folder. You will find this folder at root, instance and database level depending what context parameters you created for your report. If your report has a @InstanceIDs parameter you should see it at root level and instance level.

The name of your report will be the name of the stored procedure. Once you select your report a grid will be shown with the output of your stored procedure. If your report contains multiple result sets, you will see a drop down on the right-hand side to select which table to display.

Customization

Configuration Options

Column Options

Report Title

The report title is automatically set to the name of the stored procedure. In the Configure menu, select “Set Title” to adjust the name of the report.

Report Description

In the “Configure” menu, select “Set Description” to set a description for the report. This will display in a status bar at the bottom of the report. This can be used to provide some additional information about the report.

Remove Columns

Columns can be removed using the columns menu. If you want the changes to be persisted, click the Save Layout option in the Configure menu.

Re-order Columns

Use drag & drop to re-order columns. If you want the changes to be persisted, click the Save Layout option in the Configure menu.

Reset Layout

Use Reset Layout from the Configure menu to undo any customizations to column order, size or visibility.

Add Link

Links can be added to a column. The options available are:

  • None

Remove link

  • URL

Navigate to a website. A different Target Column can be selected to navigate to a URL in a different column.

  • Text

Load text in a new window with formatting options for SQL, PowerShell or plain text. A different target column can be selected to load the text in a different column.

  • Query Plan

Open Query plan in the default app for *.sqlplan files. A different target column can be selected to load the query plan from a different column.

  • Drill Down

Load a report and pass parameter values from the selected row. Configure columns to map to parameters as needed.

Column Alias

To rename a column, right-click the column header and select Rename Column

Column Format

To set a column format, right-click the column header and select Set Format String. This allows you to change how numbers or dates are displayed in the grid. e.g.

  • N1 = Numeric with 1 digit
  • P1 = Percent with 1 digit
  • yyyy-MM-dd - Date. e.g. 2023-10-25

More options:

Timezone Conversion

Dates are assumed to be in UTC format and are converted automatically to the local/selected timezone. To prevent this automatic conversion, right-click the column header and uncheck Convert to local timezone.

Security

To run a custom report a user needs to have EXECUTE permissions on the stored procedure. If a user doesn’t have EXECUTE permissions, the report won’t be visible and the user won’t be able to run the report. You can add a user to the RunUserReports role to grant access to all custom reports.

Sharing Reports

Script Report

🤝It’s easy to share your report creations with the community. Click the Script Report option from the Configure menu (gear icon) of your custom report (Available in 2.50 and later). This includes the stored procedure you created and any report customizations made in the GUI. Post it on your blog and/or add a 🙌Show and tell post.