SSRS 2013热图

Question

软件: MS Visual Studio Shell 2013

我目前正在为一份SSRS报告制作矩阵热图，我正试图发布这份报告。

我试图应用背景的文本框是按行和列分组的。它有一个计数函数来确定该单元格的值。

我使用的报表代码是：

public const ColorScaleRed = "#FF0000"
public const ColorScalePink= "#ff6666"

public function ColorScaleWPR(value, minValue, maxValue) as string

    ColorScaleWPR = ColorScale3(value, _
        minValue, "White", _
        ColorScalePink, _
        maxValue, ColorScaleRed)

end function

public function ColorScale3(value as object, minValue as object, minColor as string, midColor as string, maxValue as object, maxColor as string) as string

    ' Use average of minValue and maxValue as midValue
    dim midValue as object
    if IsNumeric(minValue) and IsNumeric(maxValue) then
        midValue = (CDbl(minValue) + CDbl(maxValue)) / 2
    end if

    ColorScale3 = ColorScale3(value, _
        minValue, minColor, _
        midValue, midColor, _
        maxValue, maxColor)

end function

public function ColorScale3(value as object, minValue as object, minColor as string, midValue as object, midColor as string, maxValue as object, maxColor as string) as string

    if IsNumeric(value) and IsNumeric(midValue) and CDbl(value) < CDbl(midValue) then
        ColorScale3 = ColorScale(value, minValue, minColor, midValue, midColor)
    else
        ColorScale3 = ColorScale(value, midValue, midColor, maxValue, maxColor)
    end if

end function

public function ColorScale(value as object, minValue as object, minColor as string, maxValue as object, maxColor as string, optional errorColor as string = "Transparent") as string

    ColorScale = errorColor

    if not IsNumeric(value) or not IsNumeric(minValue) or not IsNumeric(maxValue) then
        exit function
    end if

    ' Do all calculations using doubles (can't mix doubles and decimals)
    value = CDbl(value)
    minValue = CDbl(minValue)
    maxValue = CDbl(maxValue)

    if minValue >= maxValue then
        exit function
    end if

    if value <= minValue then
        ColorScale = minColor
        exit function
    end if
    if value >= maxValue then
        ColorScale = maxColor
        exit function
    end if

    dim scaleValue, r, g, b as double
    dim minRGB, minR, minG, minB as integer
    dim maxRGB, maxR, maxG, maxB as integer

    scaleValue = (value - minValue) / (maxValue - minValue)

    minRGB = GetRGB(minColor)
    minR = minRGB / 2^16
    minG = (minRGB mod 2^16) / 2^8
    minB = minRGB mod 2^8

    maxRGB = GetRGB(maxColor)
    maxR = maxRGB / 2^16
    maxG = (maxRGB mod 2^16) / 2^8
    maxB = maxRGB mod 2^8

    r = minR + ((maxR - minR) * scaleValue)
    g = minG + ((maxG - minG) * scaleValue)
    b = minB + ((maxB - minB) * scaleValue)

    ColorScale = string.Format("#{0:X2}{1:X2}{2:X2}", _
        CInt(Math.Floor(r)), _
        CInt(Math.Floor(g)), _
        CInt(Math.Floor(b)))

end function

private function GetRGB(colorStr as string) as integer

    GetRGB = 0

    if colorStr.StartsWith("#") then
        GetRGB = Int32.Parse(colorStr.Substring(1), System.Globalization.NumberStyles.AllowHexSpecifier)
        exit function
    end if

    dim c as System.Drawing.Color
    c = System.Drawing.Color.FromName(colorStr)

    GetRGB = (c.R * 2^16) + (c.G * 2^8) + c.B

end function

我的问题是当我设置背景表达式的时候。

我可以使用下面的表达式正确地渲染热图：

=Code.ColorScaleWPR(Count(Fields!Candidate_ID.Value),0,10)

但是，我不想使用静态值作为最小和最大值。我希望它们是动态的，因为根据所选参数的不同，最小和最大值可能会有很大的不同。

所以我使用了下面的代码

=Code.ColorScaleWPR(Count(Fields!Candidate_ID.Value), Min(Fields!Candidate_ID.Value,"DataSet1"), Max(Fields!Candidate_ID.Value,"DataSet1"))

当我运行报告时，它呈现为完全白色，并且没有对单元格应用任何条件。

任何让它动态化的建议都是很棒的。谢谢!

Answer 1

我不认为RS能够预先计算所有分组，然后找到它们的最小/最大值来应用色标。尝试的最佳方法是计算每个组的最小值和最大值，并将其与原始数据集查询分开。然后使用表达式根据匹配组标准拉取所需的值。我会朝那个方向走。PS。当我在旋转矩阵分组上使用色标时，我没有足够的时间让这头野兽活着：)