Can you limit the scope of the destination expression when using an SSRS LookupSet based on the category in a...












1















I have a bar chart which shows the total number of calls received in a contact centre over time. The report user chooses how to group the chart by selecting a parameter “rangeView” which has the options: “D” (Day), “W” (Week), “M” (Month). The user also chooses the date range “StartDate” and “EndDate” which gets passed to the stored proc to retrieve the data.



The dataset “BusyHourStats” contains a row for every hour of each day in the date range and has the following columns:




  • LABEL_YYYY_MM_DD_HH24 (the hour of the day eg: “2018-01-01 14”)

  • LABEL_YYYY_MM_DD (day eg: “2018-01-01”)

  • LABEL_YYYY_WE (week eg: “2018-W01)

  • LABEL_YYYY_MM (month eg: “2018-01”)

  • TOTAL_CALLS


The chart series is Sum(TOTAL_CALLS).



The chart Category Group expression uses the rangeView parameter:



=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

)


This works as expected, grouping the results by Day, Week or Month. No problems with displaying the data in the chart.



Problem:



As a Series tooltip when you hover over the category (which could be a day, week or month) I need to display Max(TOTAL_CALLS.Value) and the day and hour (or days and hours) when that the max total calls occurred. Eg: “Busy hour: 159 calls on 2018-01-03 14, 2018-01-05 16.”



I have been trying to use LookupSet but I’m not sure if this is the best way to go about it. Should a LookupSet be used when the source and destination dataset is the same dataset or is there a smarter/simpler way? My problem as best I understand is that the lookup finds the Max(TOTAL_CALLS.Value) in the source dataset and then matches it on and returns all TOTAL_CALLS.Values in the entire destination dataset (which includes all the data between the StartDate and EndDate). What I need is to have the lookup only match on the values that align with the category (day, week or month) which is being hovered over.



Is there are way to limit the scope of the destination expression in the lookup based on the current category being hovered over in the chart?



What I have got so far in the Series tooltip is (simplified for this example):



="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"), ", ")


which returns all the matching TOTAL_CALLS values in the dataset. What I need to work out is how to do something that equates to:



="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”>*, 

    Fields!LABEL_YYYY_MM_DD_HH24.Value), 

    "BusyHourStats"), ", ")


I’m not sure if I am wasting my time trying to get the results I need by using LookupSet. Is there a more appropriate function/method I should be using or can it be done this way?



Edit: Attaching image. I have also provided the exact tooltip expression below which aligns with the tooltip shown in the image:



=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 ")


As you can see when I hover the mouse over the category month "January 2018" it is returning the correct matching value for January "11am on 2018-01-12" but it is also returning the matching value for February "3pm on 2018-02-15". I'd like to only get the matches for the Month that is being hovered over (or Day or Week being hovered over depending on the category grouping chosen when the report was run).



Image:Report showing tooltip for January



Edit2: Added sample dataset below (csv). Note: removed many rows to comply to character limit in this post. The sample is exactly as used in the report (which is slightly different to the original post description).



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







share|improve this question

























  • I'm not sure I fully understand the problem, maybe a sample set of data and expected output would help... Having said that, if you expression more or less works you could try just changing the scope from "BusyHourStats" to the name of the category group. I'm not sure this will work in a chart but worth a go. The other option may be to add the data as an extra column in the SQL dataset and then just use that for the tooltip.

    – Alan Schofield
    Nov 22 '18 at 23:12











  • Thanks for your suggestion Alan. I tried replacing the "BusyHourStats" with the name of the category group "Chart12_CategoryGroup" however I get the following error: "The ToolTip expression for the chart ‘Chart12’ has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset. (rsInvalidLookupScope)". I will try to provide a sample set of data.

    – zipzip
    Nov 23 '18 at 0:48













  • I have added a screenshot showing the tooltip output. Am I able to upload a sample data set as a csv file?

    – zipzip
    Nov 23 '18 at 1:23











  • You can limit a scope in the expression for example with a grouping Max(Fields!TestField.Value, "MonthGrouping") or the whole dataset Max(Fields!TestField.Value, "DataSet1"). You can also check for a scope with the expression InScope(Fields!TestField.Value). Maybe one of this two will help you.

    – Strawberryshrub
    Nov 23 '18 at 5:34











  • Personally, I would do a lot of this on the server and let SSRS have an easier time :) I did a quick bit of t-sql to demo which you could use as your dataset. It only handle the max calls but its easy to extend to cover concurrent in the same way. I also changed one record so there are two with 85 total calls just so I got varied results. If updating the dataset query is possible then have a look at this saved query, (run it) it has additional columns with the busy hours for month, week and day level. It will need some tidying up but hopefully will be useful rextester.com/SEO17421

    – Alan Schofield
    Nov 23 '18 at 17:46


















1















I have a bar chart which shows the total number of calls received in a contact centre over time. The report user chooses how to group the chart by selecting a parameter “rangeView” which has the options: “D” (Day), “W” (Week), “M” (Month). The user also chooses the date range “StartDate” and “EndDate” which gets passed to the stored proc to retrieve the data.



The dataset “BusyHourStats” contains a row for every hour of each day in the date range and has the following columns:




  • LABEL_YYYY_MM_DD_HH24 (the hour of the day eg: “2018-01-01 14”)

  • LABEL_YYYY_MM_DD (day eg: “2018-01-01”)

  • LABEL_YYYY_WE (week eg: “2018-W01)

  • LABEL_YYYY_MM (month eg: “2018-01”)

  • TOTAL_CALLS


The chart series is Sum(TOTAL_CALLS).



The chart Category Group expression uses the rangeView parameter:



=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

)


This works as expected, grouping the results by Day, Week or Month. No problems with displaying the data in the chart.



Problem:



As a Series tooltip when you hover over the category (which could be a day, week or month) I need to display Max(TOTAL_CALLS.Value) and the day and hour (or days and hours) when that the max total calls occurred. Eg: “Busy hour: 159 calls on 2018-01-03 14, 2018-01-05 16.”



I have been trying to use LookupSet but I’m not sure if this is the best way to go about it. Should a LookupSet be used when the source and destination dataset is the same dataset or is there a smarter/simpler way? My problem as best I understand is that the lookup finds the Max(TOTAL_CALLS.Value) in the source dataset and then matches it on and returns all TOTAL_CALLS.Values in the entire destination dataset (which includes all the data between the StartDate and EndDate). What I need is to have the lookup only match on the values that align with the category (day, week or month) which is being hovered over.



Is there are way to limit the scope of the destination expression in the lookup based on the current category being hovered over in the chart?



What I have got so far in the Series tooltip is (simplified for this example):



="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"), ", ")


which returns all the matching TOTAL_CALLS values in the dataset. What I need to work out is how to do something that equates to:



="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”>*, 

    Fields!LABEL_YYYY_MM_DD_HH24.Value), 

    "BusyHourStats"), ", ")


I’m not sure if I am wasting my time trying to get the results I need by using LookupSet. Is there a more appropriate function/method I should be using or can it be done this way?



Edit: Attaching image. I have also provided the exact tooltip expression below which aligns with the tooltip shown in the image:



=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 ")


As you can see when I hover the mouse over the category month "January 2018" it is returning the correct matching value for January "11am on 2018-01-12" but it is also returning the matching value for February "3pm on 2018-02-15". I'd like to only get the matches for the Month that is being hovered over (or Day or Week being hovered over depending on the category grouping chosen when the report was run).



Image:Report showing tooltip for January



Edit2: Added sample dataset below (csv). Note: removed many rows to comply to character limit in this post. The sample is exactly as used in the report (which is slightly different to the original post description).



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







share|improve this question

























  • I'm not sure I fully understand the problem, maybe a sample set of data and expected output would help... Having said that, if you expression more or less works you could try just changing the scope from "BusyHourStats" to the name of the category group. I'm not sure this will work in a chart but worth a go. The other option may be to add the data as an extra column in the SQL dataset and then just use that for the tooltip.

    – Alan Schofield
    Nov 22 '18 at 23:12











  • Thanks for your suggestion Alan. I tried replacing the "BusyHourStats" with the name of the category group "Chart12_CategoryGroup" however I get the following error: "The ToolTip expression for the chart ‘Chart12’ has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset. (rsInvalidLookupScope)". I will try to provide a sample set of data.

    – zipzip
    Nov 23 '18 at 0:48













  • I have added a screenshot showing the tooltip output. Am I able to upload a sample data set as a csv file?

    – zipzip
    Nov 23 '18 at 1:23











  • You can limit a scope in the expression for example with a grouping Max(Fields!TestField.Value, "MonthGrouping") or the whole dataset Max(Fields!TestField.Value, "DataSet1"). You can also check for a scope with the expression InScope(Fields!TestField.Value). Maybe one of this two will help you.

    – Strawberryshrub
    Nov 23 '18 at 5:34











  • Personally, I would do a lot of this on the server and let SSRS have an easier time :) I did a quick bit of t-sql to demo which you could use as your dataset. It only handle the max calls but its easy to extend to cover concurrent in the same way. I also changed one record so there are two with 85 total calls just so I got varied results. If updating the dataset query is possible then have a look at this saved query, (run it) it has additional columns with the busy hours for month, week and day level. It will need some tidying up but hopefully will be useful rextester.com/SEO17421

    – Alan Schofield
    Nov 23 '18 at 17:46
















1












1








1


1






I have a bar chart which shows the total number of calls received in a contact centre over time. The report user chooses how to group the chart by selecting a parameter “rangeView” which has the options: “D” (Day), “W” (Week), “M” (Month). The user also chooses the date range “StartDate” and “EndDate” which gets passed to the stored proc to retrieve the data.



The dataset “BusyHourStats” contains a row for every hour of each day in the date range and has the following columns:




  • LABEL_YYYY_MM_DD_HH24 (the hour of the day eg: “2018-01-01 14”)

  • LABEL_YYYY_MM_DD (day eg: “2018-01-01”)

  • LABEL_YYYY_WE (week eg: “2018-W01)

  • LABEL_YYYY_MM (month eg: “2018-01”)

  • TOTAL_CALLS


The chart series is Sum(TOTAL_CALLS).



The chart Category Group expression uses the rangeView parameter:



=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

)


This works as expected, grouping the results by Day, Week or Month. No problems with displaying the data in the chart.



Problem:



As a Series tooltip when you hover over the category (which could be a day, week or month) I need to display Max(TOTAL_CALLS.Value) and the day and hour (or days and hours) when that the max total calls occurred. Eg: “Busy hour: 159 calls on 2018-01-03 14, 2018-01-05 16.”



I have been trying to use LookupSet but I’m not sure if this is the best way to go about it. Should a LookupSet be used when the source and destination dataset is the same dataset or is there a smarter/simpler way? My problem as best I understand is that the lookup finds the Max(TOTAL_CALLS.Value) in the source dataset and then matches it on and returns all TOTAL_CALLS.Values in the entire destination dataset (which includes all the data between the StartDate and EndDate). What I need is to have the lookup only match on the values that align with the category (day, week or month) which is being hovered over.



Is there are way to limit the scope of the destination expression in the lookup based on the current category being hovered over in the chart?



What I have got so far in the Series tooltip is (simplified for this example):



="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"), ", ")


which returns all the matching TOTAL_CALLS values in the dataset. What I need to work out is how to do something that equates to:



="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”>*, 

    Fields!LABEL_YYYY_MM_DD_HH24.Value), 

    "BusyHourStats"), ", ")


I’m not sure if I am wasting my time trying to get the results I need by using LookupSet. Is there a more appropriate function/method I should be using or can it be done this way?



Edit: Attaching image. I have also provided the exact tooltip expression below which aligns with the tooltip shown in the image:



=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 ")


As you can see when I hover the mouse over the category month "January 2018" it is returning the correct matching value for January "11am on 2018-01-12" but it is also returning the matching value for February "3pm on 2018-02-15". I'd like to only get the matches for the Month that is being hovered over (or Day or Week being hovered over depending on the category grouping chosen when the report was run).



Image:Report showing tooltip for January



Edit2: Added sample dataset below (csv). Note: removed many rows to comply to character limit in this post. The sample is exactly as used in the report (which is slightly different to the original post description).



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







share|improve this question
















I have a bar chart which shows the total number of calls received in a contact centre over time. The report user chooses how to group the chart by selecting a parameter “rangeView” which has the options: “D” (Day), “W” (Week), “M” (Month). The user also chooses the date range “StartDate” and “EndDate” which gets passed to the stored proc to retrieve the data.



The dataset “BusyHourStats” contains a row for every hour of each day in the date range and has the following columns:




  • LABEL_YYYY_MM_DD_HH24 (the hour of the day eg: “2018-01-01 14”)

  • LABEL_YYYY_MM_DD (day eg: “2018-01-01”)

  • LABEL_YYYY_WE (week eg: “2018-W01)

  • LABEL_YYYY_MM (month eg: “2018-01”)

  • TOTAL_CALLS


The chart series is Sum(TOTAL_CALLS).



The chart Category Group expression uses the rangeView parameter:



=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

)


This works as expected, grouping the results by Day, Week or Month. No problems with displaying the data in the chart.



Problem:



As a Series tooltip when you hover over the category (which could be a day, week or month) I need to display Max(TOTAL_CALLS.Value) and the day and hour (or days and hours) when that the max total calls occurred. Eg: “Busy hour: 159 calls on 2018-01-03 14, 2018-01-05 16.”



I have been trying to use LookupSet but I’m not sure if this is the best way to go about it. Should a LookupSet be used when the source and destination dataset is the same dataset or is there a smarter/simpler way? My problem as best I understand is that the lookup finds the Max(TOTAL_CALLS.Value) in the source dataset and then matches it on and returns all TOTAL_CALLS.Values in the entire destination dataset (which includes all the data between the StartDate and EndDate). What I need is to have the lookup only match on the values that align with the category (day, week or month) which is being hovered over.



Is there are way to limit the scope of the destination expression in the lookup based on the current category being hovered over in the chart?



What I have got so far in the Series tooltip is (simplified for this example):



="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"), ", ")


which returns all the matching TOTAL_CALLS values in the dataset. What I need to work out is how to do something that equates to:



="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”>*, 

    Fields!LABEL_YYYY_MM_DD_HH24.Value), 

    "BusyHourStats"), ", ")


I’m not sure if I am wasting my time trying to get the results I need by using LookupSet. Is there a more appropriate function/method I should be using or can it be done this way?



Edit: Attaching image. I have also provided the exact tooltip expression below which aligns with the tooltip shown in the image:



=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 ")


As you can see when I hover the mouse over the category month "January 2018" it is returning the correct matching value for January "11am on 2018-01-12" but it is also returning the matching value for February "3pm on 2018-02-15". I'd like to only get the matches for the Month that is being hovered over (or Day or Week being hovered over depending on the category grouping chosen when the report was run).



Image:Report showing tooltip for January



Edit2: Added sample dataset below (csv). Note: removed many rows to comply to character limit in this post. The sample is exactly as used in the report (which is slightly different to the original post description).



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




ssrs-2012






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 23 '18 at 1:40







zipzip

















asked Nov 22 '18 at 6:05









zipzipzipzip

63




63













  • I'm not sure I fully understand the problem, maybe a sample set of data and expected output would help... Having said that, if you expression more or less works you could try just changing the scope from "BusyHourStats" to the name of the category group. I'm not sure this will work in a chart but worth a go. The other option may be to add the data as an extra column in the SQL dataset and then just use that for the tooltip.

    – Alan Schofield
    Nov 22 '18 at 23:12











  • Thanks for your suggestion Alan. I tried replacing the "BusyHourStats" with the name of the category group "Chart12_CategoryGroup" however I get the following error: "The ToolTip expression for the chart ‘Chart12’ has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset. (rsInvalidLookupScope)". I will try to provide a sample set of data.

    – zipzip
    Nov 23 '18 at 0:48













  • I have added a screenshot showing the tooltip output. Am I able to upload a sample data set as a csv file?

    – zipzip
    Nov 23 '18 at 1:23











  • You can limit a scope in the expression for example with a grouping Max(Fields!TestField.Value, "MonthGrouping") or the whole dataset Max(Fields!TestField.Value, "DataSet1"). You can also check for a scope with the expression InScope(Fields!TestField.Value). Maybe one of this two will help you.

    – Strawberryshrub
    Nov 23 '18 at 5:34











  • Personally, I would do a lot of this on the server and let SSRS have an easier time :) I did a quick bit of t-sql to demo which you could use as your dataset. It only handle the max calls but its easy to extend to cover concurrent in the same way. I also changed one record so there are two with 85 total calls just so I got varied results. If updating the dataset query is possible then have a look at this saved query, (run it) it has additional columns with the busy hours for month, week and day level. It will need some tidying up but hopefully will be useful rextester.com/SEO17421

    – Alan Schofield
    Nov 23 '18 at 17:46





















  • I'm not sure I fully understand the problem, maybe a sample set of data and expected output would help... Having said that, if you expression more or less works you could try just changing the scope from "BusyHourStats" to the name of the category group. I'm not sure this will work in a chart but worth a go. The other option may be to add the data as an extra column in the SQL dataset and then just use that for the tooltip.

    – Alan Schofield
    Nov 22 '18 at 23:12











  • Thanks for your suggestion Alan. I tried replacing the "BusyHourStats" with the name of the category group "Chart12_CategoryGroup" however I get the following error: "The ToolTip expression for the chart ‘Chart12’ has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset. (rsInvalidLookupScope)". I will try to provide a sample set of data.

    – zipzip
    Nov 23 '18 at 0:48













  • I have added a screenshot showing the tooltip output. Am I able to upload a sample data set as a csv file?

    – zipzip
    Nov 23 '18 at 1:23











  • You can limit a scope in the expression for example with a grouping Max(Fields!TestField.Value, "MonthGrouping") or the whole dataset Max(Fields!TestField.Value, "DataSet1"). You can also check for a scope with the expression InScope(Fields!TestField.Value). Maybe one of this two will help you.

    – Strawberryshrub
    Nov 23 '18 at 5:34











  • Personally, I would do a lot of this on the server and let SSRS have an easier time :) I did a quick bit of t-sql to demo which you could use as your dataset. It only handle the max calls but its easy to extend to cover concurrent in the same way. I also changed one record so there are two with 85 total calls just so I got varied results. If updating the dataset query is possible then have a look at this saved query, (run it) it has additional columns with the busy hours for month, week and day level. It will need some tidying up but hopefully will be useful rextester.com/SEO17421

    – Alan Schofield
    Nov 23 '18 at 17:46



















I'm not sure I fully understand the problem, maybe a sample set of data and expected output would help... Having said that, if you expression more or less works you could try just changing the scope from "BusyHourStats" to the name of the category group. I'm not sure this will work in a chart but worth a go. The other option may be to add the data as an extra column in the SQL dataset and then just use that for the tooltip.

– Alan Schofield
Nov 22 '18 at 23:12





I'm not sure I fully understand the problem, maybe a sample set of data and expected output would help... Having said that, if you expression more or less works you could try just changing the scope from "BusyHourStats" to the name of the category group. I'm not sure this will work in a chart but worth a go. The other option may be to add the data as an extra column in the SQL dataset and then just use that for the tooltip.

– Alan Schofield
Nov 22 '18 at 23:12













Thanks for your suggestion Alan. I tried replacing the "BusyHourStats" with the name of the category group "Chart12_CategoryGroup" however I get the following error: "The ToolTip expression for the chart ‘Chart12’ has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset. (rsInvalidLookupScope)". I will try to provide a sample set of data.

– zipzip
Nov 23 '18 at 0:48







Thanks for your suggestion Alan. I tried replacing the "BusyHourStats" with the name of the category group "Chart12_CategoryGroup" however I get the following error: "The ToolTip expression for the chart ‘Chart12’ has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset. (rsInvalidLookupScope)". I will try to provide a sample set of data.

– zipzip
Nov 23 '18 at 0:48















I have added a screenshot showing the tooltip output. Am I able to upload a sample data set as a csv file?

– zipzip
Nov 23 '18 at 1:23





I have added a screenshot showing the tooltip output. Am I able to upload a sample data set as a csv file?

– zipzip
Nov 23 '18 at 1:23













You can limit a scope in the expression for example with a grouping Max(Fields!TestField.Value, "MonthGrouping") or the whole dataset Max(Fields!TestField.Value, "DataSet1"). You can also check for a scope with the expression InScope(Fields!TestField.Value). Maybe one of this two will help you.

– Strawberryshrub
Nov 23 '18 at 5:34





You can limit a scope in the expression for example with a grouping Max(Fields!TestField.Value, "MonthGrouping") or the whole dataset Max(Fields!TestField.Value, "DataSet1"). You can also check for a scope with the expression InScope(Fields!TestField.Value). Maybe one of this two will help you.

– Strawberryshrub
Nov 23 '18 at 5:34













Personally, I would do a lot of this on the server and let SSRS have an easier time :) I did a quick bit of t-sql to demo which you could use as your dataset. It only handle the max calls but its easy to extend to cover concurrent in the same way. I also changed one record so there are two with 85 total calls just so I got varied results. If updating the dataset query is possible then have a look at this saved query, (run it) it has additional columns with the busy hours for month, week and day level. It will need some tidying up but hopefully will be useful rextester.com/SEO17421

– Alan Schofield
Nov 23 '18 at 17:46







Personally, I would do a lot of this on the server and let SSRS have an easier time :) I did a quick bit of t-sql to demo which you could use as your dataset. It only handle the max calls but its easy to extend to cover concurrent in the same way. I also changed one record so there are two with 85 total calls just so I got varied results. If updating the dataset query is possible then have a look at this saved query, (run it) it has additional columns with the busy hours for month, week and day level. It will need some tidying up but hopefully will be useful rextester.com/SEO17421

– Alan Schofield
Nov 23 '18 at 17:46














0






active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53424794%2fcan-you-limit-the-scope-of-the-destination-expression-when-using-an-ssrs-lookups%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























0






active

oldest

votes








0






active

oldest

votes









active

oldest

votes






active

oldest

votes
















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53424794%2fcan-you-limit-the-scope-of-the-destination-expression-when-using-an-ssrs-lookups%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

Ottavio Pratesi

Image
.mw-parser-output .avviso .mbox-text-div>div,.mw-parser-output .avviso .mbox-text-full-div>div{font-size:90%}.mw-parser-output .avviso .mbox-image div{width:52px}.mw-parser-output .avviso .mbox-text-full-div .hide-when-compact{display:block} Questa voce sull'argomento ciclisti italiani è solo un abbozzo . Contribuisci a migliorarla secondo le convenzioni di Wikipedia. Ottavio Pratesi Nazionalità   Italia Ciclismo Specialità Strada Ritirato 1927 Carriera Squadre di club 1910-1911 Individuale 1912   Alcyon-Dunlop 1913 Globo-Dunlop 1914   Alcyon-Dunlop 1915-1922 Individuale 1923 Lygie 1924 Ostende 1925 Individuale   Modifica dati su Wikidata  · Manuale Ottavio Pratesi (Rosignano Marittimo, 1º gennaio 1889 – Livorno, 3 novembre 1977) è stato un ciclista su strada italiano, professionista tra il 1910 ed il 1925, era soprannominato il "Falco di ...
Read more

Tricia Helfer

Image
Tricia Helfer Tricia Helfer al Comic-Con 2015 Altezza 179 [1]  cm Misure 86-61-89 [1] Taglia 38 [1] (UE) - 8 [1] (US) Scarpe 40 [1] (UE) - 9 [1] (US) Occhi Azzurri [1] Capelli Biondo scuro [1] Modifica dati su Wikidata  · Manuale Tricia Helfer (Donalda, 11 aprile 1974) è una supermodella e attrice canadese, conosciuta principalmente per il suo ruolo di Numero Sei nella serie televisiva Battlestar Galactica . Indice 1 Carriera 2 Filmografia 2.1 Cinema 2.2 Televisione 2.3 Video musicali 2.4 Doppiatrice 2.4.1 Cinema 2.4.2 Televisione 2.4.3 Videogiochi 2.5 Produttrice 2.6 Agenzie 3 Doppiatrici italiane 4 Note 5 Altri progetti 6 Collegamenti esterni Carriera | Di origine inglese, tedesca, norvegese e svedese, nel 1992 Tricia vinse il concorso di "Ford Models Supermodel of the World", organizzato dall'agenzia Ford Models e fu poi assunta dall'agenzia ...
Read more

15 giugno

Image
gennaio  · febbraio  · marzo  · aprile  · maggio  · giugno 2018  · luglio  · agosto  · settembre  · ottobre  · novembre  · dicembre Ve Sa Do Lu Ma Me Gi Ve Sa Do Lu Ma Me Gi Ve Sa Do Lu Ma Me Gi Ve Sa Do Lu Ma Me Gi Ve Sa ← 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 → Il 15 giugno è il 166º giorno del calendario gregoriano (il 167º negli anni bisestili). Mancano 199 giorni alla fine dell'anno. Indice 1 Eventi 2 Nati 3 Morti 4 Feste e ricorrenze 4.1 Civili 4.2 Religiose 5 Altri progetti Eventi | 763 a.C. – Gli Assiri registrano un'eclissi solare 923 – Battaglia di Soissons: re Roberto I di Francia viene ucciso, re Carlo il Semplice viene arrestato dai sostenitori del duca Rodolfo di Borgogna 1094 – Reconquista: Valencia cade nelle mani di El Cid 1215 – Re Giovanni d'Inghilterra appone i...
Read more