时间戳

首页 > 解决方案 > 在使用基于图表中的类别的 SSRS LookupSet 时，是否可以限制目标表达式的范围？

ssrs-2012 - 在使用基于图表中的类别的 SSRS LookupSet 时，是否可以限制目标表达式的范围？

问题描述

我有一个条形图，显示一段时间内联络中心接到的电话总数。报表用户通过选择参数“rangeView”来选择如何对图表进行分组，该参数具有以下选项：“D”（日）、“W”（周）、“M”（月）。用户还选择传递给存储过程以检索数据的日期范围“StartDate”和“EndDate”。

数据集“BusyHourStats”包含日期范围内每天每个小时的一行，并具有以下列：

图表系列是 Sum(TOTAL_CALLS)。

图表类别组表达式使用 rangeView 参数：

=Switch(Parameters!rangeView.Value = "D", Fields!LABEL_YYYY_MM_DD.Value,
    Parameters!rangeView.Value = "W", Fields!LABEL_YYYY_WE.Value,
    Parameters!rangeView.Value = "M", Fields!LABEL_YYYY_MM
)

这按预期工作，按日、周或月对结果进行分组。在图表中显示数据没有问题。

问题：

作为系列工具提示，当您将鼠标悬停在类别（可能是一天、一周或一个月）上时，我需要显示 Max(TOTAL_CALLS.Value) 以及发生最大呼叫总数的日期和小时（或天和小时）。例如：“繁忙时间：2018-01-03 14、2018-01-05 16 有 159 个电话。”</p>

我一直在尝试使用 LookupSet，但我不确定这是否是最好的方法。当源数据集和目标数据集是同一个数据集时应该使用 LookupSet 还是有更智能/更简单的方法？据我所知，我的问题是查找在源数据集中找到 Max(TOTAL_CALLS.Value)，然后将其匹配并返回整个目标数据集中的所有 TOTAL_CALLS.Values（包括 StartDate 和 EndDate 之间的所有数据） . 我需要的是让查找只匹配与悬停的类别（日、周或月）一致的值。

有没有办法根据图表中悬停的当前类别来限制查找中目标表达式的范围？

到目前为止，我在系列工具提示中得到的是（为本示例简化）：

="Busy hour: " & Max(Fields!TOTAL_CALLS.Value) & "calls on " & 
Join(LookupSet(Max(Fields!TOTAL_CALLS.Value), 
    Fields!TOTAL_CALLS.Value, 
    Fields!LABEL_YYYY_MM_DD_HH24.Value), 
    "BusyHourStats"), ", ")

它返回数据集中所有匹配的 TOTAL_CALLS 值。我需要解决的是如何做相当于：

="Busy hour: " & Max(Fields!TOTAL_CALLS.Value) & "calls on " & 
Join(LookupSet(Max(Fields!TOTAL_CALLS.Value), 
    Fields!TOTAL_CALLS.Value *<where scope equals the category being hovered over such as “2018 01” or “2018-W01”&gt;*, 
    Fields!LABEL_YYYY_MM_DD_HH24.Value), 
    "BusyHourStats"), ", ")

我不确定我是否在浪费时间尝试使用 LookupSet 获得所需的结果。我应该使用更合适的功能/方法还是可以这样做？

编辑：附加图像。我还在下面提供了与图像中显示的工具提示对齐的确切工具提示表达式：

=Switch(
    Parameters!rangeView.Value = "D", "Date: " & Fields!CAL_DAY_NUM_IN_MONTH.Value & Switch(
        (Fields!CAL_DAY_NUM_IN_MONTH.Value = 1 or Fields!CAL_DAY_NUM_IN_MONTH.Value = 21 or Fields!CAL_DAY_NUM_IN_MONTH.Value = 31), "st ",
        (Fields!CAL_DAY_NUM_IN_MONTH.Value = 2 or Fields!CAL_DAY_NUM_IN_MONTH.Value = 22), "nd ",
        (Fields!CAL_DAY_NUM_IN_MONTH.Value = 3 or Fields!CAL_DAY_NUM_IN_MONTH.Value = 23), "rd ",
        1=1, "th "
    ) & Fields!CAL_MONTH_NAME.Value & " " &Fields!LABEL_YYYY.Value,
    Parameters!rangeView.Value = "W", "Week: " & Fields!LABEL_YYYY_WE.Value,
    Parameters!rangeView.Value = "M", "Month: " & Fields!CAL_MONTH_NAME.Value & " " & Fields!LABEL_YYYY.Value
) &
vbcrlf &
"Total Volume: " & FormatNumber(Sum(Fields!TOTAL_CALLS.Value),0) & IIF(Max(Fields!TOTAL_CALLS.Value) = 1, " call", " calls") &
vbcrlf &
"Busy hour: " & FormatNumber(Max(Fields!TOTAL_CALLS.Value),0) & IIF(Max(Fields!TOTAL_CALLS.Value) = 1, " call at ", " calls at ") & 
Join(LookupSet(Max(Fields!TOTAL_CALLS.Value), 
    Fields!TOTAL_CALLS.Value, 
    Fields!HourTime.Value & IIF(Parameters!rangeView.Value = "D", Nothing, " on " & Fields!LABEL_YYYY_MM_DD.Value), 
    "BusyHourStats"), Environment.NewLine & "                               and ") &
vbcrlf &
"Max Concurrent: " & FormatNumber(Max(Fields!MAX_CONCURRENT_CALLS.Value),0) & IIF(Max(Fields!MAX_CONCURRENT_CALLS.Value) = 1, " call at ", " calls at ") & 
Join(LookupSet(Max(Fields!MAX_CONCURRENT_CALLS.Value), 
    Fields!MAX_CONCURRENT_CALLS.Value, 
    Fields!HourTime.Value & IIF(Parameters!rangeView.Value = "D", Nothing, " on " & Fields!LABEL_YYYY_MM_DD.Value), 
    "BusyHourStats"), Environment.NewLine & "                                        and ")

正如您所看到的，当我将鼠标悬停在类别月份“2018 年 1 月”上时，它返回了 1 月“2018 年 1 月 12 日上午 11 点”的正确匹配值，但它也返回了 2 月“2018 年下午 3 点”的匹配值- 02-15”。我只想获得悬停的月份的匹配项（或者悬停在天或周上，具体取决于运行报告时选择的类别分组）。

图片：显示一月份工具提示的报告

Edit2：在下面添加了示例数据集（csv）。注意：删除了许多行以符合本文中的字符限制。该示例与报告中使用的完全相同（与原始帖子描述略有不同）。

BUSINESS_UNIT,CAL_MONTH_NAME,LABEL_WE,CAL_WEEK_START_DATE,CAL_WEEK_END_DATE,LABEL_YYYY_MM_DD,LABEL_YYYY_MM_DD_HH24,TOTAL_CALLS,MAX_CONCURRENT_CALLS
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-12,2018-01-12 11,85,3
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-12,2018-01-12 12,16,2
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-12,2018-01-12 13,3,1
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-12,2018-01-12 14,8,1
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-12,2018-01-12 15,9,2
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-12,2018-01-12 16,9,1
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-12,2018-01-12 19,1,1
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-13,2018-01-13 09,1,1
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-13,2018-01-13 11,2,1
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-13,2018-01-13 12,1,1
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-13,2018-01-13 13,2,1
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-13,2018-01-13 14,1,1
    <All>,January,W02,2018-01-08 00:00:00.000,2018-01-14 00:00:00.000,2018-01-14,2018-01-14 10,5,1
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-15,2018-01-15 06,1,1
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-15,2018-01-15 07,12,2
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-15,2018-01-15 08,11,3
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-15,2018-01-15 09,24,2
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-15,2018-01-15 10,6,1
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-15,2018-01-15 11,19,2
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-15,2018-01-15 12,4,1
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-15,2018-01-15 13,38,3
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-15,2018-01-15 14,49,3
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-15,2018-01-15 15,28,3
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-15,2018-01-15 16,28,2
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-15,2018-01-15 21,1,1
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-16,2018-01-16 07,5,2
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-16,2018-01-16 08,23,1
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-16,2018-01-16 09,27,2
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-16,2018-01-16 10,77,4
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-16,2018-01-16 11,19,3
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-16,2018-01-16 12,14,3
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-16,2018-01-16 13,43,2
    <All>,January,W03,2018-01-15 00:00:00.000,2018-01-21 00:00:00.000,2018-01-16,2018-01-16 14,37,2
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-13,2018-02-13 20,2,5
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 07,165,50
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 08,5,7
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 09,365,51
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 10,11,2
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 11,58,3
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 12,9,1
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 13,425,53
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 14,79,4
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 15,57,4
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 16,16,3
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 17,13,2
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 18,1,1
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-14,2018-02-14 21,1,1
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-15,2018-02-15 07,5,2
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-15,2018-02-15 08,17,2
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-15,2018-02-15 09,4,1
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-15,2018-02-15 10,30,3
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-15,2018-02-15 11,42,2
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-15,2018-02-15 12,17,2
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-15,2018-02-15 13,37,2
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-15,2018-02-15 14,62,3
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-15,2018-02-15 15,85,3
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-15,2018-02-15 16,33,2
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-15,2018-02-15 18,1,1
    <All>,February,W07,2018-02-12 00:00:00.000,2018-02-18 00:00:00.000,2018-02-15,2018-02-15 22,6,1

标签： ssrs-2012

解决方案

推荐阅读