Create SSRS report with a query

In a previous article I demonstrated how to create a SSRS report that runs against a SSAS Cube.  This time I am going to show you how to create a report with a query that you already have created.

You have this great query that you run and then extract the data and send to all of your co-workers.  That is good, but now they want it in a report form so they can run the report themselves without bothering you.

rr1

 

First open SQL Server Data Tools or Visual Studio and create a new project.  Under Business Intelligence, select Reporting Services and then select Report Server Project Wizard.  The wizard is very handy and can be used approx 80-90% of the time with great success.  I named this project Vendors.

rr2

After clicking OK, it brings up the Report Wizard greeting.  Click Next to continue.

rr3

 

First we need to create a connection to our database that the query will run against.  The type will be Microsoft SQL Server, then click edit.

rr4

That brings us to connection properties screen.  Here you need to provide the database server and database name you will be running the query on.  Wincomputer and WideWorldImporters for our example below.

rr5

 

That completes the data source section, press next.

rr6

 

Now we want to design the query.  Click Query Builder.

rr7

 

That brings us to the Query Designer.  Since we already have a sql statement prepared, we can just copy and paste it into the section that says “Select from” below.

rr8

After we paste our query into the box, press the ! icon in red.  After you do that all of the other boxes will fill in with the information from your query.  You can add or subtract fields by selecting or unselecting them and the query will automatically update.  Press OK when you are done.

rr9

 

This populates the design the query screen, press next.

rr10

 

Our report type will be tabular data, so select Tabular and press next.

rr11

 

Now we want to move our fields to the Details section of this screen.  Highlight them and then press the details button.

rr12

Press Next when they are copied over.

rr13

Name your report name…I selected VendorListing, then click Finish.

rr14

 

This brings us to the design tab in Visual Studio, click the Preview tab to see how the report is looking so far.

rr15

 

It looks ok, but there are some changes we can make to make it look more pleasing to the eyes.

rr16

We can make the columns wider, and below I am going to change the Column Titles to our corporate color.  Right click in the column heading and click Text Box Properties.

rr17

Go to the Fill tab on the left and then click the dropdown for Fill Color.  Press ok when done.

rr18

 

Lets preview our report again… and now it looks much better.

rr19

 

Now we can deploy our report to the Intranet so everyone can run it.  In the Menu, click Build and then Deploy Vendors.

rr20

 

That will now deploy it to our webserver.  Below it completed without any errors.

rr21

Opening up web browser and going to our Report Server, click the Vendors report.

rr22

 

Then click the VendorListing link.  That will run our report.

rr23

Our report runs and opens up!  It could still use some tweaking like making the Phone Number column wider and some other tweaks.  We would go back to Visual Studio make the changes and then Deploy again.

rr24

 

Now you can take the Vendor.rdl file and deploy to Sharepoint or schedule the report to automatically run and email to a group of people.

 

We just scratched the surface on reporting, but SSRS is a very powerful system that allows you to create Enterprise worthy reports.

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