SSAS-如何为计算量度配置格式编号? [英] SSAS - How to configure format number for calculated measures?

问题描述

我创建了一个计算得出的度量，以便允许用户显示有关销售的营业利润.

I created a calculated measure in order to allow user to display the operating margin about sales.

ROUND([Measures].[Total] - [Measures].[Total Cost],2)

它运作良好，但是当我尝试在Excel/PowerPivot中检索数据时出现错误.实际上，它使用，"显示值.格式以分隔小数，而我的格式为，).因此，如何配置以更改SSAS中的此行为，因为Excel由于未知类型"而无法格式化数字(它无法解析数字).

It works well, however I get an error when I try to retrieve the data in Excel / PowerPivot. In fact, It displays values by using the ",." format to separate decimals and my format is " ,). So how to configure to change this behavior in SSAS because Excel cannot format the number due to a "unknown type" (it cannot parse the number).

这是从美国到法语格式的十进制转换的示例:

This an example of decimal conversion from US to French format :

1,000,000.00 (US) -> 1 000 000,00 (France) 
99.99 (US) -> 99,99 (France)

好的，我可以在Excel中直接用逗号"代替点"，但这不是一个好习惯，目的是简化PowerPivot的使用.

OK I could replace the "dot" by a "comma" directly in Excel but it's not a good practice and the objective is to simplify the PowerPivot usage.

谢谢！

推荐答案

在您的SSAS项目中，您可以使用格式字符串"字段来指定格式:

In your SSAS project you can use "Format string" field to specify the format:

在代码中，它看起来是这样的:

In the code it looks this way:

CREATE MEMBER CURRENTCUBE.[Measures].[ASP]
 AS [Measures].[Amount]/[Measures].[Units], 
FORMAT_STRING = "$#,##0.00;($#,##0.00)";     

使用手动字符串创建精确的外观，或者如果您将所有环境都正确设置为法语语言环境，则可以使用标准的货币"格式字符串，也可能对您有利.

Use either manual string to create exact look or if all your environments are properly set up as French locale, there is a standard "Currency" format string which also might work in your favor.

这篇关于SSAS-如何为计算量度配置格式编号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！