Sales report by state
Is there a way to run a report, summary or detailed, that will show sales per customer but also have a column or similar way to group the customers by state?
Is there a way to run a report, summary or detailed, that will show sales per customer but also have a column or similar way to group the customers by state?
2 Posted by ray white on 06 Jan, 2012 07:08 PM
A custom report would need to be written to do this. This report would be external to the AccountEdge program.
If you are using AccountEdge for Windows, it would have to be created with Crystal reports using the ODBC driver.
If you are using AccountEdge for Mac, it would have to be created with FileMaker using Applescript.
Ray White
Certified Consultant
http://www.raywhiteenterprises.com
3 Posted by greenwizees on 22 Feb, 2012 11:34 PM
It would be nice if I could just choose State as a report field instead of Billing address.
4 Posted by Dan R on 23 Feb, 2012 03:55 AM
You could add the state as a prefix on your card ID's or use one of the Custom Lists or Custom Fields available on cards for the state. Add the field/list etc to the report. Send to Excel and with some tweaking sort by that column to group them. It will be a little tricky to keep the transactions and the customer name line and the subtotal lines grouped for each customer, but it is doable. If you use the Custom Lists or Custom Fields do an export customer cards - in excel copy and paste the address state column to the new list/field column, save and import the updated card info (choose Duplicate Records: Update Existing Records on import on only match the minimum needed). If it is a custom list you must first define all the states for that list or else they won't import.
The widgets sample file has one custom list set up as "region" - There are only a couple of entries, but it is an example.
:-)
Dan
5 Posted by greenwizees on 23 Feb, 2012 06:13 PM
I ended up just exporting the report to Excel with the billing address on the report. Then I moved all the cells in the address column up one cell so that the line with the state was on the same row as the dollar amount. Then at the bottom of the spreadsheet I did sumif calculations with ", CA" (example) as the criteria. I had to move a few address more than one sell if more than one line was used in the street portion of the address, like if there was a suite number or PO box.
6 Posted by greenwizees on 23 Feb, 2012 08:40 PM
Low and behold, I can't use the report I exported, Sales (Customer Detail), because for some reason it doesn't include shipping charges even though they are linked to my normal sales revenue account. Any ideas why?
Support Staff 7 Posted by Jamie on 23 Feb, 2012 10:20 PM
If they were added to the invoice under the freight box, there is another field that needs to be selected when running your customer sales detail report to include the freight. You should then be able to make another formula to add up the subtotal + freight to give you the total including shipping.
8 Posted by greenwizees on 23 Feb, 2012 10:31 PM
Dan R - I did as you suggested with exporting to Excel and added the state to Custom Field #1 and imported back to AE. All of the cards now have the state in the custom field #1 on the details tab. But when I run the Sales (Customer Detail) report that column is blank for every customer. Any ideas?
Thanks Jamie for the freight tip.
9 Posted by greenwizees on 23 Feb, 2012 10:48 PM
However, if I just run the Sales (Customer Summary) report the custom field #1 column is populated
10 Posted by Dan R on 24 Feb, 2012 02:29 AM
The detail report contains more than 1 set of custom fields. The first is for items I think. Go down the list further and add the next custom field #1. To avoid confusion rename the customer custom field #1 to "State" or whatever Lists: Custom Lists & Field Names: Customers.
:)
Dan
11 Posted by greenwizees on 24 Feb, 2012 04:26 PM
Awesome!! Thanks so much Dan!! This is my first year end using AE and suffice it to say it is pretty different than QB in some areas.