A Fine Slice Of SQL Server
Kathi Kellenberger notices an oddity with SSRS Mobile Report category charts:
Notice that OrderYear displays decimal points. I switched the dataset in the Series field name property, and found that neither of the columns in the dataset can be used.
Numeric columns cannot be set as a Series name field. To work around this, I modified the dataset, casting OrderYear as a CHAR(4).
That’s not a great situation, but at least there’s a workaround.
Comments closedJeff Pries shows how to use the Natural Earth data set in a SQL Server Reporting Services report:
After proceeding through the New Layer Wizard three times to add three layers to the map, we have all of our data present. We now just need to do a little housekeeping to make the map more presentable. We’ll go through each layer and make slight tweaks to each.
Before adjusting the layers, first notice that we essentially have two legends. The Legend box and the Map Scale box. They both give us the same information. Since the Legend is using more real estate, delete it.
There are a lot of steps involved, but the end result is a nice report.
Comments closedJeff Pries explains how to troubleshoot various Reporting Services configuration errors:
After installing SQL Server Reporting Services (SSRS), are you receiving an Error 404, Error 400, “Invalid Request” error, or “Bad Connection” error on first visiting the SSRS web portal (the error message seems to vary based on version, browser, and whether accessing via http/https or /reports vs /reportserver) ?
I’ve run into this a few times so I’m listing the steps I’ve used to fix it. For me, the root cause of this error has been the SSRS Configuration Wizard automatically configuring SSRS to use HTTPS, but assigning an invalid machine SSL Certificate. The fix is to self-generate a new and valid SSL certificate for the SSRS website to use.
Jeff then provides step-by-step instructions.
Comments closedAndy Mallon shows how to customize a display name for a sender e-mail address in Reporting Services:
The business wants the sending email address to be pretty. They want to identify the application, project, company, or… who knows what. The business wants anything but the server name. They might want it to come from NoReply@domain.com or CRM@domain.com or Reports@domain.com or…who knows what.
The problem with these more generic sender addresses is that they don’t help me troubleshoot. Is CRM@domain.com coming from SSRS or the application server? Which SSRS server is Reports@domain.com coming from? I don’t want to check 10 SSRS servers to figure out which one sent a report.
Click through to find out how to make the troubleshooting DBA and the business side happy.
Comments closedKathi Kellenberger discusses caching in SQL Server Reporting Services KPI reports:
Because these reports automatically show the data, the reports show cached data only. Imagine if hundreds or even thousands of report users brought the web portal page up each day causing the KPI reports to hit the database even when the report user was not interested in seeing the KPI reports at that time. That is why Microsoft decided to use cached data only in these reports.
When the data changes, the KPI report will continue to show the same information unless you configure a cache refresh plan on the dataset. Follow these instructions so that the KPI data will refresh on a scheduled basis.
Read on for a step-by-step guide on how to set up caching.
Comments closedWolfgang Strasser points out that SSRS log files are in a new directory structure for vNext:
The log files can be found in the Logfiles directory (it was the same directory also for the older versions). In SSRS vNext there more different log files..
The logging information seems to be splitted into multiple log files – if you want for example dig into the Power BI on-premises logging I propose to have a look at the RSPower*.log files.
This happens every once in a while, so it’s good to know when the log files move somewhere else.
Comments closed“Power BI reports in SQL Server Reporting Services: January 2017 Technical Preview now available” This feature addition will allow Power BI reports to be published to a local SQL Server Reporting Services server, entirely-on-premises without using the Power BI cloud service.
The January 2017 Technical Preview can be downloaded from: https://www.microsoft.com/en-us/download/details.aspx?id=54610
Reza Rad shows us installation and deployment:
We are in a world that rapidly running towards cloud. Your files are in Dropbox, or OneDrive these days, Your photos uploaded to a cloud storage, your emails are all backed up in a cloud backup media, and I’m in this thinking that in next few years, we might eat our food from a cloud kitchen! However there are still businesses and companies who require some on-premises solutions, and as long as a requirement exists, there should be an answer for it. Power BI for On-Premises bring the power of self-service, interactive reports of Power BI to these businesses. Power BI for On-premises is a great big step towards utilizing better data insight in all environments.
This will probably help more companies than you might think—Power BI is really useful as a reporting tool, but it can be hard getting sign-off to go to Azure.
Comments closedDave Turpin needed to perform an upgrade of a Reporting Services installation:
So let me share with you my biggest take away from this project: EVERYTHING ABOUT USER CREATED REPORTS IS STORED IN THE SQL Server Reporting databases. So if you are reading this post you probably are about to move an instance of SSRS, and may be concerned about the many, many reports involved.
Based on my one experience with this, there is need to move individual reports. If you follow the process carefully, all of the existing reports will be re-created on the new machine. It’s not quite magic, but it sure feels like it when everything shows up on the new system.
Read on for the solution Dave came up with.
Comments closedKathi Kellenberger shows off mobile reports in SQL Server Reporting Services:
Mobile Reports are dashboards that will run on most modern mobile devices as well as within the web portal. They are supported on IOS 9 and later, Android 4.4 or better, and Windows 10. To run Mobile Reports on these devices, the mobile Power BI application must be installed.
At first glance, they are simple to create. There is a new tool to use, the SQL Server Mobile Report Publisher. The tool will look familiar to you if you have worked with Datazen in the past. Microsoft purchased Datazen in 2015.
This is the first major Reporting Services update since 2008 (unless you consider sparkline support in R2 a major update), and could be a good business justification for upgrading to SQL Server 2016.
Comments closedOne of the features that took me by surprise is the ability to view data directly from a shared dataset. This feature is called Data Preview, and is available to anyone who has permission to view the dataset and the security at the data source works out. I’m not sure how often shared datasets have been used in previous versions of SSRS. They were not actually needed in many cases, and I generally recommended them for datasets that would be frequently reused such as common parameter lists. This advice will have to change with 2016, because shared datasets are required for the new KPI reports and Mobile Reports. Stored credentials will be used in the data sources in many cases, because Kerberos delegation is not supported yet with Mobile Reports.
This is a potential data leakage scenario, so if you have potentially sensitive data sets, you’ll want to read this post.
Comments closed