Accessing Micros 3700 totals with Microsoft Excel 1

[dennysguy]

I've recently taken over the job of technician for a small restaurant franchise company. I support 13 Micros 3700 systems, and 6 Micros 2700 systems. We do a lot of our daily and weekly accounting by hand, using numbers generated by the End of Day and End of Week reports. I'm currently developing a group of Microsoft Excel spreadsheets to streamline the process, and I would like to get it to import some of the data directly from the POS database.
My problem is, I don't have any formal training on the system, and I don't have any idea how the database is structured, or even what the password might be. I have a little experience with SQL, but I don't know what tables I should start looking in.
Any help would be great.

 

[Circle9]

If they are DBF files, you can use DBF Viewer Plus.

http://www.alexnolan.net/software/dbf.htm

 

[regilobo]

Ok, quick primer for 3700:
Micros provides good documentation for most aspects of the system. You can get DB schemas, explanation of every report and field within the report, and help with how to interact with the database.

Start here:
\Micros\Documentation\

REPORTS
Figure out what reports you are using for EOD and EOW. You'll use these as the basis for figuring out where to fetch the data you want. I'd guess the Daily System Sales Detail is going to be the primary report, so we'll use that for example.

Find the report in the Reports manual:
\Micros\Documentation\res\POS\Reports\3700POSrpts_Man.pdf

First, read up on the Profiles concept. The Profiles appear in almost all of the reports in some form.

Next, look for the Daily System Sales Detail under Sales Balance Reports. It will tell you which Profiles are used for the report, give an example report, and then most importantly tell you where each field in the report comes from, whether it comes from the database or is calculated in the Crystal Report.
For data extraction purposes, find what Views are used:
v_R_sys_sales
v_R_sys_trk

v_R_sys_sales essentially gives you the data you see in the top of the report (net sales, covers, voids, etc)
v_R_sys_trk gives you the 64 items you are tracking in the middle of the report, the things that are specific to your restaurant setup.

The Reports manual gives you all of the field names you need to access the data from the view, but if you want more detail, check out the SQL manual:
\Micros\documentation\RES\Common\SQL\3700SQL_Man.pdf
Search for the particular view and you'll see the tables that the view consists of, datatypes, etc.

DATABASE ACCESS
Use the Sybase manager app to explore the db. You can connect to the db using ODBC, the micros dsn, and a user/pass of support/support.
You can experiment with interactive SQL queries using dbisql (start-->Run-->dbisql) login as support/support, choosing the micros dsn

EXCEL
To pull the data into Excel, you'll want to setup an ODBC connection to the micros dsn, using the username/password combo of: support/support

A simple query to get sales and tracking group information for today (the same data you'd see in your daily report):

SELECT sales.*, trk.* FROM micros.v_R_sys_sales sales INNER JOIN micros.v_R_sys_trk trk ON sales.business_date=trk.business_date WHERE DATEFORMAT(sales.business_date, 'yyyymmdd') = '20070914'


2700...if you have the backoffice software I believe you can configure the reports to dump to text files back there. Then I suppose you could scrape them into Excel somehow. Honestly don't remember much about it.

 

[aposdimi]

I also need to find out more about Micros 3700 / RES 3000. Can anyone make available the documentation 3700posrpts_man.pdf and 3700sql_man.pdf?

 

[Nibbler01]

regilobo,

your post is very interesting; I have a similar need.
I would like to access the RES3700 database through ODBC but from a different machine on the same network.
I guess I need to install the ODBC driver for iAnywhare, do you now where can I get it?

 

[regilobo]

The easiest thing to do would be to run NetSetup on that machine, which will install the necessary ODBC drivers, etc.

I don't believe Sybase has the ODBC drivers available for download but I could be wrong.

 

[marcomb69]

Hi Regiglobo

Are you talking about SQL Anywhere Setup or Micros Setup?

The only setup I found is the SQL Anywhere Developer 9.02 (I have tried the 10 too, but the Micros DB seems too old for him). Not sure if I can use in a production environment the Developer suite, but at least itlooks like a setup and I can select just the ODBC option (while doing a custom setup)

Beside that, I am quite confused betwee the different MICROS tables. I can extract the daily ttl but I cannot get the transaction details.

I means to get the details like:
Transanction number, date and time, amount, tender type

Do you remember any other table that could be used for this?

Thanks

Marco

 

[regilobo]

Run NetSetup from the MICROS server on whatever workstation you want to get ODBC access from. It will install the MICROS backoffice with the ODBC drivers. Then you can use the micros ODBC connection from whatever app or dev tool you like.

Another option would be to use Java ODBC drivers. I've used Aqua Data Studio which includes drivers.

http://www.aquafold.com/

You can pretty easily set up a data connection and use the Query Analyzer and explore the database. Look on their site for an older version that is free for non-commercial use (4.x?)

Regarding the transaction details...the full details are only kept for 14 days, so be aware of that. That limit might have increased at some point but I think it's true up to 3.2.

There are multiple tables involved in the check detail. Look at the views v_dtl and vta_* for a start. There are multiple *_dtl tables with multiple relationships. If there is an existing report that satisfies part of your requirements, then seek out the view it uses and get some info from there.

 

[POSWizard]

Ok,

I might have one for you. What version of the 3700 are you running? I can help you run reports in micros and output them to an excel file. The trick that I have creates an excel file that will have a unique cell for each value. You could write a master spreadsheet that would look at the report excel file and import the totals you want.