Micros Price Level Export 1

[bufftrek]

I work for an independent company that integrates with POS software to collect sales information. As of recently, I moved to a territory where Micros modifies prices(for Rocks and Double upcharges, for example) by means of price levels vs. individual line items. I currently have the following SQL query that was sent to me by the CSM for the local Micros provider:

PARAMETERS period_begin , period_end;

SELECT 
MIN(DATE(business_date)),
MAX(DATE(business_date)),
mi_seq,
menuitem_number,
menuitem_name1,
menuitem_name2,
group_number,
group_name,
CAST((SUM( P1_Sales_total + P2_Sales_total + P3_Sales_total + P4_Sales_total + P5_Sales_total + P6_Sales_total + P7_Sales_total + P8_Sales_total + P9_Sales_total + P10_Sales_total) / SUM( P1_Sales_qty + P2_Sales_qty + P3_Sales_qty + P4_Sales_qty + P5_Sales_qty + P6_Sales_qty + P7_Sales_qty + P8_Sales_qty + P9_Sales_qty + P10_Sales_qty)) AS NUMERIC(8,2)) AS PriceTotal,

SUM( P1_Sales_qty + P2_Sales_qty + P3_Sales_qty + P4_Sales_qty + P5_Sales_qty + P6_Sales_qty + P7_Sales_qty + P8_Sales_qty + P9_Sales_qty + P10_Sales_qty) AS QtyTotal,

SUM( P1_Sales_total + P2_Sales_total + P3_Sales_total + P4_Sales_total + P5_Sales_total + P6_Sales_total + P7_Sales_total + P8_Sales_total + P9_Sales_total + P10_Sales_total) AS SalesTotal

FROM micros.v_R_rvc_menuitem_fam_grp 
WHERE 
DATE(business_date) >= DATE('{period_begin}') 
AND DATE(business_date) <= DATE('{period_end}')
AND 
(P1_Sales_qty + P2_Sales_qty + P3_Sales_qty + P4_Sales_qty + P5_Sales_qty + P6_Sales_qty + P7_Sales_qty + P8_Sales_qty + P9_Sales_qty + P10_Sales_qty) > 0 

GROUP BY
mi_seq,
menuitem_number,
menuitem_name1,
menuitem_name2,
group_number,
group_name

ORDER BY menuitem_number ;
OUTPUT TO 'MenuSalesExport.txt' FORMAT ASCII;

As I know close to nothing about SQL other than what I am quickly trying to teach myself, I understand the above code for the most part. Unfortunately, I do not know how to manipulate it so that the name of each price level is added to the value of its menuitem_name to create a unique name which then has the sales_qty and sales_total calculated.

Essentially, I am trying to export sales for each price level for each menuitem vs summing the sales for each price level under each menuitem.

My apologies for not conveying thoughts in a programmatic language. I have reached out to the local provider but have received nothing other than the above code in the past 5 weeks.

 

[pmegan]

Start off by looking at all the data available in that table. The one in the original query is broken down by revenue center. If you just need daily totals per item use the system sales view instead (commented out below)

select * 
from 
[indent]
micros.v_R_rvc_menuitem_fam_grp
[green]--micros.v_R_sys_menuitem_fam_grp[/green]
[/indent]
order by business_date, mi_seq

You can build your export query up from there:

SELECT 
[indent]business_date
,mi_seq
,menuitem_number
,menuitem_name1
,menuitem_name2
,group_number
,group_name

[green]-- level 1[/green]
,Price1
,main_mlvl_name1 [main_level_1]
,sub_mlvl_name1 [sub_level_1]
,P1_sales_qty
,P1_sales_total
,P1_return_qty
,P1_discount_total

[green]-- level 2[/green]
,Price2
,main_mlvl_name2 [main_level_2]
,sub_mlvl_name2 [sub_level_2]
,P2_sales_qty
,P2_sales_total
,P2_return_qty
,P2_discount_total

[green]-- etc... to level 10[/green]
[/indent]

FROM 
[indent]micros.v_R_sys_menuitem_fam_grp[/indent]
WHERE
[indent]business_date = '2014-04-01'[/indent]
ORDER BY
[indent]business_date
,mi_seq[/indent]

This line: PARAMETERS period_begin , period_end;
is defining 2 parameters that are being passed in. They're always strings so are converted to dates in the Where clause - DATE('{period_begin}')

Once you get the results you want, add the parameters and change the Where clause. At this point you'll have to run the script from a command line using something like this: dbisql -c "UID=custom;PWD=custom;DSN=micros" read test.sql '2014-04-01' '2014-04-01'

Add the parameters as the first line of the script:
PARAMETERS StartDate, EndDate;

and change the Where clause from this
WHERE

business_date = '2014-04-01'​

to this
WHERE
business_date between {StartDate} and {EndDate}

Run it from a command prompt rather than double clicking a batch file. It will make debugging a whole lot easier.

When you have your query going, it's time to add in the export. Unless you're on an ancient version of Micros, and inline UNLOAD is the way to go. (that script you're working with looks like it was partying in 1999)

So your script should look something like this:

PARAMETERS StartDate, EndDate;

UNLOAD
[indent]
SELECT 
[indent][green]-- all of your fields get listed here[/green][/indent]
FROM 
[indent]micros.v_R_sys_menuitem_fam_grp[/indent]
WHERE
[indent]business_date between {StartDate} and {EndDate}[/indent]
order by 
[indent]business_date
,mi_seq[/indent]
[/indent]
TO 'D:\\micros\\Export\\test.csv' QUOTES OFF;

Obviously change the export path and name, just use double backslashes in the path. Singles are seen as escape characters in some versions.

 

[bufftrek]

First and foremost, thank you for providing such a thorough explanation as opposed to just throwing code out there!

I'll have to wait a couple days before being able to test things and fully understand the output(the local vendor will not provide me with a demo build for some reason).

From what I can see, this is going to throw out a ton of columns that I will need to run a external script (or simply some excel functions) on to create 'unique' menu item names where the price level name is appended to the menu item name. Is this going to be a possibility to be returned via a query?

Finally, P1_return_qty and P1_discount_total are new to me - what do these values represent(I can make assumptions but prefer not to).

Once again, thank you so much for the wealth of knowledge! Is there a repository of Micros-based queries that can be viewed? This alone was extremely beneficial and I hate to be a bother with simple questions. Have a wonderful week!!

 

[pmegan]

If you run this using micros.v_R_rvc_menuitem_fam_grp you'll get one row per menu item, per rvc, per day.
If you run this using micros.v_R_sys_menuitem_fam_grp you'll get one row per menu item per day.
The one you use will depend on what the end product requires.

Either way, each of those rows will have whatever columns you decide to put in the query. If you don't need something just comment it out or remove it from the script. You shouldn't have to write anything to parse through the results.

P1_return_qty is the count of an item being returned to the kitchen/bar after being served. These items deplete from inventory but don't add to net sales.

P1_discount_total is the total of all prorated/item discounts applied to the menu item.

There's no set of pre-written queries that I know of. It's basically knowing where the database stored data and writing a query to pull it like I did here. If you have a test server, open Sybase Central, (program files/micros applications/utilities/database/sybase/sybase central), connect with your SQL credentials and look around. The views are used for a lot of the reporting, so are a good place to start.

 

[bufftrek]

The reason I mentioned returning a 'unique' name based off of combining the menu item name with the price level name is because this information will be imported in a proprietary program. This program collects sales info and requires one of two items to be used as a unique item identifier - menu item number or menu item name. Unless there is another unique identifier I can return that remains static from week to week then I will need to perform further actions with the results.
Essentially, one client has Shot, Double, and Martini Price levels. If Absolut is rang under the three price levels, I need to return Absolut Shot, Absolut Double, and Absolut Martini with their respective sales quantities and revenues on their own rows.

As for a test server, I'm having zero luck in getting the local vendor to provide me with a demo to install. Is there another way to go about receiving a copy elsewhere?

Once again, you have been extremely helpful - thanks again!!

 

[pmegan]

Ok, if that's the case you're going to have to use a bunch of queries linked together with union statements. I'd also use a common table expression to create a result set and run the query off of that just for efficiency's sake.
Something like this:

WITH CTE as (
[indent]select * from micros.v_R_sys_menuitem_fam_grp
where business_date = '2014-01-01'[/indent]
)

SELECT 
[indent]business_date
,menuitem_number
,menuitem_name1 || '-' || main_mlvl_name1
,Price1
,P1_sales_qty
,P1_sales_total
,P1_return_qty
,P1_discount_total
[/indent]
FROM CTE

UNION

SELECT[indent]
business_date
,menuitem_number
,menuitem_name2 || '-' || main_mlvl_name2
,Price2
,P2_sales_qty
,P2_sales_total
,P2_return_qty
,P2_discount_total
[/indent]
FROM CTE
ORDER BY
[indent]1,2[/indent]

What version of Micros are you working with? If I have it on my google drive I'll send you a link.

 

[bufftrek]

I'll have to get the version number. I know it is 3700 and it was recently updated(3 weeks ago) to maintain PCI compliance.

 

[bufftrek]

Vendor CSM said it is version 5.2. Thanks!

 

[pmegan]

Ok, give this like a shot - Res 5

Micros versions are really service packs for the main version, so you'll have to download and install the following, in this order:

RES 50 Prerequisite
RES 50 GR Installer
RES 52 Prerequisite
RES 52 Installer
RES 52 MR1a (this is the latest, and upgrades Sybase from v11 to v16. I'm assuming this is what they have)

The first two are ISO files for the general release CDs. Also, grab the PDFs from the release docs folder. The RES installer is very, very particular and sees like it just looks for a reason to fail. I'd highly recommend doing this on a VM if you have access to one. If not, at least get a backup image of your Win7/2008 machine before starting, and after you get service packed up to 5.2 MR1a. It'll save you a lot of grief at some point. Also, read the appendix on custom installations in one of the installer PDFs and follow the steps for bypassing the database update. You can do that once at the end when you're restoring a backup.

See if you can get a backup of your client's database and restore it after you're done installing. The installer will try to drop in a shell database, but that usually fails and the shell is useless anyway.

 

[bufftrek]

So I was able to test out the script today on one of my clients computer that uses varying price levels - it works like a charm!

The only problem I am running into is that it throws an error if I try to write the script to handle 10 price levels. If RES doesn't haven't all 10 price levels(this one in particular had a maximum of 2 but I have others that use 4 and 5 levels) filled out, is that what is causing the error? Thanks for all of the help - this has been quite the learning experience!

 

[pmegan]

It might be, what's the error message you're getting?
My guess is that it's choking on the line that creates the menu item name when the level name is null. Try making the changes in red below.
The coalesce() function checks the first argument, (main_mlvl_name1), and if it's null replaces it with the second, ('level1').
The WHERE clause will filter out items that weren't sold on each particular level.

SELECT

business_date
,menuitem_number
,menuitem_name1 || '-' || coalesce(main_mlvl_name1, 'level1')
,Price1
,P1_sales_qty
,P1_sales_total
,P1_return_qty
,P1_discount_total​

FROM CTE
WHERE

P1_sales_qty <> 0
OR P1_sales_total <> 0​

​

 

[bufftrek]

Everything works wonderfully, thank you for the assistance!

I'm in the midst of getting an old computer that had Micros on it so that I can start fidgeting with more code like this. Your answers were very insightful!

In the same grain of alcohol accountability, for the clients that don't want to make use of price levels for items like Doubles, Rocks, etc., is there a way to export sales that directly link the modifier to the menu item it was identifying?

For example, simply exporting sales on a Double Crown and Coke might give us 1 Crown sold, 1 Double sold, and 1 coke sold. Since that Double modifier isn't linked to the Crown, our exported results give us an unknown amount of usage for all modifiers sold. If this is

 

[nomed]

Hi bufftrek and all,

as i see we're all in the same page i'd like to share with you an open source project i released recently:

http://www.zeroisp.com/lilliput-0.1.0.html

The goal is to retain check details as long as you need, the project focus on that.

I wrote an extractor to export daily csv with check details from res 3700.

If you think it could be useful for you i'll be happy to receive feedbacks and suggestion to improve it.

Thank you

 

[Moregelen]

Interesting. I'm on my phone so only gave it a cursory read, but am I right in thinking this is intended as a self-hosted solution?

 

[bufftrek]

Thank you. Looks interesting. Checked out the site and will look into it further later!

 

[nomed]

Hi,

that is , self-hosted mymicros like web application.

1) It can collect data from stores at check level ( higher granularity )
2) the data retention is up to you
3) No java required
4) It Works on any browser

 

[pmegan]

What kind of server would a restaurant need to host this for themselves?

 

[nomed]

It requires Python and it supports most of the databases available:

Drizzle
Firebird
Microsoft SQL Server
MySQL
Oracle
PostgreSQL
SQLite
Sybase

By the way atm tests have been done on MySql.

It works on iOS, Linux, Windows

 

[pmegan]

So harware-wise, a web server running Windows/IIS or Linux/Apache and the engine for one of the databases listed?

 

[nomed]

I'm sorry there was a mistake on my last post.

It doesn't work obviously on iOS rather it does on OSX