Simplify Journal report?
Moderators:daleadmin, Dale Harris, Alan, Andrew
So I just tested the Journal feature the other day. And after I tried some 40-50 transactions, the journal file is unbelievably long (including opening & closing report).
This is the example:
https://drive.google.com/file/d/0B9Ftio ... sp=sharing
Is there any possibility to shorten the transactions to one line per transaction? For example:
Tran No | Tran Time |Description | Quantity | Price/Unit | Tax | Transaction Amount
0001 | 2017-01-25 10:30 | Chocolate waffle | 2 | 10000 | 0 | 20000
0001 | 2017-01-25 10:30 | Chocolate drink | 2 | 8000 | 0 | 16000
0002 | 2017-01-25 10:40 | Orange juice | 3 | 8000 | 0 | 24000
0002 | 2017-01-25 10:40 | Cheese waffle | 3 | 10000 | 0 | 30000
etc
That's what I had in mind after I saw some examples of other reports.
Please let me know if this is possible. Thank you!
This is the example:
https://drive.google.com/file/d/0B9Ftio ... sp=sharing
Is there any possibility to shorten the transactions to one line per transaction? For example:
Tran No | Tran Time |Description | Quantity | Price/Unit | Tax | Transaction Amount
0001 | 2017-01-25 10:30 | Chocolate waffle | 2 | 10000 | 0 | 20000
0001 | 2017-01-25 10:30 | Chocolate drink | 2 | 8000 | 0 | 16000
0002 | 2017-01-25 10:40 | Orange juice | 3 | 8000 | 0 | 24000
0002 | 2017-01-25 10:40 | Cheese waffle | 3 | 10000 | 0 | 30000
etc
That's what I had in mind after I saw some examples of other reports.
Please let me know if this is possible. Thank you!
Re: Simplify Journal report?
Arcovine,
Unfortunately in many locations there are legal requirements for the journal in that it has to report EVERYTHING. At least that is what their local tax buddies tell them.
Off the top of my head I cannot think of a way to get the type of report you want but with so many features in DHPOS I may come up with something.
Until then you could always try to reconfigure the deflector dish, that always works on Star Trek.
Dale
Unfortunately in many locations there are legal requirements for the journal in that it has to report EVERYTHING. At least that is what their local tax buddies tell them.
Off the top of my head I cannot think of a way to get the type of report you want but with so many features in DHPOS I may come up with something.
Until then you could always try to reconfigure the deflector dish, that always works on Star Trek.
Dale
Re: Simplify Journal report?
Lol. Thanks for the response, Dale.
Re: Simplify Journal report?
arcovine,
Well I have given this some thought and got nowhere. As a last resort I thought of the REC2CSV.EXE program that is described here http://www.dhpos.com/postip11.htm#rec2csv
This program will take the info in the record of past sales and send the transactions you select to a .CSV file that can then be loaded into a spread sheet program. From there you can make any report that you want.
Unfortunately it does save all the data from each sale to the .CSV file. You can manually copy and paste just the line you want from each sale to another spreadsheet and then get what you want but that is labor intensive and I am sure that is not what you want.
Below is the data for just one sale. However the BBS will not preserve the columns that the data is held in the spread sheet.
BEGIN SALE TRANSACTION # 1782 2/15/2017 23:11
1. DALE
STOCK NUMBER DESCRIPTION TAX RATE PIECES UNIT PRICE PRICE
60004 COLOR KEY TWO SIDED 1 1 2.79 2.79
1
SUB TOTAL 2.79
TAX1 at 10.25% 0.29
SALES TAX 0.29
TOTAL 3.08
CASH TENDERED 3.08
Well I have given this some thought and got nowhere. As a last resort I thought of the REC2CSV.EXE program that is described here http://www.dhpos.com/postip11.htm#rec2csv
This program will take the info in the record of past sales and send the transactions you select to a .CSV file that can then be loaded into a spread sheet program. From there you can make any report that you want.
Unfortunately it does save all the data from each sale to the .CSV file. You can manually copy and paste just the line you want from each sale to another spreadsheet and then get what you want but that is labor intensive and I am sure that is not what you want.
Below is the data for just one sale. However the BBS will not preserve the columns that the data is held in the spread sheet.
BEGIN SALE TRANSACTION # 1782 2/15/2017 23:11
1. DALE
STOCK NUMBER DESCRIPTION TAX RATE PIECES UNIT PRICE PRICE
60004 COLOR KEY TWO SIDED 1 1 2.79 2.79
1
SUB TOTAL 2.79
TAX1 at 10.25% 0.29
SALES TAX 0.29
TOTAL 3.08
CASH TENDERED 3.08
Re: Simplify Journal report?
Thanks, Dale. Will try it out.
- brucef2112
- Forum Regular
- Posts:336
- Joined:Mon Mar 06, 2006 11:19 pm
- Location:Broward County, Floriduhh
- Contact:
Re: Simplify Journal report?
Long, long ago (2011) I created a helper app (in Excel 2002) to work with the output file created by Dale's REC2CSV.EXE utility.
It was intended so sort out the method of payments plus some other stuff. It was helpful to 1's of people.
However aside from the summary data, I think i remember that the flattened data is saved as a separate worksheet in workbook.
It may give you the answer you need.
For the following reasons, I cannot verify this:
1. I currently don't have a PC (pre-windows 7) to run DHPOS or REC2CSV.EXE .
Therefore I don't have a REC2CSV file to see if my Excel app produces what you need.
AND
2. I have long forgot the password I used to lock access to the code modules.![Sad :(](./images/smilies/icon_sad.gif)
So I can't view the code to see if it does flatten a CSV to the format you are looking for.
Both of these are surmountable. Just not now. ('cause i now have a hobby)
The Original Forum post is here: http://www.dhpos.com/discuss/phpbb3/vie ... a95#p11629
The entire post has lots of details but what makes me think it will produce what you need is "Item 2. Worksheet with the flat REC data."
If it creates what you need you can just save that worksheet out to another name and BAM! you got what you need!!
Read the thread. Then go back and read the whole thread. In it is a link to download my Excel helper app.
The version of Excel this was written in is 2002. However some later versions did run it just fine.
I have no idea how far along the version trail it will go. I'll guess if it runs on WinXP, then that version of Excel should be fine to run my App.
You will have to give it a try and see what results it produces.
Read the forum post.
Run Dale's REC2CSV.EXE utility to get your CSV file.
Run my Excel helper. WHEN YOU OPEN MY HELPER FILE FOLLOW THE INSTRUCTIONS EXACTLY AS OUTLINED IN IT and see what it returns.
Write back and tell us of your success!
It was intended so sort out the method of payments plus some other stuff. It was helpful to 1's of people.
However aside from the summary data, I think i remember that the flattened data is saved as a separate worksheet in workbook.
It may give you the answer you need.
For the following reasons, I cannot verify this:
1. I currently don't have a PC (pre-windows 7) to run DHPOS or REC2CSV.EXE .
Therefore I don't have a REC2CSV file to see if my Excel app produces what you need.
AND
2. I have long forgot the password I used to lock access to the code modules.
![Sad :(](./images/smilies/icon_sad.gif)
So I can't view the code to see if it does flatten a CSV to the format you are looking for.
Both of these are surmountable. Just not now. ('cause i now have a hobby)
The Original Forum post is here: http://www.dhpos.com/discuss/phpbb3/vie ... a95#p11629
The entire post has lots of details but what makes me think it will produce what you need is "Item 2. Worksheet with the flat REC data."
If it creates what you need you can just save that worksheet out to another name and BAM! you got what you need!!
Read the thread. Then go back and read the whole thread. In it is a link to download my Excel helper app.
The version of Excel this was written in is 2002. However some later versions did run it just fine.
I have no idea how far along the version trail it will go. I'll guess if it runs on WinXP, then that version of Excel should be fine to run my App.
You will have to give it a try and see what results it produces.
Read the forum post.
Run Dale's REC2CSV.EXE utility to get your CSV file.
Run my Excel helper. WHEN YOU OPEN MY HELPER FILE FOLLOW THE INSTRUCTIONS EXACTLY AS OUTLINED IN IT and see what it returns.
Write back and tell us of your success!
Later,
Bruce
They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759
Bruce
They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759
- brucef2112
- Forum Regular
- Posts:336
- Joined:Mon Mar 06, 2006 11:19 pm
- Location:Broward County, Floriduhh
- Contact:
Re: Simplify Journal report?
Well, I found the password to the code module.
"Ohh joy!" I said. I'll just browse my code and see if my code flattening the CSV file will meet what the OP wanted.
Only to find out that back in the day of development I decided the code module should not only be password protected, BUT the code should be obfuscated before I posted it online.
I don't remember how i did it but I succeeded. The code is not easily readable. I'd guess some 2 or 3 hours to hammer it out into 'VBA English' again.
Well of course the projects original working copy is backed-up on a CD. Its just that it's in a Big Box with ALL my file backups (far back as 1995). Yes I backup files and backup often. I'm weird like that.
So to save my sanity we are back to the OP using their data and my tool to test if it works for them.
If the OP reports back with something good or bad I may find a moment to search 'the box' to update for their need.
Hopefully though the original App as is, gives them what they need.
I then have made this project useful for 2's of people over the last 6 years.
"Ohh joy!" I said. I'll just browse my code and see if my code flattening the CSV file will meet what the OP wanted.
Only to find out that back in the day of development I decided the code module should not only be password protected, BUT the code should be obfuscated before I posted it online.
![Sad :(](./images/smilies/icon_sad.gif)
I don't remember how i did it but I succeeded. The code is not easily readable. I'd guess some 2 or 3 hours to hammer it out into 'VBA English' again.
Well of course the projects original working copy is backed-up on a CD. Its just that it's in a Big Box with ALL my file backups (far back as 1995). Yes I backup files and backup often. I'm weird like that.
So to save my sanity we are back to the OP using their data and my tool to test if it works for them.
If the OP reports back with something good or bad I may find a moment to search 'the box' to update for their need.
Hopefully though the original App as is, gives them what they need.
I then have made this project useful for 2's of people over the last 6 years.
Later,
Bruce
They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759
Bruce
They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759
Re: Simplify Journal report?
Hi Bruce,
It is good to see that this forum is beginning to come alive again.
Dale
It is good to see that this forum is beginning to come alive again.
Dale
- brucef2112
- Forum Regular
- Posts:336
- Joined:Mon Mar 06, 2006 11:19 pm
- Location:Broward County, Floriduhh
- Contact:
Re: Simplify Journal report?
Hey Dale,
Good to see ya too.
Good to see ya too.
Later,
Bruce
They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759
Bruce
They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759
- brucef2112
- Forum Regular
- Posts:336
- Joined:Mon Mar 06, 2006 11:19 pm
- Location:Broward County, Floriduhh
- Contact:
Re: Simplify Journal report. Confirm it works
I now have a XP pc. I've tested and verified my program works. I half guarantee this to work with 2002 up to Excel 2007.
My RecCSV_Parse.xls program does flatten the journal csv to show each item sold on a single line.
You must have Excel set to allow macros to run. TOOLS > MACROS > SECURITY Select either Medium or Low.
1. When you open RecCSV_Parse.xls read the instructions. then click the 'Click To Start' button.
Enter the info for the location of your POS and the location of the CSV file already created by Dale's program.
2. On the main form leave the check box checked for "Flatten Just the stock table data in my CSV......"
Un-check all the boxes in the 'Choose to Include' section. Doing so will minimize the time to flatten the journal data.
3. Click the OK button.
Once finished, Each line contains the following fields for each Stock Item sold in the journal.
With the data flattened like this it is very easy to do a ton of analysis of your sales.
Refer to the links in this thread to get my helper app and other information needed to use Dale's program.
My RecCSV_Parse.xls program does flatten the journal csv to show each item sold on a single line.
You must have Excel set to allow macros to run. TOOLS > MACROS > SECURITY Select either Medium or Low.
1. When you open RecCSV_Parse.xls read the instructions. then click the 'Click To Start' button.
Enter the info for the location of your POS and the location of the CSV file already created by Dale's program.
2. On the main form leave the check box checked for "Flatten Just the stock table data in my CSV......"
Un-check all the boxes in the 'Choose to Include' section. Doing so will minimize the time to flatten the journal data.
3. Click the OK button.
Once finished, Each line contains the following fields for each Stock Item sold in the journal.
Code: Select all
STOCK NUMBER
DESCRIPTION
Tax Rate ID
Pieces
Unit Price
Price
PayBy Method(s) UNKNOWN Cash Check CredCrd Debit
Trans#
Trans Date
Vendor#
Vendor Name
Employee Name
Total Pcs Sold
Refer to the links in this thread to get my helper app and other information needed to use Dale's program.
Last edited by brucef2112 on Tue Apr 11, 2017 10:18 pm, edited 1 time in total.
Later,
Bruce
They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759
Bruce
They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759
Re: Simplify Journal report?
Thanks Bruce
Re: Simplify Journal report?
Hi Bruce,
How does your report treat items sold on with a sale price?
thanks
Log
How does your report treat items sold on with a sale price?
thanks
Log
Re: Simplify Journal report?
Biglog,
Bruce's program take the information from the file of past transactions. What you see there will be what is in the .CSV file. To view a past transaction press "5. Voids" from the main DHPOS menu.
Dale
Bruce's program take the information from the file of past transactions. What you see there will be what is in the .CSV file. To view a past transaction press "5. Voids" from the main DHPOS menu.
Dale
Re: Simplify Journal report?
So the "unit price" column is Bruce's spreadsheet is the "R price" from the stock table not the actual price the item was sold for, if a discount was applied?
Log
Log
Re: Simplify Journal report?
Big log,
I just ran a sale here with a sale price and the sale price ended up in the file of past sales and therefore can be viewed when using 5. Voids.
I then ran the REC2CSV.EXE program and the sale price (and the customer info) ended up in the .CSV file.
So now it is up to Bruce (since I do not know how to allow macros in Excel) to determine if the sale price ends up in his file (I think it would have to since it is the price in the .REC and .CSV files) and if the customer info also ends up in Bruce's file.
Dale
I just ran a sale here with a sale price and the sale price ended up in the file of past sales and therefore can be viewed when using 5. Voids.
I then ran the REC2CSV.EXE program and the sale price (and the customer info) ended up in the .CSV file.
So now it is up to Bruce (since I do not know how to allow macros in Excel) to determine if the sale price ends up in his file (I think it would have to since it is the price in the .REC and .CSV files) and if the customer info also ends up in Bruce's file.
Dale
Who is online
Users browsing this forum: No registered users and 263 guests