MDX 查询未给出正确答案

发布于 2024-12-25 04:45:34 字数 887 浏览 2 评论 0原文

现在我在使用不同的 MDX qry 时遇到了不同的问题。

对于当前月份的详细信息，我还获得了上个月的详细信息。我使用的 MDX 查询是：

/*
SELECT 
NON EMPTY 
    { Hierarchize ( { [Offer].[GrandTotal], [Offer].[GrandTotal].Children }) } 
        ON COLUMNS, 
NON EMPTY 
    { Hierarchize ( { [Circle].[GrandTotal], [Circle].[GrandTotal].Children }) } 
        ON ROWS 
FROM 
    [SCMAircel_ActiveBase] 
WHERE 
  ( [Measures].[TotalCount], 
    [Subscriber State].[GrandTotal], 
    [Time].[2012].[${curMonth}].[${curDay}])
*/

我使用两个参数在 Pentaho 报表设计器中提供正确的月份和日期值。但问题是用下面的例子来解释的。

我想要一天的循环报价计数。例如：日期 04/01/2012。但我也得到了上个月同一天的值..（01-12-2011）因为该值在表中可用。但我只需要 2012 年 1 月 1 日的数据。

[Subscriber State].[GrandTotal] 包含诸如 ACTIVE、GRACE 之类的值，我正在过滤的 SUSPEND ，

即使月份和日期正确传递，我也不知道该值 TOTAL_COUNT 是如何累加的......

如果有人知道，请告诉任何解决方案这

原文

Now i am having different problem with different MDX qry.

For the current month details I am getting details of the last month also..
The MDX query used by me is:

/*
SELECT 
NON EMPTY 
    { Hierarchize ( { [Offer].[GrandTotal], [Offer].[GrandTotal].Children }) } 
        ON COLUMNS, 
NON EMPTY 
    { Hierarchize ( { [Circle].[GrandTotal], [Circle].[GrandTotal].Children }) } 
        ON ROWS 
FROM 
    [SCMAircel_ActiveBase] 
WHERE 
  ( [Measures].[TotalCount], 
    [Subscriber State].[GrandTotal], 
    [Time].[2012].[${curMonth}].[${curDay}])
*/

I am using two parameter that is giving correct month and date value in the Pentaho Report Designer. But the problem is explained with the example below.

i want circlewise offerwise count for a day.for eg: for the date 04/01/2012. But i am getting the values of last month same day too..(01-12-2011) because that value is available on the table. But i need the 01-01-2012 data only..

The [Subscriber State].[GrandTotal] contains values like ACTIVE,GRACE,SUSPEND which i am filtering,

I dont know how that value TOTAL_COUNT is adding up eventhough the month and date is passing properly....

Pls tell any solution if anyone knows about this

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

绅士风度i 2025-01-01 04:45:34

我不熟悉 Pentaho，但熟悉 SQL Server Analysis Services。我假设由于 MDX 是一个标准，所以不应该有任何差异；）

我假设结果被展平为二维结果集，并且数据相应地交叉连接，然后由报告控件分组。

所以我建议你应该尝试的是（尽管如果不进一步调查就很难理解你的问题）：

/*
SELECT 
    {
        // it is best practice to put measures on columns
        [Measures].[TotalCount]  
    }
    ON COLUMNS, 
    NON EMPTY 
    {
        // if you want multiple dimension to be aggregated, you should cross join these 
        Hierarchize ( { [Offer].[GrandTotal], [Offer].[GrandTotal].Children }) } 
        *    // cross join here
        Hierarchize ( { [Circle].[GrandTotal], [Circle].[GrandTotal].Children }) 
    } 
    ON ROWS 
FROM 
    [SCMAircel_ActiveBase] 
WHERE 
  (
    //[Subscriber State].[GrandTotal], // i don't get this. You either need to filter for certain members, or you want members to be crossjoined. But putting a member hierarchy in filter doesn't make sense to me
    {[Subscriber State].[GrandTotal].[ACTIVE], [Subscriber State].[GrandTotal].[Grace] } // like this for example
    ,[Time].[2012].[${curMonth}].[${curDay}]
   )
*/

I am not familiar with Pentaho but with SQL Server Analysis Services. I assume that since MDX is a standard, there shouldn't be (m)any differences ;)

I assume that the result is flattened to an 2 dimensional result set and data is cross joined accordingly and later grouped by report controls.

So what I suggest you should try is (even though it is hard to understand your problem without further investigation):

/*
SELECT 
    {
        // it is best practice to put measures on columns
        [Measures].[TotalCount]  
    }
    ON COLUMNS, 
    NON EMPTY 
    {
        // if you want multiple dimension to be aggregated, you should cross join these 
        Hierarchize ( { [Offer].[GrandTotal], [Offer].[GrandTotal].Children }) } 
        *    // cross join here
        Hierarchize ( { [Circle].[GrandTotal], [Circle].[GrandTotal].Children }) 
    } 
    ON ROWS 
FROM 
    [SCMAircel_ActiveBase] 
WHERE 
  (
    //[Subscriber State].[GrandTotal], // i don't get this. You either need to filter for certain members, or you want members to be crossjoined. But putting a member hierarchy in filter doesn't make sense to me
    {[Subscriber State].[GrandTotal].[ACTIVE], [Subscriber State].[GrandTotal].[Grace] } // like this for example
    ,[Time].[2012].[${curMonth}].[${curDay}]
   )
*/

回复收藏 0 原文

~没有更多了~