In this article, we will explore how to effectively manage recursive queries on SSRS reports. By implementing tablix and document map, managing calculations and formulas, formatting the report, and using conditional formatting, we can create a user-friendly and visually appealing report.

Step 1 – The Table Employee

First, we need to set up the table that will serve as the basis for our report. We will be using the table DimEmployee from the ContosoBIDemo database. To display only the necessary columns, we will create an analytics view using the following SQL code:

CREATE VIEW [Analytics].[Employee]
AS
SELECT [EmployeeKey]
      ,[ParentEmployeeKey]
      ,[FirstName] + ' ' + [LastName] AS Name
  FROM [ContosoDW].[dbo].[DimEmployee]
GO

Step 2 – The Data Source

Next, we need to create a new Report Server Project and set up the data source for our report. We will create a new data source embedded in the report, but in a production environment, it is recommended to create a shared data source. Here are the steps to create the data source:

1. Create a new Report Server Project in SSDT.
2. In the Solution Explorer pane, right-click on the “Report” folder and choose “New Item…”
3. Choose a name for the new report and click the “Add” button.
4. In the Report Data pane, right-click on the “Data Sources” folder and choose “Add Data Source…”
5. In the data source creation dialog, select the server and database that contain the DimEmployee table.
6. Click the “OK” button to create the new data source.

Step 3 – The Tablix Component

Now, we can start designing the layout of our report. We will use the Tablix component to display our data as a table. Here are the steps to add the Tablix component:

1. In the Toolbox pane, select the “Table” component.
2. Double-click on the “Table” component to add it to the report layout.
3. Drag and drop the dataset fields onto the Tablix to display the data.

Step 4 – Calculations

In this step, we will add calculations to our report. We will reorganize the table, add the level of the hierarchy, and count the total employees in charge and directly in charge by the manager. Here are the steps to add the calculations:

1. Reorganize the table by deleting unnecessary columns and renaming headers.
2. Add a column for the level of the hierarchy and use the Level() function to display the level.
3. Add a column to count the total employees in charge and use the CountRows() function.
4. Add a column to count the total employees directly in charge and use the LookupSet() function.

Step 5 – Formatting

In this step, we will format the tablix component to improve the visual appearance of our report. We will change the header style, apply conditional formatting to the row data, and add space indentation to the employee names. Here are the steps to format the report:

1. Change the border color, background color, font, and alignment of the table header.
2. Apply conditional formatting to the row data by changing the background and font color based on the level value.
3. Add space indentation to the employee names using the Space() function.

Step 6 – Document Map

The document map is a useful feature in SSRS that allows users to navigate the report more efficiently. In this step, we will add a document map reflecting the hierarchy of the table. Here are the steps to add the document map:

1. In the Group Properties dialog of the recursive parent group, go to the Advanced section.
2. In the Document map field, enter an expression that concatenates the level, employee key, and name.

Step 7 – Conditional Export to Excel

Finally, we will ensure that our report exports correctly to Excel. We will hide unnecessary columns and adjust the formatting for a better user experience. Here are the steps to conditionally export the report to Excel:

1. Hide the toggle item column in Excel by setting the column visibility based on the render format.

By following these steps, you can effectively manage recursive queries on SSRS reports and create visually appealing and interactive reports for your users.

I hope this article has provided you with valuable insights into managing recursive queries on SSRS reports. Happy reporting!