Select last element from a multi-select prompt

[qvixote]

Hi!

Y have a prompt to select year(s) and filter the querys in the report. The filter in each query looks like that:

[DBV_01].[Periodo].[Periodo].[Año] in (?Param_Periodo?)

It filters perfectly by all the years the user selected. But in one of the queries, I want to filter only by the last year that was selected in the prompt, it means if the user select 2007, 2008 and 2009, that query must be filtered only by 2009.

Does anyone know how can I get it done?

Thanks!

[navissar]

Hi,
This is one of these requirements that make me raise an eyebrow. It's not very good UX. Nonetheless:
Filter your query by year parameter. Then add another filter where [year]=maximum([year]). That should do it.

[CognosPaul]

Nimrod, I can think of a few cases where you might want to do this. For example you can detect if a user is selecting more than one year, and have two graphs, the first showing the average for the previous years, and another showing the actual for the last selected. Of course I'm using a cube there, which makes that type of thing much easier.

Doing a maximum on the data set is probably not the best way to go. That means the query would still have to process all of the selected years.

Try something like this:
[Year] = #
substitute('Last','',join('',
grep('Last',split(';',promptmany('Years','integer','','','','Last')))))
#

With the 'Last' in the postText parameter, the promptmany returns a string that looks like this: 2012;2013;2014Last
We can split that into an array, and grep that element. Join it back into a string and substitute out Last, and we're left with the last element from the original prompt.


Actually, now that I've reread the question, it looks like this is a dimensional query. Is this DMR or a real cube?

Instead of doing [Cube].[Dim].[Hier].[Level] in (set), you need to either slice your query by that set or, if you're showing the years, stick the set directly into the data item.

tail(set(#promptmany('Param_Periodo','mun')#))

Will return the last member selected in the value prompt.

[navissar]

Hey Paul,
Great stuff. One clarification: I wasn't saying this isn't at times necessary; just that it isn't good UX. There's nothing to lead the user to the understanding this is what his selections will be used for.

[qvixote]

Hi!

Thanks for your answers. My report contain some crosstables that show data for years the user select, and in addition it shows a map that is colored using a calculation of a ratio. The client wants the map show only the last of the selected years.

Adding another filter like [year]=maximum([year]) didn't work for me.

Yes, this is a dimentional query. I'm working with a cube made with Transformer. I used tail(set(#promptmany('Param_Periodo','mun')#)) in a slicer and it worked fine, but now it generated an additional problem related with drill through.

Now when I call this report from another using drill through, passing a prompt value to 'Param_Periodo', I see this error message (some lines of it):

QE-DEF-0261 QFWP - Texto de análisis: tail(set())RSV-SRV-0042 Rastreo:RSReportService.cpp(726): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRun_RequestRSASyncExecutionThread.cpp(828): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(277): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRun_RequestRSASyncExecutionThread.cpp(884): QFException

(Yes, it's in spanish)

I can note this: "tail(set())" in the message. The functions appears empty. I don't know why it's happening.

Any idea?

[CognosPaul]

What is the value being passed? Is it also from this cube?

[navissar]

You're probably not passing MUN in the drill.