create a report that shows individual items by payment type (cash or credit)

[creatorofstuff]

I just used Square for the very first time yesterday. Unfortunately, I have the desktop version of Quickbooks, so I can't use the free app that integrates the two, so I'm going to create one receipt showing all of the products purchased with cash, and another one showing the products purchased with credit card. 

The problem is, I can't seem to create a custom report that shows both individual products and whether they were paid for with cash or credit card. It seems that I can only see total credit card sales, and total cash sales. 

Please help! I guess I could go through each transaction individually (cumbersome!) but it would be nice to dump it to an Excel file, or at least see it all in one place (less cumbersome). 

If you have any tips on doing this whole process with Quickbooks a better way, please let me know.

[creatorofstuff]

After posting this, I discovered that you can see a version of what I'm asking, which is better than nothing. I'd still like an actual report, so it is listed by product. This one shows all the products purchased in a single transaction on one line, so that if there are too many, you see "..." instead of the complete list of products. But it's a start. 

Incidentally, I know you are aware of the fact that you can't see what you are typing in the text box, but I wanted to also mention that I clicked on "preview" to see my message before sending it (since I couldn't see what I was typing) but the message went away. Another bug. 😞 

Thanks! 

[Sean]

@creatorofstuff - Glad to hear you were able to track these details down. As a follow up, would you mind clarifying the bug you're talking about? This was when you were trying to reply in the Seller Community? 

[creatorofstuff]

I didn't exactly track the details down in a readable fashiion. If you could pint me to a report, it would be helpful. 

As for the answer to your other question, I tried to add my second comment (effectively a reply, I guess), and the comment box already has text in it, so when you try and type your comment, you can't see it because it is superimposed over the text that was already there. So, after I typed my comment, I clicked on "preview", to see if I made any typos, but instead of showing me a preview of my comment, it was blank, and my comment was gone. I had to go back and re-compose. 

Anyway, I'm still on a quest for an actual report that shows all of the products, and how they were paid. Can you help me? (The thing I found is problematic for the reason I mentioned in the above post.)

[Sean]

@creatorofstuff - Appreciate the follow up. We have an open case to address the issue you described with replying. Hopefully it will be resolves soon! As for your other question, have you considered exporting your transactions from the Transactions tab of the Square Dashboard to a .CSV? This will allow you to drill down on a granular level. 

[creatorofstuff]

Thank you for the suggestion. I just tried that. It shows a ton of information, but not whether the payment was cash or charge. This is just unbelievable. It's only helpful if I can see my cash sales (by item) and my credit card sales (by item). There must be a way.

Do you have any other ideas? 

[Sean]

@creatorofstuff - The spreadsheet can be a little tough to read at first (information overload), but if you check out the headers, A1 - AK1, they'll each reflect different bits of information, including the tender type. Would you mind taking another look? Cash sales should be listed under column N.

[creatorofstuff]

I sure appreciate you sticking with me, by the way. 

I hear what you are saying, but none of those reports have exactly what I need. I think I'll have to do this by hand, unfortunately. I'm trying to create a receipt with ALL cash sales on one receipt (itemizing the products), and another receipt with ALL credit card sales on the other receipt (itemizing the products). Maybe I'm going about this all wrong. I'm an artist, not an accountant, unfortunately. But I need to track the inventory and sales. 

Anyway, you've been terrific, and I appreciate it so much. I'll talk to an accountant and see what I can do. Thanks. 

[Sean]

@creatorofstuff - You're welcome. Happy to help. 🙂

[vadershero]

I would love to hear your outcome. I am needing all of this in a report just like what you are saying.
I have a large event coming up where I will run consignment sales for people. Each seller must pay the the fee (removed from thier sales totals) for credit card sales. So I must break down the report for each seller and each item they sold on credit card.

I did one in February, but didn't have many consignments then. Only about 5. Upcoming event I'll have about 75. Very important to get that information. I tried to find that break down in February until my eyes went dead and computer stood a high chance of being abused. I want to get this resolved though before May when my next event is. PLEASE HELP

[vadershero]

Just to clarify. I did find the report exports on the transaction tab under the sales area. The problem is, as it seemed the other individual was dealing with, each item in a transaction is combined into one total. I would like to have an "individual item report", with a cash or credit sale attached. That way, rather than as it currenly is, I don't have one line with items from person 3, person 48, and 3 items from person 7 that I have to now manually break out.

It would work great if I was needing to look at an individual transaction, but I need to look at individual items within that transaction instead.

The sales summary is great for each item, but does not have the ability to know whether that item was paid with cash or credit.

This would speed payment at the end of the 3 day sales event. We sell about $15,000 during that event, so this could equate into many hours of breaking out individual sales.

I've attached a picture of what I currently have in the transactions CSV. Just giving me a line of titles makes it difficult. 😞

2X "title" is not helpful here

[creatorofstuff]

Sorry @vadershero for the delay in responding. I never really resolved my problem. I ended up tackling the bookkeeping part of it differently on the other end, since the report didn't break things down the way I wanted. My sales are on a much smaller scale than yours, so I can see why it's a bigger deal for you. 

Good luck! I'm finding Square to be good in some ways (the support is good) and limited in others (the most obvious being the inability to delete a transaction). 

[hundleybk]

Hoping this might help here.

I manage photography sales and I need to track whose pictures have sold, not who sold them.  The best option I found was to utilize the Dining Option tool. (found under items/settings)  You can customize the options and create a custom report that lets you see which persons items were sold and the total sales, but it does not calculate the % they receive from the sale. I'm sure you could easily export the data to a spreadsheet and calculate commission. Unfortunately, you cannot export a custom report, so to export the data, you have to select Item Sales and use Advanced Options -> Display = Dining Option (filter = dining option doesn't give the correct results). The only other problem I have with this, is you can't print the report without expanding all the categories, but it is nice that it shows you the exact items sold under that category. I would prefer the option to print the summary list only. I guess screenshot and print will work for this.  I hope this helps anyone who is struggling to find a solution to track sales with a different category. 

[kygirlnco]

I too need a report like this.  It used to be available a couple of years ago. 

The transaction report shows all the items in a transaction.  So if there are multiple items per transaction so you can't see how much of the total is for each item.  

I need a report that shows total sales for each item, how much was purchased on a credit card and how much the fees were.  There should be a way to do this.

[LWood]

I had the same problem (if I understand your question correctly) and have solved it by using some Excel Jiu-jitsu:

1) download a CSV of the Transaction CSV and the Item Detail CSV for the dates you want

2) copy the data from each CSV respectively into a Transactions tab and Item tab in a master spreadsheet.

Now the fun part using the master spreadsheet:

3) In the Transaction Tab cut and paste the Transaction ID from column W to Column A (you need to do this so the Vlookup below has a common data field)

4) Create a column in the Items tab for Payment Type in Column W

5) Create a VLookup function in the B row of the newly created Payment Type column. The formula looks like this: =VLOOKUP(N2,Transactions!$A$2:$AH$1000,24,FALSE) Note: this is the forumla Excel uses to pull in the payment type used with each transaction from the Transactions Tab. If you need help figuring it out then use the Excel help.

6) Copy this forumla down the Payment Type column for each row in the Items Tab

You're almost done:

7) In the Items tab create a Pivot Table

😎 In the pivot table put Items in Rows, Payment Type in Columns, and Net Sales in the Values and you should have your answer.

Then go have a nice cocktail.

There are a bunch of steps but once it's set up then you just have to cut and paste updated transaction and item data from Square to make it work. 

Good luck!

[kygirlnco]

Shouldn't have to be an Excel expert to get this report.

[JustinC]

Hey there @kygirlnco !

There isn't one report that details all the info you're looking for. Perhaps you can pull an Item Sales Report and use the filter Display by Payment Method that breaks down each item's sales by cash, card, other tender, etc. However, this report doesn't include fees. For that, you'll need to export a Transactions CSV report. 

For now, I'll tag your post as a feature request, as we're constantly implementing filters and features to make Reporting more layered and streamlined. Thanks for sharing !