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.

 

Dashboard In SSRS

Drilldown Chart in SSRS without using Sub Reports.

In SSRS as we develop drilldown reports in the same way we can develop drill down charts. Once you click on the main chart the corresponding detail level chart should display on the same report without using Sub report.

Let’s develop a drilldown chart

I have shared Dashboard.rdl with respect to this post you need to set your data source (AdventureWorksLT) Download

We have one bar chart showing total sale yearly. Once you click on the any year bar the corresponding pie chart should display with product wise sale.

Steps to create a drilldown chart.

1. Create a new report and name it dashboard with your shared data source (I am going to use sample Adventure works database)
2. Create a dataset with the following query
SELECT   Floor (SalesLT.SalesOrderDetail.LineTotal) as Sale, DATEPART (yyyy, SalesLT.Product.SellStartDate) AS Year FROM  SalesLT.Product INNER JOIN                         SalesLT.SalesOrderDetail ON SalesLT.Product.ProductID = SalesLT.SalesOrderDetail.ProductID
3. Now you need to add a bar chart to your report
4. Right click on the chart and set the chart properties

 

Now our first bar chart is ready to preview.

Our main task is to create a sub chart on clicking on the year bar(Bar Chart) corresponding pie chart should display with information.

5. Now create another data set for the second chart and name it Product.

SELECT   top 5    SalesLT.ProductCategory.Name, Floor(Sum(SalesLT.SalesOrderDetail.LineTotal)) as LineTotal FROM    SalesLT.Product INNER JOIN  SalesLT.SalesOrderDetail ON SalesLT.Product.ProductID=SalesLT.SalesOrderDetail.ProductID INNER JOIN  SalesLT.ProductCategory ON SalesLT.Product.ProductCategoryID= SalesLT.ProductCategory.ProductCategoryID WHERE        (DATEPART(yyyy, SalesLT.Product.SellStartDate) = @year)

Group by SalesLT.ProductCategory.Name

 The above query has parameter named year

6. Now drag another pie chart on the report and map it with the new dataset.

7. Right click on the chart and set its properties

8    8. Pass the parameter from main chart(bar) to pie chart

   How to pass the parameter from main chart to detail chart (pie)

    Now right click on the bar and then click on the bar chart series properties.

8.1   Go to Action tab and click on option Go to report.
8.2   Choose the same report i.e. Dashboard
8.3   Click on Add button and add parameters the report

9. Set some default parameter to your report so when you preview your report it should not ask you for        parameter it should render with    some set of parameter.
10.Go to the year parameter and right click on it and set default value to it as -1

 

11. Now we need to set some visibility to second chart so when you run your report first time only bar chart  should display on clicking     on the any bar of the first chart than only second chart should display.
12. Now you right click on the Pie chart and then click on the chart properties.
13. Then you click on visibility.
14. On show and hide based expression place this 

=IIF(Parameters!year.Value>0,false,true) 

Now you report is ready to preview
For the look and feel of the charts format your charts as per your requirements.
Preview your report  first you will see this chart in pic. Below

 Click on any year bar  you will have your pie chart along with bar chart

Error,Object reference not set to an instance of an object

A message that indicates that the object reference is not set to an instance of the object is typically caused by one or more of the following reasons:

•         There are soft page breaks within empty lists in the report.

•         There are hidden groups within the report and you tried to export it to PDF.

·         The report has a link to a subreport that is not published on the report server and you tried to export it to CSV.

·         There are text boxes with a width and/or height of zero in the report.

·         A text box is either hidden or the NoRows property is set to true.

·         A text box spans table cells where some cells have a collection of null values.

·         The report has static columns and/or rows and, in the Visibility properties, Hidden is set to true.

·         The report has static columns and/or rows and there are grand totals in the report.

·         The report has static column headings and, in the Visibility properties, Hidden is set to true.

·         The matrix report has multiple columns and rows and, in the Visibility properties, Hidden is set to true and you tried to export to PDF or TIFF.

For More detil Visit this  rrRenderingError

 

SSIS

Core component of SSIS is Packages

• Packages
• Control Flow
• Data Flow
• Connection Managers
• Package Configurations
• Data Sources

Package

Package is a collection of task snapped together to execute in an orderly fashion to meet the business requirements. Precedence constraints are used to connect the task together and manage the order in which the task will execute. The package is compiled into a .DTSX file that is actually an XML structured file with collection of properties.

Control Flow

A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, we use precedence constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow

Data flow

consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package. Data Sources, Transformations, and Data Destinations are the three important categories in the Data Flow.

Connection Managers

Connection managers is gateway to connecting with different data sources, such as relational databases, Analysis Services databases, and files in CSV and XML formats. Connection managers contain the data source connection string and other related properties. At package execution time the connection managers manage the physical connectivity to data sources and destinations. Multiple tasks can share the same connection manager.

Package Configurations

After development your package and before deploying the package in production environment from UAT you need to perform certain package configurations as per production Server .This relates to storing certain aspects of the packages like variable values, database connections, and flat file/ftp connection information. There are different ways to perform configurations

Data Sources

A data source is a connection reference that you create outside a package. A data source represents a simple connection to a data store, which includes all tables and views in the data store