Ask Search:
John DemitorivJohn Demitoriv 

Opportunity Report of all Opportunities with all Opportunity Products that contain Product X

Hi There, 
 

I need to create a report that list all Opportunities and all Opportunity Products for all Opportunties that have Product X. 

So if an Opportunity has Product X, Y, and Z the report should show all 3 Opportunity Products.
 

I am doing this to test a hypotheseis that one of our products is driving up our ASP so I want to look at the value of all opportunites that have this product. 
 

I tried creating a roll-up summary but issue is the roll-up summary filter does not work on formula fields and both the Product Code and the Produt Description are added to the Opportunity Product via a formaula  (PricebookEntry.Product2.Name ). Other standard SF fields are lookups and don't show up in the filter.
 

Only way I could find to get the Product Code and Product Name onto the Product Line Item (Opportunity Product record).

And of course the RUS does not like formulas.....
 

So, any thoughts?

 

Thank you.

John

PS - We are using Steelbrick CPQ and the Opportunity Line Items are Synched from the Quote Line Items.....if that matters.
 

PS -- I know how to do with in a custom report but this requirement is also behind other use cases we have.

Best Answer chosen by John Demitoriv
Steve MolisSteve Molis
PS.  All you need is a csv file with Opportunity ID in one column and a 1 in the other column to update the Checkbox field from unchecked to checked

All Answers

Steve MolisSteve Molis
Could you create a non-formula field and update it using Process Builder or Workflow?  Then you could use that as your Report Filter
John DemitorivJohn Demitoriv

Thanks Steve, I could going forward but issue is all the existing records.....I thought about doing a mass update for the existing records and then adding the workflow  going forward....That may be the way to go........
 

There is no way for force a Process Builder process to run on all existing records without editing them, right? So for the massudpate : I guess I run a report of Opportunites with Opportunies Products including the Product Line Item ID, export to Excel. Add the values I want to the Opportunity Line Item columns, and then run an update with the data loader.  Then add the Process Builder Flow


This is the kind of thing if that would be pretty easy if I could attach a SQL database to SF........ (-;

Thanks for your help.

-John
 

 

Steve MolisSteve Molis
Yeah, you'll need to run a report and Mass Update the existing Opportunties using a csv field and the Dataloader
Steve MolisSteve Molis
PS.  All you need is a csv file with Opportunity ID in one column and a 1 in the other column to update the Checkbox field from unchecked to checked
This was selected as the best answer
John DemitorivJohn Demitoriv

Thanks Steve,
 

I am going to add a process builder to copy the Product Code to the Opportuntiy Product into a Text feild,  and then use data loader to run the mass update. This way I will be able to use the RUS for other Products....

Thanks for the help...

-John

John DemitorivJohn Demitoriv

Hey Steve,
 

The Opportunity Product Line Item ID is not exposed in custom reports. I noticed that the Product Code Text Field is exposed in the RUS filter but the Line Description is. Both are the same feild tpyes, TEXT 255. 
 

Any thoughts on how to get the Product Line Item ID via a report?
 

Also, any  thoughts as to why the Product Code is not exposed in the RUS Filter? Field Assessibity is ok, checked that....I am an Admin of course.
 

so....what do you think?

-JohnUser-added imageUser-added image

John DemitorivJohn Demitoriv
I added this field CASESAFEID(Id)