SpagoBI OLAP report passing optional parameter

Question

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 :)

what tests have you done? Can you pass a parameter into any mdx script? I suggest setting up a very simple script (like I included in a previous post!) to try to figure out if your client is seeing a value for ${param} ? — whytheq, Aug 19 '16 at 10:29
Possible duplicate of [OLAP report with optional parameter](http://stackoverflow.com/questions/38970610/olap-report-with-optional-parameter) — whytheq, Aug 19 '16 at 10:30
When I choose value for my parameter from list box it executes fine, when I manualy put the parameter value in my MDX it executes as well. But when parameter is empty my OLAP don't know what is `${param}` in MDX query. I can pass parameter to my mdx. If I check that parameter is required OLAP works fine, but I need optional parameter. When its unchecked, report should contain all data. — LucasPG, Aug 19 '16 at 10:36

score 1 · Accepted Answer · edited May 23 '17 at 12:01

This question is a direct copy oF your previous posts:

https://stackoverflow.com/questions/38970610/olap-report-with-optional-parameter

Is ${param} is a string?

If it is a string then the following should function:

WITH MEMBER [Measures].[x] AS ${param}
SELECT
NON EMPTY {[Measures].[x]} ON COLUMNS
FROM [TRANSACTIONS];

Does it function?

If it does not function then the question is not really mdx related - as for some reason your syntax or the way the parameter is moving to the client is wrong.

note

The above is the equivalent of this super simple script:

WITH 
  MEMBER [Measures].[x] AS "hello world" 
SELECT 
  NON EMPTY 
    {[Measures].[x]} ON 0
FROM [Adventure Works];

Do you have AdvWrks cube? Try these:

WITH 
  MEMBER [Measures].[x] AS "I'm not empty" 
SELECT 
  {
    IIF
    (
      (IsEmpty([Measures].[x])) //<< this returns False
     ,[Product].[Product Categories].[Category].MEMBERS
     ,{[Measures].[x]}          //<< this is what IIF returns
    )
  } ON 0
FROM [Adventure Works];

It returns this:

Now I tested out IsEmpty:

WITH 
  MEMBER [Measures].[x] AS "I'm not empty" 
SELECT 
  {
    IIF
    (
      (NOT //<< added this to check functionality of IsEmpty
        IsEmpty([Measures].[x]))
     ,[Product].[Product Categories].[Category].MEMBERS //<< this is what IIF returns
     ,{[Measures].[x]}
    )
  } ON 0
FROM [Adventure Works];

We get the following:

What I think is happening in your scenario is this - the param is not empty but is actually a zero length string:

WITH 
  MEMBER [Measures].[x] AS "" 
SELECT 
  {
    IIF
    (
      (
        IsEmpty([Measures].[x]))
     ,[Product].[Product Categories].[Category].MEMBERS
     ,{[Measures].[x]}  //<< the zero length string goes to here
    )
  } ON 0
FROM [Adventure Works];

Results in:

My MDX query editor tells me **Unexpected character '$'** I dont know whats wrong with SpagoBI syntax — LucasPG, Aug 19 '16 at 10:39
@LucasPG I added another possibility - maybe a zero length string is getting passed in — whytheq, Aug 19 '16 at 11:10
So how can I check in my IFF condition if ${param} length equals 0? — LucasPG, Aug 19 '16 at 11:51
@LucasPG can you add this to the start of the script `WITH MEMBER [Measures].[x] AS LEN(${param})` or does it still say `Unexpected character '$'` ? — whytheq, Aug 19 '16 at 12:24
if you can add that measure then maybe you could also try changing the condition to `IIF([Measures].[x]=0 ...) ` ? — whytheq, Aug 19 '16 at 12:28
I already did it, now my mdx looks like this `WITH MEMBER [Measures].[x] AS LEN(${param}) select NON EMPTY {{[Measures].[VALUE]}} ON COLUMNS, NON EMPTY { IIF([Measures].[x] = 0 ,{[CUSTOMER].[COUNTRY].Members}, {[CUSTOMER].[${param}]} ) }ON ROWS from [TRANSACTIONS]` but still I am getting same error :( — LucasPG, Aug 19 '16 at 12:30

SpagoBI OLAP report passing optional parameter

1 Answers1

Linked