Dashboard中的BI Services与Crosstabs(可变长度列）

译者注：BI WS是一个很好的功能，甚至有些BI顾问将其称为革命性的。因为它允许我们将WEBI的某一个块儿作为web service发布。可是它有一个很明显的缺点，就是需要预先知道WEBI块的样子，如果WEBI块是列数不固定的，那么SAP并没有提供out-of-box的功能来绑定到Dashboard单元格。这篇文章提供了一个很好的workaround供大家参考。

原文发表于：http://blog.davidg.com.au/2012/06/bi-services-with-crosstabs-variable.html

BI services(BIWS-译者注)很棒。但是它有一些缺点，而与Live Office相比不足的是没法处理Crosstab数据。换句话说，你必须知道在Xcelsius里使用BI Service之前预先知道在Webi报表块里有多少列。在这篇博客里，我将演示一个小技巧来解决这个限制。

我所使用的是：
BI4.0 FP3 Patch 3（但是这个方法适用于到XI 3.1的所有版本）
Xcelsius 2011 FP3 Patch 3

我假设：
你了解Webi、 Xceelsius、 BI Service

一个例子

这是你的数据并且你想要将它们显示在你的超屌的Dashboard里。
没问题。生成一个BI Serivce并且绑定到Xcelsius模型的单元格里。帅~~
之后，六月悄然离去。只有七列的单元格不够长了。让我们解决它。

使用BI Sservice

按正常步骤创建BI Service。技巧在如何使用。
首先让我们看看没问题的时候是什么样子的。

这里有一个spreadsheet组件和一个area chart使用BI Service的范围数据。但是如果元数据的月份数发生变化了，比如新的一个月到来了或者用户可以选择过滤特定月份，这又将会怎么样呢？

当元数据发生这种变化（七个月）：

你的dashboard会变成这样：

背后的原因是：第一行数据是抬头。这是一个单独的范围所以额外的列在此被截断了。从下一行开始，开始输出Australia了。它输出了前六个月，这没什么问题。但是已经没有额外的单元格输出七月了，它循环到下一行输出。

由此，我们要铭记的一点：从BI Services产生的数据没有行的概念。它只不过是一个长字符串。所以一切都依赖于你知道会有多少列然后在spreadsheet里将BI Service绑定到那么多列上。

咱们来帮着协调一下

那么我们该怎么做呢？将BI Service数据转化成一列的，如：

然后编写一个公式选择出正确的单元格数据填充你的图表。以下是步骤。

1. 在Xcelsius里，找到Conneciton，设置input values属性turnOutputToVTable为TRUE
   
2. 在Output Values里，将table > row > cell数值映射到一列单元格上。如果是一个10×10的表，那么映射到100行上（一列）。

    

3. 仍然是在output values框里，将nbColumns和nbLines映射到spreadsheet里。
   
   
4. 现在数据在spreadsheet里类似：
   

    

    

这就是可以用来重建webi块的全部数据。很兴奋，是吧？

这是最终结果的样子。灰色单元格是连接获得的数据范围（BI Service直接填充）。橙色各自是Excel公式。不同深度的橙色相邻的单元格具有不同的公式。我知道不会超过12个月也不会超过10行，所以这是我的定义。你可能使用多或者少一些。

最上面的橙色单元格保存了“属于”这个位置的这部分的数据的行数。记住，我们的目标是重建最初的webi块。下面的橙色表格使用上面表格来从灰色区域的列里超找数值。

小技巧：如果你想知道BI Service数据是在XC模型里是什么样的，单击preview，等模型加载完毕后，点击File > snapshot > current excel data。保存Excel数据，关闭preview，然后将excel数据导入到模型里。

四个公式

第一个公式只是简单地返回一个数值反映了块的哪一行应该被显示。它只显示和webi块一样多的行。nbLines是数据行数。你需要考虑抬头行，所以要加一。

第二个公式对列进行相同的操作。注意，BI Services保存crosstab横向有多少列。所以这里我要包含进第一列，要加一。

上面橙色的范围只是简单地把上一个单元格的数值加X。

那么如果有8列数据，再加上一列国家名字，那就是9列。下一行的第一个单元格将是第十个位置的值。灰色部分的原始数据里，“Canada”是第十个值。明白了么？

为了防止没有数据时公式报错，所有公式都包含在IF判断力，检查单元格在原来webi块的行列范围之内。

第四个公式用于下面橙色表格，十分简单。

用INDEX函数从原始数据里选择第X行数据。

但是抬头（Jan 2012, Feb 2012等）怎么办？这与以前没什么区别。BI Service属性Headers > row > cell直接绑定到这些单元格上。

图表

显而易见，像往常一样绑定图表到单元格范围。要确保在behaviors tab将ignore blank cells in series和in values勾选上。

大功告成

现在BI service和Crosstab和谐共处了，但是如果你创建了数千个带公式的单元格，那么要注意加载时间了。我敢说，不要听信网上说的你应该在dashboard里使用多少公式，用秒表，在机器上运行，观察内存使用情况然后自己做出判断。