In this article, I will show you how to use Indicators in SSRS Report.

1. First of all create table named as TopFootballPlayers in Database and insert some data as in following script :

CREATE TABLE TopFootballPlayers

(

    No                  INT,

    PlayerName          VARCHAR(50),

    Country             VARCHAR(50),

    InternationalGoals  INT

)

INSERT INTO TopFootballPlayers VALUES (1,'Ali Daei',' Iran',109)

INSERT INTO TopFootballPlayers VALUES (2,'Ferenc Puskás',' Hungary',84)

INSERT INTO TopFootballPlayers VALUES (3,'Kunishige Kamamoto',' Japan',80)

INSERT INTO TopFootballPlayers VALUES (4,'Pele',' Brazil',77)

INSERT INTO TopFootballPlayers VALUES (5,'Sandor Kocsis',' Hungary',75)

INSERT INTO TopFootballPlayers VALUES (6,'Bashar Abdullah',' Kuwait',75)

INSERT INTO TopFootballPlayers VALUES (7,'Kiatisuk Senamuang',' Thailand',71)

INSERT INTO TopFootballPlayers VALUES (8,'Stern John',' Trinidad and Tobago',70)

INSERT INTO TopFootballPlayers VALUES (9,'Hossam Hassan',' Egypt',69)

INSERT INTO TopFootballPlayers VALUES (10,'Gerd Müller',' West Germany',68)

INSERT INTO TopFootballPlayers VALUES (11,'Miroslav Klose',' Germany',67)

INSERT INTO TopFootballPlayers VALUES (12,'Majed Abdullah',' Saudi Arabia',67)

INSERT INTO TopFootballPlayers VALUES (13,'Jassem Al-Houwaidi',' Kuwait',63)

INSERT INTO TopFootballPlayers VALUES (14,'Ronaldo',' Brazil',62)

INSERT INTO TopFootballPlayers VALUES (15,'Hussain Saeed Mohammed',' Iraq',61)

INSERT INTO TopFootballPlayers VALUES (16,'Imre Schlosser',' Hungary',59)

INSERT INTO TopFootballPlayers VALUES (17,'Didier Drogba',' Côte dIvoire',58)

INSERT INTO TopFootballPlayers VALUES (18,'Gabriel Batistuta',' Argentina',56)

INSERT INTO TopFootballPlayers VALUES (19,'Carlos Pavón',' Honduras',56)

INSERT INTO TopFootballPlayers VALUES (20,'Romario',' Brazil',55)

INSERT INTO TopFootballPlayers VALUES (21,'Kazuyoshi Miura',' Japan',55)

INSERT INTO TopFootballPlayers VALUES (22,'Jan Koller',' Czech Republic',55)

INSERT INTO TopFootballPlayers VALUES (23,'Joachim Streich',' East Germany',55)

INSERT INTO TopFootballPlayers VALUES (24,'Carlos Ruiz',' Guatemala',55)

INSERT INTO TopFootballPlayers VALUES (25,'Cha Bum-Kun',' South Korea',55)

INSERT INTO TopFootballPlayers VALUES (26,'Robbie Keane',' Republic of Ireland',54)

INSERT INTO TopFootballPlayers VALUES (27,'Adnan Al Talyani',' United Arab Emirates',54)

INSERT INTO TopFootballPlayers VALUES (28,'Samuel Etoo',' Cameroon',53)

INSERT INTO TopFootballPlayers VALUES (29,'Poul Nielsen',' Denmark',52)

INSERT INTO TopFootballPlayers VALUES (30,'Zico',' Brazil',52)

INSERT INTO TopFootballPlayers VALUES (31,'David Villa',' Spain',52)

INSERT INTO TopFootballPlayers VALUES (32,'Fandi Ahmad',' Singapore',52)

INSERT INTO TopFootballPlayers VALUES (33,'Jon Dahl Tomasson',' Denmark',52)

INSERT INTO TopFootballPlayers VALUES (34,'Lajos Tichy',' Hungary',51)

INSERT INTO TopFootballPlayers VALUES (35,'Hakan Sukur',' Turkey',51)

INSERT INTO TopFootballPlayers VALUES (36,'Thierry Henry',' France',51)

INSERT INTO TopFootballPlayers VALUES (37,'Karim Bagheri',' Iran',50)

INSERT INTO TopFootballPlayers VALUES (38,'Hwang Sun-Hong',' South Korea',50)

Let us check output after a simple select query.

1 2	`SELECT` `*` `FROM` `TOPFOOTBALLPLAYERS`

Output is as below :

2. Now open Visual Studio 2010 and open your SSRS project. Then add report named as DemoIndicator in your project.

Now create a Dataset. In the dataset, we will use following query :

1

2

3

SELECT      * 

FROM        TOPFOOTBALLPLAYERS

ORDER BY    PLAYERNAME

3. Now right click on report area and go to Insert–>Table. Then select data fields as a column in table. After that your Report Design will look like below.

4. Then right click on 3rd column and insert columns on right side.

In 4th column select data field Internationalgoals.

Repeat 4th step to add another column which is for indicator.

5. Now your report design will looks like below:

6. Now right click on last column and Select Insert–> Indicator.

7. A Select Indicator Type window opens. Then select Directional from left pane & select 3 Arrows(Colored). Then click on Ok button.

8. Now right click on Indicator & select Indicator Properties.

9. A Indicator properties window opens up. In that window, select Value and States from left pane. Then select Value, States Measurement Unit & Add Indicator States as shown in below screen :

Then click on Ok button.

10. So we have done with indicators in SSRS. To see the result click on Preview Tab.

Congratulations! We successfully completed Indicators in SSRS Report.

Indicators in SSRS 2014

2015-07-22 by suresh

In this article we will show you, How to create and configure the Indicators in SQL Server Reporting Services 2014. For this, We are going to use below shown report. Please refer SSRS Table Report, Data Source and Dataset articles to understand the creation of Data Source, Dataset and Table report. If you observe the below screenshot, It was a normal Table report with City, Name, Job Title, Sales Last Year and Sales Year to Date columns.

We used the Embedded Dataset for the above report and the Dataset is shown in below screenshot

SQL Command we used as the Dataset is:

1

2

3

4

5

6

SELECT [City]

      ,[FirstName]+' '+[LastName] AS [FullName]

      ,[JobTitle]

      ,[SalesYTD]

      ,[SalesLastYear]

  FROM [AdventureWorks2014].[Sales].[vSalesPerson]

Creating Indicators in SSRS 2014

In this example, We will create and add the Indicator on the right hand side of the Sales YTD and the Indicator should satisfy the below conditions:

If the value of Sales YTD is less than Sales Last year then display Red Indicator.
Anything Greater than Sales Last Year, Display Yellow Indicator.

First, Let us create an Empty column by right clicking on the Sales YTD column and select the Insert Column option and then select Right option.

Next, Drag and drop the Drop the Indicator from Toolbox to Empty column. When you drag and Drop the Indicator from Toolbox to Empty column, a new window called Select Indicator Type will be opened to select required Indicators (Directional, Symbols, Shapes and Ratings) from the available once. In this example, we are selecting Simple Shape Indicator as shown in below screenshot.

Click Ok to add Indicator to newly created empty cell. Now, we have to configure the Indicator value. First, Click on the Indicator will open the Gauge Data window.

In this example, we want to show the Indicator for Sales YTD so, Please drag and drop or select the Sales YTD as the Indicator Value.

NOTE: Please be careful when dragging the columns to values region, if you mishandled or dropped in design area will end up in mess.

Next we have to configure the Indicator Properties so, Right Click on the Indicator will open the context menu. Please select the Indicator Properties option from it.

Once you select the Indicator Properties option, a new window will be opened to configure the indicator values. First, goto Values and State tab, you can notice the default settings of the indicator values here.

By default States Measurement Unit value is Percentage and from 0 to 33 Red Indicators, 33 to 66 Yellow Indicator and 66 to 100 Green Indicator. If you want to change the percentage values, you can replace the default value or else use the fx button for custom expressions.

In this example we want to display the indicators based on Numeric values so, Please change the States Measurement Unit value from Percentage to Numeric as shown in below screenshot. We are also removing Green Indicator by selecting the Green and deleting it.

From the above screenshot, For the Red indicator we are fine with the Start value 0 but we have to change the End value so, we are selecting the fx button next to End value Text box and write the following expression.

It means, If the Sales YTD value is between 0 and Sales Last Year Value then Red Indicator will be displayed.

Next, We are changing the Starting value of the Yellow indicator by clicking the fx button next to Start value Text box and write the following expression.

Next, We are leaving the End value of a Yellow Indicator empty. It means, If the Sales YTD value is greater than Sales Last Year then Yellow Indicator will be displayed.

Click Ok to finish configuring the Indicator properties. Lets see the Report preview by clicking on preview tab

Thank you For Visiting Our Blog

Formatting Indicators in SSRS 2014

2015-07-23 by suresh

In this article we will show you, How to add labels to indicators, change the Indicator Colors, Indicator Icons, changing the Indicators type, adding multiple indicators and adding gauges, indicators in one column in SQL Server Reporting Services 2014 with example.

To explain the available Format options, We are going to use below shown report. Please refer SSRS Indicators article to understand the Data Source and Dataset we used for this report.

First, Right click on the Indicator and select the Add Labels option from the context menu to show the values.

Now, Let us configure (or Change) the Text produced by the label. First, right-click on the Text will open the context menu as shown in below screenshot. Please select the Label Properties.. option from that menu

Once you click the Label Properties.. option, a new window will be opened to format the labels. From the below screenshot you can observe that, By default it is displaying Text as Label so, Please click on the fx button to write the custom expression.

Once you click on fx button, a new window called Expression will be opened with available functions. Here, we are displaying value as Sales of Year to Date – Sales of last year and also rounded the value using Round function.

Click Ok to finish configuring custom Expression as label Text

Changing Font Style and Font Size of an Indicator Label in SSRS 2014

Within the Label Properties window, goto Font tab and style the font as per your requirements. In this example we are changing the Font to Lucida Sans and Color to Black

Change Indicator Type in SSRS 2014

SSRS allows us to change the Indicator type even after creating the Indicator. First, select the Indicator and right-click on it will open the context menu. Please select the Change Indicator Type… option from the context menu

Once you select the Change Indicator Type… option, it will open new window called Select Indicator Type will be opened to select required Indicators (Directional, Symbols, Shapes and Ratings) from the available once. In this example, we are selecting Simple Directional Indicator as shown in below screenshot.

Click Ok to finish changing the Indicator type and preview the report

Format the Indicator Color and Icons in SSRS 2014

SSRS allows us to change the Indicator Icons and colors as well. For this, Right Click on the Indicator will open the context menu. Please select the Indicator Properties option to configure the indicator values. First, goto Values and State tab, you can notice the default Icons and colors for the indicators.

Please change the Indicator Color and Indicator Icons as shown in below screenshot.

NOTE: By clicking the fx you can customize the colors using Expression

From the below screenshot you can observe that, We changed the Color and Indicators

Click Ok and goto Preview tab to see the report preview

Multiple Indicators

We can also add multiple indicators in one column. Right click on the column will open the context menu and from that menu, Please select the Add Indicator option

From the below screenshot you can observe that, Indicator column is holding two Indicators

Add Gauge along with SSRS Indicator

First, select the Indicator and Right Click on the Indicator will open the context menu. Please select the Gauge Panel option and then select Add gauge from it. Once you select the Add gauge option, a new window will be opened to select the gauge

Select the required gauge from the list and click Ok. Here we selected the simple gauge as you can see in below screenshot

Thank You for Visiting Our Blog

Microsoft Technologies

Pages

Monday, October 5, 2015

Indicators in SSRS Report