Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Reporting Services 2005

By Anubhav Bansal,

SQL Server 2005 Reporting Services

Introduction

With the launch of the SQL Server 2005, working with reporting services has been simplified to some extent. Thanks to, Business Intelligence Studio (BI). BI Studio once installed with the SQL Server 2005 offers an interface similar to VS 2005.Net.

Assuming the user or developer has created a team reports using VSTS or BI Studio, he might want to carry out modifications in the reports (often referred to Team Reports).  

There are two ways in which you can modify the Team Reports(.rdl). We will explain both the mechanisms one at a time as stated below:

  1. Using the Business Intelligence Studio.
  2. Using the Report site

In this article I’ll outline the procedure to modify the team reports using first method i.e. using BI Studio. My next article would walk through the second method.

Assuming the you are having VSTS or VS.Net 2005 installed on team system along with SQL Server 2005.

Modify Reports using Business Intelligence Studio

  1. Open the existing report project with the VSTS IDE.

  1. Open the report (.rdl file) that you want to modify by clicking on the report name in the solution explorer. You will notice that there are three tabs in the designer.

Data -> Used to create or modify existing query.

Preview -> View the report that is generated.

Layout -> Change the appearance of the cells and to modify the columns.

  1. Go to the Data Tab. You will notice the data query open up in the designer. Uncheck or check the columns that you do not want to show in the report anymore. I have unchecked first 2 columns for demo. Once you have done this, do execute the query so that changes are reflected. However, if you go to preview tab after this, you will get that report1 is invalid. SO before going to “Preview”, Tab, click open the “Layout” Tab

  1. Click on the “Layout” Tab. Right click on the column that you wish to remove from the report as shown below and click “Delete Columns” from the context menu. In case, you added columns in your previous operation on the Data Ta, then you will be required to add the columns using “Insert Columns”

To insert columns back to the report, first include that column in the query on the Data Tab. Changes won’t be reflected on the Layout Tab directly. In order to add the column to the table, right click on the existing table in the layout and click Insert Columns. Drag and drop the columns from the dataset at the required position.

You can as well modify the properties by right-clicking on the control (cells, table, etc) and selecting the properties.

  1. Now click on the preview the tab. Changes will be reflected in the report that is generated.

To Modify the reports in VSTS in case the BI Studio in not available on the machine having VSTS

In this case we have the three different layers -> App Tier/ Business Layer/Data Layer.

 Data Layer has the SQL Server 2005 running. From the app tier follow the following instructions:-

  1. Make sure the SQL Server 2005 in data tier is up and running
  2. Open the internet explorer. Type in the url where the reports reside on the data tier. For demo purpose we used :- http://tfsjdata/reports/ 

You will be re-directed to http://tfsjdata/Reports/Pages/Folder.aspx Similarly, for your case, replace the tfsjdata with the address of your server where SQL Server 2005 with BIS is there. “This is what I meant by report site”

  1. On the site you’ll have a few templates. Click on the TfsReportsDS. You will find tabs at the top as shown -> Contents/ Properties/Reports.

Click on reports or contents which ever is shown in the tabs. It will list down all the existing reports. 

  1. Click open one of the reports sayàBug List and bring up the properties. By default, you will have the focus on views. Click on the “properties” Tab.

  1. Click on General and there will be two links under Report Definition -> Edit and Update

  1. Click on Edit and save the file on the system (i.e. app tier)
  1. Then, open the file in the VSTS or note pad and make some changes and save them.

  1. Go back to report definition and click on Update as indicated by the arrow in the fugure below.

  1. Browse to the updated file and Press OK.

It is likely possible when you add a report to a report project and navigate to the Data Tab, you get a “Connection error – Cannot connect to database. Set and correct the connection settings”. To resolve this, please follow the following steps:-

To resolve the same, please follow the following steps:-

  1. Once you have added the Bug List.rdl to your project, navigate to the Data Tab. You will be prompted for the error as shown above. Once you get this error, click Ok button. Then you need to edit the data set settings. For this, click on the button -> next to combo box as depicted in the figure below.

  1. Once the Data Set Wizard opens up, click on the Edit button to edit the Data source as indicated by the arrow (1). Then the Data Source wizard would open up.

Once this happen, uncheck the checkbox encircled by blue (2). This would enable to edit the connection string

  1. Once the Edit button gets enabled click on it the button (3) as in above figure. Specify the server name where reports reside in the database server. For this, you can connect to the reporting server in the SQL Server Management Studio and check for the connection string properties of the Data Source. This will tell you the database name that you need to connect to.

  1. Use this connection string to get the Initial Catalog and Data Source. These would be the parameters that would be entered in the connection properties as shown below.

  1. Test Connection succeeds. Click Ok. On th e Data Source Window, Click Ok once again. And finally on the Data Set window, click Ok for the final time. You will be rendered with Data Tab in expected manner with Queries. You can navigate to “Preview” to verify the effects.

Total article views: 11523 | Views in the last 30 days: 3
 
Related Articles
FORUM

Printing Report by clicking an ASP.NET button.

Printing Report by clicking an ASP.NET button programatically.

FORUM

SSRS - Error / Message while connecting to Report Server

SSRS - Error / Message while connecting to Report Server

FORUM

The report server cannot open a connection

The report server cannot open a connection to the report server database.

FORUM

Need One Click Button to Run All Reports

One Click Button to run all reports

FORUM

Reporting Services Remote Connections

Reporting Services Remote Connections

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones