I'm starting this post in hopes that there may be a way to build a dashboard (or, if not a dashboard, a report) that could provide metrics related to sales volumes by device/location of device.
Use case: We have a very porous campus, with five buildings (four of which contain exhibitions space). Visitors can enter through and purchase entry at, any one of at least three different admission desks. We are planning to open a fourth (which has been closed since the pandemic shut things down). Management would like to be able to gauge the volume of transactions happening at each desk.
As a hosted site, I wasn't able to find a reliable way to collect and present the volume of sales for memberships and tickets at each desk - there doesn't seem to be a way to make a collection of devices (like you can with NScan devices) that will deliver the total number of tickets or memberships sold on a Point-of-Sale device. Confounding the issue more is the fact that our admissions desk attendants move around - so the Seats and Tickets cube element "Created by" won't work. Someone may be at Desk 1 part of the day and Desk 2 later (to facilitate lunches and breaks). Someone suggested creating a Mode-of-Sale for each PoS location but we would end up with at least 20 MoS;s - that just seemed completely unruly.
What would be great is a way to group the computers that are at each desk into a collection and report on the membership sales and ticket sales as a percentage of the days sales for the whole campus. We've done something similar with our NScan devices, so we can see where the traffic is flowing through the exhibitions.
Is anyone doing anything like this? If not in analytics, the only way to do this might be to create a local table and build a custom report to aggregate the data but this would be perfect for Analytics, if the data elements existed in the cubes.
Any ideas or direction would be greatly appreciated.
We use special access areas for this very purpose. We have one for each entry point and when someone purchases a ticket or picks one up, it is scanned as a ticketed event and as the access area for the entrance they used.Anne RobichauxThe Historic New Orleans Collection
That's close but we are trying to do this with the Point-of-Sale, not a scanner. We want to be able to total the sales of tickets and memberships separately, and report on them by the Point-of-Sale computers location. Technically, we have scanners there to scan for General Admission, so we could kind of report on those. Our exhibitions are scanned at the exhibition entrance, so the exhibition ticket sale can't be linked to the location that it was sold at using access areas.
Hi Phillip - forgive me as I don't know anything about the hosting environment but we have done this before using machine location as each of our point of sale tills device ID is in that table.
you beat me to it by less than 60 seconds, lol!
Can you expand on that...I looked at that table but was unable to link that through to individual transactions.
The machine location is returned in the create_loc column
we used
Select distinct o.order_no ,p.perf_no ,o.customer_no ,o.MOS ,o.create_loc ,h.num_seats_pur ,h.num_seats_ret ,p.perf_dtfrom T_ORDER o (nolock)join T_LINEITEM h (nolock)on o.order_no = h.order_noJoin T_SUB_LINEITEM s (nolock)on h.order_no = s.order_noJoin T_PERF p (nolock)on p.perf_no = h.perf_nowhere perf_dt between '2022-09-04' and '2022-09-20'and p.prod_season_no = 140040
and then pivoted the results to see which entry was being used more. Would be interested in how you get on with getting it into Analytics!
Thanks for the code Jo. In our case the create_loc isn't going to work because the data in that field is the user name and not the actual machine name. I believe this is due to us being on Tessitura Hosting. It appears that when a user logs in though Citrix, what is being written in the create_loc field is not the actual device ID (like the windows device ID or some other assigned value that is related to the physical machine), but a virtual device ID that contains the user name found in the table TX_MACHINE_NAME. Because our users move around to various physical devices, those ID's can't be mapped to a specific location, as the machine_name field in TX-MACHINE_LOCATION is their login, which is where I'm assuming the application gets the create_loc value.
Really thinking this is going to have to be something custom.
sorry it's not going to work for you - good luck!
If you are not already using the Order Category field in the orders module, you could have a category set aside for each location (and you can make it required so that it is not forgotten). That would require your sales staff to make one more update to each order, which I am sure would be at least a little annoying at first, but they would get used to it in the end and then you would at least have a standard method of returning data about which desk is selling what.
Absent that, I think all other viable solutions that occur to me have already been suggested.
John A. Moskal II
Hi Phillip
We use a different batch type at each location, and batch type groups for reporting. When our attendants move between locations during the day, they close the old batch and open a new one for the new position. I think batch info is available in the finance analytics cube only, which may not work for your reporting.Alison AtkinsonThe National Gallery