I am running an export query with all the invoice items for a specific timeframe.
However I have many duplicated values that should not be there. After some digging, I found out that this happens due to our Rate Plan.
Is there any way I can deduplicate my invoice items knowing about this condition?
I've tried many alternatives on the where clause, with no success.
Solved! Go to Solution.
All of the data sources, including invoice item, report all the selected fields for all returned rows. So if I'm querying invoice items and inlcude the rate plan name in the results AND I have a subscription rate plan that has two charges with each charge generating an invoice item then I'll see the rate plan name appear (repeated) on each invoice item row, so it'll appear twice. So if we have a subscription name of 'Sub-01', a rate plan of 'RP-01' and two rate plan charges, 'RPC-01' and 'RPC-02' which then generate invoice items on INV-123 of $1 and $2 and then run a query of these columns for INV-123 you'll see something like the following:
WIth our reporting tool or with the API using ZOQL you can sum the amounts per invoice or per invoice per rate plan, for example to get:
But is this info helpful? In our reporting tool you'd leverage our Summary report feature creating a report on the invoice item data source. If you take a look at the OOTB report, 'Gross billing by product over time' you can see an example where the report sums invoice amounts per product, but you could customize this to do a 'per rate plan' summary instead if you wished.