Sorting on column click

We see many Web applications where web page has been designed to give a rich sorting feature to end user. There uses click on any column and entire data gets sorted according to that column. Let’s design a report we can give this rich sorting feature to our end user.

Here I am assuming that you are done with your report design and you have to just introduce sort feature on various columns in your report.

Step 1. Right click on that particular column and 

select Text box Properties option. Refer Image-1

Image-1

 

Step 2. Now select Interactive Sorting option and also check the Enable sorting option on this textbox. Refer Image-2

 

Image-2

  

Step 3. You see other options available there and those are as

·        Choose what to sort:

(I) Detail Rows: if your report has only one group/row i.e. detail group/row then choose this option

(II) Groups: if your report has multiple groups and you want to sort the result specific to a group then choose this option

·        Sort by: select the available field name on which you want to perform an sort operation

·        Apply this sorting to all groups and data regions in: choose this option, if you want to sort the whole records specific to your dataset/table/table group

Step 4. You need to repeat the above steps on each column one by one on which you want to give sort feature. Once you done with this and preview your report, the columns will have icons as up-down arrows. Refer Image-3

Image-3

Download sample report

On Demand Sorting

Let’s give an option to our end user to sort the report output as per their choice. Not by report design.

To keep the sorting feature more rich and relevant, let’s go through with the following steps:

Step 1. Create a new parameter and list out all the field names under Available Values property of the parameter. Specify field names under Label as you want to show to your end user and keep the value same as in your dataset field name. Refer Image-1

Image-1

Step 2. Now open property window of your table’s group and select Sorting. Refer Image-2

Image-2

Step 3. Now put following expression under Sort by. Refer Image-3

=Fields(Parameters!p_sortby.Value).Value

Image-3

DOWNLOAD SAMPLE REPORT

Merging in SSRS

SSRS Report Merging in  Excel output Format

Designing play an important role while designing  SSRS report  to the  presentation of data  in excel to your users. This situation occurs when you have not designed your report properly and there will be column and row merging issue while presenting data to your  user in excel output format. Due to this user will not be able perform calculation, filtration and other task on the excel sheet. Text in the columns will wrapped,On the whole we can say presentation of data is not be good. 

Let’s see in example below

You have placed multiple text box , images etc in the report header and in report body you placed table ,your table column and report header  control (text box , image etc )are not aligned properly then you  will see the column and row merging issue. See in the below image column 1, 2, 3  red circled box here report header text box are not properly aligned with report body control i.e table, In red circled box 4 there is space between first and second report header text box .

.RDL Design 1

The output in excel for this type of designed report like below image

See Sale territory column of report occupying two column of excel A,B  so forth D,E column and F,G column, row 1 and 2 is overlapped row 4 and 6 is hidden due to this it is difficult to perform filtration, calculation task on the excel to user   

Now see the resolution to this situation align the report headers with report body controls properly . see image below, see boxes marked in red 1,2,3 are aligned properly with table columns, in red circled box 4 there is no space between first header control and second control so forth second header control and third header control and third header control and report body control. See .RDL design and .RDL design 2 carefully 

.RDL Design 2

Now all our header control are aligned well horizontally and vertically (column, row wise )we can see the output like below image in excel and each report column is occupying one column of excel. Rows 2, 4, 6 are perfectly visible.  

No Display Folder to Calculated Measure in SSAS under Translation

In SSAS when you create a calculated measure and assign a some folder to it, It seems good when you have assign no translation to it. The calculated measure will be under the same folder. But when you assign some translation to the calculated measure it will come out of that folder. It will appear in the root folder. 

Problem: Let’s say you have created a calculated measure in SSAS (ABC) in SSAS and assign it some translation (XYZ) in some other language.

Here you have create a calculated measure ABC with some calculation and assign associated measure group Internet Sale and display folder is Translation 

When you will browse your cube it will be displayed under translation folder 

Now assign some translation to this calculated measure.

1. Go to translation tab

2. Choose you language in which you wish to provide the name to this calculated measure.

3. Right Click on the calculated measure, it will ask you new translation

4. Choose you language provide some name (Translation in other language) like below

Save your cube and process it , Now you will see when you browse your cube in specified language the calculated measure will be out of translation folder because while providing other language name to calculated measure there is no option to provide folder name in translation Tab. In this example I have used English name of the calculated measure as ABC and Translated Measure Name XYZ in Japanese language. While browsing in Japanese language the measure name should be XYZ and it should be in Translation folder under Internet sale measure group   but it will be out of translation folder you can see in below pic.

Solution:-

Go to you cube in solution

1.       Right click on cube and view code

2. Find the corresponding <Translation> tag - look for the Foreign name of your Calculated member
3. Below the <Caption> tag place a line with <DisplayFolder> tag. The properties within Translation tag :
             <Language>1041</Language>---This is your foreign language code different for different language
             <Caption>XYX</Caption>---- Foreign name of calculated measure

                  --In our case XYZ------
             <DisplayFolder>Name your Folder</DisplayFolder> This display filder Tag we have to put

                                         -----In our case it is Translation
4. Do it for all the calculated measures

Now save the cube and  process it. your calculated measure will be under your expected folder

Please note: This is risky process be careful while doing this process there should be no alteration in other code while doing it. I would suggest it should be done by some experience guy.

Dynamically show/hide columns based on selected multi-valued parameter

Sometime, we have a long list of columns in our report that is required to show more information on report. But problems come to that point when we have to restrict only selected columns in export/print. We can achieve this by giving a multi-valued parameter to hide the selected columns on demand. Let’s start with following steps:

image-1

Step 1. Add a multiple values parameter in report.
Do not forget to check “Allow multiple values” option.
Refer image-1

Step 2. Specify list of column names

Image-2

that you want to include to hide on demand.
Refer Image-2

Step 3. Now select individual applicable column one by one
and open its Column Visibility option. Refer Image-3

Image-3

Step 4. Under each applicable column Visibility property, put a expression like following. Refer Image-4

Image-4

=Join(Parameters!p_column_name.Value,",").Contains("Employeeid")

To discuss above expression:

·        Join(Parameters!p_column_name.Value,",") will join all the selected parameter values delimited to a character (“,”) and will retrun a string

·        .Contains("Employeeid") will return true if the given input value exists in the string otherwise will retrun false.

You can DOWNLOAD A SAMPLE REPORT