🏭🎗️🗒️Microsoft Fabric: Real-Time Dashboards (RTD) [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 10-Mar-2025

Real-Time Intelligence architecture [5]

[Microsoft Fabric] Real-Time Dashboard

• [def]
• a collection of tiles
• optionally organized in pages
• act as containers of tiles
• organize tiles into logical groups
• e.g. by data source or by subject area
• used to create a dashboard with multiple views
• e.g. dashboard with a drillthrough from a summary page to a details page [1]
• each tile has 
• an underlying query
• a visual representation
• exists within the context of a workspace [1]
• always associated with the workspace used to create it [1]
• {concept} tile
• uses  KQL snippets to retrieve data and render visuals [1]
• can be added directly from queries written in a KQL queryset [1]
• {concept} data source
• reusable reference to a specific database in the same workspace as the dashboard [1]
• {concept} parameters 
• significantly improve dashboard rendering performance [1]
• enable to use filter values as early as possible in the query
• filtering is enabled when the parameter is included in the query associated with the tiles [1]
• {concept} cloud connection
• uses dashboard owner's identity to give access to the underlying data source to other users [2]
• when not used for 90 days, it will expire [2]
• ⇒ a new gateway connection must be set up [2]
• via Manage connections >> Gateways page >> Edit credentials and verify the user again
• a separate connection is needed for each data source [2]
• {feature} natively export KQL queries to a dashboard as visuals and later modify their underlying queries and visual formatting as needed [1]
• the fully integrated dashboard experience provides improved query and visualization performance [1]
• {feature} encrypted at rest
• dashboards and dashboard-related metadata about users are encrypted at rest using Microsoft-managed keys [1]
• {feature} auto refresh
• allows to automatically update the data on a dashboard without manually reloading the page or clicking a refresh button [1]
• can be set by a database editor
• both editors and viewers can change the actual rate of auto refresh while viewing a dashboard [1]
• database editors can limit the minimum refresh rate that any viewer can set
• ⇐ reduces the cluster load
• when set, database users can't set a refresh rate lower than the minimum [1]
• {feature} explore data 
• enables users to extend the exploration of dashboards beyond the data displayed in the tiles [3]
• begins with viewing the data and its corresponding visualization as they appear on the tile [3]
• users can add or removing filters and aggregations, and use further visualizations [3]
• ⇐ no knowledge of KQL is needed [3]
• {feature} conditional formatting
• allows users to format data points based on their values, utilizing 
• colors
• {rule} color by condition
• allows to set one or more logical conditions that must be met for a value to be colored [4]
• available for table, stat, and multi stat visuals [4]
• {rule} color by value
• allows to visualize values on a color gradient [4]
• available for table visuals [4]
• tags
• icons
• can be applied either 
• to a specific set of cells within a designated column [4]
• to entire rows [4]
• one or more conditional formatting rules can be applied for each visual [4]
• when multiple rules conflict, the last rule defined takes precedence over any previous ones [4]
• {action} export dashboard
• dashboards can be exported to a JSON file
• can be useful in several scenarios 
• {scenario} version control
• the file can be used to restore the dashboard to a previous version [1]
• {scenario} dashboard template
• the file can be used as template for creating new dashboards [1]
• {scenario} manual editing
• edit the file to modify the dashboard and imported the file back to the dashboard [1]
• ADX dashboards can be exported and imported as RT dashboards [6]
• {action} share dashboard
• one can specify if the user can view, edit, or share [2]
• ⇐ the permissions are not for the underlying data [2]
• permissions are set by defining the identity that the dashboard uses for accessing data from each data sources[2]
• {type|default} pass-through identity
• used when authenticating to access the underlying data source [2]
• the user is only able to view the data in the tiles [2]
• {type} dashboard editor’s identity:
• allows the user to use editor’s identity, and thus permissions[2]
• the editor defines a cloud connection that the dashboard uses to connect to the relevant data source [2]
• only editors can define cloud connections and permissions for a specific real-time dashboard [2]
• each editor that modifies the real-time dashboard needs to set up own cloud connection [2]
• if a valid connection doesn't exist, the user is able to view the real-time dashboard but will only see data if they themselves have access to it [2]
• {action} revoke a user’s access permissions
• remove access from the dashboard [2]
• remove the cloud connection.
• via Settings >> Manage connections and gateways >> Options >> Remove
• remove the user from the cloud connection.
• via Settings >> Manage connections and gateways >> Options >> Manage users >> {select User} >> Delete
• edit the Data source access permissions.
• via Data source >> New data source >> edit >> Data source access >> Pass-through identity
• ⇐ the user uses own identity to access the data source [2]
• {prerequisite} a workspace with a Microsoft Fabric-enabled capacity [1]
• {prerequisite} a KQL database with data [1]
• {setting} Users can create real-time dashboards [1]

Previous Post <<||>> Next Post

References:

[1] Microsoft Learn (2024) Fabric: Create a Real-Time Dashboard [link] 

[2] Microsoft Learn (2024) Fabric: Real-Time Dashboard permissions (preview) [link] 

[3] Microsoft Learn (2024) Fabric: Explore data in Real-Time Dashboard tiles [link] 
[4] Microsoft Learn (2024) Fabric: Apply conditional formatting in Real-Time Dashboard visuals [link]

[5] Microsoft Learn (2025) Fabric: Real Time Intelligence L200 Pitch Deck [link]
[6] Microsoft Fabric Updates Blog (2024) Easily recreate your ADX dashboards as Real-Time Dashboards in Fabric, by Michal Bar [link]
[7] Microsoft Learn (2025) Create Real-Time Dashboards with Microsoft Fabric [link]

Resources:

[R1] Microsoft Learn (2024) Microsoft Fabric exercises [link]
[R2] Microsoft Fabric Updates Blog (2024) Announcing Real-Time Dashboards generally available [link]

[R3] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]

Acronyms:
ADX - Azure Data Explorer
KQL  - Kusto Query Language
MF - Microsoft Fabric
RT - Real-Time

💎🤖💫SQL Reloaded: Copilot Stories (Part VII: Using the sp_MSForEachTable Undocumented Stored Procedures in SQL Server)

Probably few know that SQL Server makes available a set of undocumented stored procedures that can be useful in certain scenarios that don't involve code that will deployed into production. One of them is sp_MSForEachTable on which I wrote several posts until now. The stored procedure executes one or more scripts based on a set of constraints against the subset of available tables. The first surprising fact is that Copilot does know the respective stored procedure and seems to correctly generate code for it!

Disclaimer: The below code was generated by Copilot via prompts used for demonstration purposes!

Please note that the following code is provided as it is and you can use it on your own risk! Don't run the code in a productive environment and make sure that you have a usable backup! Please consider all caution when you use a feature marked as undocumented!

Let's start with a simple script and increase the complexity of the queries as it goes!

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure in SQL Server

Output:

-- query that uses this stored procedure to update a specific column in all tables:

EXEC sp_MSForEachTable 'UPDATE ? SET YourColumn = ''NewValue'' WHERE YourCondition'

/*
? is a placeholder for the table name.
YourColumn is the column you want to update.
NewValue is the new value you want to set.
YourCondition is the condition that must be met for the update to occur.*/

As the description says, "?" acts as placeholder for table's name while the other values need to be provided explicitly. The code can be used to overwrite the value of a column that exists in some of the tables. For example, when the data come over an interface from various system, it may happen that there is a mix of upper and lower case values that designate the same value. This might not be an issue for Dynamics AX but can create issues further on. 

Here's the actual code for performing the update:

-- update DataAreaId for DMEMF company
EXEC sp_MSForEachTable 'UPDATE ? SET DataAreaId = Upper(DataAreaId) WHERE DataAreaId = ''demf'''

Executing the code blindly can create serious damage, so always test first the code in a test environment and have a backup available! One needs to be careful if there are fields which have same name(s) but different meaning! It's important to perform a thorough analysis in this direction.

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to select records from a set of tables.

Output:

-- return all columns
EXEC sp_MSForEachTable 'SELECT * FROM ?'

-- return specific columns based on condition
EXEC sp_MSForEachTable 'SELECT Column1, Column2 FROM ? WHERE YourCondition'

The second query will result in errors if the columns don't exist, though the execution runs further! The errors can be filtered out in Excel by sorting them accordingly.
The query works in general for combinations of columns available in many tables. It's the case of DataAreaId and RecId available in Dynamics 365 for Finance and Operations for a high percentage of the tables. RecId should be available for all the tables.

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to count all the records from a table

Output:

-- retrieving the number of records for all tables
EXEC sp_MSForEachTable 'SELECT ''?'' AS TableName, COUNT(*) AS RecordCount FROM ?'

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to select all the records from tables containing the word invent.

Output:

DECLARE @sql NVARCHAR(MAX)

SET @sql = ''
EXEC sp_MSForEachTable 
    @command1 = 'IF ''?'' LIKE ''%invent%'' BEGIN SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; '' END',
    @replacechar = '?',
    @command2 = 'EXEC sp_executesql @sql'

PRINT @sql

Here's the working version of the query:

-- retrieve the records for tables having 'invent' intheir name
EXEC sp_MSForEachTable 
    @command1 = 'DECLARE  @sql nvarchar(max) = ''''
		IF ''?'' LIKE ''%invent%'' BEGIN SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; '' END;
                    EXEC sp_executesql @sql;'

A few days later, using the same prompt in Edge I got the following answer, simpler and correct:

-- second version run on Edge Copilot
EXEC sp_MSForEachTable 
    @command1 = 'IF ''?'' LIKE ''%invent%'' 
                BEGIN 
                    PRINT ''?'' 
                    EXEC(''SELECT * FROM ?'') 
                END'

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure when the DataAreaId field exists in the table.

Output:

DECLARE @sql NVARCHAR(MAX)

SET @sql = ''
EXEC sp_MSForEachTable 
    @command1 = 'IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''DataAreaId'') 
                 BEGIN 
                     SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; '' 
                 END',
    @replacechar = '?',
    @command2 = 'EXEC sp_executesql @sql'

PRINT @sql

The query uses the metadata available to retrieve the tables in scope.

EXEC sp_MSForEachTable 
    @command1 = ' DECLARE  @sql nvarchar(max) = ''''
	IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''DataAreaId'') 
        BEGIN 
            SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; '' 
			EXEC sp_executesql @sql
        END'

Here's the answer in Edge:

EXEC sp_MSForEachTable 
    @command1 = 'IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(''?'' ) AND name = ''DataAreaId'') 
                BEGIN 
                    PRINT ''?'' 
                    EXEC(''SELECT * FROM ?'') 
                END'

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to select records from a set of tables while it checks whether the fields exist in the table.

Output:

DECLARE @sql NVARCHAR(MAX)

SET @sql = ''
EXEC sp_MSForEachTable 
    @command1 = 'DECLARE @table NVARCHAR(128)
                 SET @table = PARSENAME(''?'', 1)
                 IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME = ''Field1'')
                 AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME = ''Field2'')
                 BEGIN
                     SET @sql = @sql + ''SELECT Field1, Field2 FROM '' + ''?'' + ''; ''
                 END',
    @replacechar = '?',
    @command2 = 'EXEC sp_executesql @sql'

PRINT @sql

The query used the metadata available in the INFORMATION_SCHHEMA.COLUMNS. Unfortunately, the code doesn't work as expected. The second command is not necessary for performing the task, so the code can be written as follows, and now it works:

EXEC sp_MSForEachTable 
    @command1 = 'DECLARE @table NVARCHAR(128), @sql nvarchar(max) = ''''
                 SET @table = PARSENAME(''?'', 1)
                 IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME = ''DataAreaId'')
                 AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME = ''ItemId'')
                 BEGIN
                     SET @sql = @sql + ''SELECT DataAreaId, ItemId FROM '' + ''?'' + ''; ''
                 END
		 EXEC sp_executesql @sql'

The query is easy to modify to include more columns after the same pattern, if such tables exist after all (e.g. in Dynamics 365 one can search for the combination ItemId, DataAreaId and one or more Inventory dimension like InventSiteId, InventLocationId, etc.). 

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to count all the records for a subset of tables.

Output:

DECLARE @sql NVARCHAR(MAX)

SET @sql = ''
EXEC sp_MSForEachTable 
    @command1 = 'IF ''?'' LIKE ''%subset%'' 
                 BEGIN 
                     SET @sql = @sql + ''SELECT ''''?'''' AS TableName, COUNT(*) AS RecordCount FROM '' + ''?'' + ''; '' 
                 END',
    @replacechar = '?',
    @command2 = 'EXEC sp_executesql @sql'

PRINT @sql

Unfortunately, that's not what I was expecting. So, let's give it a second try:

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to count all the records for a subset of the tables available in the SYS.TABLES.

Output:

EXEC sp_MSForEachTable 
    @command1 = 'IF OBJECT_ID(''?'' , ''U'') IN (SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME IN (''YourTable1'', ''YourTable2'', ''YourTable3'')) 
                BEGIN 
                    PRINT ''?'' 
                    EXEC(''SELECT COUNT(*) FROM ?'') 
                END'

Replacing the tables names accordingly, the query becomes

EXEC sp_MSForEachTable 
    @command1 = 'IF OBJECT_ID(''?'' , ''U'') IN (SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME IN (''InventTable'', ''InventDim'', ''InventDimCombination'')) 
                BEGIN 
                    PRINT ''?'' 
                    EXEC(''SELECT COUNT(*) FROM ?'') 
                END'

Comments:
1) Edge Copilot returned the expected solution directly and I wonder what the reason could be? It can be the fact that I run the tests on different machines at different points in time, though more likely the reason is that I used two different Copilots - from Edge, respectively Microsoft 365. The expectation was to get better answers in M365 Copilot, which wasn't the case in the above tests!

The two Copilots have different architectures and were trained with different sets of data, respectively have different sources. One important difference is that Edge Copilot primarily relies on web content and users' interactions within the browser, while M365 Copilot relies on data from the D365 services. Both use LLM and NLP, while M365 Copilot uses Microsoft Graph and Syntex.

One thing I remarked during the tests is that sometimes I get different answers to the same question also within the same Copilot. However, that's expected given that there are multiple ways of addressing the same problem! It would still be interesting to understand how a solution is chosen.

2) I seldom used sp_MSForEachTable with more than one parameter, preferring to prepare the needed statement beforehand, as in the working solutions above.

Happy coding!

Refer4ences:
[1] SQLShack (2017) An introduction to sp_MSforeachtable; run commands iteratively through all tables in a database [link]

Acronyms:
LLM - large language model
M365 - Microsoft 365
NLP - natural language processing

Previous Post <<||>> Next Post

🪄SSRS (& Paginated Reports): Design (Part II: Parameter Dependencies in Dropdowns)

Introduction

Quite often there are dependencies between the values of two distinct dropdowns, typically the values representing the different levels of a direct on implied hierarchy. This means that the choice at the upper level determines the values which will be shown at a lower level, the corresponding query needing to handle the value as a parameter. There are several ways on how one can handle this, the choice depending also on the data provider considered for the report.

Because the AdventureWorks database is used within an SQL Server, the available choices are MSOLEDBSQL, SQLOLEDB, ODBC or SQL Server Native Client. It should be noted that the SQLOLEDB and the Native Clients providers have been deprecated and shouldn't be used for new developments! In the project was used the Microsoft OLE DB Provider for SQL Server (MSOLEDBSQL) provider.

For exemplification will be used the reports build previously. The hierarchy will be built based on the State and Territory, a Country belonging to a Territory, while a State to a Country. For the beginning we will build the dependency existing between the Country and State. 

First Level of Dependency 

The text parameter for StateCountry dataset will be defined similarly to the one consider for the Country, and have as source the following query:

-- State Provinces
SELECT '' Id
, '(All)' Name
UNION ALL
SELECT StateProvinceCode
, StateProvinceCode + ' ' + Name
FROM [Person].[StateProvince]
WHERE CountryRegionCode = ISNULL(NullIf(?, ''), CountryRegionCode)
ORDER BY Id

The Country parameter will be handled in the WHERE constraint - it's the role of the question mark. When an empty string was selected, the equivalent for ignoring the parameter, the formula used will result to an identity, which however makes sense only if there's always a value for the Country Code in the Person.StateProvince table. Otherwise the same line needs to be written as follows:

WHERE IsNull(CountryRegionCode, '') = COALESCE(NullIf(?, ''), CountryRegionCode, '')

This might look strange, though it does the trick of handling the nulls. According to the rules of defensive programming, one should treat the eventuality of nulls. However, this depends also on application's design. If one develops reports with this supposition, then the supposition should be made explicit in the documentation!

Once the query provided, SSRS recognizes that a parameter was provided and requires to map the question mark to the respective parameter. Within dataset's properties click on the "Parameter" tab and selected @Country as Parameter Value:

Parameters in Dataset Properties

A question mark needs to be considered in the query for each parameter, the parameters from the Dataset Properties being displayed in the order in which they appear in the query.

Once the dataset defined, one can go on and define the StateProvince parameter similarly to the Country parameter and consider the StateProvinces dataset as source. The StateProvince parameter needs to be added also in report's header:
= "State Province: " & Parameters!StateProvince.Label

Within the Addresses main dataset there needs to be added the declaration for the StateProvince parameter, while in where the corresponding clause:

-- SQL Parameter
& " DECLARE @StateProvince as nvarchar(3) = '" & Parameters!StateProvince.Value & "'" & vbcrlf 

-- constraint in WHERE clause
& IIf(Parameters!StateProvince.Value<> "", "     AND SIC.StateProvinceCode = @StateProvince", "") & vbcrlf W

With these changes the report should be ready to run.

Notes:
The above query for the StateProvince dataset can be used inside of a formula with a few small changes. This should work independently of the provider used. Just replace the query in the formula and delete the parameters previously created for the data source via the Dataset Properties:

= " -- State Provinces " & vbCrLf
 & " DECLARE @Country as nvarchar(3) = '" & Parameters!Country.Value & "'" & vbcrlf 
 & " SELECT '' Id " & vbCrLf
 & " , '(All)' Name " & vbCrLf
 & " UNION ALL " & vbCrLf
 & " SELECT StateProvinceCode " & vbCrLf
 & " , StateProvinceCode + ' ' + Name " & vbCrLf
 & " FROM [Person].[StateProvince] " & vbCrLf
 & IIf(Parameters!Country.Value<>"", "     WHERE CountryRegionCode = @Country", "") & vbcrlf 
 & " ORDER BY Id " & vbCrLf

Second Level of Dependency

The second dependency is based on the Territory Group formed of North America, Pacific and Europe. There is however no Territory Group table, the relations between Countries and Territory Groups being stored into the Sales.SalesTerritory table. Therefore, the query for the corresponding dropdown can be written via a DISTINCT:

-- Territories
SELECT '' Id
, '(All)' Name
UNION ALL
SELECT DISTINCT [Group] Id
, [Group] Name
FROM Sales.SalesTerritory
ORDER BY Id

When the user selects a Territory, in the Countries and State Province dropdowns are supposed to be shown only the corresponding values for the selected value. We need to modify thus both queries via formulas.
The query for the Country dataset includes the Territory parameter and the corresponding constraint via an EXIST:

= "  -- Countries  " & vbCrLf 
& " DECLARE @Territory as nvarchar(50) = '" & Parameters!Territory.Value & "'" & vbcrlf 
& " SELECT '' Id  " & vbCrLf 
& " , '(All)' Name  " & vbCrLf 
& " UNION ALL  " & vbCrLf 
& " SELECT SSP.CountryRegionCode Id  " & vbCrLf 
& " , SSP.Name   " & vbCrLf 
& " FROM [Person].[CountryRegion] SSP " & vbCrLf 
& IIf(Parameters!Territory.Value<> "", "     WHERE EXISTS (SELECT * FROM [Sales].[SalesTerritory] SST WHERE SSP.CountryRegionCode = SST.CountryRegionCode AND SST.[Group] = @Territory)", "") & vbcrlf 
& " ORDER BY Id " & vbCrLf

In exchange the StateProvince dataset is slightly more complex:

= " -- State Provinces " & vbCrLf
 & " DECLARE @Country as nvarchar(3) = '" & Parameters!Country.Value & "'" & vbcrlf 
 & " DECLARE @Territory as nvarchar(50) = '" & Parameters!Territory.Value & "'" & vbcrlf 
 & " SELECT '' Id " & vbCrLf
 & " , '(All)' Name " & vbCrLf
 & " UNION ALL " & vbCrLf
 & " SELECT PSP.StateProvinceCode " & vbCrLf
 & " , PSP.StateProvinceCode + ' ' + PSP.Name " & vbCrLf
 & " FROM [Person].[StateProvince] PSP" & vbCrLf
 & "      JOIN [Sales].[SalesTerritory] PST" & vbCrLf
 & "       ON PSP.TerritoryId = PST.TerritoryId " & vbCrLf
 & " WHERE 0=0 " & vbCrLf
 & IIf(Parameters!Country.Value<> "", "     AND PSP.CountryRegionCode = @Country", "") & vbcrlf 
 & IIf(Parameters!Territory.Value<> "", "     AND PST.[Group] = @Territory", "") & vbcrlf 
 & " ORDER BY Id " & vbCrLf

Once the datasets changed, is still needed to modify the order of the parameters to reflect the order in which they are called within dependency, respectively Territory, State Province, Country. In the end the report looks like this:

Notes:
(1) If the order of the parameters doesn't reflect the otder in which they are called, you'll get a generic error similar with the following one (the error appears when there are problems with the query):
An error occurred during local report processing. The definition of the report '/Customer Addresses' is invalid. The CommandText expression for the query 'Countries' contains an error: [BC30277] Type character '8e does not match declared data type 'String'.

(2) When designing parameters one should consider also the best practices for avoiding the poor design of parameters.

Previous Post <<||>> Next Post

🪄SSRS (& Paginated Reports): First Steps (Part III: Report Formatting)

Introduction

In the previous post were defined the parameters for the Customer Addresses report, however the design looks pretty basic as it was generated by the 'Reporting Wizard'. This can be slightly enhanced with a few changes. The order in which they are applied usually doesn't matter much.

Changing Columns' length

In Design mode drag of the columns to match the length of the attributes stored in them. This might require several tries.

Adding a Header/Footer

In Within reports body right click and from the floating menu choose 'Add Page Header', respectively 'Add Page Footer' like in the below screenshot: 

Adding the header/footer

Usually in the head I consider report's name as well two text boxes containing the date at which the report was added, respectively the number of pages the report displays. 

Cut report's header textbox from report's body into the header and move it to match the leftmost side of the report. In the header add two text boxes and provide the following formulas for the current date, respectively the number of pages: 

= Now().Date().ToString("dd.MM.yyyy"))

= Globals!PageNumber & " from " & Globals!TotalPages

Then drag of controls to match the dimensions of the blow grid like in the screen below:

Header

Note:

Then It's important to align the various controls so their margins match together, otherwise will be added empty columns or rows in the Excel exports, users having to delete the empty cells manually each time they want to use the data.

Adding the Parameters:

Even if the parameters are displayed within browser, as soon the data are exported to Excel they are lost. Therefore, to identify with which values the report was used, it is useful to add the parameters in table's header. Add one or two lines above the header, and merge a few cells to accommodate the parameters as below:

The merged cells contain the following formulas:

= "Country: " & Parameters!Country.Label

= "Customer Name: " & Parameters!CustomerName.Value

= "Postal Code: " & Parameters!PostalCode.Value

= "Phone Number:" & Parameters!PhoneNumber.Value

= "Address Type: " & Parameters!AddressType.Label

= "Person Type: " & Parameters!PersonType.Label

Formatting Table

One can go with the available formatting, however table's header could be changed of 10 pt font size. One can add also a background color for the header, format the top and bottom border and align columns' name in the center. The background color for the rows with the parameters must be non-transparent (e.g. white) if the header is fixed.

As concerns the data rows, one can add different colors for alternate rows. For this select the row and as background color provide the following formula:

=IIf(RowNumber(Nothing) Mod 2 = 0, "White", "LightSteelBlue")

Freezing the Header

One can freeze the header during scrolling by setting the property FixedData='True' for each static row. For this you need to change to Advance mode and set FixedData  property for the first three static rows as follows:

Thus, when scrolling, the header will remain fix:

Probably the choice of collars is not the best, though it can be for example adapted to organizations' colors. One can add also a logo, the name of the user who run the report, or whatever information are still needed.

Previous Post <<||>> Previous Post

🪄SSRS (& Paginated Reports): First Steps (Part II: Report Parameters)

Introduction

One of the advantages of SQL Server Reporting Services (SSRS) is that once  a query available, in just a few minutes one can display the data into a report. This post shows how to add parameters to a report. For exemplification is used the same view from the first post of the series.

As prerequisite for this post one needs to have ideally the Reporting Services Extension installed. 

Identifying the Parameters

The first step is deciding which parameters are required or make sense to add to the report. The parameters together with their type can be documented in a list similar to the one below:

Parameter list

SSRS differentiates between five types of parameters:  Text, Boolean, Date/Time, Integer and Float. 

Reviewing the Source Query

Before adding the parameters to the report is recommended to look at the source query to check whether it allows the efficient use of parameters. Based on the above list of parameters the standard view needs to be modified as follow (the new columns are marked with a 'new' comment):

-- Individual Customers view
ALTER VIEW [Sales].[vIndividualCustomer] 
AS 
SELECT p.[BusinessEntityID]
, p.PersonType PersonTypeId--new
, CASE p.PersonType 
	WHEN 'SC' THEN 'Store Contact'
    WHEN 'IN' THEN 'Individual (retail) customer'
    WHEN 'SP' THEN 'Sales person'
    WHEN 'EM' THEN 'Employee (non-sales)'
    WHEN 'VC' THEN 'Vendor contact'
    WHEN 'GC' THEN 'General contact'
 END PersonType --new
, p.[Title]
, p.[FirstName]
, p.[MiddleName]
, p.[LastName]
, p.[Suffix]
, pp.[PhoneNumber]
, pnt.[Name] AS [PhoneNumberType]
, ea.[EmailAddress]
, p.[EmailPromotion]
, at.[AddressTypeID] --new
, at.[Name] AS [AddressType]
, a.[AddressLine1]
, a.[AddressLine2]
, a.[City]
, sp.StateProvinceCode --new
, [StateProvinceName] = sp.[Name]
, a.[PostalCode]
, sp.CountryRegionCode -- new
, [CountryRegionName] = cr.[Name]
, p.[Demographics]
FROM [Person].[Person] p
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = p.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON at.[AddressTypeID] = bea.[AddressTypeID]
	INNER JOIN [Sales].[Customer] c
	ON c.[PersonID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[EmailAddress] ea
	ON ea.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PersonPhone] pp
	ON pp.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
	ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]
WHERE c.StoreID IS NULL;

Preparing the Source Query

Based on this, the source query can be defined as follows:

-- Customer Addresses
SELECT SIC.Title   
, SIC.PersonType   
, SIC.FirstName   
, SIC.LastName   
, SIC.AddressType   
, SIC.AddressLine1   
, SIC.City   
, SIC.PostalCode   
, SIC.CountryRegionName   
, SIC.PhoneNumber   
, SIC.EmailAddress   
FROM Sales.vIndividualCustomer SIC 
WHERE CountryRegionName = 'Germany'
  AND PostalCode = '42651'
ORDER BY SIC.FirstName  
, SIC.LastName   
, SIC.AddressLine1   

Note:
When preparing a source query for your report is important to limit the number of the records returned to reduce the waiting time until the data are displayed and minimize the traffic to the database. 

Generating the Report

Creating a report is straightforward in SSRS because the 'Report Wizard' takes care of everything in the background. 

Generated report with the Report Wizard

  After testing the report, right click on the created dataset (Dataset1) in design mode and change its name to be meaningful (e.g. Addresses).

Dataset properties

   In this step I prefer transforming the query into a formula because it allows more flexibility in handling the parameters dynamically. Click on the formula and replace the query with the following text: 

 = " -- Customer Addresses " & vbCrLf
 & " SELECT SIC.Title    " & vbCrLf
 & " , SIC.PersonType    " & vbCrLf
 & " , SIC.FirstName    " & vbCrLf
 & " , SIC.LastName    " & vbCrLf
 & " , SIC.AddressType    " & vbCrLf
 & " , SIC.AddressLine1    " & vbCrLf
 & " , SIC.City    " & vbCrLf
 & " , SIC.PostalCode    " & vbCrLf
 & " , SIC.CountryRegionName    " & vbCrLf
 & " , SIC.PhoneNumber    " & vbCrLf
 & " , SIC.EmailAddress    " & vbCrLf
 & " FROM Sales.vIndividualCustomer SIC  " & vbCrLf
 & " WHERE CountryRegionName = 'Germany' " & vbCrLf
 & "   AND PostalCode = '42651' " & vbCrLf
 & " ORDER BY SIC.FirstName   " & vbCrLf
 & " , SIC.LastName    " & vbCrLf
 & " , SIC.AddressLine1 " & vbCrLf

This can be obtained pretty easy by using a formula in Excel like below:

Preparing the dynamic query statement

Formula: = "& "" " & A1 & " "" & vbCrLf "

Note:
In previous versions of reporting environment there must be an empty space after each vbCrLf except the last line, otherwise an error will be shown and the report can't be used at all. When the empty space misses is tricky to find the line where this happened. To validate this one can put the mouse at the end of each line and see whether it lands after the vbCrLf or after an empty space.

Preparing the Queries for Dropdowns

Before adding the parameters it might be a good idea to prepare the queries for the dropdowns. For this let's consider the simplest parameters: 

-- Address Types
SELECT 0 Id
, '(All)' Name
UNION ALL
SELECT AddressTypeID
, Name
FROM [Person].[AddressType]
ORDER BY Id

The query has two parts, one made from the database query to which is added a row marking the '(All)' scenario in which the parameter is actually ignored, and thus all the records for this parameter will be shown. This is typically the default value. 

SSRS has another solution for showing all the values, though I prefer this approach. 

In all the dropdown queries I prefer to use the same names ('Id', respectively 'Name'), independently on how the attributes are called, as they make the purpose explicit. Because the data type of the parameter is integer and is a reference key, the default value can be 0 or also -1 if 0 values are available in the dataset. 

For Text parameters one can use the empty string as parameter, like in the below example for Country Codes: 

-- Countries
SELECT '' Id
, '(All)' Name
UNION ALL
SELECT CountryRegionCode Id
, Name 
FROM [Person].[CountryRegion]
ORDER BY Id

Adding the Parameters

To add a Parameter right click on 'Parameters' within the 'Report Data' and then klick on 'Add Parameter'. This will open the 'Report Parameter Properties' which by default looks like this:

Report Parameter Properties

Each parameter type requires different settings to be considered. 

Defining Freetext parameters

Customer Name, Postal Code and Phone Number are Text parameters shown in Freetext controls and all three are setup similarly (see Parameter List above): 

General settings for text parameters

Defining Dropdowns with Fix Values

The General setting for the Person Type are similar with the ones considered for Freetext, however it has a set of predefined values which can be added in the Available Values as follows: 

Available Values

The default value can be provided into the 'Default Values' section. Just click on 'Add' and enter an empty space:

Default Values for fix dropdown

Defining Dropdowns with Dynamics Values

Country parameter is defined similarly, however as Available Values is selected the 'Get values from a query' option. The Countries dataset is selected as source while 'Id' and 'Name' are provided as below: 

Available Values for dynamic dropdown

Same can be done also for the AddressType parameter having the AddressTypes datasource as source. 

After adding all the parameters and choosing the order in which they should be displayed, the Report Data section will look like this:

Report's parameters

Note:

When designing parameters one should consider also the best practices for avoiding the poor design of parameters.

Adding the Parameters to the Query

For this the query needs to be added as follows: 

= "-- Customer Addresses " & vbCrLf
 & " DECLARE @Country as nvarchar(3) = '" & Parameters!Country.Value & "'" & vbcrlf 
 & " DECLARE @CustomerName as nvarchar(100) = '" & Parameters!CustomerName.Value & "'" & vbcrlf 
 & " DECLARE @PostalCode as nvarchar(15) = '" & Parameters!PostalCode.Value & "'" & vbcrlf 
 & " DECLARE @PhoneNumber as nvarchar(25) = '" & Parameters!PhoneNumber.Value & "'" & vbcrlf 
 & " DECLARE @AddressType as int = " & Parameters!AddressType.Value & vbcrlf 
 & " DECLARE @PersonType as nvarchar(3) = '" & Parameters!PersonType.Value & "'" & vbcrlf 
 & " SELECT SIC.Title   " & vbCrLf
 & ", SIC.PersonType   " & vbCrLf
 & ", SIC.FirstName   " & vbCrLf
 & ", SIC.LastName   " & vbCrLf
 & ", SIC.AddressType   " & vbCrLf
 & ", SIC.AddressLine1   " & vbCrLf
 & ", SIC.City   " & vbCrLf
 & ", SIC.PostalCode   " & vbCrLf
 & ", SIC.CountryRegionName   " & vbCrLf
 & ", SIC.PhoneNumber   " & vbCrLf
 & ", SIC.EmailAddress   " & vbCrLf
 & " FROM Sales.vIndividualCustomer SIC " & vbCrLf
 & " WHERE 0=0 " & vbCrLf
 & IIf(Parameters!Country.Value<> "", " 		  AND SIC.CountryRegionCode = @Country", "") & vbcrlf 
 & IIf(Parameters!CustomerName.Value<> "", " 		  AND (SIC.FirstName  LIKE '%' + @CustomerName + '%' OR SIC.LastName  LIKE '%' + @CustomerName + '%')", "") & vbcrlf 
 & IIf(Parameters!PostalCode.Value<> "", " 		  AND SIC.PostalCode = @PostalCode", "") & vbcrlf 
 & IIf(Parameters!PhoneNumber.Value<> "", " 		  AND SIC.PhoneNumber LIKE '%' + @PhoneNumber + '%'", "") & vbcrlf
 & IIf(Parameters!PersonType.Value<> "", " 		  AND SIC.PersonTypeId = @PersonType", "") & vbcrlf
 & IIf(Parameters!AddressType.Value<> 0, " 		  AND SIC.AddressTypeId = @AddressType", "") & vbcrlf 
 & " ORDER BY SIC.FirstName  " & vbCrLf
 & ", SIC.LastName   " & vbCrLf
 & ", SIC.AddressLine1   " & vbCrLf

For each report parameter is defined a SQL parameter in the header with the appropriate data type and length, and a constraint within the WHERE clause. The parameter will be considered only if a valid value was provided. One has to take into consideration the type of the parameter. 

Testing the Report

If everything worked correctly the report will run without problems:

Testing the report

For testing the report it makes sense to choose the parameters one by one from the general to the more particular case. For example for a first test I would choose 'Germany' as Country, then in a second run i would choose one of the values from the resulting dataset, e.g. 'Arthur' as Customer Name, while in a thirst run I would pick a value for the Postal Code. This approach allows minimizing the volume of time required for testing. 

Troubleshooting

Errors in formulas are sometimes difficult to troubleshoot especially when the report refuses to run. The SQL errors are easier to troubleshoot into Management Studio (SSMS). Just copy the query in SSMS and replace the added formatting.

Errors into the formula require unfortunately more time, however most of the errors I had were related to the missing empty space, or forgetting to close a quote or incorrect formatting.

Previous Post <<||>> Next Post