Helo. Today is my another fight day with OLAP raport with optional parameter. I have problem with MDX query. I wrote it like this:
select
NON EMPTY {{[Measures].[VALUE]}} ON COLUMNS,
NON EMPTY {
IIF(ISEMPTY([CUSTOMER].[${param}]) //CHECKING IF PARAMETER IS EMPTY
,{[CUSTOMER].[COUNTRY].Members},
{[CUSTOMER].[${param}]}
)
}ON ROWS
from [TRANSACTIONS]
${param}
is my optional parameter for [CUSTOMER].[COUNTRY]
. I unchecked "required" check button for my parameter, so OLAP should have all [VALUE]
after executing it without parameter. And there is a problem, because after launching my OLAP raport parameter probably wants to be filled with something. It gives me an error.
Profile attribute 'param' not existing.
But I dont want to fill it with profile attribute. I have created list of values, and analytical driver for my parameter, which I use to pass possible values to my list box string parameter - ${param}
.
Is there possibility to have OLAP report with optional parameter? Any BI master here? I would be greatfull for any help.
Update: I have done something like this, I think this syntax is right, (I was checking SpagoBI examples)
WITH MEMBER [CUSTOMER].[SELECTED] AS ' Parameter("param") ' , SOLVE_ORDER = 2
MEMBER [CUSTOMER].[LEN] AS ' LEN(Parameter("param")) ', SOLVE_ORDER = 1
select
NON EMPTY {{[Measures].[VALUE]}} ON COLUMNS,
NON EMPTY {
IIF([CUSTOMER].[LEN]=0
,{[CUSTOMER].[COUNTRY].Members},
{[CUSTOMER].[CUSTOMER].[SELECTED]}
)
}ON ROWS
from [TRANSACTIONS]
But now I have same error for both possibilities (set/unset) parameter
javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: com.tonbeller.jpivot.olap.model.OlapException: 1
Any ideas? Thanks :)