Choosing which fields appear in the Item Detail report?

[FIRSTNH]

I need to prepare a report for our bookkeeper that shows the items sold each week. The Item Summary report has too little information and the Item Detail report has too much information in it.

I want to create a custom Item sales report where I select the columns that our bookkeeper will need to update the books. What I am doing now is exporting the detail CSV report and removing all the columns from the report that are not needed.

This type of operation seems to be an obvious thing to do, but I cannot find a way to do that. I attempted to create a custom report, expecting that that would be the way to do what I need, but that doesn't seem to be it.

Thank you for any guidance.

[Candlestore · Accepted solution]

@FIRSTNH ;

As I see this is your First post let me welcome you to the Square Community Forum.

Now I do not know of a way to do this with Square but I do know a quicker way then what you are doing.

If you use excel to edit your csv files, you could down load the Details.csv file save it to a Folder called For Bookkeeper for example.

Open a blank Excel file.

Then use PowerQuery Editor to connect to the BookKeeper Folder.

Tell power Query to open Files From a folder.... open files that Contain Details in the name.

This will give you a sample data File.  In the power Query editor delete all the Rows you do not need for the bookkeeper.

Then Close and Load that to your Blank CSV file.

This will show only the columns for the Bookkeeper that you want.  Now save the File as an .xlsx file named edited4bookkeeper.

This is so the Power Query you just created is saved in the .xlsx file edited4bookkeeper.xlsx.

Week 2

Delete the Detail.CSV file from the Bookkeeper folder from the previous week.  

Log into Square and Download the new Details.csv folder and move it to the Bookkeeper Folder.

Open the edited4bookkeeper.xlsx file.

The right click in the file or on the Ribbon at the Top to Refresh All data.

If you set this up correctly in week 1, you should see only the columns you need to send to the bookkeeper.

Then Save as Bookkeeperwk2.csv to send to bookkeeper for example.  

Attach to an email to bookkeeper.

everything in week 2 should be abe to be done in under 5 minutes, the longest part is usually signing into Square and getting to Transacations and waiting for the download.  

I use a similar process for downloading my Transactions Details and My Transactions Items csv files to merge them together to get the data I want to import to QB's Desktop.  This has worked great for me for the past 2-3 yrs.

Here is more about Power Query : https://www.youtube.com/watch?v=0aeZX1l4JT4 

Now at 7:40 in the video he talks about filtering xxxxx out of the data from the drop down.  This is where i changed a few things to Choose Filter, then saw I could choose Filter Contains.  By doing this on the source column, you can filter out your square files.  So if you put filter for conatins Details.... then you will only have csv files that have Details in the name.  This is how you can just keep using this one file and delete the previous Details file so they do not get all added to one huge CSV file for your bookkeeper.

Here are screen shots to show you what I mean.  In my example I have a folder that I have all my Square Transactions details for a year that get combined in a different type of power query that I export as a Pivot table to compare different years.

What I get after choosing the Get Data from my folder.

In the below picture on the source choose red circles  1st by Source Name   2 By Text Filters

You should then see the below and choose Contains.

Red Boxes use the Drop down arrows to choose contains and Ends With.... Green areas are what you want to have Power Query Transform

When you have your Sample data setup the way you need to get it to your bookkeeper, in the top Left Choose Close and Load to...

You will then see this Box on your Blank Excel worksheet.  Choose to put the data in the exsisting worksheet.

Now you can save this in your Folder for Bookkeeper as PowerQuery for Bookkeeper or some other name that you know to leave this alone except for pressing Refresh Data.  To refresh data  Right Click in this excel Sheet and choose Refresh.  Then you will see the data change then choose File SAVE AS for bookkeeper CSV.  This keeps the file saved for emailing to the bookkeeper, is not a Details .csv file so won't be in your power query next week and since its a CSV file will not overwrite your xlsx file with the Power Query in that deletes or removes columns for bookkeeper.  

I use the same file name for my Power Query file and for my QBs upload, BUT the .csv and .xlsx makes them sepperate and keeps everything straight.  Only thing is every once and a while I open the .csv file and Click Refresh and nothing happens.  Then I look at the file name and close it and open the correct xlsx file extension.  By keeping the names the same I just have to choose the .csv and not type in a name also.  

Any other questions you can ask.

[FIRSTNH]

Thank you @Candlestore for the detailed reply. I was hoping that Square had the flexibility in the reports where you could select the fields that I needed, but the approach you describe using Power Query in Excel absolutely does the trick for me.

And, I learned something new today, so thank you for that, too. I hadn't used Power Query before.

Ken

Director - FIRST New Hampshire Robotics

[Candlestore]

@FIRSTNH ;

Ken,

Even if Square had that ability you would need to make sure you selected ALL the correct fields for the columns you would need.  You could miss a few or mark the wrong ones.  Using Power Query helps reduce human error.  Also since you learned a little of Power Query, you might be able to use other power query tricks to organize Squares Data or other csv files too.