How To: Report Authoring in SSRS 2005 – Part III (Designing Manually)

We are now at the Part III of our “Report Authoring in SSRS 2005” series. In Part I we have created our database and written the query that fulfilled our basic report requirement. As we progress in this series, we may need to modify the existing database and write more queries. We have not discussed the concepts and the preliminary topics pertaining to system requirements and installation process of the SQL Server 2005 Reporting Services. Those topics are covered greatly by the Microsoft sites. The Part II of this series showed how to utilize the Wizard to help us design our report. The wizard is good when the report requires only basic elements.

In the Part III we will do the same report we created in Part II. This time we do that manually. We will build more complex reports upon this basic report as we progress to our series. Now roll your sleeves and get ready to do the dirty job.

The first step is to create a Data Source for the project. You open the Solution Explorer and just right-click the Shared Data Sources folder, and click on “Add New Data Source” (see figure below):

The Shared Data Source dialog then pops out and you fill in the information that the IDE needs to create the data source for the project.

I would want to store my credential for this Datasource so the database will not ask me to enter my credential info everytime I use the Datasource.

The next step is to add a new report to the project. We do that by right-clicking on the Reports folder then on Add >> New Item. The Add New Report option in the context menu triggers the Report Wizard.

We name our report “SSRSDemoManual”. The next step is to create a Dataset for our report. We create a Datasource for the project, then Dataset for the report. A Datasource can be shared among many reports.

In the Dataset Dialog, we have two options for “Command Type” (Text and StoredProcedure). We will take Text at this time and do the StoredProcedure in the next part of our series.

We can test the dataset if it works by executing the query (you can click the Exclamation Point Icon to execute the query).

Then we just drag a Table control to the designer to contain the fields that we want for our report.

We can add a new column to the table by right-clicking on the row tab of the table and then click on Insert Column to the Left/Right.

Go to the Datasets Tab. If it is not already showing, go to View Menu and find “Datasets”

Then we can just Drag and Drop the Report Dataset Field to the Detail Row of the Table.

That’s it. We just created a report manually. You can check how the report will look like when the report is published by previewing it. And this is how our report looks like after I add some formatting:

Tip: To edit date field to only show the m/d/yyyy format, you can use the “FormatDateTime” expression function like this:

=FormatDateTime(Fields!EmpBirthDay.Value)

You can play around the designer for formatting features and additional report elements.

Next >> Adding Parameters to the Report

How To: Report Authoring in SSRS 2005 by Marlon Ribunal is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.