HOW TO deduplicate Invoice Items what have multiple Rate Plans?

[heberbrandao]

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.

[Richard · Accepted 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:

Sub Name	RatePlan	ChargeName	InvNum	Amount
Sub-01	RP-01	RPC-01	INV-123	$1
Sub-01	RP-01	RPC-02	INV-123	$2

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:

Sub Name	RatePlan	ChargeName	InvNum	Amount
Sub-01	RP-01	RPC-01	INV-123	$3

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.

[heberbrandao]

I managed to pull the right results by running my query Grouping the rows by Invoice Number. 🙂