dynamic sorting and grouping in pixel perfect report or visualizer

tdouglass

Hi,

Has anyone ever created a report in Birst where the sorting and the totaling is dynamically defined based off prompts of the report?  For example, sometimes (depending on who is running the report) someone might want to group by Transaction Type and the Product Group (with our without item) and others might want to group by Plant and Transaction Type.  It is probably similar to a pivot, but would a user have to go in and create/modify the pivot every time, versus running a report and entering parameters?

Thoughts?

Tina

unknown

We've built columns that accept parameters for sorting by different values, or grouping by different values in Visualizer.

The easiest way we've found is to create an expression column. In the column you'd have a GETPROMPTVALUE that would look to the parameter being set. We used an IIF to return an Attribute/Measure based on what was selected in the parameter. We then wrapped the entire thing in an <EVAL! !> so that it evaluates the prompt and returns the result, prior to running the report query.

We then add those expression columns to break by, or sort as needed.

It looks something like this:

---

<EVAL!
IIF(
GETPROMPTVALUE('PROMPT_01','') = 'STRING_VALUE01'
,'[Dimension01.Attribute01]'
,IIF(
GETPROMPTVALUE('PROMPT_01','') = 'STRING_VALUE02'
,'[Dimension02.Attribute07]'
,IIF(
GETPROMPTVALUE('PROMPT_01','') = 'STRING_VALUE03'
,'[Dimension02.Attribute05]'
,'[Dimension03.Attribute03]'

)

)

)
!>

---

Notes:

Single quotes are important!

1. An EVAL (Macro) is executed when the query is constructed and not after the result set. If you enter [Dimension.Attribute] without single quotes it will attempt to insert the result of that value into the query as a column, which at this point should be NULL since it hasn't evaluated what [Dimension.Attribute] has for a value. This typically results in NULL (Is Missing) being returned. '[Dimension.Attribute]' or '[Measure]' returns and inserts that value into the query. This means it will run the query with that Attribute/Measure being passed properly, similar to if you add an expression to the report that simply has [Dimension.Attribute] or [Measure].
   1. Ultimately you're declaring what you want to return in that column with the expression, after it has evaluated the expression, but before it actually runs the query for the result set.
2. The last entry is the "default" if there is no match found. I've found that easier than defining a default value every time I reference GETPROMPTVALUE in this use case.
3. I believe Nested IIFs still have a 10 maximum limit, so that's something to keep in mind if you have more options for grouping/sorting. I've used this to group and sort on multiple different parameters. Typically choosing to group the report by one, and sort by one or two others at the same time.
4. GETPROMPTVALUE always returns values as VARCHAR.
5. I haven't used this method in Designer yet, but presumably it would be similar with grouping.

This may not be exactly what you're looking for, but I hope it helps put you on the right path to a solution.