Format DateTime into Quarter

Format DateTime into Quarter

Most of the time, we format DateTime value in various formats like day, month year etc. If we have to format a DateTime value into Quarter, we can use following expression syntax

=Format(DatePart(DateInterval.Quarter, <Date Value>))

For instance,

=Format(DatePart(DateInterval.Quarter, cdate("11/26/2012"))) will result 4.

AND

="Q"+Format(DatePart(DateInterval.Quarter, cdate("11/26/2012")))+","+cdate("11/26/2012").Year().ToString() will result Q4,2012

AND in following expression, we are going backward to 4 quarters

="Q"+Format(DatePart(DateInterval.Quarter, DateAdd(DateInterval.Quarter,-4,cdate("11/26/2012"))))+","+DateAdd(DateInterval.Quarter,-4,cdate("11/26/2012")).Year().ToString()

Will result Q4,2011

Calculate Column difference in Matrix

Sometimes we fall into a situation where we need to calculate columns difference in Matrix. Something likes image1

Image1

Let’s go through with following steps to achieve this output in Matrix. I believe that you already have created required column and row group in your matrix. The following steps are additional work to achieve the column difference in the matrix.

Step 1. Create a calculated column in the dataset named as ColumnDiff and assign this expression under Field Source

="Diff : "+Format(Cdate(Fields!OrderDate.Value),"MM-yyyy")+" - "+DateAdd(DateInterval.Month,-1,Cdate(Fields!OrderDate.Value)).ToString("MM-yyyy")

Image2

Refer image2 regarding this step.

Step 2. Now select the matrix and create a new Parent Group under your Column Groups

Image3

Refer image3 regarding this step.

Choose [ColumnDiff] field as a Group by expression and click ok button.

Image4

Refer image4 regarding this step.

Now click on newly created column group and give an approprite name as grp_ColumnDiff. Because we will use this group name in a expression in next step.

Image5

Refer image5 regarding this step.

Step 3.  Now use following expression in your child group row & [ColumnDiff] column cell.

=SUM(Fields!TotalAmount.Value)-Previous(SUM(Fields!TotalAmount.Value),"grp_ColumnDiff")

Refer image6 regarding this step. 

Image6

Let’s discuss the above expression.

SUM(Fields!TotalAmount.Value) will give the total value for the current column group and Previous(SUM(Fields!TotalAmount.Value),"grp_ColumnDiff") will give the total value of the previous column group corrosponding to grp_ColumnDiff column group

Step 4. Now set the following column visibilty expression for the last column that says [ColumnDiff]

=CBOOL(Cdate(Fields!OrderDate.Value).Month mod 2)

Refer image7 regarding this step.

Image7

This expression will return True for ODD month number and False for even month number.

Download  sample report

You need to only change the report connection to appropriate connection string to execute this report at your own enviornment. This sample is designed in SSRS2008R2 version. So lower version of SSRS might give some design error.

ProperCase in SSRS

Probably, we all have used UpperCase/LowerCase text formatting by using UCase/LCase method in SSRS. But if we have to use ProperCase then SSRS does not give any direct method for this.

To use ProperCase in SSRS, we need to use following expression syntax:

=Microsoft.VisualBasic.StrConv(<Text Expression>,Microsoft.VisualBasic.VbStrConv.ProperCase)

For example, following expression

=Microsoft.VisualBasic.StrConv("this is a propercase example.",Microsoft.VisualBasic.VbStrConv.ProperCase)

Will give the output as

This Is A Propercase Example.

Read wild card character as Charater

Requirement :- Let's have a sample data as below and you need to search the data started with %, _ in your table
Id    Item

1    _Computer
2     %Computer
3    %Desk
4   %pen
5  _pen
Create table #test (ID int , Item varchar(50))
insert into #test values (1,'_Computer')
insert into #test values (2,'%Computer')
insert into #test values (3,'%Desk')
insert into #test values (4,'%Pen')
insert into #test values (5,'_Pen')
Solution :- Just put your wild card character in open and closed square brackets
Example :-
Select * from #test where Item like '[%]%'

Rank and Dense Rank in MDX

WITH

MEMBER [Measures].[Rank]ASRank(
[Product].[Model Name].CurrentMember,[Product].[Model Name].[Model Name],
[Measures].[ORDER Count]
)

SET [Sorted Models]AS
ORDER(
[Product].[Model Name].[Model Name]
,[Measures].[ORDER Count]
,BDESC)MEMBER [Measures].[Previous Model INDEX]AS(Rank(
[Product].[Model Name].CurrentMember,[Sorted Models]
)
-
2
)

MEMBER [Measures].[Dense Rank]AS
CaseWhen [Measures].[Rank] = 1
Then 1
Else(
[Sorted Models].Item([Measures].[Previous Model INDEX]),[Measures].[Dense Rank]
)
+

Iif(
(
[Sorted Models].Item([Measures].[Previous Model INDEX]),[Measures].[ORDER Count]
)
=
[Measures].[ORDER Count]
,0
,1
)
EndSELECT{
[Measures].[Rank],
[Measures].[Dense Rank]
} ON 0,{
[Sorted Models]
}
ON 1FROM [Adventure Works]

How to remove all numeric/non-numeric characters from the string in SSRS

Let’s say we have a string like 1;B4Y;dce5;6fgh;This

And we are expecting an output like this BY;dce;fgh;This

To remove all the numeric characters from a string, we need to use regular expression like following:

=System.Text.RegularExpressions.Regex.Replace("1;B4Y;dce5;6fgh;This", "[0-9]", "").Trim(";")

This expression will give the output as BY;dce;fgh;This

And vise versa, if we want only numbers then we need to use regular expression like following:

=System.Text.RegularExpressions.Regex.Replace("1;B4Y;dce5;6fgh;This", "[^0-9]", "").Trim(";")

This expression will give the output as 1456

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.