Trouble matching Micros.dtl data to Reported Sales 1

[jschweppe]

I am having a heck of time trying to get data in the micros.dtl to match up with data from the reports. I have yet to get things to come out correctly. I have tried these three tables micros.dtl, micros.vta_basedtl, micros.v_dtl. Nothing seems to match the reports from the autosequence/reports module. I think the autosequence/reports module pulls from the daily tables such as micros.dly_sys_mi_ttl. But things just don't match up. For instance some menu items show up in the mircos.vta_basedtl table more times than in the menu item report and not and all in the mircos.dtl table. Am I doing something wrong, or is the database borked or what?

 

[pmegan]

Micros detail tables are a pain to work with. I generally try to avoid using them unless I need check specific info.

The micros.dtl table holds a kind of overview of everything that ops does. Menu items are anything marked as type "M". to get the actual menu item detail join micros.dtl with micros.mi_dtl on the trans_seq and dtl_seq fields. If you want to see the item names, join in the mi_def table on mi_def.mi_seq = mi_dtl.mi_seq.

The micros.vta_basedtl view is meant as a base for transaction analyzer. I've never used it just because its a pain to work with.

There are 2 unadvertised reports in the \micros\res\pos\reports folder that can help when you'r trying to make sense of this stuff; Domain.rpt and Schema.rpt.

 

[jschweppe]

Ok, so how do I run these reports if they are unadvertised? I was doing the joins as you described(I actually based this off of a previous post that you replied to that was quite helpful:

http://www.tek-tips.com/viewthread.cfm?qid=1164015&page=1),

but I was also doing joins on fam_grp_def.fam_grp_seq and maj_grp_def.maj_grp_seq and then a where clause on the business_date. This allowed me to try to match family group and major group totals for different days. The totals for the the family group and major groups didn't match what was on the reports. So I decided to run a menu item total report for the different days. Some of the menu items matched but others didn't even show up in the dtl table for that day.

 

[pmegan]

I usually open the templates in Crystal Reports and preview them. This way you have the tree view on the left which makes the reports easier to navigate.

How far back are you trying to run these report for? Micros holds check details for 14 days. If you're looking more than 14 days back you'll get nothing, less than 14 days you should be good. The problem I've run into is that if you're running a query for the business date that's 14 days back you'll get half-purged data which is about useless.

What is it you're trying to do? There may be an easier way than going through the detail tables.

 

[jschweppe]

I am trying to run it for the previous day.

I guess the best explanation for what I am trying to do is export all of the Menu Item transaction data. This way I can pull it into another database and statistically analyze it.

I have been able to do this already to some effect; however, when it doesn't match what is Menu Item reports, then it is almost useless.

What I need is trans seq, check seq, check employee, menu item sequence, menu item name, price sold, workstation, date and time etc..

 

[CWSA]

When you are exporting are you using the chk_ttl or rpt_ttl columns from dtl?

 

[jschweppe]

I am exporting rpt_ttl. But it is more than just the total that isn't adding up. I am having problems where certain menu items show that they were sold x times in the built in report and I only see them y times in micros.dtl joined to micros.mi_dtl on mi_seq. In this case y is usually less than x. However, when I look at the micros.vta_basedtl it shows them at z times and in this case z is usually greater than or equal to x.

 

[CWSA]

What is the query that you are using to get the data?

 

[jschweppe]

I have tried something like this:
select * from micros.trans_dtl INNER JOIN micros.dtl on micros.trans_dtl.trans_seq = micros.dtl.trans_seq INNER JOIN micros.mi_dtl on micros.dtl.trans_seq = micros.mi_dtl.trans_seq and micros.dtl.dtl_seq = micros.mi_dtl.dtl_seq INNER JOIN micros.mi_def on micros.mi_dtl.mi_seq = micros.mi_def.mi_seq INNER JOIN micros.fam_grp_def on micros.fam_grp_def.fam_grp_seq = micros.mi_def.fam_grp_seq INNER JOIN micros.maj_grp_def on micros.maj_grp_def.maj_grp_seq = micros.mi_def.maj_grp_seq where micros.trans_dtl.business_date = '2008-03-13';