Get started Bring yourself up to speed with our introductory content.

FAQ: Troubleshooting SQL Server Reporting Services

Troubleshoot SQL Server Reporting Services (SSRS) issues with these FAQs. If you're having trouble exporting and importing report data, or if you have general configuration issues with SSRS and its reports, find expert advice here.

  SQL Server Reporting Services (SSRS) can help you create and deploy reports more efficiently. But what do you do when there's a configuration problem, or you simply want to extend the tool's functionality? In this batch of frequently asked questions, learn how to export from and upload reports to Microsoft Excel, solve SQL reporting errors, manipulate code and order data when creating SSRS reports and manage configuration problems that may arise in this reporting application.


SQL SERVER REPORTING SERVICES ISSUES

  1. How can I properly configure SSRS for Web service use?
  2. Can you import Microsoft Excel data to SSRS?
  3. Can we deploy SSRS reports on our personal website?
  4. What other software is needed to design reports for SSRS?
  5. Can we use datagrids for our report in SSRS?
  6. How can I guide users on input parameters in SSRS?
  7. What are the drawbacks of reporting in SSRS?
  8. How can I properly order data in an SSRS report?
  9. Will running SSRS on Windows XP limit the number of users?
  10. Are there issues when exporting SSRS reports into Microsoft Excel?

  1. How can I properly configure SSRS for Web service use?

The Web service in my edition of SQL Server 2005 Reporting Services is not functioning properly. After a few minutes of browsing, I get the message, "This page cannot be displayed." Why is this happening?

It looks like you are using Network Service, which is a valid built-in account to use, and that you must be attempting to reach the Report Manager website. You must ensure that you have configured Reporting Services from the Reporting Services Configuration program in the SQL Server Configuration Tools program group. This is where you will go to configure the Web Service, Application Pool and Windows Service Identities. This step is often missed in SQL Server 2005.
—Mark Kromer, Business Intelligence Expert

Return to SQL Reporting Services FAQs

  2. Can you import Microsoft Excel data to SSRS?

Reporting Services does not import data. It only queries data in whatever format it is stored in their native storage system. I will assume that you're asking whether you can create reports and use Excel spreadsheets as data sources. The answer is Yes, Reporting Services supports a wide variety of data sources, including Excel files. You'll get the best performance with the built-in native .NET providers but you should be able to connect to any ODBC or OLE-DB data source, whether it comes from Microsoft or a third-party company.

You can report against Excel data by using ODBC. First, create a new DSN in Windows Administrative Tools, select the Excel driver and point it to the Excel file you want to use. Then in your Reporting Services project create a new data source of the ODBC type, click on the Edit button next to the Connection String text box and select the DSN you had previously created. Once the data source is set up, you can create datasets in the query designer by running SQL statements against the Excel data. For example, let's say you have a file that contains a worksheet called Users. You can run the following query to retrieve your data:

SELECT * from [Users$]

Note: You need to append the dollar sign to the worksheet name.
—Roman Rehak, Reporting Services Expert

Return to SQL Reporting Services FAQs

  3. Can we deploy SSRS reports on our personal website?

Your reports can only be deployed on a reporting services site. Your only option for viewing them from other sites is an HTTP link. Some tools, like SharePoint offer controls allowing you to view reports in the context of the other websites, but the report is still deployed to and hosted from reporting services.
—Eric Johnson, Reporting Services Expert

Return to SQL Reporting Services FAQs

  4. What other software is needed to design reports for SSRS?

Assuming I have SQL Server, what else do I need to run the report designer for Reporting Services and the Business Intelligence Analyzer? It sounds like the Visual Studio environment hosts these tools, but what version of Visual Studio is required: Express, Standard or Professional? In other words, what do I need to buy after SQL Server to design reports for SQL Server Reporting Services?

All you need to create reports for SSRS is the SQL Server Business Intelligence Development Studio (BIDS), which is basically Visual Studio with only the SQL Server components installed. The good news is that BIDS comes with most versions of SQL Server. If you have the client tools installed, you will have BIDS.
—Eric Johnson, Reporting Services Expert

Return to SQL Reporting Services FAQs

  5. Can we use datagrids for our report in SSRS?

I've got an ASP.NET project that populates a datagrid. I'd like to use the datagrid as my datasource for my report using SQL Server Reporting Services. Is this possible?

The simple answer is no. However, nothing's ever simple. A set of reporting controls was added in Visual Studio 2005 allowing you to report in a dataset, on data that was supplied by you. So, if you retrieved your data into a dataset, bound the datagrid to the dataset so it had data to display, you could then use that dataset as the datasource for the reporting controls. These are then client-side reports, not server reports though.
—Greg Low, Development Expert

Return to SQL Reporting Services FAQs

  6. How can I guide users on input parameters in SSRS?

I am working on a report that has three input parameters. Is there a way to program a message box or text box that appears if the user tries to view the report without inputting the required information?

You need to change the properties of the input parameters to now accept null values. The user interface will then require a value to be input.
—SQL Server Expert Denny Cherry, IT Knowledge Exchange

Return to SQL Reporting Services FAQs

  7. What are the drawbacks of reporting in SSRS?

For many years, Microsoft had no direct solution for reporting with the SQL Server besides Crystal Reports. Now, they have SQL Server Reporting Services, but it does have several drawbacks. It is still complex to understand the complete functionality and structure of this new component, and many users are still relying on the reporting application they are more familiar with, which is Crystal Reports.

Also, components in SSRS like Report Builder and Report Designer are meant for different users for different aspects of the report process, yet complete understanding and exposure to both is important to utilize both functions fully and extensively. There are also issues when exporting very large reports to Microsoft Excel, as it can lead to a loss of data.
—Jaideepkhanduja, IT Knowledge Exchange

Return to SQL Reporting Services FAQs

  8. How can I properly order data in an SSRS report?

I am using a case statement to order data on a SRS report. When the statement is run, it returns the correct order, but when I save the RDL file and preview it, it returns to ascending order. How do I correct this?

You should put the SELECT statement within a stored procedure, and then call the procedure. This way SSRS can't mess around with the SELECT statement.
—SQL Server Expert Denny Cherry, IT Knowledge Exchange

Return to SQL Reporting Services FAQs

  9. Will running SSRS on Windows XP limit the number of users?

Yes, but not because of SSRS. The Internet Information Services (IIS) component of Windows XP only allows a small number of users to connect to the website at once. As SSRS runs via IIS, this would prevent more than a few people from using SSRS at once.

Also, the only edition of SSRS that will install on Windows XP is the Developer Edition. This edition can not be used for production use. You need Standard or Enterprise Edition for production use, which requires a Server OS to install on (Windows 2003 Standard, Windows 2008 Standard, etc).
—SQL Server Expert Denny Cherry, IT Knowledge Exchange

Return to SQL Reporting Services FAQs

  10. Are there issues when exporting SSRS reports into Microsoft Excel?

When my users are trying to export a SSRS report into Microsoft Excel, one or two columns in the report appear to merge together. Why might this be?

Exporting from SSRS is not always perfect, even if you stay within the Microsoft range of products. If you have extra resources, you could splurge for an add-on that offers much better control over exporting to Excel, such as OfficeWriter.

From my experience, though, it is usually headers or footers that cause exporting issues. If any of these headers or footers overlap with data columns in your report, you will find that the exported version of the report has merged cells. Also, check columns next to each other to make sure that there is no overlap, as well.
—Squashjunkie, IT Knowledge Exchange

Return to SQL Reporting Services FAQs

 

Didn't find what you were looking for?   
 
Join the thousands of SQL Server pros who are finding the answers they need on IT Knowledge Exchange. Discover an IT community where real-world knowledge and experience is shared among industry peers.

Another option is to visit our Ask the Expert section where you can browse Q&As or submit a question directly to one of our SQL Server experts.

 
This was last published in November 2008

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close