Question about Matrix report - How to group by two dates(date range)

Question

Hi I am not well versed with SF reports and wanted some help with a matrix report that I am trying to put together.

Lets say I have a custom object to store records that have a start date and end date. This object is a child object in a Master-Detail relationship with another object. The custom object also has a percent field to store Distribution.

Lets say I have two records:

1. Record 1
Start Date: 02/17
End Date: 03/15
Distribution % = 60%

2. Record 2
Start Date: 03/17
End Date: 04/3
Distribution % = 100%

Right now in my report, I have a column grouping for End Date and have grouped dates by Calendar week. The Distribution percentage field has been added as a summarized row and as an average.

For the first record for example, I expected to see 60% marked in all the cells for every week starting 03/16-3/22 and so on.

But the 60% appears only under the column 3/30 - 4/5.

How it should look

            2/16-2/22 2/23-3/1 3/2-3/8 3/9-3/15  3/17-3/22  3/23-3/29 3/31-4/5...................                
Record 1    60%        60%       60%      60%
Record 2                                            100%      100%      100%

How it looks

            2/16-2/22 2/23-3/1 3/2-3/8 3/9-3/15  3/17-3/22  3/23-3/29 3/31-4/5...................                
Record 1                                  60%
Record 2                                                                100%

So how can I change this to show that the distribution is 60% in all the cells beginning from the week containing the start date to the week containing the end date.

I hope this made sense. Thanks.

Answer 1

I don't think it can work for you. You're asking for 1 record to appear in several groups. Imagine putting the record count into the matrix instead of the distribution - you're expecting it to count 1 record 4 times?

You will need 1 entry per group (in your case group = week) for example. Either in your original object or in some helper object (created / synchronized with a trigger?). The helper could be something custom or maybe a simple Task/Event (assuming "Allow Activities" is checked).

If that's not working for you you can always use visualforce to report. I've made some cool Gantt charts using http://taitems.github.io/jQuery.Gantt/ as baseline.

---

Edit

A helper class/trigger to create N fake tasks, one for each weak on the parent record could be built along these lines (this is untested and a rough idea only for inserts...you'd have to handle updates that move the ranges as well, either in trigger or with some "recalculate" button):

// in after insert trigger
List<Event> events = new List<Event>();
for(My_object__c o : trigger.new){
    for(Date d = o.Start_Date__c; d < o.End_Date__c; d += 7){
        events.add(new Event(
            WhatId = o.Id,
            Subject = 'Reporting helper, please ignore',
            Type = 'Other',
            ActivityDateTime = d,
            DurationInMinutes = 1,
            Distribution__c = o.Distribution__c
         ));
    }
}
insert events;

The other option - a Visualforce dashboard component... I admit I was embedding these Gantt charts on record's detail page or used as standalone pages (custom tab or just link to it somewhere). You might need lots of space that makes it impractical to add to a dashboard.

I can't provide too many details as I don't hold the IP rights to it anymore. With VF you'll be able to code it in html without any fancy javascript library - but the resulting page might be huge and take forever to load depending on how you'll attack it (big table with lots of <td>? some divs?). I've used that plugin married with StandardSetController (so you get natural controls like previous / next, how many rows per page and if people want to filter stuff - you can use listviews or homemade queries). I also have a hidden divs area (built with <apex:repeat>) to which I refer to display the hover balloon when somebody moves over a bar. From what I remember you might have some issues on iPads and in IE < 8 though. Takes more time to build but I'm very happy with my results.

P.S. From what I remember a dashboard with component like that can't contain dynamic filters and will ignore the dashboard's running user settings - so whole security boils down to how you wrote your queries and whether do you have "with sharing" in the controller extension.

Answer 2

From what I can understand you are trying to get the distribution to show for a date range, ie you want the same distribution to be shown for the entire range (Start Date- End Date)

The report generated is accurate based on the parameters you have provided: 1) You've selected the End Date as the column 2) You've summarized the Distribution for the End Date

The report is showing you 60% distribution only in the range "3/9-3/15" since your 1st end date ( 3/15) falls in that particular range. Its the same thing for the 2nd record.

As far as I know the only way to implement the functionality in Salesforce to associate data with a custom date range (Without getting into development) is using the built in Products and Schedules. It allows you to define your own schedules for a particular product and then see reports based on these schedules. Look at this link for more information on how to set this up.