I have an excel spreadsheet that I am linking to our ERP system. The spreadsheet contains a forecast number for each product code for each customer (in a specific region) by quarter. The goal is to have the report display the forecasted number along with the actual quantity shipped for that quarter for that product for that customer.
Product Code Forecast Q1 Sold Q1
Product Code Forecast Q2 Sold Q2 (etc...)
I was successful when creating a subreport for the quantity sold value and tying that (by a formula to return Q1 or Q2 or Q3 or Q4) to the same field in the forecast spreadsheet
The issue is with this that if there is nothing forecasted for a specific quarter in the excel spreadsheet (main report) it will not return a quantity shipped value if product was shipped in that quarter that was not forecasted (on the spreadsheet database). I can not figure out how to change the join of a subreport
Essentially, I only want records that have a value greater than 0 either for the forecasted amount or the actual quantity sold. If I use the tables that have the quantity sold information in them in the main report, I can tie the customer field and the product field but not the Quarter field.
Does this make any sense?