Hi!
I'm making a report over a powercube, with a dimension that have codes like:
01-01
01-02
01-03
...
01-02
02-02
03-02
...
And I need to filter the dimension using initial and final value, so, if user select 03-04 as initial value and 15-11 as final value, the query have to include:
03-04
04-04
05-04
...
13-11
14-11
15-11
I pretend to use two value prompt to select initial and final value, and then create a filter using the two parameters, but I don't know how to make this filter, or if I should use a slice instead. Can anyone give me some advise?
Thanks.
Hello qvixote,
Lets say a MUN sample in your case is of the format [Initial Part of your MUN].[01-01], then you can create two data items to capture the From and To parameters from the user and convert it to MUNs as such:
[From]:
#'[Initial part of your MUN].['
+
prompt('From','token')
+
']'#
[To]:
#'[Initial part of your MUN].['
+
prompt('To','token')
+
']'#
Then create a third data item [Members Between Selections]
INTERSECT
(
lastPeriods(1000, [To]),
lastPeriods(-1000, [From])
)
This will get you the members between the selections. You can use this SET expression as a column on your report. OR, pull it into the slicer if you only want to filter the data.
-Cognos810
Thanks a lot, Cognos810!
I didn't use yout recomendation on the first 2 data items, because the value prompts I inserted in the report are linked to the level on the dimention, so they already return a MUN and I don't need to construct the MUNs from strings.
I created the data item using intersect and lastPeriods, but when I try to use the data item as categories in a line chart, I receive the following error (in spanish, sorry):
OP-ERR-0052 No se soporta el operador de expresión 'prompt'.
In english it must be something like "'prompt' expression operator is not supported". This is the code of the data items I've created:
[DiaInicial]:
prompt ('Param_DiaInicial')
[DiaFinal]:
prompt ('Param_DiaFinal')
[DiasFiltrados]:
INTERSECT
(
lastPeriods(1000; [DiaFinal]);
lastPeriods(-1000; [DiaInicial])
)
My configuration have setted semicolon as parameters separator, so it's not an error.
Thanks.
It's not going to work like this. Prompt is not a function but a Cognos macro so it has to be embedded in hash sign (#). The prompts will return only the value chosen, not
the entire MUN so you may have to create the data items using the method suggested by cognos810.
Hello Qvixote,
If you are already returning the MUNs from the prompts, then your expressions should be this:
[DiaInicial]:
#prompt ('Param_DiaInicial','memberuniquename')#
OR
Simply,
[Your Level]->?Param_DiaInicial?
[DiaFinal]:
#prompt ('Param_DiaFinal','memberuniquename')#
OR
Simply,
[Your Level]->?Param_DiaFinal?
Thanks, cognos810! I corrected the expressions and it worked.