Skip to content

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

February 14, 2009

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:


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

Next >> Adding Parameters to the Report

Creative Commons License
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.

6 Comments leave one →
  1. ronron permalink
    December 6, 2009 11:53 AM

    Nicely done, to the point, useful. Compliments to the chef.

  2. Phani Kumar permalink
    September 17, 2009 2:37 PM

    Good presentation, would be much more useful for starters like me…


  3. Joe permalink
    July 8, 2009 5:10 PM

    Its very good. If possible you can add next one as how to make a chart report and sub reports.


  1. How To: Report Authoring in SSRS 2005 – Part V (Pimp Your Parameters) « Marlon Ribunal's Note On SQL Server
  2. How To: Report Authoring in SSRS 2005 - Part IV (Adding Parameters) « Dangling On The Shoulders Of Giants
  3. How To: Report Authoring in SSRS 2005 - Part II (The Wizard) « Dangling On The Shoulders Of Giants

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

  • Archives

  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video

  • Software Blogs - BlogCatalog Blog Directory
  • %d bloggers like this: