Indicators in SSRS

SQL Server Reporting Services provides several ways to analyze the data. Indicators are minimal gauges that convey the state of a single data value at a glance and are mostly used to represent the state value of Key Performance Indicator (KPI, a measurable value which has business significance with a specific target or goal that indicates whether things are going good or bad). Indicators can be used in either dashboards or free-form reports but more commonly are used in tabular or matrix reports to visualize data in rows or columns. 

Step by step implement Indicators in SSRS.

Here I am using AdventureWorksDW2008R2 data base and want to implement indicator based on the number of product sales unit. My sql query is below

select P.EnglishProductName,Count(FIS.ProductKey)as [Number of sales Unit] from FactInternetSales FIS Inner join DimProduct P ON P.ProductKey=FIS.ProductKey Group by P.EnglishProductName

Now open reporting service project.

Create new Report.

Create new data source.

Now create data set using the above sql query.

Click ok.

Now in report design insert a table having three columns as below.

In 3^rd Column we need to insert Indicator.

Click ok. You will get the following window

According to you requirements you can select the indicators. Click ok.

Select Indicator properties.

You will get below screen.

In general tab you can write the name of the Indicator. If you want to write the tool tip you can.

Now select Value and States Tab

A.     Select the value for the indictor.

B.    Select the Measurement unit either % or Numeric.

C.    Set the Icon, Color Minimum values and maximum value for the Icon

If you want to add more icon click on the add

In place of icon you can put your image for that you need to click on the Image button and browse the image.

Similarly for removing the icon you need to click delete button.

If you want to perform any action select Action tab.

D.   Click ok.

Now you can review the report you will get the desirer report.

 

Gauges Report in SSRS

The Gauge data region is a one-dimensional data region that displays a single value in your dataset. An individual gauge is always positioned inside a gauge panel, where we can add child or adjacent gauges. We can use the gauge panel to create multiple gauges inside a single gauge panel that share common functions such as filtering, grouping, or sorting.

We can use gauges to perform many tasks in a report:

Ø  Display key performance indicators (KPIs) in a single radial or linear gauge.

Ø  Place a gauge inside a table or matrix to illustrate values inside each cell.

Ø  Use multiple gauges in a single gauge panel to compare data between fields.

There are two types of gauges: radial and linear. The following illustration shows the basic elements of a single radial gauge in the gauge panel.

Step by Step creating Gauge report

Create data source connection

Create new data set. User this query. I am using AdventureWorksDW2008R2 database.

select p.EnglishProductName, Sum(SalesAmount)as [Total sales] from FactInternetSales f Inner join DimProduct p on P.ProductKey=f.ProductKey Group by  p.EnglishProductName

Report design we take table

In next column we are inserting Gauge.

Select the Gauge type. There are two type of Gauge

Ø  Redail

Ø  Linear

Here I am selecting Redial

Select Gauge data set Values.

Select the Gauge properties.

Write the Gauge name if you want to set the tool tip give the value for it.

Now select Gauge scale properties.

Here you can set the scale value and interval. You can also define the layout and action, Major and minor tick marks, borders etc.

Now view in browser. You will get this report.

Parameterized Report in SSRS

SSRS provide a feature of Parameter with the help of it a user can supply value to filter the report. Parameters are used in a report to provide a dynamic feature by providing user input to the report execution process .Parameters are usually used to filter large set of records so as to narrow down the end report for better analysis.

For Example:-

I am using AdventureWorksDW2008R2 database. I want to generate report based on the product categories, product sub categories and product and want to know the total sales according to this group. Here I have three parameter Product categories, Product subcategories and product. This is cascading report.

Lest start step by step to create Cascading Report

Open the reporting services and create a new project.

In Solution Explorer Right click on Report -> Click on Add -> Click on new Item

You will get below screen.

Select the Report and write the report name and click add.

 Create a new data source.

Right click on Data source and click on Add Data source

A: - write the data source name

B: - you can used two type of connection

1.       Embedded connection

2.       Shared data source

There are following type of data source

C: - you can write the connection string on text box or Click on Edit button

Write the sever name and select the data base. Click on test connection.

Click on ok.

You will get the connection string here.

D: - if you want to use shared data source then select this option.

E: - click ok.

New data source is created.

Once data source is created we need to create dataset. For creating dataset right click on data set

Click on Add Dataset

Write the data set name. If you want to use shared data set you click on shared data set.

User a dataset embedded in my report and select Data source.

You can write the sql query in text otherwise you can use query designer

For creating the data set you can use sql query or stored procedure.

select ProductCategoryKey,EnglishProductCategoryName from DimProductCategory

Click on Fields. You will get the selected fields here.

Click on Options

Here you can set collection, case sensitivity, Accent sensitivity, width sensitivity etc.

If you want to create filter you can click on filter.

If you are using any parameter you can select Parameters and add new

Click ok.

New data set has been created.

Now we need to create

Right click on Parameter and select Add parameter and click.

A: -write the parameter name

B: - write the prompt name. This name will be display.

C: - select the data type of the parameter. There are following type of data type as below.

D: - There is the following option which you can set on the parameter.

Ø  Allow blank value

Ø  Allow null value

Ø  Allow multiple values.

E: - if you want to hide you parameter the select hidden.

   

Click on Available Values.

If you are selecting none. You will get the text box you have to supply the value.

If you are selecting Specify values you can select that.

Here I am  getting  values from query so I have selected Get values from a query option.

Select the data set. After that you have to select values fields and label fields.

If you want to select the default value display on the parameter. Select default value tab. Select get from a query. Select dataset and value fields.

 Select advance tab for refreshing the data.

Now click on ok.

Parameter has been created.

Now you can preview page you can see one drop down list has been created.

I have created one parameter similarly I am going to create other parameter for product sub category.

Create a new data set for Product sub category.

select ProductsubCategoryKey,EnglishProductSubCategoryName from DimProductSubcategory where ProductCategoryKey=@Prod_Catg

Here I am creating a variable and this value I will take from the product category parameter.

We can do setting as above describe. Now come to Parameter tab.

Click on add you will see the variable which is declare in query. Now we can map it which I have created.

Click on ok.

Now data set has been created.

Similarly we need to create a parameter for product sub category.

Now similarly I am going to create a data set for product.

Query for that

Select P.EnglishProductName,Count(*) as [Seles Unit],SUM(FIS.salesAmount) As [Total seles] from FactInternetSales FIS INNER JOIN DimProduct P ON P.ProductKey=FIS.ProductKey INNER Join DimProductSubcategory PSC ON PSC.ProductsubCategoryKey=P.ProductsubCategoryKey where P.ProductsubCategoryKey = @Prod_Sub_Catg group by P.EnglishProductName

Now see the report