There are many times when users want dynamic lists. They may want to be able to control which columns are visible at runtime. And while this is all functionality provided by Query Studio, the users may demand a more structured environment.

The most basic solution is to use token prompts. Have a list which contains as many columns as you have fields, an equal amount of checkbox prompts with the use as the field reference, an equal amount of render variables, and you have a report in which the user can check every field he wants to see.

For example:
The column: #promptmany(‘renderYear’,’token’,sq(‘Not Rendered’))#
Value Prompt: optional, multiselect, Static Choice use=[Business Layer].[Time].[Year], display=Year
Render Variable: paramValue(‘renderYear’) is not null

Personally I’m not a big fan of that solution. When using one prompt, column, and variable per field, building the report can be a nightmare. Dozens, if not hundreds, of fields can make the report simply too big to open. The user would also be unable to control the order of the fields.

Instead, let’s build a prompt in which the user can select the fields he wants to see, and choose their order.

This consists of three separate prompts. The prompt on the left contains the static choices for the columns. The right is an empty value prompt. There is also a hidden prompt that contains the currently select choices.

When the report first opens, the user can select the desired. Pressing the Add Fields button will move the option from one side to the other. The vertical move buttons behave as expected. When the finish button is selected, the JS will loop through the selected fields prompt, copying the values to the hidden prompt. This will allow us to automatically reselect the same fields after the page has been refreshed. The JS will then loop through again, appending the use value with the row number. So the first value selected might look like [Business Layer].[Time].[Year]|0|, and the second as [Business Layer].[Retailers].[Country]|1|.

By appending the Row ID, we are salting the fields with a value we can grep inside the query. Consider the following macro:
#
substitute('\|0\|'+sq('All'),'',
join('',
grep('\|0\|',
split(';',promptmany('SelectedFields_Attributes','token',sq('All')))
)
)+sq('All'))
#


This retrieves all of the selected fields, splitting them into an array. It will grep (or search) for any array elements containing “|0|”. Once it finds the value, it converts it back into a fragment with join. A render variable can be set on the column with the expression, ” ParamValue(‘SelectedFields_Attributes’) contains ‘|0|'”.

By doing this, we can arbitrarily limit the number of fields visible in the report, while still giving the flexibility to select whichever fields the user wants. The dynamic nature of the solution will ensure that the size of the report is kept to a minimum, and updating the report will be a simple matter of adding the desired field to the list.

The attached report is using 10.2.1. It’s actually not using the Prompt API, so this technique should work in any version since 8.4.