SSRS – Working with Column Charts

Introduction

Many of times you would have noticed that high level mangers and senior members of a firm often deals with numbers and lots of data, it is alwasy benificial if there they could get a summaried, easy to understand, a graphical representation of their data. SQL Server Reporting Services (SSRS) is one such solution to their concerns. It enables user to generate report with various features which are not only easy to implement but effective in making business decissions. In this article and future articles I will try to introduce you to this wide area of exploration with simple examples. 

Using the code

Target to achieve:

Create a “Total sales per Employee” report in a column chart format showing employees and their total sales done. Following steps shall help you designing this report. 

1. Start SQL Server Business Intelligence Development studio (Introduction to BI development studio)  
2. Create a Report project, by going to Files – New – Project and selecting Report Server Project as shown below

3. Give name to your project and place your project in a directory of your choice.
4. Once you click OK button, you shall be able to see 3 folders in your solution explorer window.
   1. Shared Datasource:  This folder contains datasource which is shared across all reports. Thus in case of production environment, where single project contains many reports pointing to single connection string, in such situations shared datasource is a good and ideal option to choose. In this course for learning we will be integrating datasource within reports and hence shall not create any shared datasource. One may explore this area on his own, as both ways process of creating datasource remains the same.
   2. Shared Dataset: Like datasource which is basically the connection string to the database, you now need an object (or a query or stored procedure) which when executed on database shall return you the resulting rows need to be shown on report. This definition is stored in Dataset (w.r.t SSRS). Like shared Dataset, shared Datasource is again used in situations where you need to display same/similar data in multiple layouts. Or you may have a large dataset from which you intend to create multiple reports.
   3. Reports: This is the location where you reports shall reside. Every report you create whether using shared dataset/datesource or having embedded dataset/datasource shall be at this location. You can add new reports by right clicking on this folder and select “Add New Report” to open report creation wizard or simply go Add -> New item and select Report from the widow shown below.

5. Give name to your report and click “Add” button. Your report is now available under Reports folder; double click the report to open it in designer window.
6. Once you add and open a report, a new pane appears on the left side of IDE named “Report Data”, this pane contains another set of folders required during report creation.
7. In the Data Sources folder we will create embedded data source specifically used by the report. So will go ahead and right click on that folder, and select “Add Data Source…”.  This will open up a window where we shall put in our required connection string point to our database.
8. Give  a good name to your data source, I typically prefer giving it a prefix “ds” which stands for Data Source.  Click on “Edit” button and a window shall open up (people with .NET application development background should be familiar with this window) as below

9. Provide Data source type if you are connecting to database other than SQL Server. In this example we shall continue with SQL Server itself.
10. Give your server name, and then credentials to connect to that server.
11. Later provide your database name in the dropdown named “Select or enter the database name”. Click on Test Connection to verify your input and then OK to continue
12. Now your data source window is populated with the connection string. Click on credentials link on the left side of this window
13. You can provide connection string credentials here, so that you need not have to pass them while running reports every time. Click OK to proceed.
14. Similarly we will now set up the dataset, which contains the definition based on which the data will be retrieve from database mentioned in data source above. Right click on Datasets folder and click “Add Dataset…”

15. Click on “Use a dataset embedded in my report” option, so that you can set up the datasource and the query to fetch data.
16. Select Data source form the dropdown or you may create a new one if you haven’t created one yet.
17. You can then define your query as Text or just a Table from database or a Stored Procedure name which shall retrieve you your relevant data. For this example we will use a simple query as below

SELECT E.EmployeeId, EmployeeName, OrderPrice TotalRevenue
FROM EmployeeProductOrder EPO
INNER JOIN Employee E on EPO.salespersonid = E.employeeid

18. I should now probably explain you the database structure being used for this report. Below is the database diagram and its quite simple in nature and easy to understand
19. There are basically two base tables Employee and Product where Employee contains employee’s details and Product contains product details of a company. Table EmployeeProductOrder contains relationship between the two base tables as which employee has sold which product. I will further improve this database as we move ahead with our SSRS exploration. Currently all three tables contains a Primary Key and foreign keys to link them.
20. Moving back to our report solution, in the window to add a new Dataset, there are other option available on the left side of the window.
    1. Fields: This lists down all the fields getting returned from your query/Stored procedure. You can add further fields which can be used in report as compute columns. I’ll explain this feature of adding columns/fields in another article but you can always explore this on your own.
    2. Options: Define collation, Case sensitivity, Accent Sensitivity etc
       1. Collation: Collation is the assembly of written information into a standard order. These are set of rules that determine how the data is sorted or compared. And the Collation name is a string identifying the alphabet or language whose sorting rules are applied when dictionary sorting is specified.
       2. Case Sensitivity: Data in SQL server can differ in meaning based on differing use of uppercase and lowercase letters. This is a flag to determine to follow this rule or not.
       3. Accent Sensitivity: This is a criteria specified to treat the data as per its pronunciation. Accents can be confused with dialects which are varieties of language differing in vocabulary, syntax, and morphology, as well as pronunciation. Like a and á, o and ó are treated in the same way if it is accent in-sensitive.
       4. Kanatype Sensitivity: This specifies that SQL Server distinguish between the two types of Japanese kana characters: Hiragana and Katakana. This is a flag to use it or not.
       5. Width Sensitivity: It shows how SQL Server treats the single-byte characters (half-width) and the double-byte characters (full-width) for the same data. This is a flag to determine whether these two set of data to be treated same or not.
    3. Filters: here you can define any filter condition to be applied over the retuned result set. This gives you the ability to drill down the data required specifically for the report when the returning dataset is very large and may contain result set for other section of same report.
    4. Parameters: here you can define the parameters if specified in the query/Stored Procedure. You can either define report parameter value using  “fx” button next to Parameter Value text box as expression like below

21. The above feature of SSRS one of the most important feature of report designing, you can design and formulate various predefined values, functions over data value and even control program flow (like IIF, SWITCH etc). I shall try to cover these options as we go further in our report designing task. One may of course explore these options on their own.
22. Once you are done with you Data source and data set. Now you have to start designing your report. For which you need a toolbox to fetch controls from.
23. Usually Toolbox exists at the left hand side of IDE along with Report Data, if you couldn’t find it there then go to View -> Toolbox in you IDE
24. From the toolbox drag a chart to your report body. As soon as you drop the chart control to you report body, a new window pops up for you to select the chart type.
25. SSRS provides wide range of chart types available for various kinds of reports. For this example we will use the default one i.e. Column chart’s first option and click OK at the bottom.

26. Now your report body will have a demo chart, giving you an idea how you report would look like. Notice that you still haven’t told chart report what data it should work with, the chart being displayed right now is just an over view of how this chart type will look like.
27. Click on the chart area and it will display couple of options which are the optimum options one would like to set to get the chart report going, for this example we’ll be good with the few parameters as shown below

28. To set values simple click on the ‘+’ icon and select TotalRevenue tool will automatically select Summation operation on the selected field, this is the default behaviour and one can easily change this by right clicking on the TotalRevenue series and go to Series Properties and change value  field on the window pop up similar to the one shown below. Here again you need to provide expression for the value field.

29. So now we have set up total revenue as the value field but we need to show total revenue per employee of the company, hence we need to have employees name in the category axis which we shall do next following the same procedure as above i.e. clicking ‘+’ sign against category section and selecting EmployeeName field.
30. To set up a grid like pattern at the background of chart area, we will click on the chart area and then select “Chart Area Properties…” which will pop up following window
31. Now on the Fill option on the left we get couple of nice variations that can be made to chart area background, here we have made above changes and selected pattern style to show large grid, this gives the report a nice look to compare data in the report.
32. Next task would be to define threshold lines, you will notice that there are three threshold horizontal lines, first at 20 next at 40 and finally at 60. There are actually many ways of doing this trick, the very basic one would be to click on the major grid lines (make sure you select the major grid lines i.e horizontal lines visible on the chart) and after right clicking on that select “Major Gridline Properties…” which shall pop up following window

    

33. In General tab, select you desired line color, style and width for the grid lines. Under Set gridline interval section select “Grid interval type” as Integer and Grid Interval to 20, this will create grid lines at an interval of 20 points of vertical axis i.e. one at 20 second at 40 and third at 60 just the way we wanted. Please note that the value 20 is actually based on the data value, as the maximum possible value for Total Revenue per employee is 100, thus I have divided it in interval of 20. You need to make your choice of value appropriately; otherwise you can also set express which would be an average of total revenue column. But remember, this is an interval field and value you provide would repeat a line after this interval.  
34. You can also provide single value which would then display line only at that interval. Otherwise you can create an expression in line color option to have distinguished line at selected interval based on some report parameters or calculated fields etc.
35. Please note that modifying Major axis attribute is just a work around for getting desired outcome. Actual work of Major lines remain to show horizontal lines across all bars so that their value can be estimate correctly. The point here to note is, it is good to exploit given leverage but only to certain extend so that the feature doesn't loose its crude value. 
35. Now if you click on Preview tab on top of your report design section. You can see the actual report running as below

36. Setting Fill property of chart body doesn’t color the outer section of chart which can be done by setting background property of chart object. For that select complete chart and right click on it to select “Chart Properties…”.
37. This would open up a similar window where in the fill section you can select the color of you choice and fill your chart area with appropriate colors.

This was a very simple example of SSRS reports with chart. Hope you enjoyed it. Till next time keep exploring !!!

Points of Interest

Here in SSRS, there are lot of ways of exploring your creativity your skill to obtain a particular report format using controls provided and a very strong support for expression. I am sure once you start exploring then sky is the limit!!! Hope this interests new web developers and also those who are new to SSRS.