Dynamic Color Coding in SSRS Charts

In this article, I will explain how to change the color of a chart dynamically. In other words, how to provide custom color palette feature at run time.

Lets start off with an example including step by step operations.

Step1. Open or create new Report Server Project. I am creating a new Report Server Project using SQL Server 2012 and saved it with LearnSSRS2012 name.

Step2. Add new Shared Data Source by right click on "Shared Data Sources" folder.

 

 Now give any name to Share Data Source (Local, in my case) and click on Edit button to define Server Name and Database Name. In this example, I will use static data sources so I am using "." (you can also use "(local)") as ServerName. I am not defining any Database name so I will keep it blank (default will be msater). You can define any database, if you want. Now click on OK.

Step3. Now create a new report by clicking on Reports --> Add --> New Item --> Report --> Add. I don't like default report name like Report1, Report2, Report3 so I have renamed it to ChartColorCode.rdl.

Step4. Create Data Source for ChartColorCode report. To create a new Data Source, right click on "Data Sources" followed by "Add Data Sources" under Report Data as shown below:














We will use Shared Data Source we created above. So click on "Use shared data source reference" and select Local data source created at step 2. Give any name to this report level data source. I will assign "Local" again. Click OK to proceed.

Step5. Now lets create couple of datasets. To create a new dataset, right click on "Datastes" followed by "Add Datastes" under Report Data as shown below:














I will use two data sources in this example, one for Color Palette and another for Chart.

To create new Dataset for ColorPalette, Right click on DataSets --> Add Dataset... and type "ColorPalette" in Name textbox. Select "Use a dataset embedded in my report" option and select Local as Data Source.

Select Text option in "Query Type" and type following query in Query window:

SELECT 'Red' AS ColorName UNION
SELECT 'DarkOrange' AS ColorName UNION
SELECT 'Green' AS ColorName UNION
SELECT 'Blue' AS ColorName UNION
SELECT 'Olive' AS ColorName UNION
SELECT 'SeaGreen' AS ColorName UNION
SELECT 'Brown' AS ColorName UNION
SELECT 'Gray' AS ColorName UNION
SELECT 'Tomato' AS ColorName

Click OK to proceed.


Create one more dataset using following query and name it Chart.ds:

SELECT 2010 AS [Year], 12000 Amount UNION
SELECT 2011 AS [Year], 15000 Amount UNION
SELECT 2012 AS [Year], 13000 Amount


Step6. Drag and drop "3D Cylinder"  bar chart from toolbox. Drag and drop Amount field from Chart.ds to value field (Y axis) and Year field to Category Groups as shown below.

To make the chart more meaningful and dainty, do following formatting:
1. Replace Chart Title with "Year vs Amount".
2. Replace Y Axis Title with Amount.
3. Repalce X Axis Title with Year.
4. Remove the Chart Legend.
5. Change the font size and color as you wish.
6. Right click on the bar and select "Show Data Labels". Select Times New Roman in data labels font because numbers are better visible in this font.

After incorporating all the above changes, click on Preview tab to view the report. My report looks like one shown below:

Step7. Now I will explain how to change the chart color at run-time.
First of all, I will create a report parameter for dynamic color. Click on Parameters folders in Report Data and then click on Add Parameter... Define pColor as report parameter Name and Color as Prompt. Select parameter visibility Hidden. You can keep it as Visible if you wish to do so.

Click on Available Values under Report Parameter Properties window. Select "Get values from a query" option under "Select from one of the following options". Select ColorPalette in Dataset box, ColorName in "Value field", and ColorName in "Label field".

Click on Default Values under Report Parameter Properties window. Select "Get values from a query" option under "Select from one of the following options". Select ColorPalette in Dataset box, and ColorName in "Value field". Click OK to proceed.


Step8. Drag and drop Table control from toolbox.
Do set following properties for table control.
a) Table control will have three default columns. Delete one column.
b) Merge Table Header and write "Color Palette" in the textbox.
c) Write following expression in the second column of Table Details.
=IIF(Parameters!pColor.Value=Fields!ColorName.Value,"◄","")
 and write following expression in background property of same text box:
=Fields!ColorName.Value

d) Select Table Details and then right click --> select "Row Group" --> select "Group properties"

e) In "Group Properties" window, click on expression button under "Group on" box and write following expression =Fields!ColorName.Value.

f) Select first text box of Table Details and then right click to select "Test Box Properties" as highlighted below:

g) in "Text Box Properties" dialogue box, click on Action and select "Go to report" under "Enable as an action" and "ChartColorCode" in "Specify a report" box. Also select report parameter pColor in Name and [ColorName] in Value as shown below:

Step9. Now we are at the final step where we need to change the properties of bar chart to change the color at run time. Right click on bar chart and select "Series Properties".

In "Series Properties" window, click on Fill. and then click on expression button to open Expression window. Double click on pColor under parameter or write following expression  manually as shown below:
=Parameters!pColor.Value

Step10. Its time to run the report and see the results.

First Preview:

Second Preview: Following report will be displayed once we click on Red color under:

Third Preview: Following report will be displayed once we click on DarkOrange color under:

I hope you like this article!

 

Adding Custom Color in SSRS Chart Reports

Do you ever come across a requirement where you need to use user defined colors instead of default colors in a chart (for example Pie Chart).

In this article, I'll explain how to create a Custom Palette and how to use that palette in a Pie Chart.

In the above screen-shot, I am using color Blue, Red, Teal and Gold for the first four values of the Period column and same colors are used in Pie Chart as well.

Here are the steps to achieve this task:

STEP1:
Create your custome color palette in the report using Custom Code in your report. To do so, click Report => Report Properties => Code and copy below code:

Private colorPalette As String() = { "Blue", "Red", "Teal", "Gold", "Green","#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}

Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String
   If mapping.ContainsKey(groupingValue) Then
      Return mapping(groupingValue)
   End If
   Dim c As String = colorPalette(count Mod colorPalette.Length)
   count = count + 1
   mapping.Add(groupingValue, c)
   Return c
End Function

You can define your own colors in the above function.

STEP2:
In the Pie Chart, select Series Properties and select the Fill option from left side as shown below:

Now write following expression in the Color expression:

=code.GetColor(Fields!Year.Value)

Here Fields!Year.Value is a field of dataset which is used as Chart Category fields.

STEP3:
In the left most column of the tabular report, insert character █ and in the color property of the textbox write following expression:

=code.GetColor(Fields!Year.Value)

Here I am considing that tabular report is grouped on Fields!Year.Value field.

Thats it. Click on the preview and enjoy your custom colors in charting.

Cheers!!!

Displaying Dynamic Columns in SSRS Report

Problem: How to display selected columns dynamically in SSRS reports.
Example: A report contains more than 30 fields. Some users want to see only 5 fields, some users 10 fields, and other may want to see 20 fields.
Solution: Add a Report Parameter having the values as the name of all the fields of dataset. Now set hidden expression for each column of the tabular report.

Here is the solution with an example:

STEP1:
Create a report with required dataset. Drag and drop table control and select dataset fields.
In my example, I have following fields in the dataset: Year, Quarter, Month, Date, Product Name, Customer Name, Sales Region, Sales Country, Order Number, Sales Amount.

STEP2:
Create a dataset dsColumns using below query:
SELECT 1 ID, 'Year' AS ColumnName UNION
SELECT 2 ID, 'Quarter' AS ColumnName UNION
SELECT 3 ID, 'Month' AS ColumnName UNION
SELECT 4 ID, 'Date' AS ColumnName UNION
SELECT 5 ID, 'Product Name' AS ColumnName UNION
SELECT 6 ID, 'Customer Name' AS ColumnName UNION
SELECT 7 ID, 'Sales Region' AS ColumnName UNION
SELECT 8 ID, 'Sales Country' AS ColumnName UNION
SELECT 9 ID, 'Order Number' AS ColumnName UNION
SELECT 10 ID,'Sales Amount' AS ColumnName


STEP3:
Create a new parameter with name pDisplayFields and Promt Display Columns as shown below:

In Available Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field and label field.

In Default Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field.

STEP4:
Now you have to set the expression to display the colummns which are selected in the pDisplayColumn parameter. Right click on First Column (Year in my example) and click Column Visibility...

Write following expression in Show or hide based on an expression of Column Visibility wizard:
=IIF(InStr(JOIN(Parameters!pDisplayFields.Value,","),"Year")>0,False,True)

Now repeat this expression for all the columns by modify the expression for the respective column name accordingly.

Thats all. Now preview the report. You will see all the columns by default.

Now select required columns in Display Column parameter to modify the report layout at run time.

Cheers!!!

Convert string into Camel Case using Visual Basic Code

I have already written T-SQL Function to convert a string into Camel Case. In this article I am going to share same function but in Visual Basic code. This function can be used directly in SSRS code.

Here is Visual Basic code to convert a string into Camel Case (Proper case):

Public Function CamelCaseInVB(ByVal str as String) as String
  Dim strFinal As String
  Dim i As Integer
  scrstr = Trim(LCase(str))
  i = 1
  If str.Length > 0 Then
    While i <= str.Length
       If i = 1 Then
          strFinal = strFinal + UCase(Mid(str, i, 1))
          i = i + 1
       ElseIf Asc(Mid(str, i, 1)) = 32 Then
          strFinal = strFinal + Space(1) + UCase(Mid(str, i + 1, 1))
          i = i + 2
       ElseIf Asc(Mid(str, i, 1)) = 40 Then
          strFinal = strFinal +"(" + UCase(Mid(str, i + 1, 1))
          i = i + 2
       Else
          strFinal = strFinal + Mid(str, i, 1)
          i = i + 1
       End If
    End While
  End If
  Return strFinal
End Function

Groups in SSRS 2008

In Report Designer, Groups are created to organize data on the report or to calculate aggregate summaries. An understanding how to define groups and use group features helps you design reports that are more concise and communicate summarized data in a more compact format.

We also use Groups in SSRS reports to provide Drill-down features. In this article, I'll walk you through SSRS Groups to create a report shown in below image:

STEP1: Add new report in Report Server project and rename it to GroupsInSSRS.rdl.
Create new Shared DataSource for database AdventureWorksDW2008R2.
Click here to download AdventureWorksDW2008R2 database.

STEP2: Create a new dataset dsMain with Shared Data Source. Use below query for this dataset:
SELECT
D.CalendarYear AS [Year]
,D.CalendarQuarter AS [Quarter]
,D.EnglishMonthName AS [Month]
,D.FullDateAlternateKey AS [Date]
,P.EnglishProductName AS [ProductName]
,C.FirstName + LastName AS [CustomerName]
,ST.SalesTerritoryRegion AS [SalesRegion]
,ST.SalesTerritoryCountry AS [SalesCountry]
,F.SalesOrderNumber AS [OrderNumber]
,F.SalesAmount
FROM FactInternetSales F
JOIN DimProduct P
  ON P.ProductKey = F.ProductKey
JOIN DimCustomer C
  ON C.CustomerKey = F.CustomerKey
JOIN DimDate D
  ON D.DateKey = F.OrderDateKey
JOIN DimSalesTerritory ST
  ON ST.SalesTerritoryKey = F.SalesTerritoryKey

STEP3: Drag and drop a table control and select CustomerName, OrderNumber, and SalesAmount fields in the table.

STEP4: Select table detail row --> Right click --> select Add Group --> Row Group --> Parent Group

STEP5: Select ProductName in Tablix group window and check add group header check box. Click OK and sace changes.

Now you can see Product Group in the table as shown below:

Delete Group1 Column and rename Customer Name as Particulars. Select ProductName in Product Group as shown below:

STEP6: Select ProductName group and add one more group for SalesCountry in similar way mentioned at step5. Delete the group column and select SalesCountry in CountryGroup as shown below:

STEP7: In the same way add Groups for the following Fields:

• SalesRegion
• Date
• Month
• Quarter
• Year

Finally table will look like one shown in below image:

STEP8: we are done with report Groups. You can Preview the report:

STEP9: Format Groups: This is the final step. Without formatting the tabular data look meaningless and its very difficult to understand the report. You can do following formatting to make it easier to understand:

Group	Left Indent	BackgroundColor
Year	2pt	#7c73c8
Quarter	12pt	#9894ca
Month	22pt	#b4b4c8
Date	32pt	#c7c7d8
SalesRegion	42pt	#dadaeb
SalesCountry	52pt	#e7e7f0
ProductName	62pt	#f4f4fc
CustomerName (detail row)	72pt	White

Toggle Groups:

Select Quarter Group and click on Group Properties. Select Visibility in Group Properties and select Hide option button. Check Dispaly can be toggled by this report item  check box and select Year from combo box.

Similar way toggle other report items.
Also Toggle Order Number column with Particular text box.

Now click on Preview and generate the report:

Thats all about Report Groups...
Cheers!!!

Conditional Formatting in SSRS 2008

Alternate Background Color in Tabular Report:
Alternate background color of rows in a table make it easier to read horizontally. Also it makes report look and feel better. This is very common but simple task to achieve in SSRS report using expression.

Select the detail row of the data table in the layout tab.

Go to property window and select expression of BackgroundColor and enter below expression:
=IIF(RowNumber(Nothing) Mod 2 = 0,"Transparent","#cdfcbe")

Now click and Preview the report:

Format Phone number using REGEX function:
Suppose you want to display Phone no in (ddd) ddd-dddd format instead of ddd-ddd-dddd. To do this, Right click on the required textbox --> click expression... and enter below expression:

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")


Now click on Preview and see the report:

 
Changing Font Color dynamically:
Sometime we need to format data based on the their values. Below is an example for Base rate color selection:

Base Rate	Color
<= 15	Green
> 15 AND <=25	Blue
> 25 AND <=40	Black
> 40	Red

To do this, Enter C (Currency format) in Format and below expression in Color property of BaseRate field:
=SWITCH(Fields!BaseRate.Value <= 15,"Green",

Fields!BaseRate.Value <= 25,"Blue",
Fields!BaseRate.Value <= 40,"Black",
Fields!BaseRate.Value > 40,"Red")

Now click on Preview and see the report:

Swapping Font Color and BackgroundColor:
Suppose you want to swap Font Color and BackgroundColor of Gender field. Use below expressions in BackgroundColor and Color properties respectively:

=IIF(Fields!Gender.Value = "M","Tomato","Teal")

=IIF(Fields!Gender.Value <> "M","Tomato","Teal")

Now click on Preview and see the report:

SSRS Expressions

Reporting Services Expressions
In Reporting Services, Expressions are used frequently in reports to control content and report appearance. Expressions are used throughout the report definition to specify or calculate values for parameters, queries, filters, report item properties, group and sort definitions, text box properties, bookmarks, document maps, dynamic page header and footer content, images, and dynamic data source definitions.

Expressions begin with an equal (=) and are written in Visual Basic. Expressions can include a combination of constants, operators, and references to built-in values (fields, collections, and functions), and to external or custom code. Expressions can be one of the following two types:

• Simple - An expression that is a single reference to an item in a built-in collection, such as, a dataset field, a parameter, or a built-in field. Simple expressions appear on the design surface and in dialog boxes in brackets, such as [FieldName], which represents the underlying expression =Fields!FieldName.Value. You can type simple expressions directly into a text box on the design surface and the corresponding expression text is set as the value of a placeholder inside the text box.


• Complex - An expression that includes more than a simple reference. Complex expressions appear on the design surface as <>. You can create complex expressions in the Expression dialog box or type them directly into the Property pane.

Examples of SSRS Expression

Using Built-in Fields

Display  Report Execution Time in a textbox:

="Report Execution Time: " & Globals!ExecutionTime

Display Page No in a textbox:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages

Similar way you can use other built-in Fields in expressions e.g. Report Folder, Report Name, ReportServerUrl, UserID, Language etc. as shown below:

Date & Time Functions

The Today() function provides the current date. The expression  =Today() can be used in a text box to display the date on the report, or in a parameter to filter data based on the current date. This function will return date in M/d/yyyy 12:00:00 AM format. You can use Format function to display required format. Some of the popular date formats are given below:

Expression	Output
=FORMAT(Today(),"M/d/yy")	8/23/10
=FORMAT(Today(),"MM-dd-yyyy")	08-23-2010
=FORMAT(Today(),"MMM-dd-yyyy")	Aug-23-2010
=FORMAT(Today(),"MMMM dd, yyyy")	August 23, 2010
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss")	Aug 23, 2010 01:43:33
=FORMAT(DateField,"MMM dd, yyyy HH:mm:ss")	Aug 23, 2010 13:43:33
=FORMAT(DateField,"MMM dd, yyyy HH:mm:ss.fff")	Aug 23, 2010 13:43:33.587
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss tt")	Aug 23, 2010 01:43:33 PM

Note: FormatDateTime function can also be used to format the date field e.g. =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)

DateAdd - Returns a Date value containing a date and time value to which a specified time interval has been added. this function can be used in an expression to add/substract time(day, month, year, sec etc.) from given date field:
=DateAdd(DateInterval.Month, 6, Parameters!StartDate.Value)

DateDiff - Returns a Long value specifying the number of time intervals between two Date values.
=DateDiff("yyyy",Fields!BirthDate.Value,Today())

DatePart - Returns an Integer value containing the specified component of a given Date value.
=DatePart("q",Fields!BirthDate.Value,0,0)

=DatePart(DateInterval.Quarter,Fields!BirthDate.Value, FirstDayOfWeek.System, FirstWeekOfYear.System)

There are many other Date &Time functions which can be used expression:

String Functions

• Combine more than one field by using concatenation operators and Visual Basic constants. The following expression returns two fields, each on a separate line in the same text box:

=Fields!FirstName.Value & vbCrLf & Fields!LastName.Value

•Format dates and numbers in a string with the Format function.

=Format(Parameters!StartDate.Value, "M/D") & " through " & Format(Parameters!EndDate.Value, "M/D")

•The Right, Len, and InStr functions are useful for returning a substring, for example, trimming DOMAIN\username to just the user name. The following expression returns the part of the string to the right of a backslash (\) character from a parameter named User:

=Right(Parameters!User.Value, Len(Parameters!User.Value) - InStr(Parameters!User.Value, "\"))

The following expression results in the same value as the previous one, using members of the .NET Framework System.String class instead of Visual Basic functions:
=User!UserID.Substring(User!UserID.IndexOf("\")+1, User!UserID.Length-User!UserID.IndexOf("\")-1)

• Join - Display the selected values from a multivalue parameter
=Join(Parameters!MyParameter.Value,",")

•The Regex functions from the .NET Framework System.Text.RegularExpressions are useful for changing the format of existing strings, for example, formatting a telephone number. The following expression uses the Replace function to change the format of a ten-digit telephone number in a field from "nnn-nnn-nnnn" to "(nnn) nnn-nnnn":
=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")

There are many other function which can be used in expression as shown below:

Conversion Functions

You can use Visual Basic functions to convert a field from the one data type to a different data type.

• 
  
  The following expression converts the constant 100 to type Decimal in order to compare it to a Transact-SQL money data type in the Value field for a filter expression: =CDec(100)

• The following expression displays the number of values selected for the multivalue parameter MyParameter: =CStr(Parameters!MyParameter.Count) 

Decision Functions

The IIF function returns one of two values depending on whether the expression is true or false. The following expression uses the iif function to return a Boolean value of True if the value of Total exceeds 100. Otherwise it returns False:

=IIF(Fields!Total.Value > 100, True, False)

Use multiple IIF functions (nested IIFs) to return one of three values depending on the value of PercentComplete. The following expression can be placed in the fill color of a text box to change the background color depending on the value in the text box.

=IIF(Fields!PercentComplete.Value >= 10, "Green", IIF(Fields!PercentComplete.Value >= 1, "Blue", "Red"))

A different way to get the same functionality uses the Switch function. The Switch function is useful when you have three or more conditions to test. The Switch function returns the value associated with the first expression in a series that evaluates to true:

=Switch(Fields!PercentComplete.Value >= 10, "Green", Fields!PercentComplete.Value > 1, "Blue", Fields!PercentComplete.Value = 1, "Yellow", Fields!PercentComplete.Value <= 0, "Red",)

A third way to get the same functionality uses the Choose function. The Choose function uses the first parameter as an index to one of the remaining function parameters. The first parameter must be an integer. If the background color of a text box in a table is set to this expression, the value of MyIndex controls the color.

=Choose(Fields!MyIndex.Value,"Red","Green","Yellow")

Check the value of the PurchaseDate field and return "Red" if it is more than a week old, and "Blue" otherwise. This expression can be used to control the Color property of a text box in a report item:

=IIF(DateDiff("d",Fields!PurchaseDate.Value, Now())>7,"Red","Blue") 

Recursive Hierarchy Group in SSRS 2008

In this article, I am going to explain how to create a Recursive Hierarchy Group in SSRS 2008 to display hierarchical levels. This is helpful when we want to display hierarchical data in a report e.g. employees in an organizational chart as shown below: 

Pre-requisites for this article:

1. SQL Server 2008 along with SSRS installed
2. Business Intelligence Development Studio (BIDS)
3. AdventureWorksDW2008R2 database. Click here to install this database from codeplex.
4. Familier with SSRS

Below are the stpes to create Recursive Hierarchy Group:
STEP 1: To begin, create new report in your existing solution. Add new Shared Data Source. If you don't know how to create a shared data source, click Creating Shared Data Source in SSRS 2008.

 

STEP 2: Create a new dataset for the report. Just right click on the data source and click Add Dataset... to create new data set as shown below:

In Dataset Properties window, enter dsMain in Name textbox and below T-SQL code in Query textbox:
SELECT [EmployeeKey],[ParentEmployeeKey
,[FirstName] + ' ' + ISNULL([MiddleName],'') + ' ' + [LastName] EmployeeName],[Title]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]

 

Finally click OK to close the window and save changes. The Report Data will look like below image:

STEP 3: Drag and drop Table control and map with dsMain dataset. Select EmployeeName in first column, Title in second column and =Level() expression in the third column. Set following properties for table control:

• Header Background color = DarkBlue
• Header Color = White
• Font = Verdana, 8pt

STEP 4:  Set Group Properties to reference Recursive Parent. Select table control --> click View and check Grouping as shown below:

Open Row Group properties, click on add button and select [EmployeeKey] in Group on box.

 

Click on Advance tab of Group Properties window and select [ParentEmployeeKey] in Recursive parent box. Click OK and save changes.

 

STEP 5: We are done with report development. Click on Preview to generate the report. You can see a report like one shown below:

Here, you can see the Employee Hierarchy but not as per standard. We need to format the text to make it better report. Do below formating for EmployeeName textbox:
1) Enter below expression in FontWeight under Font Property:
=IIF(Count(Fields!EmployeeName.Value, "Details", Recursive) > 1, "Bold", "Normal")

2)  Enter below expression in LeftIndent under Indent Property:
=CStr(2 + (Level()*20)) + "pt"

Again Preview the report, report will look like below image:

Cheers!!!