Reporting Services - An error occurred within the report server database....Database 'ReportServerTempDB' cannot be opened

Yesterday, I posted on an error related to Reporting Services. Here is another error related to Reporting Services encountered at one of my client sites. When the Reporting Services portal is visited, it says;

When the Web Service is visited, it clears says that something is wrong with the Reporting Services Temp DB.

An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) Get Online Help

Database 'ReportServerTempDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

This says that we need to check the database immediately. When it was viewed via Management Studio, we noticed that ReportSererTempDB is missing. It can happen for various reasons. However, if you are experiencing the same, you may be able to fix it without doing much. We fixed it without much effort, steps used for fixing are listed below;

For simulating the same, I deleted the mdf file related to ReportServerTempDB and this is how the database is shown with my server now. It is similar to the issue faced by my client.

What should we do? Here are the steps;

1. Backup the Report Server database if you have no backups (In my case, it is ReportServer)

2. Open Reporting Services Configuration Manager and go to Database. Click on Change Database.

3. Wizard starts. Select Create a new report server database and click Next to continue.

4. Enter a new name for the database. Note that the original database (ReportServer) is not deleted and we are not replacing it. This creates two new databases; ReportServer1 and ReportServer1Temp. Complete the wizard.

5. Once completed, you should see two new database. Note the old database too.

6. Since it is a new database now, Portal does not show the reports we had. We need to bring the old database back.

7. Go to Management Studio and rename the current Report Server temp database to old name. If the old Report Server Temp database is still exist, it has to be deleted first. Once renamed, I will be having my databases as below.

8. Now we need to go back to Reporting Services Configuration Manager and start Change Database again. Select Choose an existing report server database.

8. Select the old report server database and complete the wizard.

9. Once done, you should be able to see your old content. You may delete the report server database created for fixing this (in my case, it is ReportServer1 database)

Reporting Services - HTTP Error 404. The requested resource is not found.

What if you suddenly experience this when you open the Reporting Services Portal?

HTTP Error 404. The requested resource is not found.

There can be many reasons for this error and you may find the exact reason by reading Windows Application Log, Report Server Service Trace Log (default location is C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles) or SQL Server Error Log. When I go through the trace, I found that it was related to HttpListner and permission.

What shall we do? It is always better to find the exact root-cause and fix it. But, if you need to do a quick fix, there is a simple way to try and see.

It is all about change the Virtual Directories of Reporting Services. You may not fix the issue with this method but can try and see;

1. Open the Reporting Services Configuration Manager.

2. Go to Web Service URL and change the name of the Virtual Directory. In my case, it was ReportServer and I change it as ReportServer1.

3. Do the same for Web Portal URL too.

4. Once this is done, you should be able to open the portal. As you see, nothing has happened to content of it, it shows all items it had.

If you need to set the same Virtual Directories you had, you can do the same again for renaming them.

** Note that, you need to keep regular back of your report database and all content of report server. If you cannot get your errors fix, only option is reinstalling and setting it up again.

Adding Power BI Reports to Reporting Services

The integration between Reporting Services and Power BI was started with Reporting Services 2016, allowing us to pin SSRS Reports to Power BI Dashboards. Not only that, we can have Power BI files hosted in Reporting Services but Reporting Services does not support opening them inside the portal.

This integration has been extended and it will be available with the next version of SQL Server. With this, we can create reports using Power BI and add them directly to Reporting Services. Not only that, Reporting Services Portal supports opening Power BI Reports inside the portal. Can we see it now? Yes, Technical Preview is available for testing this.

In order to test this, you need to download two files; Power BI Desktop For SSRS (PBIDesktopRS_x64) and SQLServerReportingServices.

Power BI Desktop for SSRS is a separate Power BI instance and it can be installed side by side without removing existing Power BI installation. SQLServerReportingServices is the latest installation for SQL Server which is a standalone installation. It installs Reporting Services and allows us to configure just like the way we do with Reporting Services 2016.

You can download both files from: https://www.microsoft.com/en-us/download/details.aspx?id=54610

Installing Reporting Services

Let's start it with Reporting Services installation. Just like the way you install any other software, double-click on SQLServerReportingServices.exe and start the installation. You will get the usual agreement window, accept it and continue.

And you should see the final output within less than one minute;

This works without any issue even if you have SQL Server 2016 installation in your machine. The configuration of newly installed Reporting Services can be started by either clicking the button in the last window or searching and opening the right Configuration Manager. If you search for it, you will see two with the exact name (if you have already installed 2016), need to pick the right one.

When you open the Configuration Manager for the one you installed, you should get a similar screen, note the instance name: RSServer.

If you remember the 2016 installation, you know that you get an option for configuring Reporting Services during the installation: Install and Configure or Install Only. Since we did not get anything like that with this installation, we need to configure everything manually. First thing is creating the database.

Go to Database page and click on Change Database.

It starts the wizard, make sure that you select Create a new report server database. This requires a SQL Server database engine. If you have not installed SQL Server, then you need to install it before configuring the report server database. If you have already installed Reporting Services 2016, then you have a Report Server database that is for 2016 instance. If you have, DO NOT OVERWRITE THE EXISTING ReportServer DATABASE. Make sure you give a different name for it. As you see below, I have named my Report Server database as ReportServer_2017.

You can accept the default values for other pages in the wizard unless you need to change it.

Once it is configured, you need to configure Web Portal URL and Web Service URL. Again, make sure it does not conflict with your existing Reporting Services URLs. As you see, I have set the Virtual Directory for Web Portal as Reports_2017.

You need to do the same for Web Service URL as well. Once done, you should be able to browse the Reporting Server with the configured URL.

Installing Power BI

No difference between this installation and standard Power BI Desktop installation.

Once the installation is completed, open it (Note that if you had previous Power BI Desktop installation, now you have two Power BI instances). You should see SQL Server Reporting Services tag when opening Power BI. That confirms that you open the right instance.

Creating Reports and Publishing to Reporting Services

Note that this can be used for creating standard Power BI reports as well. However, if you create a report for Reporting Services, you need to make sure the following;

• Source is Analysis Services
• Connection type is Live Connection

At the moment, it supports only Analysis Services but we will surely see all other types with future releases.

Connect with your Analysis Services database and create a report as you need. This is what I created.

In order to publish this to Reporting Services, you can either save this directly to Reporting Services or save as a local file and upload it using Reporting Services Upload File menu. Let's save it directly to Reporting Services.

You should see new saving option as SQL Server Reporting Services.

Select it and enter the URL configured for the portal.

Name it and click OK.

You should see the success message if it can connect with your Reporting Services. Once saved, go to the portal and see it. You should see the added Power BI report. With the previous version of Reporting Services, if you click on added Power BI file, it downloads the file. But with this version, if you click on it, it opens the report and all functionalities works just like the way it works with Power BI online service.

Good thing is, it allows you to edit the report using Power BI;

Once you modified, you do not need to upload the file again because Power BI can directly save it to Reporting Services. The changes will be immediately appear in Reporting Services.

See, how easy it is. Try and see, you will see a lot more options with future releases.

Reporting Services (SSRS) - Upload Multiple Files

Although we have a new version of SQL Server 2016, multiple file upload facility is still not given as a built-in function and it annoys us when we have multiple RDL files to be uploaded manually. I am currently working on a Reporting Services project and have the same requirement, hence thought to make a simple application to upload multiple files.

If you make a search, you find related codes, written in many ways. I did a study on some of the code published and made a Windows Application for uploading my files. This is how it looks like;

I can select multiple files and upload;

It works fine for me but need to do more test, I will be continuous enhancing and testing it, if you need the same, please use the download link given below.

Link for the Windows Application: https://1drv.ms/u/s!Ahey1yiqH_9AgZwd-FOt5Qg3ej35oQ 

SQL Server 2016 Reporting Services Cookbook - Published!

I am happy to announce that SQL Server 2016 Reporting Services Cookbook is published and available for all SQL Server enthusiasts. It is my first book as a co-author.

It was not an easy task but with the great help from Amrita Noronha (Packt), Robert C. Cain (co-author) and Vinay Argekar (Packt), the dream came true, hence my sincere thank to all of them.

This book covers new features related to SQL Server 2016 as well as some important topics for report solution implementations. This is what you get from this book;

• Key capabilities, architecture, and components of Reporting Services
• New features that have been added to Reporting Services
• Design the architecture for reporting solutions
• Design the architecture for BI solutions
• Implement reporting solutions using Reporting Services
• Improve the performance, availability, and scalability of the reporting solution
• Enhance reporting solutions with custom programming and improved security

Here is the link for more info and if you like to read;

https://www.packtpub.com/big-data-and-business-intelligence/sql-server-2016-reporting-services-cookbook

SQL Server Brain Basher of the Week #044 - Reporting Services

Let's talk about something related to Reporting Services. You are asked to install and configure Reporting Services in one of the servers given. It is a new server, no instances of SQL Server has been installed. You are asked not to configure anything manually, install it with default configurations. You start the installation and select Reporting Services - Native in Feature Selection page and continue the wizard. You want to make sure that wizard configure your reporting services but you notice that the option Install and configure is disable.

What is the reason for this?

If you ask wizard to install files and configure reporting services for you, it needs to create two database for you. Now the issue is, it cannot find SQL Server database engine in the same instance as you are installing a new instance and you have not selected Database Engine Services in Feature Selection page. Therefor wizard cannot create database required for configuring Reporting Services. If you have already installed SQL Server with database engine services and you are updating the same instance by adding Reporting Services, or if you have selected Database engine services in the Feature Selection page, then you will see this option is enable and you can continue with the wizard. 

Repeating headers in Table data region created with Reporting Services 2008 R2

This is not about reports created with Matrix data region, so no column headers. And this may not relevant to latest version such as Reporting Services 2014 and 2016. While troubleshooting a report hosted in SSRS 2008 R2, I was trying to address an issue related to repeating headers on a particular report. It was a simple report, created using a Table data region and no groups. Repeating headers had been addressed as follows;

Although it has required settings done for getting headers repeated, it was not working as expected. Unfortunately, all my searches showed posts related to Matrix, not for Table, hence could not find the solution easily. Luckily, I was guided by another person, instructed me to add some elements to rdl file manually, just like below.

It started working immediately. What is the reason? As per the research done, this issue comes when the Table has not been added through the Wizard but manually. If it was created using the Wizard, above setting is enough for getting headers repeated.

I will be checking this with latest version and updating the same soon.

SSRS: Issue with SSRS Excel export - The number in this cell is formatted as text or preceded by an apostrophe

This is not a new issue, it has been there for long time but it still appears in the forum and we still experience it. One of the issues I worked in this week related to it; Once a report with few number of numeric columns is exported to Excel, some of the numeric columns are formatted as text, showing a message on each cell;

How can this happen? We are 100% sure that source-column used for this report-column is numeric and there are no non-numeric values. But still exporting treats it as text and format it as text.

As per the questions that have been published in forums, this issue appears mostly because of additional functionalities added to the expression. In my case, it is somethign like this;

Although many have suggested to use Immediate If (Iif) for handling this, because it can be used for checking whether the value is either empty or null and setting the column-value for zero before applying any other functionalities required. However it does not work all the time. The other technique that can be used for this is, multiply the final result by either 1 or 1.0 based on the output format you need.

This solved my issue, you may solve it by either applying this or applying Iif. There was another suggestion; making the report-column as Currency but it is not possible with all our columns. What could be the reason for this? One reason could be, how Excel identifies the type of the column. Generally, Excel uses first 8 rows for deciding the data type, I am not sure whether same theory is applied with this, but similar is applied for sure.

Reporting Services (SSRS) - Showing both overall and group page numbers

This is nothing new or related to latest which is SQL Server 2016 Reporting Services. But I think it is still important to blog on it as this question still appears in forums. SSRS offers multiple ways to control how pages need to be divided and printed or rendered with the support of pagination. Generally page numbers are shown with all reports without considering groups added. But, occasionally, page numbers specifically on groups are required while showing overall page numbers.

The report shown is created with a dataset prepared from AdventureWorksDW2014 database. It shows Cost and Sales for Category and Product and it is grouped by Category. Note that there are two texts that show pagination; one for overall pagination and other for grouping.

How this has to be configured? 

Here are steps for configuring it. First of all, few properties of the group have to be set;

Under PageBreak property, three properties are displayed; BreakLocation, Diabled and ResetPageNumber that can be used for defining explicit behaviors. As you see, BreakLocation has been used for setting the page break between groups and ResetPageNumber has been used for restarting the page numbering when page break generates. Once these properties are set, Globals.PageNumber and Globals.TotalPages are affected, hence page numbers are based on the group.

Now, how can we show overall page numbers with this? For that, we can use another two global properties called Globals.OverallPageNumber and Globals.OverallTotalPages. This is how displaying of both grouping and overall page numbers have been set with two text boxes;

SQL Server Brain Basher of the Week #039 - SSRS

This week's Interview question is based on SQL Server Reporting Services. Just like other applications hosted, Reporting Services requires authentication and it needs to handle all authentication requests. Now the question is;

Which component Reporting Services uses for handling authentication?

1. Internet Information Services (IIS)
2. SQL Server Reporting Services itself
3. SQL Server Database Engine
4. Domain or machine configured

Answer is simple, starting from SQL Server 2008, Reporting Services started using its own internal components for handling authentication, not by IIS. IIS was used only be Reporting Services 2005, hence the answer is (2).

Cannot find rsreportserver.config file

Have you ever come across a situation where you cannot see/find the rsreportserver.config file related to Reporting Services?

You are 100% sure that Reporting Services is installed and it works fine without any issue. Then why does not it appear in searches with file explorer?

Generally, path for the file is: C:\Program Files\Microsoft SQL Server\MSRS12.SQL2014\Reporting Services\ReportServer. If you navigate through this path without searching;

You can find it. And once found, searches starts working too.

The reason for this is security. You will notice that when you navigate the folders, it asks administrations rights and you need to accept it for continuing. Since it is sensitive file and a system file, it cannot be discovered using general searched.

Naming Excel sheets exported from Reporting Services

SQL Server Reporting Services allows us to export the opened report into different formats such as Excel and Word. When exporting into Excel, it is a common requirement that rows need to be organized in different Excel sheets based on added groups and it is something we always see in many forums. How it can be done with the designer? 

It can be implemented using two properties related to group; BreakLocation and PageName. All we have to do is, set these two properties as per the requirement;

As you see, Matrix data region is formed with a dataset populated from AdventureWorksDW2014 and it is grouped by Category. If we need to get Excel sheets generated based on groups, properties have to be changed as shown.

Once changed and exported using Excel, sheets will be created just like below Excel;