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):

ssrs2k5_001

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.

ssrs2k5_002

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.

ssrs2k5_006

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.

ssrs2k5_003

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.

ssrs2k5_004

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.

ssrs2k5_0051

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

ssrs2k5_007

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

ssrs2k5_008

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.

ssrs2k5_009

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

ssrs2k5_010

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

ssrs2k5_011

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:

ssrs2k5_012

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

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.

Advertisements
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…

    Thanks,

  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.

Trackbacks

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

You are commenting using your Google+ 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: