Recently I was presented with an interesting problem. The developer made a chart report which allowed the user to define the x-axis through a radio button. In order to do this the developer dragged all the fields he needed into the query, and used the following expression to change the axis:
This means if Month was selected in the radio, the x-Axis would be [Month].
Next he set up a drill through to a list report. The user would click on one of the bars, and it would filter the detail report based on the category selected, and the bar clicked. The optional filter is:
#sb(prompt('Category','token'))# = #prompt('Value','string')#
Resolving to [Month] = ‘Jan/2010’ or maybe [Product] = ‘Glowsticks’
I’d welcome ideas on different ways to accomplish this, as the solution I’m about to present is difficult to understand and maintain.
First I changed all of the prompts on the page to match the values the categories were passing. So the filter [Country] = ?p_dest_country? was changed to [Country] = ?Country? and [Month] = ?p_Month? was changed to [Month]=?Month?
The categories filter was changed to:
#sb(prompt(‘Category’,’token’,’1′))# in (#promptmany(prompt(‘Category’,’token’),’string’,sq(prompt(‘Value’,’string’,’1′)))#)
Assuming the user drilled on Country = UK, this will resolve to:
[Country] in (#prompt(‘Country’,’string’,sq(‘UK’))#)
Since the Country parameter in the report is null, it will default to UK, ultimately resolving to:
[Country] in (‘UK’)
Since the drilled value is only in the default parameter of the macro, selecting any value from the prompt will always take precedence.
Please feel free to leave a comment if you can think of another way of handling this.