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:
- Using the Business Intelligence Studio.
- 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
- Open the existing report project with the VSTS IDE.
- 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.
- 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
- 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.
- 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:-
- Make sure the SQL Server 2005
in data tier is up and running
- 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”
- 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.
- 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.
- Click on General and there will be two links under Report Definition -> Edit and Update
- Click on Edit and save the file on the system (i.e. app tier)
- Then, open the file in the VSTS or note pad and make some changes and save them.
- Go back to report definition and click on Update as indicated by the arrow in the fugure below.
- 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:-
- 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.
- 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
- 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.
- 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.
- 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.
