根据一个多维数据集中的两个日期维度计算百分比率

问题描述

设置-带有OLAP多维数据集(由供应商构建)和MS Report Builder v3的SSAS 2012.无法访问BIDS.

Set up - SSAS 2012 with OLAP cubes (built by supplier) and MS Report Builder v3. No access to BIDS.

我正在构建一个报告，该报告需要基于来自单个多维数据集的数据来计算处置率.从历史上看，这是根据两个单独的数据表计算得出的，按记录的日期按月统计新项目，按处理的月份按月统计.然后可以使用查找或类似方法将其转换为处置率.

I am building a report which needs to calculate a disposal rate based on data from a single cube. Historically this would have been calculated from two separate tables of data, giving a count by month of new items by date recorded and a count by month of items disposed by month of disposal. This can then be turned to a disposal rate using a lookup or similar.

空白处理日期很好(可能需要花费数月的时间来处理物品).

Blank disposal dates are fine (can take months to dispose of items).

我想将其保留在单个查询中，以便我可以引入额外的维度来分析数据并轻松地以多种方式表示它.我的怀疑是我需要一个有计划的成员，但是我不确定从哪里开始.任何帮助都会得到很大的帮助-我正在尝试一些事情，并且会在我解决自己的问题时进行更新.

I would like to keep this in a single query so that I can introduce extra dimensions to analyse the data and represent it multiple ways easily. My suspicion is that I need a calculated member but I am not sure where to start with these. Any help would be greatly received - I am trying out a few things and will update this should I solve myself.

简单公式应为

=(sumif(Items, DateReported="July 2014"))/(sumif(Items, Disposal Date="July 2014"))`

所以以下数据...

Month Recorded  Month Disposed  No of Items
May-14          May-14          25
May-14          Jun-14          3
May-14          Jul-14          45
Jun-14                          232
Jun-14          Jun-14          40
Jun-14          Jul-14          46

应该产生...

Month         No Recorded  No Disposed  Disposal Rate
01/05/2014    73           25           34%
01/06/2014    48           43           90%
01/07/2014    45           91           202%

我当前的MDX语句:

SELECT
NON EMPTY { [Measures].[No of Items] } ON COLUMNS,
NON EMPTY 
     { 
       ([Date Reported].[Calendar Months].[Month].ALLMEMBERS 
        * 
        [Disposal Date].[Calendar Months].[Month].ALLMEMBERS ) 
     } ON ROWS 
FROM [Items] 

推荐答案

您可以使用 LinkMember ，以将对一个层次结构(如[Date Reported].[Calendar Months])的引用移动到另一层次结构(如[Disposal Date].[Calendar Months])，前提是两个层次结构具有完全相同的结构.因此，仅在查询中使用[Date Reported]，该计算才能使用[Disposal Date].该查询将如下所示:

You can use LinkMember to move a reference to one hierarchy (like [Date Reported].[Calendar Months]) to another one (like [Disposal Date].[Calendar Months]), provided both hierarchies have the exact same structure. Thus, only using [Date Reported] in your query, the calculation can use [Disposal Date]. The query would be like the following:

WITH MEMBER Measures.[Disposed in Date Reported] AS
            (Measures.[No of Items],
             LinkMember([Date Reported].[Calendar Months].CurrentMember, [Disposal Date].[Calendar Months]),
             [Date Reported].[Calendar Months].[All]
            )
     MEMBER Measures.[Disposal Rate] AS
            IIf([Measures].[No of Items] <> 0,
                Measures.[Disposed in Date Reported] / [Measures].[No of Items],
                NULL
               ), FORMAT_STRING = '0%'
SELECT { [Measures].[No of Items], Measures.[Disposed in Date Reported], Measures.[Disposal Rate] }
       ON COLUMNS,
       [Date Reported].[Calendar Months].[Month].ALLMEMBERS
       ON ROWS
FROM [Items]

可能，您希望调整报表中的列标题.我忽略了这一点，并使用了成员名称来掩盖他们的工作，而不是应向用户显示的内容.

Possibly, you would want to adapt the column titles in your report. I left that out and used member names that desribe more what they do than what should be shown to users.

这篇关于根据一个多维数据集中的两个日期维度计算百分比率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！