0

I think that it might be easy, but I cant figure out how to make it work.

I have a column chart with tickets grouped by time (rounded to 15 minutes)

  • X Axis - Time Group (Rounded to 15 minutes)
  • Y Axis - Count(Tickets)

I'd like to show "ticket number" and "title" of all grouped tickets in one column as a tooltip

I have already tried lookup / lookupset / miltiplelookup - it didn't work. I have also tried to join the filed -> =Join(Fields!IB.Value, ", "). That didn't work either. Im just getting #error by hovering over my column. If i'm just displaying the field, i'm getting only one value =Fields!IB.Value.

Any thoughts?

p.s. im Using Report-Builder and Ssrs2016

This how it may look like:

enter image description here

As i mentioned earlier, I have used some rounding function to group Tickets by the time Column. This is the code:

=Mid(Fields!time.Value,1,3) & Replace( Mid(Fields!time.Value,4,5),

Mid(Fields!time.Value,4,5),
    Switch(

Cint(Mid(Fields!time.Value,4,5)) >= 00 And Cint(Mid(Fields!time.Value,4,5)) <= 14, "00" ,

Cint(Mid(Fields!time.Value,4,5)) >= 15 And Cint(Mid(Fields!time.Value,4,5)) <= 29, "15" , 
   
Cint(Mid(Fields!time.Value,4,5)) >= 30 And Cint(Mid(Fields!time.Value,4,5)) <= 44, "30" ,
    
Cint(Mid(Fields!time.Value,4,5)) >= 45 And Cint(Mid(Fields!time.Value,4,5)) <= 59, "45" ))
Dmytro
  • 372
  • 2
  • 12
  • I believe the solution involves LookupSet wrapped in a Join .... I recall doing something like this in the past, but it's been a while. LookupSet should return the type of object that can be "join"-ed. Obviously, a reference to the grouped / aggregated fields cannot be a direct parameter of Join() because it's not the right object type. – Craig Mar 23 '21 at 11:16
  • @Craig I dont rlly understand, how can I make a reference to a grouped data? E.g.: I'm using `=lookupset(1, 1, Fields!Title.Value, "DataSet1")` -> it's getting me all the data in the chart (but I need only the data which was used to generate this particular column) – Dmytro Mar 23 '21 at 11:36

2 Answers2

1

As you probably guessed, your lookupset() will not work as it's compares 1 to 1 and therefore returns all values.

Assuming your time group column is called TimeGroup and your ticket number and title columns are called 'TicketID' and 'TicketTitle' then..

You need to change the lookupset to something like

=JOIN(
    LOOKUPSET(
        Fields!TimeGroup.Value,
        Fields!TimeGroup.Value,
        CSTR(Fields!TicketID.Value) & ": " & Fields!TicketTitle.Value,
        "DataSet1"),
        "," + vbcrlf
    )

Time group will always be evaluated in the context of the tooltip, so if you hover over time group A, time group will be evaluated as A and therefore the lookupset will only return values matching A.

The return part of the expression converts the ticket number to a string, adds a colon and then appends the title.

We wrap the whole thing in a JOIN() to combine the results of the LOOKUPSET array into a single string using , and carriage return /line feed between each instance so that the resultS show 1 per line.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • That's the problem that I have. I dont't really understand what values should I use in the first 2 parameters. As I understood it; `lookupset` loops through all values and tries to find matches in it (1st parameter is my array, 2nd is the array in dataset, 3rd one will be shown, if there is a match). In my chart I have chosen `count(TicketID)` as a Y-Axis Value Therefore Ticket-IDs are to be matched, right? In your answer you have used TimeGroup, but there is no such column, I have just rounded TimeCreated Column to 15 minutes and groupt IDs by it – Dmytro Mar 23 '21 at 12:51
  • Just edited my question to show my grouping algorithm – Dmytro Mar 23 '21 at 12:56
0

I have solved this problem with the function from @Alan Schofield using TimeGroup, but for that I hade to redefine my group settings:

  1. Add custom field to my DataSet with the group function I mentioned in my question
  2. Group my ColumnChart by this field
  3. Use count(Fields!TimeGroup.Value) for the Y-Axis (instead of count(Fields!TicketID.Value))
  4. Join the Group from the Chart with the Group in DataSet and listing all results in the tooltip

P.S. I hate this solution, but it works well

Dmytro
  • 372
  • 2
  • 12