SSRS: List Report

Using a list report to display separate discrete record sets.

I’ll begin by creating new “rptEmployees” report to display employee data.

Next, I’ll drag a list object unto the design surface.
When prompted with the “Dataset Properties,” I’ll name my new dataset, and to use an “embedded” dataset, then I’ll click “New” to create a new datasource.

1-list-report

2-list-report

When prompted with the “Datasource Properties,” I’ll name it and chose to “Edit” the credentials for connecting to my database.

During this step, I’ll select the Server name, authentication type, and database.

Before I click “OK,” I’ll click “Test Connection” to ensure my final connection object is valid.

3-list-report

Now I’m back at the “Dataset Properties” tab with a valid configured Datasource and ready to configure my Dataset.

4-list-report

Using the “Query Designer” is the preferred method for configuring the Dataset that will populate a report because of its intuitive GUI.
Another benefit of using Query Designer is the ability to test your query and see data returned in real time instead of waiting until pre-deployment.
Using the designer is as simple as adding tables which contain the desired data, selecting which fields should be included in the report, and any sort or filter criteria which should be used.

5-list-report

When I’ve completed configuring the query, I now see the SQL provided.
Satisfied with my Dataset, I’ll now click “OK” to complete the process and am back to the designer.

6-list-report

In order to properly layout my fields and the data, I’ll engage the Ruler (Report -> View -> Ruler).
As you can see, with Rulers turned on, I’m provided helpful indicators when aligned fields to ensure a clean layout.

7-list-report

With all of my fields present on the design surface, I’ll add textboxes to their left to indicate to the user each field’s purpose.
Also, I’ll add a header to the report (Report->Add Page Header) to indicate to the user (and anyone who may see future print outs) the purpose of for the report.

8-list-report

When I “Preview” the report, it successfully displays Employee records, but the records are displayed sequentially down the page instead of one at a time like in a phone catalog.

To enable page breaks between each record set, I’ll select “Group Properties” in the “Details” window in the bottom pane.

9-list-report

Now when I run my report, each record set displays on its own page.

10-list-report

rptEmployees
(right-click, Save As)

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 )

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 )

w

Connecting to %s