April 2021’s free Microsoft Endpoint Manager Configuration Manager (ConfigMgr/SCCM/MEMCM/MECM) giveaway is the SSRS Report Metrics Dashboard.
We want to know what you think about this dashboard, so we are looking for feedback. How useful did YOU find this report? In addition to SSRS reports, do you also have performance issues with your Power BI reports? Let our team know by emailing firstname.lastname@example.org or contacting our Chief Architect, Garth Jones, directly on Twitter: https://twitter.com/garthmj.
Keep reading to find out more about April’s giveaway!
At one point or another, we’ve all heard this complaint, “My report is so slow!” But, is there a dashboard that gives you usage details about each one of your SQL Server Reporting Services (SSRS) reports? Or, you might want to clean-up unused reports from SSRS or the Power BI Report Server. With hundreds and hundreds and hundreds of SSRS reports, is there a quick and easy way to determine which reports are never opened? Maybe you want to know what SSRS reports are popular and opened frequently in your organization, but where can you find these details?
Currently, there isn’t a built-in MECM report you can turn to for help whether it’s for troubleshooting SSRS report performance issues or for knowing which reports are the most or least popular. So, our customers asked us to create a new report.
Solution: SSRS Report Metrics Dashboard
You can use the SSRS Report Metrics Dashboard in either SSRS or Power BI Report Server where it queries the SSRS database for execution details about your paginated reports (yes, that is the technical term for SSRS reports) and summarizes the results. This dashboard was designed specifically with the ConfigMgr administrator in mind. The results are sorted, by default, to display the reports with the most executions first. You can also see more details, such as average times for data retrieval and processing, listed for each report execution.
Each of the five main columns list times in milliseconds (ms). Here is a summary about each one:
- Data Retrieval Time – This time represents how long it took to run the query on the SQL Server.
- Processing Time – This time represents how long it took to process the report in SSRS or Power BI Report Server. This includes processing Tablix groups, sorting, sub-reports, etc.
- Rendering Time – After the report is processed, this time represents how long it took to display the final report.
- Byte Count – This is the number of bytes returned from the SQL query.
- Row Count – This is the number of rows returned from the SQL query.
Once you know all of these metrics, then you can focus on the report area that is slowing things down in order to speed things up.
- If the data retrieval time is taking a longer than expected amount of time then you can see if the SQL query can be optimized to run faster.
- Is the byte count large? Network speed or networking issues might be affecting performance. In this case, is there extra data being returned within the SQL query that is NOT being used? If so, remove all extra details because the report should only show valued information.
- The same can also be true of row count as was said about byte count. If you are only reviewing a few rows within the larger dataset, why return all rows? Edit the SQL query to reduce the number of rows to only those that will be displayed within the report.
Here are a few references to help with troubleshooting (listed in no particular order):
Troubleshooting Reports: Report Performance
Troubleshoot Report Design Issues with Reporting Services
Troubleshoot Reporting Services Report Rendering Issues
Troubleshoot Reports: Map Reports (Report Builder and SSRS)
Troubleshoot Server & Database Connection Problems with Reporting Services
Troubleshoot Reporting Services Report Issues
Remote Report Server
If the report server is remote from the MECM SQL Server, then you will want to create a linked server on your MECM SQL Server. This blog post shows you the steps: https://www.enhansoft.com/cloud-reporting-how-to-setup-a-linked-server/.
Once you create the linked server, update the hidden prompt and add the linked server’s name. The high-level steps can be found in this blog post: https://www.enhansoft.com/default-report-parameters/.
In this example, the linked server is called cm-pbi19e-cb1.gartek.tst, therefore the prompt value is [cm-pbi19e-cb1.gartek.tst].ReportServer
One of the best parts about this SSRS report is that, not only can you export it to a PDF and print it (the columns all fit on one page) but you can setup an email subscription to it. Once a subscription is setup, the report is emailed to you at scheduled times. This feature is also available in a number of Enhansoft Reporting’s dashboards/reports. For more information about how to setup an email subscription, please see this post, https://www.enhansoft.com/how-to-set-up-an-email-subscription-in-ssrs/.
Enhansoft Reporting – SSRS Report Metrics Dashboard
The SSRS Report Metrics Dashboard is a brand new report, so it is NOT found within any of Enhansoft Reporting’s 26 categories. At least not yet! We want to know what you think about it before we add it to Enhansoft Reporting, so we are looking for your feedback. How useful did YOU find this report? Do you have performance issues with your Power BI reports too? Let our team know by emailing email@example.com or contacting our Chief Architect, Garth Jones, directly on Twitter: https://twitter.com/garthmj.
Are you looking for another ConfigMgr/SCCM/MEMCM report? Are you looking for Power BI reports? Do you find that Configuration Manager reporting is difficult? Reporting shouldn’t be hard and with Enhansoft Reporting’s 150+ reports, sorted into 26 different categories, we make it easy for you!
Find out more about the reports in Enhansoft Reporting by clicking on any of the 26 category buttons below.
Still not sure that we have the right report for you? Just ask and we will be more than happy to help you out! Send a note to firstname.lastname@example.org.
All of our free reports have the Role-Based Administration (RBA) feature enabled. This means that whether you are using Configuration Manager Current Branch or System Center 2012 R2 Configuration Manager, these reports will work with all RBA settings. Do you have an idea for a report set that you would like us to create? Submit them to our Ideas page, or drop us a line on Facebook or Twitter!