Alternate Column/Row color in Matrix

Most of the time, we use alternate row color on table control to make it more easy readable for the end user. But we get stuck and make it tricky if we have to use alternate ROW/COLUMN color in MATRIX. Let’s see how we can achieve

·  Alternate Column color in Matrix

·   Alternate Row color in Matrix

·  Alternate Row color in Table

If you want, you can download a sample report from here before moving down. Because I have used fields/group names in below steps explanation and it will help you to understand the steps quickly.

Alternate Column color in Matrix

Image1

To achieve this, we need to follow following steps. For this section, refer Matrix_Alternate_Column_Color control in the sample report.



1.     Add a fake parent column group in your matrix

     under Column Groups section and give any value to

     Group expression (refer Image1 regarding this step).

2.     Select cell/textbox of the matrix column group on which you want to set the alternate background color and open the property window by pressing F4 key.

3.     Under the BackgroundColor property, use this expression (change the color name as per your need) 

=IIF(RunningValue( Fields!OrderDate_MonthYear.Value, countDistinct, "FakeParentColumnGroupForRowNumber" ) MOD 2, "Maroon", "DarkBlue")

 

Here you need to change the Fields!OrderDate_MonthYear.Value with your filed name that you are using in the column group expression as a topmost subsequent child group.

In the sample, I am using Fields!OrderDate_MonthYear.Value as a field because my ChildColumnGroup expression is on this field.

Now you are done with this section. Preview your report and check it out.

Alternate Row color in Matrix

To achieve this, we need to follow following steps. For this section, refer Matrix_Alternate_Row_Color control in the sample report.

1.     Right click on your left most column of the matrix and insert/add a new column by choosing option Inside Group – Left. You can say this column as a fake column.

2.     Now give an appropriate textbox name to this newly added fake column’s cell/textbox that appears in row group. I am using txtRowNumber as textbox name in the sample report and put this expression under that textbox

=RunningValue(Fields!Product_Id.Value,countDistinct,Nothing)

Here you need to change the Fields!Product_ID.Value with your filed name that you are using in the Row Group expression. In sample, my Row Group expression is on Fields!Product_ID.Value

3.     Now select cell(s)/textbox(s) of the matrix row on which you want to set the alternate background color and open the property window by pressing F4 key.

4.     Under the BackgroundColor property, use this expression (change the color name as per your need) =IIF(VAL(ReportItems!txtRowNumber.Value) MOD 2,"Teal","White")

5.     Since we are using first column as a fake column, we can hide it in the output. So, let’s set the cell/textbox visibility of this column as false and also you can minimize the width of this column as much as you want.

Now you are done with this section. Preview your report and check it out.

Alternate Row color in Table

To achieve this, we need to follow following steps. For this section, refer Table_Alternate_Row_Color control in the sample report.

1.     Select cell/textbox of the table row on which you want to set the alternate background color and open the property window by pressing F4 key.

2.     Under the BackgroundColor property, use this expression (change the color name as per your need) =IIF(RowNumber(Nothing) mod 2,"Silver","Transparent")

Here in the expression, I am using Nothing for scope name. Because I am using RowNumber() method on detail level group. If you are using this expression on any Row Group, then you need to specify the name of that group as a scope name inside the RowNumber(<Scope Name>) method.

Download sample report

You need to only change the datasource of the sample report.

Lookup Function in SSRS

Sometime during the report generation process we come in a situation where the report has multiple dataset and we need to combine the some data fields from two or more dataset in single data region  

Lookup functions allow you to combine data from two datasets in a single data region in report. There are three lookup functions available:

Note: I have attached LookupReport.rdl with respect to this post  Download

·         Lookup

·         Lookupset

·         MultiLookup

Lookup

The lookup function compares a value in the current scope to a value in a destination dataset and returns a single value from the destination dataset if a match is found.  The Lookup function is used when there is a 1:1 relationship

Syntax

LookupSet(source_expression, Destination_expression, Result_expression, Dataset) Source_expression

(Variant) An expression that is evaluated in the current scope and that specifies the name or key to look up.

Destination_expression

(Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on.

Result_expression

(Variant) An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve

Dataset

A constant that specifies the name of a dataset in the report.

We have two dataset dataset1 and dataset2.

In this example  lookup function will look dataset1’s stateid in to datset2 stateid and will fetch dataset2 State Name into Resultant column

 

=Lookup(Fields!StateDataset1_ID.Value, Fields!StateDataset2_ID.Value,Fields!State_Name.Value,"DataSet2")

Finally your report output is as in pic. below

Note

1. If multiple matches are found, the value from the first matching row will be returned.
2. We cannot use any aggregate functions in the result_expression.

LookUpSet
In the Lookup () we have seen It is used to fetch the first matching value from the other DataSet. Now, if we want all the matching values from the other DataSet. There is another function  LookupSet.
LookUpSet()
The LookupSet function compares a value in the current scope to a value in a destination dataset and returns a list of values from the destination dataset based on matches found. 
Note: I have attached LookupsetReport.rdl with respect to this post Download
Syntax
LookupSet(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 

Dataset – The Dataset in which we want to look the values into.

Returns – A VariantArray, or Nothing if there is no match.

Note
         1. if multiple matches are found, all the values from the matching rows will be returned. 
         2. we cannot use any aggregate functions in the result_expression

In this example  lookupSet() function will look dataset1’s stateid into datset2 stateid and will fetch dataset2 city name in one column in comma separated value

=Join(LookupSet(Fields!State_ID.Value, Fields!State_ID.Value,Fields!city.Value,"DataSet2"),",")

Expected Result

Here we have used two function

1         LookupSet() – To get an Variant array of the matching values

2         JOIN () – To join all the elements of the array as a comma separated string

MultiLookup
The MultiLookup function compares a list of values in the current scope to the values in the destination dataset and returns a list of values from the destination dataset based on matches found.  MultiLookup is equivalent to calling the Lookup function for a set of key values and is used when there is a 1:1 relationship.
Note: I have attached MultiLookupReport.rdl with respect to this post Download
Syntax
Multilookup(source_expression, destination_expression, result_expression, dataset)
source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound. The only difference from the previous lookup functions is that, here this is a VariantArray.
destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 
result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 
Note, If multiple matches are found, the value from the first matching row will be returned for all the values in the source expression. And we cannot use any aggregate functions in the result_expression.
Dataset – The Dataset in which we want to look the values into.
Returns – A VariantArray, or Nothing if there is no match.

In this example we are the Multiplelook up will compare the list of values of dataset1 city value with city_id value of Dataset2 and will fetch the corresponding city name in the required column

  Join(MultiLookup(Split((Fields!City.Value),","),Fields!City_ID.Value,Fields!city.Value,"DataSet2"),",")

we have used 3 functions -

      Split() – To convert the comma separated City value into a value array.

        Multilookup() – To find the Name of City value for the matching  City_ID.

        Join() – Prepare the comma separated string for the names returned by the Multilookup() as  array.