Dynamic Sorting based on prompt values

[SGD]

Hi All,

I need to apply dynamic sorting on a list report whenever User selects any of value from 2 different prompts.

On report prompt page User wants two value prompts i.e. 'Primary Sorting' & 'Secondary Sorting' to select soriting orders while running the report. Primary Sorting value prompt will have 3 static values (actually column names) which are 'A', 'B' & 'C' respectively. Secondary Sorting value prompt will have 3 static values (actually column names) which are 'X', 'Y'' & 'Z'.

Once User selects Primary Sorting value 'A' and Secondary Sorting value 'X' then list report should sorted based on list column 'A' & 'X' and so on.

Could you please suggest how should I achieve this functionality? Do I need to create multiple list and hide it based on different conditions or is there any optimum way to implement it?

Thanks in advance. Your help is highly appreciated.

[MeenaTikare]

Hi,

If my understanding is correct, you can try the below

Add a calculated Data item, say 'Primary_Sort' which should fetch the value from either column A, B or C depending on the prompt value.
The calculation should look something like  if (?Primary_sort_prompt? ='A' ) then ([A]) else if (?Primary_sort_prompt? = 'B') then () else ([c])

Similarly add one more calculated data item, say 'Secondary_Sort' and get the value of this column depending on the second prompt value.

Now you just have to sort your list based on 'Primary_Sort' and 'Secondary_Sort' columns.

Hope this solves yours issue.

[SGD]

Hi Meena,

Thanks for your reply however that is not my requirement.

Actually 'A', 'B', 'C', 'X', 'Y' and 'Z' are list report columns. Now User has to decide on which list column he wants primary and secondary sorting order.

due to 6 different prompt values (column names) there will be 6 different conditional scenarios.

I personally think that having 6 lists with different hardcoded sorting order and then conditionally hide those based on primary-secondary sorting combination is not a good idea. so just wanted to check in any other way I can acheive this.

[pricter]

Create two query items as the below ones

#prompt('first','token','[A]')#
#prompt('second','token','[X]')#

In the properties of the list define the list should be order by these two data items
Create two prompts with static values
e.x
parameter : first
Use Value Display Value
[A]              A
....              ....
parameter : second
Use Value Display Value
[X]              X
....              ....


I hope this is what r you looking

[SGD]

Hi Pricter,

I am afraid to say that I am still not convince with your suggestion. I have tried implementing as suggested above but it did not work. :(

Could you please send sample report XML for reference?

[SGD]

Any clues?

[pricter]

Hi Pricter,

I am afraid to say that I am still not convince with your suggestion. I have tried implementing as suggested above but it did not work. :(

Could you please send sample report XML for reference?

I can not understand what changed from yesterday and you were conviced by proposed solution?

Why did you modified your post and not created a new one?

At which point the proposed  solution does not work?

[SGD]

Hi pricter,

I have a list with 5 columns as below:

Location ID
Location Name
Location Short Desc
Locatuon Long Desc
System Name

User wants to see primary and secondary sorting order on above list columns based on selected values in value prompt. Hence I have created 2 value prompts and added all column names as static values in each value prompt.

I hope till this point its clear to you. I have implemented as proposed solution however it does not work as expected?

Please suggest on the same otherwise please send me sample report xml file for my understanding and reference.

Thanks in advance.

[nmcdermaid]

SGD, I can tell you that this particaulr solution works as I have used it on many occassions.

In what way does it not work as expected? Why don't you start by troubleshooting.

Start by just trying to implement one order column. Put the dynamic column on your report and ensure that the value of the column changes as you make different selections in your prompt. Yes/No?

Now order that column. Does it order as expected. Yes/No?


If you can pinpoint your issue with some constructive troubleshooting it's easier to assist.