SpagoBI OLAP报告传递可选参数 [英] SpagoBI OLAP report passing optional parameter

问题描述

你好.今天是我另一个使用可选参数OLAP raport的战斗日.我对MDX查询有疑问.我是这样写的:

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}是[CUSTOMER].[COUNTRY]的可选参数.我没有为我的参数选中必需"复选按钮，因此OLAP在不带参数的情况下执行后应具有所有[VALUE].这是一个问题，因为在启动我的OLAP raport参数之后，可能想要填充一些东西.它给了我一个错误.

${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.

个人资料属性"param"不存在.

Profile attribute 'param' not existing.

但是我不想用配置文件属性填充它.我已经创建了值列表和参数的解析驱动程序，用于将可能的值传递给列表框字符串参数-${param}.

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}.

是否有可能带有可选参数的OLAP报告?这里有BI主管吗? 如果有任何帮助，我将不胜感激.

Is there possibility to have OLAP report with optional parameter? Any BI master here? I would be greatfull for any help.

更新:我已经做了类似的事情，我认为这种语法是正确的，(我正在检查SpagoBI示例)

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

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

推荐答案

此问题是您以前帖子的直接副本:

This question is a direct copy oF your previous posts:

> https://stackoverflow.com/questions/38970610/olap-report-with-optional -参数

$ {param}是字符串吗?

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];

它起作用吗?

如果它不起作用，则说明该问题与mdx无关-由于某种原因，您的语法或参数移至客户端的方式是错误的.

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.

注释

以上等效于此超级简单的脚本:

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];

---

您有AdvWrks多维数据集吗?试试这些:

---

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];

它返回以下内容:

现在我测试了IsEmpty:

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];

我们得到以下信息:

我认为您的情况正在发生的是-参数不是空的，但实际上是零长度的字符串:

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];

结果:

这篇关于SpagoBI OLAP报告传递可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！