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.
<SPAN style="mso-spacerun: yes">?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/
<SPAN style="mso-spacerun: yes">?
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.?
<SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Click open one of the reports say<SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Wingdings; mso-ascii-font-family: Arial; mso-hansi-font-family: Arial; mso-bidi-font-family: Arial; mso-char-type: symbol; mso-symbol-font-family: Wingdings"><SPAN style="mso-char-type: symbol; mso-symbol-font-family: Wingdings">༳pan style='font-size: 10.0pt;font-family:Arial'>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