Table of Contents
The following examples are provided to query both the database and report server. Additionally, I have included some helpful scripts and documents for reference.
SQL Server Reporting Services (SSRS) enables the creation, deployment, and management of interactive, web-based reports. These reports provide a platform for sharing and configuring data, as well as delivering actionable insights. The examples below illustrate how to query both the database and the report server effectively.
Report Listing
This report is designed for querying deployed reports, their subscriptions, and execution logs. It includes hyperlinks to the folder, report, subscription, and execution log, along with various parameters to ensure quality maintenance. |
Report Subscriptions
This report is used to query deployed subscriptions and their schedules. It includes hyperlinks to the folder, report, subscription, and execution log, along with various parameters for detailed tracking and management. |
Report Execution Log
This report is used to query the report server execution log, with various parameters available to filter the results. |
Scheduled Jobs
This report is designed to query and analyze the scheduled jobs within a SQL Server database. The detail shows the start time, end time or end date, and frequency of execution. The timeframe is displayed as a Gantt chart. |
Database Dictionary
This report is designed to query and explore the database dictionary of a SQL Server database. The detail includes a collection of names, definitions, and attributes about data elements that are being used in a database. |
Heat Map Calendar
The paginated report features a nested tablix within a matrix to display a calendar view. The heat map is created using a .NET function in custom code and a report variable for the base color, generating a gradient effect. Each day on the calendar is hyperlinked to a detailed Reporting Services report. 
You can then add it to a SharePoint team site with a report viewer web part. |
Power BI templates ensure that all reports adhere to a consistent design, maintaining a professional appearance that aligns with the organization's branding guidelines. By leveraging these templates, organizations can streamline reporting processes, foster collaboration, and create a polished, cohesive framework for data presentation. Standardized visuals and layouts minimize the risk of misrepresenting data or overlooking critical details. These templates also serve as a valuable starting point for new team members, reducing their learning curve and boosting productivity. Standardization helps stakeholders interpret data more effectively, reducing confusion and enhancing decision-making.
Example Template
A Power BI template featuring a matrix formatted as a calendar, with bookmarks for day, week, month, quarter, and year to adjust the date timeline selection and control the X-axis date hierarchy level on charts. It also includes date filtering capabilities based on specific timeframes and aging criteria. Templates ensure that all reports adhere to a consistent design, maintaining a professional appearance that aligns with the organization's branding guidelines. |
Performance Template
A Power BI template with a trend and detail execution log, bookmarks for half hour, hour, day, week and month to change the date timeline selection and the X axis date hierarchy level on the trend. The log contains information on the length of time spent running a report's dataset query and the time spent processing the data. If you're a report server administrator, you can review the log information and identify long running tasks. You can also make suggestions to the report authors on the areas of the dataset or processing report they might be able to improve. |
Service Now Template
A Power BI template for ServiceNow that integrates with the REST API, featuring a slicer to adjust the date used in the relationship, and another slicer to modify the Y-axis on the clustered bar chart. The template also includes bookmarks for day, week, month, quarter, and year, enabling users to toggle the date timeline selection and control the X-axis date hierarchy level on the charts. It's a comprehensive visualization tool designed to provide deep insights into the performance, trends, and key metrics related to tickets generated within the ServiceNow platform. This dashboard offers a user-friendly interface that allows stakeholders, IT managers, and support teams to monitor, analyze, and optimize ticket management processes effectively. |
Power BI External Tools are additional applications or links to websites that integrate seamlessly with Power BI Desktop, enabling advanced modeling, debugging, optimization, and customization tasks. These tools extend Power BI's capabilities by providing specialized functionalities that are not natively available in the Power BI Desktop interface.
The order of the buttons in the External Tools menu is determined alphabetically by the JSON file name. To manage this, I use a two-digit number prefix.
Place the .json files in the following directory: C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools
I used base64-image.de to generate Base64 Image code for icons. To create and modify the icons I used GIMP.
You can find the JSON files in my GitHub project here. Please update the URLs in the JSON files to match your organization's sites for services like ServiceNow, Jira, and Confluence.
Here is the list of installs for External Tools:
| Link | Name | Purpose |
|---|---|---|
| ⚡ | Analyze in Excel | Allows direct analysis of Power BI datasets using Excel’s PivotTable and PivotChart features. |
| ⚡ | Bravo | Tool for managing Power BI datasets, optimizing models, and formatting DAX queries. |
| ⚡ | DAX Studio | Advanced tool for writing, analyzing, and optimizing DAX queries in Power BI and SSAS. |
| ⚡ | Tabular Editor | Lightweight editor for creating, managing, and optimizing Tabular models. |
| ⚡ | ALM Toolkit | Schema comparison tool for managing and deploying Tabular model changes. |
| ⚡ | Power BI Report Builder | Tool for creating paginated reports to complement Power BI visualizations. |
| ⚡ | SQL Server Management Studio | Comprehensive tool for managing SQL Server and related services. |
| ⚡ | SQL Server Profiler | SQL Server trace tool for analyzing and debugging database performance issues. |
| ⚡ | Azure Data Studio | Cross-platform data management and development tool. |
| ⚡ | Model Documenter | Automates the generation of comprehensive Power BI model documentation. |
| ⚡ | Microsoft Loop | Collaborative workspace for organizing and documenting processes and workflows. |
| ⚡ | Measure Killer | Identifies and removes unused or redundant measures in Power BI models. |
If you have any other suggestions, please add them in the comments.
There is also an article on Microsoft Learn about External Tools.
Below is a curated list of essential software I use to manage and enhance my development, reporting, and productivity tasks. Each entry includes the name, description, and a direct link for easy access. This list reflects my preferred tools for efficient development, data management, and reporting tasks.
| Link | Name | Purpose |
|---|---|---|
| ⚡ | Power BI Report Server | On-premises report server for hosting and sharing Power BI and paginated reports. |
| ⚡ | SQL Server 2022 Reporting Services | Advanced reporting tool for creating, publishing, and managing reports. |
| ⚡ | SQL Server 2019 Reporting Services | Legacy version of SQL Server Reporting Services for enterprise reporting. |
| ⚡ | Visual Studio | Integrated development environment (IDE) for coding, debugging, and deploying applications. |
| ⚡ | Reporting Services Projects 2022 | Extension for Visual Studio to design SQL Server Reporting Services (SSRS) reports. |
| ⚡ | SQL Server Management Studio | Comprehensive tool for configuring, managing, and administering SQL Server instances. |
| ⚡ | RedGate SQL Prompt | Productivity tool for writing, formatting, and refactoring SQL code. |
| ⚡ | Azure Data Studio | Cross-platform data management tool for SQL Server and Azure SQL Database. |
| ⚡ | Azure DevOps Services | Cloud-based platform for managing DevOps workflows, CI/CD, and source code repositories. |
| ⚡ | Azure Storage Explorer | Tool for managing Azure Storage accounts, including blobs, queues, and tables. |
| ⚡ | Microsoft Power Automate | Workflow automation tool for streamlining repetitive tasks and processes. |
| ⚡ | Microsoft PowerToys | Utility toolset to enhance Windows productivity and usability. |
| ⚡ | Microsoft Loop | Collaborative workspace for managing projects, content, and ideas seamlessly. |
| ⚡ | Notepad++ | Lightweight text and code editor with extensive plugin support. |
| ⚡ | Animated Files (ScreenToGif) | Tool for creating animated GIFs and recording on-screen activities. |
| ⚡ | Image Markup (Snagit) | Screen capture and image annotation tool for professional workflows. |
| ⚡ | Image Editor (GIMP) | Open-source image editing software for graphic design and photo manipulation. |
| ⚡ | Microsoft Problem Recording | Step-by-step problem recorder for documenting workflows and troubleshooting. |
| ⚡ | Password Management (KeePass) | Secure tool for managing and storing passwords. |
| ⚡ | Disk Usage (WinDirStat) | Visualizes disk usage for identifying large files and optimizing storage. |
| ⚡ | Mock Data (Mockaroo) | Tool for generating realistic mock data for development and testing. |
- Activity Monitor
- Data Sources
- Database Dictionary
- Execution Log
- Heat Map Calendar (using execution log)
- Integrations
- Job Search
- Permissions
- Scheduled Jobs
- Report List
- Subscriptions