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.