Micros Res 3700 and Excel

[Andy16H]

I have copied my micros.db file to another computer and I am trying to setup a connection to it with excel. Is this possible and if so how. I copied the micros.db file so i would not screw anything up on the real database.

 

[azrobert]

You would need the database server to be running on that computer.... I have never tried just installing sybase, an easier solution would be to install micros on a virtual machine or set up a "test" computer and install Micros on it.

It's not an easy thing to set up a machine but not rocket science either.... You need to be fairly proficient in setting up Micros to get it running. Took me quite a few tries to get it right.

Micros gurus..... Please correct me if I am wrong or have any other ideas....

 

[Andy16H]

That makes since. The Bar has two computers in it. The Micros PC is running Windows XP and our other computer is running windows vista home. The Micros computer does not have excel on it. What would be teh easist way for the vista machine to access the micros db?

 

[azrobert]

I guess the biggest question is what are you trying to accomplish? Are you just wanting to pull data out of tables?
if that is the case you can use sybase central and just query they database there and save the results to a file.....

 

[Andy16H]

Yes I do all of the bookkeeping in excel and would like to not have to read all of the daily reports and then key that info into excel. I am very efficient with excel and vb. Just trying to get the data I need into excel.

 

[azrobert]

Easiest solution would be to use sybase central from the Micros pc and collect the data you need. There is an option to save the results in just about any format you need.

Warning..... I have found the Micros database very difficult to track down the data you need. You will need to join multiple tables to get data that is usable. I have spent years working with Micros and still have problems finding what I am looking for in the database. If you have crystal reports you can look at an existing micros report and see where it is getting the data from, but it takes a lot of time to figure it all out.

 

[Bennett000]

Depending on your understanding of relational databases, and what you need for your accounting, the micros DB, might not be what you're actually looking for. Azrobert's exactly right (IMO), the data in the micros database can be *difficult* to track down, and what's worse is that it can be *seemingly* contradictory, ie you'll find multiple sets of sales totals for a day, but they won't match, this is especially true if you're writing your own queries.

If you do dive into the DB, I would suggest starting with the daily sales totals. These tables are named dly_xxx_xxx where the x's are the parts of the system, and the items that are being totaled (dly_sys_mi_ttl = daily system menu item totals). These tables seem to be calculated at the end of business dates.

That are far more detailed tables that actually capture the definitions of what make the micros POS work (menu item defs, employee defs, condiment defs, major group defs, etc). There are also tables that capture every detail of a transaction (menu item, seat number, taxes paid, time, etc).

Personally I find the micros database easier to deal with than the software, however I'm familiar with SQL. Also with that being said, I also find the micros database to be 'a work in progress', any more ranting would be OT, but I could go on at great length.

 

[pmegan]

Azrobert hit it on the head - what data are you looking to retrieve? If you're looking to extract data and key it in manually you may be able to find a report that's already written but not active on your system. Chances are that if it exists, somebody here has used it. You can also check out the Transaction Analyzer or Financial Management modules. TA just requires a license code to get going, FM is a bear, and it will cost you some setup $, but you provide the spreadsheet template so it's more customizable.

Definitely get a copy of the Res software and load it on a test pc, virtual or physical; your dealer should be able to provide a copy. You can run it in demo mode for free, your total will just all post to a dummy date. Developing on the live server shouldn't even be on the radar.

The 3700 db is a beast to learn. There are 1,674 tables and views, 93 of which are for posted totals and 254 for various details used to post those totals. Some totals, time periods come to mind right off the bat, aren't exactly held in the database but are calculated through a stored procedure. If you're comfortable with SQL and up for a challenge go for it (on the test server of course). If not it may take more time to figure things out than you can afford to spend.

 

[michelle305]

Bennett,
I would like to hear it...

 

[DrZogg]

You could start with Micros BOH (Win32 CAL CLIENT) on a bah pc, and experiment with REPORT EXPLORER to get an idea of your needs. Once thats done (and you have the reports already there) I can give you a script to alter the Crystal Reports Engine to produce reports in PDF or EXCEL format (default is TXT), and the autosequence can print to file in the desired format.

 

[kpizz]

I guess I am coming in on this pretty late but I hear that you may be able to link Access with Micros. It will put files in csv but not in excel. I am also trying to find a way to get data off the database easily. Has anyone been able to pull real time info off of Sybase Central. ie what if you wanted to keep a csv file updating with item details and transactions as they occur… is that possible

@Andy16H did you have any luck with your solutions and do you know where I can get my hands on a copy of RES?

Thanks guys

 

[Wildbar]

DrZogg,
I would be interested in that Crystal Re[rts script if its poossible
Thanks

 

[kpizz]

i'd also be intersted to see how this script works.

 

[DrZogg]

Save as batch file:


@echo off
:: ***********************************************************************
:: * PROGRAM : text_to_other.bat [txt|pdf|xls|csv] *
:: * AUTHOR : *
:: * VERSION : *
:: * *
:: * INSTALLATION: Copy this file to the micros\res\pos\scripts *
:: * directory. *
:: * FUNCTION : This script allows the user to print micros 3700 *
:: * reports to disk in pdf, xls or csv format from EOD. *
:: * Usage : create 4 external programs with these command lines *
:: * 1. text_to_other.bat pdf *
:: * 1. text_to_other.bat xls *
:: * 1. text_to_other.bat csv *
:: * 2. text_to_other.bat txt *
:: * *
:: * create an autosequence *
:: * step 1 calls 1, 2 or 3 external program *
:: * subsequent steps call report with print to disk *
:: * ext of PDF, XLS or CSV... this is required! *
:: * last step calls 4th external program *
:: * *** Do NOT SKIP THIS STEP *** *
:: * *
:: ***********************************************************************
:: * M O D I F I C A T I O N L O G *
:: ***********************************************************************
:: * *
:: ***********************************************************************
setlocal ENABLEEXTENSIONS
c:
cd\
cd Program Files\common files\crystal decisions\2.0\bin
set CrystalDir=%ProgramFiles%\common files\crystal decisions\2.0\bin
if not exist u2ftext.dll.org (copy u2ftext.dll u2ftext.dll.org)
if {"%1"}=={"txt"} (copy u2ftext.dll.org u2ftext.dll)
if {"%1"}=={"pdf"} (copy crxf_pdf.dll u2ftext.dll)
if {"%1"}=={"xls"} (copy crxf_xls.dll u2ftext.dll)
if {"%1"}=={"csv"} (copy u2fsepv.dll u2ftext.dll)
endlocal & goto :EOF
:EOF


===============================================================
Use steps:
1. Batch file calls this
2. run report (save to file path in format)
3. Batch file calls this with TXT parameter.

Failing to do the last step will make your reports i.e 40 column print in gibberish. Return to TXT and everything is cool again