Ok, so let me explain my use case:
We use Salesforce for our Custom Support Cases or tickets or whatever you want to call them.
Goal:
I need a bar chart report that will tell me how many cases were open on any give day (be it past or present).
I am using the Salesforce connector to get the following data:
LOAD AccountId as SFAccountId,
CaseNumber as SFCaseNumber,
Category__c as SFCaseCategory,
ClearQuestID__c as SFCaseBugId,
ClosedDate as SFCaseDateClosed,
DayStart(ClosedDate) as SFCaseDayClosed,
WeekStart(ClosedDate) as SFCaseWeekClosed,
MonthStart(ClosedDate) as SFCaseMonthClosed,
QuarterName(ClosedDate) as SFCaseQuarterClosed,
ContactId as SFCaseContactId,
CreatedById as SFCaseCreatedById,
CreatedDate as SFCaseDateCreated,
DayStart(CreatedDate) as SFCaseDayOpened,
WeekStart(CreatedDate) as SFCaseWeekOpened,
MonthStart(CreatedDate) as SFCaseMonthOpened,
QuarterName(CreatedDate) as SFCaseQuarterOpened,
DaysSinceModify__c as SFCaseDaysSinceLastModified,
End_Company__c as SFCaseEndCompany,
Id as SFCaseId,
IsClosed as SFCaseIsClosed,
IsEscalated as SFCaseIsEscalated,
OwnerId as SFCaseOwnerId,
Priority as SFCasePriorty,
Severity__c as SFCaseSeverity,
Status as SFCaseStatus,
Subject as SFCaseSubject,
Support_Pack_Version__c as SFCaseProductServicePack,
Team__c as SFCaseTeam,
True_Modified_Date__c as SFCaseTrueModifiedDate,
Type as SFCaseType;
SQL SELECT AccountId,
Age,
CaseNumber,
Category__c,
ClearQuestID__c,
ClosedDate,
ContactId,
CreatedById,
CreatedDate,
DaysSinceModify__c,
End_Company__c,
Id,
IsClosed,
IsEscalated,
OwnerId,
Priority,
Severity__c,
Status,
Subject,
Support_Pack_Version__c,
Team__c,
True_Modified_Date__c,
Type
FROM Case;
So basically, I have cases and their open and closed times.
What I can do:
1. Find out how many cases were opened a certain day.
2. Find out how many cases were close a certain day.
What I can't figure out
1. How to I have a report where I click on a day and it shows me the cases that were open that day (even if they weren't opened or closed on that specific day). So image the each tech has 20 cases that range in age from 1 hour to 3 months. I need to see each day how many cases were in the open state (not opened that day but any case that on that day was in the opened state). So I have the date range, I have the SFCaseDateCreated and the SFCaseDateClosed fields. So I want to click a date and if the data is inbetween those field, the case is counted.
2375 2346
[] []
[] []
11/1/09 11/2/09
Any suggestions on how to click on a day and see the total number of cases that were already open at the start of the day by SFCaseTeam?
