Click here to Skip to main content
15,867,330 members
Articles / Database Development / SQL Server / SQL Server 2008

SSRS Series Part II: Working with Subreports, DrillDown Reports, Expressions and Custom codes, Databars, Indicators, Custom Sorting and Paging and many more

Rate me:
Please Sign up or sign in to vote.
4.95/5 (93 votes)
12 May 2011CPOL17 min read 494.6K   28.1K   125   57
In this article we will learn creation of subreports, drilldown reports, custom coding and expression, web service and wcf service consumption frm SSRS,custom paging and sorting, working with databars and indicators with real time examples

Table of Content

  1. Introduction
  2. Background
  3. Data Source
  4. Creating a SubReport in SSRS (Download Subreport.zip file)
  5. Creating a DrillDownReport in SSRS (Download DrilDownReport_Example.zip file)
  6. Working with Expressions and Custom code(Download WorkingWithExpression.zip file)
  7. Working with Calculated fields (Download CalculatedFields.zip file)
  8. Sorting of Columns (Download Sorting.zip file)
  9. Custom Paging in SSRS report (Download CustomPaging.zip file)
  10. SSRS and WCF data source (Download SSRS_WCF_Example.zip file)
  11. SSRS and Web Service data source (Download SSRS_WebService_Example.zip file)
  12. Working with Data Bars (Download DataBars&Indicators.zip file)
  13. Displaying Colors in Data Bars based on conditions (Download DataBars&Indicators.zip file)
  14. Displaying Data Labels along with Data Bars (Download DataBars&Indicators.zip file)
  15. Working with Indicator (Download DataBars&Indicators.zip file)
  16. Conclusion

Introduction

Since the last few months I have been working in SSRS and has gained some knowledge which I wanted to share here especially for those who are about to start learning SSRS from more of a step by step approach as well as for those who has generated some basic SSRS reports but did not got much exposure to it and wanted to learn more. We have already started the journey from here with the very basic report generation.In this series we will look into some more interesting stuffs of reporting services.This will be an nth series of articles so in every part I will talk about some new feature. Kindly note that I am using Sql Server Denali CTP 1 for this article.

Background

SSRS has now become a defracto reporting tool and has become a necessity rather than luxury to become familiarise with it.I have found many peoples who have interest in the BI side, but didnot get any chance to work with because of many reason(may be they are not getting the exposure in their work field, lack of time to spend on the subject, frequent movement of projects etc.).Henceforth, I thought of writing this series of articles (SSRS/SSIS/SSAS) where basically I will talk about those features which I have touched upon as of now in my real time project. I will try to compile this series of articles more of step by step hands on approach so that people can refresh/learn by looking into it.Afterall, one picture is worth a thousand words.

Data Source

For this experiment, we will use the below script to generate and populate the Players table which is named as (tbl_Players)

-- Drop the table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Players' AND type = 'U')
    DROP TABLE tbl_Players
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_Players (
	PlayerID INT IDENTITY,
	PlayerName VARCHAR(15),
	BelongsTo VARCHAR(15),
	MatchPlayed INT,
	RunsMade INT,
	WicketsTaken INT,
	FeePerMatch NUMERIC(16,2)
)

--Insert the records
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Won','India',10,440,10, 1000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Cricket','India',10,50,17, 400000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('B. Dhanman','India',10,650,0,3600000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('C. Barsat','India',10,950,0,5000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Mirza','India',2,3,38, 3600000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('M. Karol','US',15,44,4, 2000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Hamsa','US',3,580,0, 400)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Loly','US',6,500,12,800000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Summer','US',87,50,8,1230000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.June','US',12,510,9, 4988000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A.Namaki','Australia',1,4,180, 999999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Samaki','Australia',2,6,147, 888888)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('MS. Kaki','Australia',40,66,0,1234)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Boon','Australia',170,888,10,890)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('DC. Shane','Australia',28,39,338, 4444499)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Noami','Singapore',165,484,45, 5678)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Biswas','Singapore',73,51,50, 22222)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Dolly','Singapore',65,59,1,99999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Winter','Singapore',7,50,8,12)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.August','Singapore',9,99,98, 890)

The partial output after running a simple Select query

Select * from tbl_Players
is as under

1.jpg

We will also have the below stored procedure created in our database whose script is as under

If Exists (Select * from sys.objects where name = 'usp_SelectRecordsByPlayerName' and type = 'P')
    Drop Procedure usp_SelectRecordsByPlayerName
Go
-- Create the  stored procedure
Create Procedure [dbo].[usp_SelectRecordsByPlayerName]
( @PlayerID int )
As
Begin
	Select 
		PlayerID
		,PlayerName
		, BelongsTo
		, MatchPlayed
		,RunsMade
		,WicketsTaken
		,FeePerMatch
	From
	tbl_Players
	Where PlayerId = @PlayerID
End

(A) Creating a SubReport in SSRS

A report within another report is a sub report. That is there will be two reports one the Master and the other the child where the master will invoke the child report. The child report or sub report can accept parameters from the master report and will execute its work. Moreover, it can be executed independently. Let us step into action.

Step 1:We will have two reports. The steps to be followed for both the reports are same only in the case of master report we will execute the below query

SELECT
             [PlayerID]
             ,[PlayerName]
             ,[BelongsTo]     
  FROM [SSRSExperiment].[dbo].[tbl_Players]

And for the Subreport we will execute the below query

SELECT 
      [PlayerName]      
      ,[MatchPlayed]
      ,[RunsMade]
      ,[WicketsTaken]
      ,[FeePerMatch]
  FROM [SSRSExperiment].[dbo].[tbl_Players]
WHERE  [BelongsTo]= @CountryName

Note that, we are passing the @CountryName parameter. So at runtime based on the parameter value passed , the sub report will be generated. Once the reports are created we will have two reports in our project as shown below

2.jpg

Testing the PlayerSubReport alone yields the below result

3.jpg

Step 2:Add a SubReport control in the main/master report.

4.jpg

Step 3:Right Click on the Subreport -> Subreport Properties

5.jpg

Step 4:From the General section of Subreport Properties window, select the subreport name (here it is PlayerSubReport) from the dropdown as shown below

6.jpg

And from the Parameters tab after clicking on the Add button, let us eneter the Parameter name as "CountryName"and the value as " =First(Fields!BelongsTo.Value, "DataSet1")". Once done , click on OK button

7.jpg

Step 5:That's it. Now let us run the report

8.jpg

So our Subreport has been generated.

(B) Creating a DrillDownReport in SSRS

Creating a drill down / Tree view report in SSRs is very simple.Let us see the following steps to do so.

Step 1: Open BIDS and creatre a new Shared Data Source

Step 2: Create a Table type report as shown below (The steps for doing so has been described in Part I series).

69.jpg

Step3: Next add Parent Group for Belongss To field as depicted under

70.jpg

Step 4: From the Tablix Group window, let us choose [BelongsTo] from Group By DropDown and check Add Group Header checkbox, then click OK.

71.jpg

At this point if we the report looks as under in the design view

72.jpg

While running the report gives the below impression in the Preview tab

73.jpg

Step 5: From Row groups, choose [Belongs To] Details and then choose Group Properties

74.jpg

Step 6: From the Group Properties window that opens up, choose Visibility tab. Then select Hide radio button and check the Display can be toggled by this report item checkbox.Then from the drop down that will be enable,select the name of the group (which is BelongsTo1 here) then click on OK

75.jpg

Step 7: Delete the [Belongs To] Details column

76.jpg

Step 8: Now it's all done. Run the application and the output is as under

77.jpg

(C) Working with Expressions and Custom code

In this section we will see how to use expression and custom code in our Report. We will learn these through some examples

Objective

What we are going to do is that, if a player has made more than 500 runs and has taken more than 10 wickets, then we are going to display the row and the particular columns with green color

For this, let us first click on the report design body and choose the report properties

9.jpg

The Report Properties window opens up as shown under

10.jpg

Navigate to the Code tab for writing the below custom code

Public Shared Function SetColor(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer) As String
SetColor= "Transparent"
If RunsMade   >= 500  AND  WicketsTaken >= 10 Then
SetColor= "Green"
End IF
End Function

The code is pretty simple to understand. We have declare a function by the name SetColor which accepts two integer variable,the first for the run and the next for the wickets taken. We set the initial value of the Setcolor to "Transparent" . Now if the condition meets , we will set the SetColor to "Green".

11.jpg

Next let us choose the RunsMade column and right click to open the TextBox menu from which we will choose the TextBox Properties

12.jpg

In the TextBox Properties window that appears, click on the Fill option and click the fx Button.

13.jpg

Enter the below expression in the Expression Window

=Code.SetColor(Fields!RunsMade.Value,Fields!WicketsTaken.Value)

Again the argument passing code snippet is very simple. In the first one, we are passing the RunsMade values while in the second we are passing the WicketsTaken values in the SetColor function that we just created.

14.jpg

Click OK button and repeat the same for the Wickets Taken column.Once done, let us run the application and the result is as under

15.jpg

Let us add some more customization to our report. If the above criterion satisfies, we will make the Player Name value as bold and the Belongs To as Italic.

So let us add two more functions in the Custom Code as under.

Function : SetBoldFontWeight

' Function to set the font weight as bold
Public Shared Function SetBoldFontWeight(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer) As String
SetBoldFontWeight= "Default"
If RunsMade   >= 500  AND  WicketsTaken >= 10 Then
SetBoldFontWeight= "Bold"
End IF
End Function

Function : SetItalicFontWeight

'Function to set the font weight as italic
Public Shared Function SetItalicFontWeight(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer) As String
SetItalicFontWeight= "Default"
If RunsMade   >= 500  AND  WicketsTaken >= 10 Then
SetItalicFontWeight= "Italic"
End IF
End Function

The Custom Code area will now look as under

16.jpg

Next in the PlayerName column right click to bring the TextBox properties and from the Font option, choose the fn for Bold

17.jpg

In the expression editor enter the below code

=Code.SetBoldFontWeight(Fields!RunsMade.Value,Fields!WicketsTaken.Value)

In the Belongs To column right click to bring the TextBox properties and from the Font option, choose the fn for Italic

18.jpg

In the expression editor enter the below code

=Code.SetItalicFontWeight(Fields!RunsMade.Value,Fields!WicketsTaken.Value).

We are done. Run the report and the result is as under

19.jpg

We will look into one more situation where we will generate alternate row color based on the Player ID column.

For this purpose add the below function in the custom codewindow

Public Shared Function SetAlternateRowColor(ByVal PlayerID As Integer) As String
SetAlternateRowColor= "Yellow"
If PlayerID Mod 2 = 0 Then
SetAlternateRowColor= "Green"
End IF
End Function

In this code, we are setting the color for even rows as Green .And for all the columns, let us write the below expression for the BackgroundColor

=Code.SetAlternateRowColor(Fields!PlayerID.Value)

The result is as under

20.jpg

Similarly we can set the alignments, hide rows depending on condition and many more stuffs by using expression and custom code.

(D)Working with Calculated fields

A Calculated field is a field that is derived from another field.

Objective

Suppose we want to twice the match fee for every player who ever has bagged more than 10 wickets and made a score of more than 500 runs. In such a case, that row will be will be green colored and the Calculated column value will be made bold.

Solution

Step 1: As a first step let us write the custom codes

Function: SetColor

Purpose: This function will set the color for the entire row to green if the runs made are more than or equals to 500 and wickets taken are more than or equals to 10.

'Function to set the color 
Public Shared Function SetColor(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer) As String
SetColor= "Transparent"
If RunsMade   >= 500  AND  WicketsTaken >= 10 Then
SetColor= "Green"
End IF
End Function

Function: SetBoldFontWeight

Purpose: This function will set the Row data to Bold for the Calculated column where the runs made by the Player are more than or equal to 500 and wickets taken are more than or equal to 10.

' Function to set the font weight as bold
Public Shared Function SetBoldFontWeight(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer) As String
SetBoldFontWeight= "Default"
If RunsMade   >= 500  AND  WicketsTaken >= 10 Then
SetBoldFontWeight= "Bold"
End IF
End Function

Function: DoubleMatchFee

Purpose: Doubles the player match fees if the runs made by the Player are more than or equal to 500 and wickets taken are more than or equal to 10.

'Function to double the match fee
Public Shared Function DoubleMatchFee(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer,ByVal OriginalMatchFee As Integer) As Integer
DoubleMatchFee= OriginalMatchFee 
If RunsMade   >= 500  AND  WicketsTaken >= 10 Then
DoubleMatchFee= OriginalMatchFee * 2
End IF
End Function

Step 2: Choose Data Set. Right Click and choose Add Calculated field

21.jpg

The DataSet Properties window opens up. Enter a Calculated field Name and click on the Expression button (fx)

22.jpg

Next Add the below expression in the expression window

=Code.DoubleMatchFee(Fields!RunsMade.Value,Fields!WicketsTaken.Value,Fields!FeePerMatch.Value)

23.jpg

Click OK.

Step 3: Drag and drop the DoubleMatchFee column to the Report Designer.

24.jpg

Right click on the DoubleMatch Fee column and from the text box properties choose Font and let us write the below expression against the Bold Font Weight

=Code.SetBoldFontWeight(Fields!RunsMade.Value,Fields!WicketsTaken.Value)

25.jpg

For all other columns, let us enter the below expression against the Fill color obtained from the Text Box Properties

=Code.SetColor(Fields!RunsMade.Value,Fields!WicketsTaken.Value)

26.jpg

And we are done. Let us run the report and the output is as under

27.jpg

Hope that we are now comfortable to work with calculated filed.

(E) Sorting of Columns

In this section we will look into how to perform sorting of columns in the report. Column sorting is an important feature and it can be done from the database side also. But if the same data source is being use by various other application and each demands the result to be displayed in different order, then it is better to do the sorting in the client applications only as opposed to the database.

Let us look into the below steps as how can we perform the same in our report.

Step 1: Right click on the column header and choose Textbox Properties.

28.jpg

Step 2: Choose Interactive Sort from the Textbox properties dialog and

  1. Check the checkbox for Enable interactive sort on this Textbox
  2. Choose Player ID field from the Sort by drop down.

29.jpg

And we are done. Let us perform the same for PlayerName field.Now run the report

30.jpg

As can be seen that the sorting has been enabled on two columns and the report is presented in descending order of Player ID.

(F)Custom Paging in SSRS report

This section will give us the way of generating custom paging in our report. We will also learn the use of Global variable in doing so.

Step 1: In the report design screen, right click and from the context menu, choose Add Page Footer

31.jpg

Once the Page Footer is added, we can then add controls to it.

32.jpg

Step 2: Let us add four textboxes from the Report Item toolbox onto the designer as shown under

33.jpg

Step 3:Choose the 2nd textbox and click on the Expression

34.jpg

Step 4:From the expression window that opens, let us write the expression

=Globals!PageNumber

35.jpg

Similarly for the 4th Textbox, let us write the expression as =Globals!TotalPages.

Step 5:Run the report and the output is as under

36.jpg

(G)SSRS and WCF data source

In this section we will look into how to consume WCF service from SSRS.

For doing so, first of all let us create a project of type "WCF Service Application". Let us add a class call Player and decorate the class with DataContract attribute. The class peoperties are being decorated with DataMember attribute.

using System.Runtime.Serialization;

namespace WcfServiceApps
{
    [DataContract]
    public class Player
    {
        [DataMember]
        public int PlayerID { get; set; }
        [DataMember]
        public string PlayerName { get; set; }
        [DataMember]
        public string BelongsTo { get; set; }
        [DataMember]
        public int MatchPlayed { get; set; }
        [DataMember]
        public int RunsMade { get; set; }
        [DataMember]
        public int WicketsTaken { get; set; }
        [DataMember]
        public double FeePerMatch { get; set; }
    }
}

Now, expose two operation contract from the Service contract Interface (which is IService1 here)

  • GetAllPlayers() // Get the list of all players
  • GetByPlayerId(int playerID) // Get the players by thier PlayerID

using System.Collections.Generic;
using System.ServiceModel;

namespace WcfServiceApps
{    
    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        List<Player> GetAllPlayers();

        [OperationContract]
        List<Player> GetByPlayerId(int playerID);
    }    
}

The class that implements the interface is the Service1 class here. The implementation goes as under

using System;
using System.Collections.Generic;
using System.Linq;

namespace WcfServiceApps
{    
    public class Service1 : IService1
    {
        public List<Player> GetAllPlayers()
        {
            return PlayerDataSource();
        }

        public List<Player> GetByPlayerId(int playerID)
        {
            return PlayerDataSource().Where(i=>i.PlayerID == playerID ).ToList();
        }

        #region PlayerData source
        private List<Player> PlayerDataSource()
        {
            List<Player> playerList = new List<Player>();
            Random r = new Random();
            for (int i = 1; i <= 20; i++)
            {
                if (i <= 5) playerList.Add(new Player { PlayerID = i, PlayerName = string.Concat("PlayerName", i), BelongsTo = "India", RunsMade = i * 10, WicketsTaken = i + 5, MatchPlayed = i + 10, FeePerMatch = r.NextDouble() });
                if (i > 5 && i <= 10) playerList.Add(new Player { PlayerID = i, PlayerName = string.Concat("PlayerName", i), BelongsTo = "Australia", RunsMade = i * 10, WicketsTaken = i + 5, MatchPlayed = i + 10, FeePerMatch = r.NextDouble() });
                if (i > 10 && i <= 15) playerList.Add(new Player { PlayerID = i, PlayerName = string.Concat("PlayerName", i), BelongsTo = "US", RunsMade = i * 10, WicketsTaken = i + 5, MatchPlayed = i + 10, FeePerMatch = r.NextDouble() });
                if (i > 15) playerList.Add(new Player { PlayerID = i, PlayerName = string.Concat("PlayerName", i), BelongsTo = "Singapore", RunsMade = i * 10, WicketsTaken = i + 5, MatchPlayed = i + 10, FeePerMatch = r.NextDouble() });
            }
            return playerList;
        }

        #endregion
    }
}

The code is simple to understand.The one last thing that we need to do is that, we need to specify the binding as BasicHttpBinding

<bindings>
     <basicHttpBinding>
       <binding name="PlayerBinding" />
     </basicHttpBinding>
   </bindings>

The complete configuration file is as under

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <system.serviceModel>
        <bindings>
            <basicHttpBinding>
                <binding name="BasicHttpBinding_IService1" closeTimeout="00:01:00"
                    openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
                    allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
                    maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
                    messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
                    useDefaultWebProxy="true">
                    <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
                        maxBytesPerRead="4096" maxNameTableCharCount="16384" />
                    <security mode="None">
                        <transport clientCredentialType="None" proxyCredentialType="None"
                            realm="" />
                        <message clientCredentialType="UserName" algorithmSuite="Default" />
                    </security>
                </binding>
            </basicHttpBinding>
        </bindings>
        <client>
            <endpoint address="http://localhost:49158/Service1.svc" binding="basicHttpBinding"
                bindingConfiguration="BasicHttpBinding_IService1" contract="IService1"
                name="BasicHttpBinding_IService1" />
        </client>
    </system.serviceModel>
</configuration>

Next, let us open BIDS and create a Report Server Project. In the Shared Data Source, let us specify the Type as XML and the Connection String to "http://localhost:49158/Service1.svc"

58.jpg

And let us write the below query in the query designer

<Query>
  <Method Namespace="http://tempuri.org/" Name="GetAllPlayers" />
  <SoapAction>http://tempuri.org/IService1/GetAllPlayers</SoapAction>
</Query>

Click on the Run icon to get the below result

59.jpg

Next follow the steps for generating the report as described in the first series and the final result will be

60.jpg

N.B.~ If we want to pass the parameter as we have for the Operation contract GetByPlayerId(int playerID), the query will be change to

<Query>
         <Method Namespace="http://tempuri.org/" Name="GetByPlayerId">
	     <Parameters>
                    <Parameter Name="playerID"><DefaultValue>10</DefaultValue></Parameter>
             </Parameters>
          </Method>
         <SoapAction>http://tempuri.org/IService1/GetByPlayerId</SoapAction>
</Query>

Note that, the Parameter Name in the query and the method has to be same

61.jpg

In this we have hard coded the value as 10 for the PlayerID parameter. We can however, pass it at runtime. Follow the steps given as under for doing so

Step 1: Go to View->Report Data. Alkternatively Press CTRL + ALT + D

Step 2: The Report Data Window appears. From there right click on the Parameter folder to add parameter

62.jpg

Step 3: In the Report Parameter Properties Window, enter a Parameter Name (PlayerID here), Enter a prompt (this is optional) and Enter the Data Type as Integer. Once done, click on OK button.

63.jpg

Step 4: Right-click the dataset, and click DataSet Properties

64.jpg

Step 5: Next click the Parameters tab on the left and add Parameter Name as playerID and Parameter Value as [@PlayerID]

65.jpg

That's all. Run the report. Enter the Player Id in the prompt and click on the View Button to view the resultant report

66.jpg

(H)SSRS and Web Service data source

This is very similar to the above. Let us first create a Asp.net Web service project. Then add the same Player Entity and the data Source that we had in the previous example. The Web method is as under

[WebMethod]
public List<Player> GetAllPlayers()
{
  return PlayerDataSource();
}

Next, let us open BIDS and create a Report Server Project. In the Shared Data Source, let us specify the Type as XML and the Connection String to "http://localhost:49277/WebService/WebService.asmx"

67.jpg

And let us write the below query in the query designer

<Query>
   <Method Namespace="http://tempuri.org/" Name="GetAllPlayers"> </Method>
   <SoapAction>http://tempuri.org/GetAllPlayers</SoapAction>
   <ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>

Next follow the steps for generating the report as described in the first series and we will get the desired result

68.jpg

N.B.~ For adding parameter to the report, it follows the same step as described above and henceforth is ignored in this section

(I) Working with Data Bars

Data bars are use to make "Tornado charts" in order to get an idea as how values are distributed.

We will use the same data source tbl_Players for this demonstration too.

Objective

In this experiment, we will see how the players runs are distributed using Data Bar.

DataBar into action

In the design view, add a table with three columns. In the first column, let us add [Player Name] field while in the last two column let us add the [Runs Made] column. Let us rename the header of the third column to [Runs Analysis]

37.jpg

From the Report Item toolbox, let us drag and drop a Data Bar control on to the third column. The Data Bar Type properties window appears. From there let us choose the "Stacked bar" Data Bar type

38.jpg

And click OK. Right click on the Data Bar and from the context menu, choose Chart Properties

39.jpg

In the General section of the Chart Properties window, choose Gray Scale from the Color Pallet drop down.

40.jpg

And in the Fill section, choose Gradient in the Fill Style and provide some suitable gradients

41.jpg

Click OK and view the report

42.jpg

We can reverse the direction of the Data Bars. Right click on the Data Bar and from the context menu,choose Horizontal Axis Properties.From the properties dialog check the Reverse Direction checkbox of the Scale options.

43.jpg

The report is as under

44.jpg

(J) Displaying Colors in Data Bars based on conditions

By this time we have seen how Data Bars works. Now let us customize it a little bit for displaying custom colors based on conditions. What we will do is that, if the runs made is greater than or equal to 100 we will display Green Color bars else Red color Bars.

Let us enter the below custom code to our report (Kindly refer to Working with expression section for understanding how to enter custom code to report)

'Function to set the color 
Public Shared Function SetColor(ByVal RunsMade As Integer) As String
SetColor= "Red"
If RunsMade  > 100 Then
SetColor= "Green"
End IF
End Function

The code is simple enough to understand. It will return the color as Red or Green depending on the condition.

Now consider we have designed our report as under (we have already seen this earlier. Henceforth, I am not explaining it again)

45.jpg

Let us select the Data Bar for getting the Chart Data popup. Then right-click on the bar and select Series Properties.

46.jpg

In the Series Properties window, select the Fill tab and click the Expression button (fx).

47.jpg

And in the expression editor enter the below expression

=Code.SetColor(Fields!RunsMade.Value)

48.jpg

That's it. Now if we run the report, we will get the desired result

49.jpg

Kindly note that, we have given some gradient effect to our report for beautification.

(K) Displaying Data Labels along with Data Bars

Let us see how we can display the data labels along with the data bars.

Right click the Data Bar and choose Show Data Labels.

50.jpg

The Data Label appears as shown below

51.jpg

Right click on the data label and choose the Series Label Properties

52.jpg

In the Series Label Properties window, go to the Border tab and set the Line Style to Solid.

53.jpg

Run the report. As can be figured out that, we now have the Data Bar and the Data Label together.

54.jpg

(L) Working with Indicator

Indicator finds its applicability when we do KPI (Key Performance Indicators) Analysis.

We will perform the same experiment as what we did for Data Bars.

So, we will add a three column table in which the first column will hold [Players Name] and the rest two will have [Runs Made]. We will also change the last columns header to [Runs Analysis].

From the Report Item toolbox, let us drag and drop a Indicator control on to the third column. The Indicator Type properties window appears. From there let us choose the "3 Arrows (colored)" Directional Indicator type.

55.jpg

And click OK. We can change the value and states of the Indicator from the Values and States option available in the Indicator Properties. We can even add more indicator states. The default is shown below

56.jpg

Now run the report and the result is as under

57.jpg

Now let us analyze the report and understand what the arrows means. If we remember, in the values and states, the ranges were set as under

ColorStart RangeEnd Range
Red033
Yellow3366
Green66100

Now let us analyze the first three rows. In the first row the run was 440 i.e. 440/10 is 44 which is in the Yellow color range. The next is 50 i.e. 50/5 = 5 which is in the Red quota. Likewise the third is in the Green's room.

Hope this helps.

Conclusion

Hope this article has helped those who wants to start learning SSRS and equally those who wants to refresh their reporting topics in which they are not in touch for a long time.In first part of the SSRS series we have seen different ways of report creation. In this part, we have seen working with subreports, treeview/drill down reports, working with expressions and custom code, calculated fileds, databars, indicators etc. with more real time example. Also we have seen how to consume with WCF services as well as Web services and to pass parameters at runtime. In the next article we will look into some other interesting aspects of SSRS like hierarchial reports, working with charts, gauses, sparkline, maps, Sql Azure as data source etc. with real time example.

Thanks for reading the article.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
GeneralMy vote of 3 Pin
rmpbklynnyc1-Oct-19 13:49
rmpbklynnyc1-Oct-19 13:49 
Praisegood work Pin
Thande Njuguna11-Sep-18 1:57
Thande Njuguna11-Sep-18 1:57 
QuestionSub-Report not loading data though header loaded Pin
Rajesh Kumar Chekuri18-Apr-17 14:34
Rajesh Kumar Chekuri18-Apr-17 14:34 
GeneralMy vote of 5 Pin
Tex-CodeProject8-Aug-16 11:16
Tex-CodeProject8-Aug-16 11:16 
GeneralMy vote of 5 Pin
Dan Randolph29-Jan-16 9:15
Dan Randolph29-Jan-16 9:15 
QuestionThanks Pin
Member 109259089-Dec-15 0:43
Member 109259089-Dec-15 0:43 
GeneralExcellent Pin
cesarms897-Jul-15 14:24
cesarms897-Jul-15 14:24 
GeneralVery good Article Pin
Member 158647323-Feb-15 20:33
Member 158647323-Feb-15 20:33 
Question(L) Working with Indicator Pin
Member 114321505-Feb-15 23:21
Member 114321505-Feb-15 23:21 
QuestionGreat artical Pin
SunilGupta048517-Dec-14 3:53
professionalSunilGupta048517-Dec-14 3:53 
QuestionCreating a Subreport using Code Project Series-ii, When execute the Master Report the displayed the Fields BelongsTo is India only displayed. I dont know the logic is Pin
sridharan.N26-Nov-14 23:02
sridharan.N26-Nov-14 23:02 
QuestionPlayerSubReport data Pin
Vivek Chandel11-Aug-14 2:42
Vivek Chandel11-Aug-14 2:42 
QuestionSubreport data changes based on main report data selection Pin
Member 109708486-Aug-14 2:22
Member 109708486-Aug-14 2:22 
QuestionGreat Great Great Article Pin
leketekoa31-Oct-13 21:31
leketekoa31-Oct-13 21:31 
QuestionHierarchial report Pin
Member 1021604317-Aug-13 21:09
Member 1021604317-Aug-13 21:09 
GeneralMy vote of 5 Pin
Vijaya Sekhar Sreeram16-May-13 2:34
Vijaya Sekhar Sreeram16-May-13 2:34 
QuestionVery Useful Article Pin
Dipti Mamidala16-Apr-13 0:45
Dipti Mamidala16-Apr-13 0:45 
QuestionThanks Too Much Pin
Mahmoud_fo2ad3-Apr-13 0:15
Mahmoud_fo2ad3-Apr-13 0:15 
GeneralMy vote of 5 Pin
Manjunath Shrikantiah4-Dec-12 0:37
Manjunath Shrikantiah4-Dec-12 0:37 
GeneralMy vote of 5 Pin
wsc09183-Dec-12 14:08
wsc09183-Dec-12 14:08 
GeneralMy vote of 5 Pin
Firdaus M21-Nov-12 1:22
professionalFirdaus M21-Nov-12 1:22 
GeneralMy vote of 5 Pin
Shivprasad koirala4-Nov-12 3:28
Shivprasad koirala4-Nov-12 3:28 
AnswerAwesome! Pin
rj.cruz30-Oct-12 9:21
rj.cruz30-Oct-12 9:21 
Ey! Terrific, Awesome Work... you got ten (10) point with this job.
I know a lot about RSS, but alway I'm looking for something more, more... and this article's fantastic....
Just continue doing like you doing! Smile | :) ....

Sorry by my English not is my natural Language
GeneralSSRS Pin
Parshuram Shinde23-Oct-12 2:49
Parshuram Shinde23-Oct-12 2:49 
Generalgreat work form the author Pin
sathi8323-Oct-12 1:42
sathi8323-Oct-12 1:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.