与SUM()等效的函数，用于SQL Reporting中的乘法 [英] Function equivalent to SUM() for multiplication in SQL Reporting

问题描述

我正在寻找以下功能或解决方案:

I'm looking for a function or solution to the following:

对于SQL Reporting中的图表，我需要将A列中的值相乘.为求和，我将对图表使用=SUM(COLUMN_A).但是我可以用什么做乘法-到目前为止我还没有找到解决方案?

For the chart in SQL Reporting i need to multiply values from a Column A. For summation i would use =SUM(COLUMN_A) for the chart. But what can i use for multiplication - i was not able to find a solution so far?

当前，我正在按以下方式计算堆积列的值:

Currently i am calculating the value of the stacked column as following:

=ROUND(SUM(Fields!Value_Is.Value)/SUM(Fields!StartValue.Value),3)

我需要一些东西来代替值的和. 像这样的东西:

Instead of SUM i need something to multiply the values. Something like that:

=ROUND(MULTIPLY(Fields!Value_Is.Value)/MULTIPLY(Fields!StartValue.Value),3)

编辑#1

好吧，试图让这件事开始运行. 图表的表达式如下:

Okay tried to get this thing running. The expression for the chart looks like this:

=Exp(Sum(Log(IIf(Fields!Menge_Ist.Value = 0, 10^-306, Fields!Menge_Ist.Value)))) / Exp(Sum(Log(IIf(Fields!Startmenge.Value = 0, 10^-306, Fields!Startmenge.Value))))

如果我手动计算需求"，我必须得到以下结果:

If i calculate my 'needs' manually i have to get the following result:

在我的SQL报表中，我得到以下结果:

In my SQL Report i get the following result:

为简化起见，这些是原始值:

To make it easier, these are the raw values:

，您可以按CW，CQ或CY对图表进行分组 (第一张图片的值是FertStufe汇总的原始值的总和值)

and you have the possibility to group the chart by CW, CQ or CY (The values from the first pictures are aggregated Sum values from the raw values by FertStufe)

编辑#2

尝试了您的表情，结果是:

Tried your expression, which results in this:

只需说明一下

列中的值

=Value_IS / Start_Value

第一张图片中的

彼此相乘

in the first picture are multiplied against each other

0,9947 x 1,0000 x 0,59401 = 0,58573

Diffusion Calenderweek 44次汇总

初始值:1900,00，值:1890,00 == yield:0,99474

Startvalue: 1900,00 Value Is: 1890,00 == yield:0,99474

Waffer unbestrahlt Calenderweek 44总结

初始值:620,00，值是:620,00 ==产生1,0000

Startvalue: 620,00 Value Is: 620,00 == yield 1,0000

制粒日历周44求和

初始值:271,00，值是:160,00 ==产生0,59041

Startvalue: 271,00 Value Is: 160,00 == yield 0,59041

产量扩散x产量薄饼x产量颗粒=图表中的值= 0,58730

yield Diffusion x yield Wafer x yield Pellet = needed Value in chart = 0,58730

编辑#3 原始值如下所示:

EDIT #3 The raw values look like this:

在这些字段上将图表ist分组(如图像中所示) CY(日历年)，CM(日历月)，CW(日历周)

The chart ist grouped - like in the image - on these fields CY (Calendar year), CM (Calendar month), CW (Calendar week)

您可以在此处将数据下载为xls:

You can download the data as xls here:

https://www.dropbox.com/s/g0yrzo3330adgem /2013-01-17_data.xls

我使用的表达式(从编辑窗口复制/过去)

The expression i use (copy / past from the edit window)

=Exp(Sum(Log(Fields!Menge_Ist.Value / Fields!Startmenge.Value)))

我已将整个报告结果导出到excel，您可以在此处获取: https://www.dropbox.com/s/uogdh9ac2onuqh6/2013- 01-17_report.xls

I've exported the whole report result to excel, you can get it here: https://www.dropbox.com/s/uogdh9ac2onuqh6/2013-01-17_report.xls

推荐答案

这实际上是一种解决方法.但是我很确定这是解决这个臭名昭著的问题的唯一方法:D

it's actually a workaround. But I am pretty sure is the only solution for this infamous problem :D

这就是我的做法:

Exp(∑(Log(X)))，所以您应该做的是:

Exp(∑(Log(X))), so what you should do is:

Exp(Sum(Log(Fields!YourField.Value)))

谁说数学一文不值? = D

Who said math was worth nothing? =D

更正了公式.

顺便说一下，它已经过测试.

By the way, it's tested.

解决Ian的问题:

Exp(Sum(Log(IIf(Fields!YourField.Value = 0, 10^-306, Fields!YourField.Value))))

想法是将0更改为一个非常小的数字.只是一个主意.

The idea is change 0 with a very small number. Just an idea.

根据您更新的问题，这是您应该做的事情:

Based on your updated question this is what you should do:

Exp(Sum(Log(Fields!Value_IS.Value / Fields!Start_Value.Value)))

我刚刚测试了上面的代码，并得到了您希望的结果.

I just tested the above code and got the result you hoped for.

这篇关于与SUM()等效的函数，用于SQL Reporting中的乘法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！