Efficient way of handling Excel Exceeding 65556 rows limit issue in SSRS 2008R2

Let’s say we have more than 65556 rows in our report output. If we export it in excel, we will experience an export error that says “Exceeding 65556 rows limit”. We have a solution to overcome from this issue is to restrict the number of records on per page that will not exceed more than 65556 rows including header rows. We need to use this expression on your table’s parent group.

=Int((RowNumber(Nothing)-1)/65500).

For more details about to restrict the number of records on per page, take a look into Restrict Number of Records on Per Page.

It will solve the excel limit issue but opens another issue. The issue you will face is this change will impact all the other formats like PDF, CSV etc.

Below we will see how to implement Restrict Number of Records on Per Page for Excel only that will not impact other export formats.

Let’s follow following steps.

Step 1: Design your table as per your requirement without implementing any Restrict Number of Records on Per Page logic.

Step 2: Make a copy of the existing table and paste it on the report. Let’s say the name of this table is TableForExcelOnly and original table name is TableNotForExcel

Step 3: Select TableNotForExcel and open property window by pressing F4 key

            Now set this visibility expression under Hidden property =IIF(Globals!RenderFormat.Name="EXCEL",TRUE,FALSE)

Step 4: Select TableForExcelOnly and open property window by pressing F4 key

 Now set this visibility expression under Hidden property

=IIF(Globals!RenderFormat.Name="EXCEL",FALSE,TRUE)

And DataElementOutput property as NoOutPut

Step 5: Now implement the Restrict Number of Records on Per Page logic on the TableForExcelOnly as discussed in starting of this post.

Now you are done. Preview your report and check report output.

Download Sample Report

To execute this sample report, you need to only change the datasource of the report.

Let’s discuss few of the properties we have used above.

RenderFormat.Name

This is a new method in SSRS 2008R2 that returns the report rendered format.

DataElementOutput

            This property Indicates whether the item appears in output rendered by the XML rendering extension. It has following possible values:

            Output :  Indicates the item appears in the output.

            NoOutput : Indicates the item should not appear in the output.

            Auto : If the item is a text box with a constant value, such as a label, the item does not appear (NoOutput). If the item is a rectangle, the output is the same as it is for ContentsOnly. For all other report items, the item appears in the output (Output).

You can see some important usage of the DataElementOutput property under

Remove non required columns and give appropriate column name in CSV
And
Export Matrix data into Tabular format in CSV

Chart - Common Properties

We have lots of properties of the chart. Let’s discuss few of them that might help to solve the visual appearance problems.

Select the Axis and press F4 key. In the opened property window, you will notice the below mentioned properties.

·         VariableAutoInterval

If VariableAutoInterval=True is set then Chart Axis Interval will be calculated automatically based on available size.

If VariableAutoInterval=False is set then Chart Axis Interval will be calculated based only on the data range.

Chart1 and Chart2 will illustrate this property in the attached sample Report.

·         Margin

If you want to start your chart series immediately without any margin then you need set the Margin=False.

Chart3 and Chart4 will illustrate this property in the attached sample Report. In chart4, Minimum value of the series is touching Y-axis whereas it is not in Chart3.

We can see the below properties under MajorGridLines, MinorGridLines, StripLines, MajorTickMarks etc. You can Refer Chart5’s MajorGridLines properties in attached sample regarding these.

·         Interval

Setting this property states that in which interval you want to repeat the line/Mark.

·         IntervalOffSet

Setting this property states the first occurrence of the line/Mark on the Axis.

·         IntervalOffSetType

Setting this property states the unit of the first occurrence of the line/Mark on the Axis. If your Axis data type is not matching with any of the available values given under this, select the IntervalOffSetType as AUTO.

·         IntervalType

Setting this property states the unit of the interval that will be considered by the Interval property.

Download Sample Report

You need to only change the report’s datasource.  This RDL has been designed in SSRS 2008R2

Print matrix from right to Left

Most of the time, we print the Matrix from left to right (LTR). What if we have to print it from right to left (RTL), like image1? Let’s do this simple change to achieve this.



Image1

1.    Select your Matrix control and open the property window by pressing F4 key

2.    You will see a property called LayoutDirection. Set this property to RTL

Now execute your report and check the output.

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