Micros 9700 ver 3.xxx report help!!! 2

[nonny2001k]

Hi

I'm just wondering if anyone can help me. I'm trying to get some data out of micros database for report purpose. The specific data I'm looking for are Menu item number, description, quantity, price, check number, time check posted in each RVC.

It's similar to CpAll.day report but I can't turn that report in to excel, and also there are no menu item code which I need.

Can anyone help with this?. Thank you.

 

[microsGuy16]

Do you have access to the sql server? I have a script on my home PC that will give you the info you are looking for. I can provide it later today

 

[nonny2001k]

Thank you for your reply. Unfortunately, I don't have access to sql server. I have to run it from cmd. If you can help that would be very helpful. ^_^

 

[microsGuy16]

i'll have to take a look at the sql executable that comes with micros. That can be run from a command line and dumped into a text file, however, i'll have to play with it first. The logic is slightly different than straight sql.

 

[nonny2001k]

microsGuy16 - Thank you. If it's not to much trouble then I'm happy to wait.

 

[safinramil]

On which database based your micros system ? You can take data direct from database or develop report in Netvupoint(mymicros report)

 

[nonny2001k]

We have 2 version of micros 9700
1. Version 3.50.132
2. Version 4.0.0000.65

Where can I find NetVupoint and how can I extract data out?

 

[safinramil]

Netvupoint is site based reporting and used Localhost address (See screanshot

http://prntscr.com/ec0zfj).

Also you can get data direct from oracle if you server based on oracle, if you need i can write sql query?

 

[nonny2001k]

I have tried to login to NetVupoint as you suggested, but unfortunately I am unable to get into it. All I can see "page can't be displayed". I think the server is not config for internet. I normally use the scripts on cmd. If you have script that I can use with cmd that would be very helpful. Thank you.

 

[safinramil]

Try this script

select to_char(string_table.STRINGTEXT) rvc,checknumber,
to_char(check_detail.detailpostingtime,'hh24:mm dd.MM.YY'),
menu_item_master.objectnumber,name.stringtext miname,salescount,total
from checks,hierarchy_unit,string_table,CHECK_DETAIL,menu_item_detail,
menu_item_definition,string_table name, menu_item_master
where checkclose is not null and checks.revctrid=hierarchy_unit.REVCTRID
and hierarchy_unit.NAMEID=string_table.STRINGNUMBERID
and checks.checkid=check_detail.checkid
and check_detail.checkdetailid=menu_item_detail.checkdetailid
and menu_item_detail.menuitemdefid=menu_item_definition.menuitemdefid
and menu_item_definition.name1id=name.stringnumberid
and menu_item_definition.menuitemmasterid=menu_item_master.menuitemmasterid
order by rvc, checknumber

 

[safinramil]

But it for oracle database

 

[nonny2001k]

Thank you for your help and for the script, but I can only use cmd. I just thought someone might have a script that they already use. Thank you for any comments anyway.

 

[safinramil]

We can uload remotely if you want?

 

[osorio82]

Good morning,

I'm fairly new to MicrosDB and trying to create a report out of the Oracle database (micros 9700 version). I need to report Total Items sold, Gross Sales and Discounts per Revenue Center.
I'm having issues with the split checks. I'm not sure how to count the items or the revenue in these cases. I would really appreciate your help on this one.

This is the query I have so far.It think split checks are messing the numbers.

SELECT ch.CHECKID ,
ch.CHECKNUMBER ,
ch.TABLEOPEN ,
ch.CHECKOPEN ,
ch.CHECKCLOSE ,
ch.CHECKPOSTINGTIME ,
ch.STATUS ,
ch.SPLITFROMCHECKNUM ,
ch.SPLITOUT ,
ch.ADDEDTOREVCTRID ,
ch.ADDEDTOCHECKNUM ,
ch.ADDEDIN ,
ch.ORDTYPEID ,
st1.STRINGTEXT menuclass ,
st.STRINGTEXT menu ,
ms.OBJECTNUMBER ,
SUM(c.SALESCOUNT) salescount ,
SUM(CASE WHEN c.DETAILTYPE = 1 THEN c.TOTAL
ELSE 0
END) check_detail_total ,
SUM(CASE WHEN c.DETAILTYPE = 2 THEN c.TOTAL ELSE 0 END) check_discount_total
FROM dbo.CHECK_DETAIL c
LEFT JOIN dbo.MENU_ITEM_DETAIL m ON c.CHECKDETAILID = m.CHECKDETAILID
LEFT JOIN dbo.MENU_ITEM_DEFINITION d ON m.MENUITEMDEFID = d.MENUITEMDEFID
LEFT JOIN dbo.MENU_ITEM_MASTER ms ON d.MENUITEMMASTERID = ms.MENUITEMMASTERID
LEFT JOIN dbo.MENU_ITEM_CLASS mc ON d.MENUITEMCLASSID = mc.MENUITEMCLASSID
LEFT JOIN dbo.MENU_ITEM_PRICE mp ON m.MENUITEMPRICEID = mp.MENUITEMPRICEID
LEFT JOIN dbo.STRING_TABLE st ON ms.NAMEID = st.STRINGNUMBERID
LEFT JOIN dbo.DISCOUNT_DETAIL di ON c.CHECKDETAILID = di.CHECKDETAILID
LEFT JOIN dbo.CHECKS ch ON c.CHECKID = ch.CHECKID
LEFT JOIN dbo.STRING_TABLE st1 ON mc.NAMEID = st1.STRINGNUMBERID
WHERE ch.CHECKPOSTINGTIME >= '2017-06-04 10:43:01.000'
AND ch.CHECKPOSTINGTIME < '2017-06-05 05:24:01.000'
AND c.REVCTRID = 6
AND c.DETAILTYPE IN ( 1,2 )
AND mp.PRICE <> 0
GROUP BY ch.CHECKID ,
ch.CHECKNUMBER ,
ch.STATUS ,
ch.ORDTYPEID ,
st1.STRINGTEXT ,
st.STRINGTEXT ,
ms.OBJECTNUMBER ,
ch.CHECKID ,
ch.CHECKNUMBER ,
ch.TABLEOPEN ,
ch.CHECKOPEN ,
ch.CHECKCLOSE ,
ch.CHECKPOSTINGTIME ,
ch.STATUS ,
ch.SPLITFROMCHECKNUM ,
ch.SPLITOUT ,
ch.ADDEDTOREVCTRID ,
ch.ADDEDTOCHECKNUM ,
ch.ADDEDIN ,
ch.ORDTYPEID ,
st1.STRINGTEXT ,
st.STRINGTEXT ,
ms.OBJECTNUMBER
ORDER BY 1

I would really appreciate your help on this one. Thank you!!