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

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.

• Has anybody come across a similar issue? Mar 18, 2014 at 22:45

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){
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.

• Thanks :) Yes it looks like I will need to add something to the data model or do this in VF. Can you please elaborate on how the helped class could be a task/event? Mar 20, 2014 at 15:03
• That library is awesome thanks. Also this report will end up in a dashboard, would you suggest I use this library or use standard VF components? Mar 20, 2014 at 16:47
• Check google-developers.appspot.com/chart/interactive/docs/gallery/… too, it's one of the new chart types available. Mar 20, 2014 at 23:06
• Thanks currently I was thinking of using just an Apex:Datatable to do this but am having trouble with creating the week buckets as columns. Perhaps I need to just assume a set number of weeks to display the data for. Mar 21, 2014 at 18:39
• Hm, sounds messy. I'd say you need 2 loops (can be 2 repeats, can be repeat + datatable)... Because you still need to generate "something from nothing" - more than 1 table entry for each record that spans more than 1 week... The inner loop would have to be through weeks (up to you to figure min/max...) I sense lots of work. Consider posting the code you have as edit to this Q, then we migh assist further. But I don't think I'd go that way. You'll generate huge `<table>` that takes forever to render and page size will be in MB... Mar 22, 2014 at 0:42

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.