Data & Intelligence

Sales Report in SSRS

I hope everyone has found my articles useful and have used my articles in creating some great looking SSRS reports.  This week we will take a look at creating a product breakdown report or a detailed sales report.

Let us refresh our memory.  Our sales table has the following fields:

Fields Description Data Type
ID Identifier (Primary Key) Number
Product _Type Candles, hand sanitizers, perfumes, etc Varchar2
Product Detail Fragrance of the product such as Strawberry, Vanilla, Eucalyptus, etc Varchar2
In Store Timestamp of the product arrival in store Date
Sold Timestamp of product sold Date

 

Imagine a scenario in which a manager wants a count of every product sold by its product type.  We generate such a report by creating tabular reports and using SQL’s group by clause.

If you missed my article on creating tabular reports, don’t fret! This report will take you through the all the steps required to create a great looking sales report using tables in SSRS.  This article will be a great refresher course.

The first step is to create a data source and this step will be common to all the reports I cover in my future articles.

Adding DataSource:

1.)   Click on New and Select Data source:

pic1

 

 

 

 

 

2.)   Enter name for your datasource. (Optional)

3.)   Choose “Use a connection embedded in my report”

4.)   Choose Connection Type

pic2

 

 

 

 

 

 

 

 

 

 

5.)   Click on Build:

pic3

 

 

 

 

 

 

 

 

 

6.)   Enter your username and password and Click on Test Connection.

7.)   Click OK if you see the window shown below.

pic4

 

 

 

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

 

 

 

 

Let us add a dataset that can hold our SQL query.

Adding Datasets:

1.)   Click on New and Select Data source.

pic5

 

 

 

 

 

 

 

 

 

2.)   Enter a name for your dataset. (Optional)

3.)   Choose Data source (In our case, it should be DataSource1 –if you haven’t renamed)

4.)   Write your SQL Query.

Let us write our SQL query that will count the product_details for every product.  I have also written the query in such a way that will return the count from highest to lowest (descending)

SQL query:

 

SELECT

[candle_soap_shop$].Product_Type

,[candle_soap_shop$].Product_Detail,

count(*)

FROM

[candle_soap_shop$]

group by [candle_soap_shop$].Product_Type

,[candle_soap_shop$].Product_Detail

order by count(*) desc

 

Now, that we have all the tools to create a great looking sales report, lets dive straight into it.

We will be using a table wizard to create the great looking report.  To access the wizard, click on Insert and click on table wizard.

pic6

 

 

 

 

 

 

Next, follow the steps on the wizard.

My dataset for this report is called DataSet2, so I will choose that on the first screen.

Since, we want the report to be grouped by product or in other words, grouped by product row, drag product_type and product_detail under row group.  Finally, drag the count to values field and click on Next.

pic7

 

 

 

 

 

 

 

Next page will show you a report layout.  You must always verify that the layout matches your report requirements and if it does, click on Next.  On the next page, SSRS will offer you several styles to apply to your report.  I like blue color so I will go ahead and choose ocean.

This report should be available on the canvas.

pic8

 

 

 

 

I am going to rename the field ID – Product Sold just because it is more descriptive.  However, doing so is entirely your choice.  Your report is ready to be viewed.

pic9

 

 

 

 

 

Stay tuned for more articles!

 

 

About the Author

More from this Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up