Dynamic sorting of 3 columns of the list

[JuliaM]

Hi!
I have a list report with 3 numeric columns (sales figures for customers) and 3 columns with calculations based on sales figures. I added 3 prompts for each calculated column header with static selection (0=Do not sort, 1=Sort ascending, -1=Sort Descending).

The problem I have is - if one of the prompts is used, the other two need to be 0=Do not sort, and I can't figure out how to set them automatically.

Thank you for helping!

[Francis aka khayman]

is your problem in the sorting? or the setting of values in the prompt?

[BigChris]

I'm not sure how you've set your report up, but the sort of approach I've used before is to have a single prompt, called pSortField,  with 3 static choices, 1 for each column that you're going to sort on. Then in your query I've created a calculated field called SortField, which looks something like:

Code Select Expand

CASE ?pSortField?
  when 'Column1' then [Query].[Column1]
  when 'Column2' then [Query].[Column2]
  when 'Column3' then [Query].[Column3]
Else NULL
END

Then just sort your report by SortField.

[JuliaM]

Hi!
Thank you for answering.
I initially set the first prompt to 1=Sort Ascending, the second to 0=Do not sort.
The first one works fine. I'd like to set the first one to 0=Do not sort if the second one is used.
Sorting by the second one is not working properly either, I think.

If I use one prompt for the report (I actually need three columns sorted), will it sort ascending/descending automatically?

[BigChris]

Looking at your data it looks like you're sorting on numeric data, which makes things much simpler. If you follow what I suggested earlier, you would then need a second prompt to allow the user to choose ascending or descending. I'd call that second prompt SortDirection. in your prompt page, or at the top of your report if that's where you're going to put the options, you'd have the first prompt with three static choices, and a second prompt with two static choices. Your SortField would then look something like:

Code Select Expand

CASE ?pSortField?
  when 'Column1' then [Query].[Column1]
  when 'Column2' then [Query].[Column2]
  when 'Column3' then [Query].[Column3]
Else NULL
END
*
CASE ?pSortDirection?
  when 'Ascending' then 1
  when 'Descending' then -1
  Else NULL
END


[JuliaM]

I would need 2 sort fields then? In my query?

[BigChris]

No, just one field, but two prompts. The code that case for one field - there's a multiplication in the middle of it.