[bones]

I am trying to run a report that does the following.

Show me only the customers that are YES to a custom field (this works)

Of those filtered from above, I want to see the dollars that these customers spent with us. This works, but it drops customers from the report who do not have any transactions at all.

The problem is that if there are customers that haven’t spent ANYTHING with us, they are not showing up in the report at all. I would prefer to see them in the list with a $0 total.

Any help is appreciated.

Thanks
This is a cached copy. Click here to see the original post.

[evan_goldberg]

RE: Customer report – show customers with no transactions

How are you showing the transaction total currently?

[bones]

Well, the only way I know is to use the Transaction: Amount field. Is there a different/better way?

[evan_goldberg]

RE: Customer report – show customers with no transactions

Just adding that field should not be filtering out any customers. Do you have a filter on transaction type, e.g.?

[bones]

When I do a basic report on Company, filtering out ONLY the Yes/No Checkbox (custom Field) That i mentioned, it pulls the list of all 77 records – which is correct. As soon as I add the Transaction Amount to the report, it filters out the companies that do not have any transactions. Logically, it seems to make sense that the report would do that, because you can’t have an amount on a transaction if it doesn’t exist. But I would prefer that it just state $0 for the total.

I am also grouping them together as a sum, but that shouldn’t matter either, right?

[evan_goldberg]

RE: Customer report – show customers with no transactions

That’s not the results I observe. I get the same # of customers and it includes customers with no transactions, so I am not sure what is different. You are sure there is no transaction field being used in your filters, either standard or summary criteria?

[bones]

Well, the Date filter is there, from the “Sales” Report Component.

I guess I am starting this report from the wrong area?

[evan_goldberg]

RE: Customer report – show customers with no transactions

Sorry – I thought you were using search. That’s my area of expertise – someone else will need to chime in about using standard reports in this way.

[bones]

Is this something i can do with a saved search?

[evan_goldberg]

RE: Customer report – show customers with no transactions

Well, it gets a bit tricky actually because you need to filter out certain transaction types and/or filter to mainline, and then it becomes tricky to maintain the rows with no transactions. Generally the best way to do it is with a formula result field, whereby group by name and sum a formula such as

case when {transaction.mainline}=’*’ and {transaction.type}=’Sales Order’ then {transaction.amount} else 0 end

If you do this then it will show 0 for customers with no transactions and the sum of sales order amounts for those with transactions.

[Author]

Posts