+ Start a Discussion
ashleysisti1.3911321512638052E12ashleysisti1.3911321512638052E12 

Need help with rolling up OLI schedule amounts for date range specific to the Opportunity...

I'm in desperate need of some sort of development solution to solve a business issue where I only want to report on dollar amounts from Opportunity Line Item Schedules where the date range falls in a criteria that is specific to each Opportunity. 

For example, Opportunity with close date 1/31/2014. Contract Start date is 2/1/2014 and Contract End Date is 1/31/2017. Opportunity has 1 product with the following schedules:

1/31/2014: $100.00
1/1/2015: $60.00
1/1/2016: $60.00

In my closed won Opportunity report, I need ONLY the amount from the first billing period, (regardless of the date) or for the first four billing periods if it was quarterly billing instead of annual, or from the first 12 if was monthly billing. I can't do a rollup of Schedule $$ on OLI and I can't create a custom field on OLI schedule to define which billing period it is in the order (1,2,3, etc) and use that in the report. 

Any ideas for how to populate a field on OLI called "1st Year Contract Amount" from all the billing periods for the first year of the contract? I'd greatly appreciate the help! 
Ashish_SFDCAshish_SFDC
Hi Ashley, 

Currently this is not possible, 

Here is a work around, 


Whenever I need to report on Opportunity Products /Line Item schedules, I usually do it in Excel.  Sometimes I run separate Opportunity and Opp line item reports and link them via VLookup or Sumif functions.  Other times I run a Opp Line Item report with all of the repeating Opportunity level fields and use an Excel Pivot table to group the data as needed.

The Connect for Office Excel add-in is great for importing single or multiple reports into Excel workbooks so that formulae and pivot tables are instantly reused.


https://success.salesforce.com/answers?id=90630000000gzhuAAA

Vote for the Idea, 

Consider Opportunity Line Item Schedules for your product roadmap

https://success.salesforce.com/ideaView?id=08730000000BqwPAAS


Related Idea: 

Opportunity Forecast Report with Schedules

https://success.salesforce.com/ideaView?id=08730000000BrTp


Regards,
Ashish

ashleysisti1.3911321512638052E12ashleysisti1.3911321512638052E12
Excel is not an option for management reports when we have spent over $200K on Salesforce. The whole point is to NOT run reports in Excel anymore - that was part of the motivation for purchasing Salesforce in the first place. This requirement is for basic pipeline reports for our team. I'll definitely upvote the line item schedules idea, because it's ridiculoust that this object is completely not customizable.
Ashish_SFDCAshish_SFDC
Hi Ashley, 


We understand it looks very simple but there is lot in the background that has to be worked to get this kind of feature work. 

Kindly contact your Account Executive with this requirement. 


Regards,
Ashish