Metabase Reporting tool

Metabase and other 3rd party reporting

Moderator: crwheelr

Active Member
Posts: 45
Joined: Wed Jun 19, 2013 10:47 am

Metabase Reporting tool

Postby crwheelr » Wed Jul 24, 2019 9:28 pm

Greetings pizza shop owners. Its Regis Wheeler. Wife and I own two shops in Cali Bay Area and have been using POSPizza for six years now. Great software and even greater tech support provided by Scott. Scott granted me this forum section to cover a new topic regarding 3rd party reporting tools.

POSPizza offers many reports already to help owners manage their business. As it goes with reports, there's always some other one you may wish you had. I've been working off and on for past few months on adapting a 3rd party graphing and reporting tool for the POSPizza application. The tool is called Metabase. Its open source and free of charge.

Some reports i made up using Metabase are as follows:
Section A - watching the daily cash flow
This one is setup to send an email to select persons (you) with the things needed to watch the cash. each day after shift close an email goes out.

- open cashouts - track who forgot to charge customers
- voids - track overall voids, who made them, the reason
- cash voids - same as above but more critical to track separately
- bar chart of daily shift close imbalance
- bar chart of daily driver drop imbalances
- cashier-as-customer purchases - track that cashiers are not amassing points on walk in customer purchases.

These simply pop up in the email, giving you opportunity to question cashiers, esp ones who can close the shifts. Things are likely to run tighter if cashiers know they are being watched :)

Section B - Analytics
This is more on the side of looking for sales trends and potentially doing something about unfavorable trends.
- weekly sales year over year bar chart
- Sales by type bar chart (dine/takeout/delivery)
- Sales vs discounts dual bar chart )track effectiveness of coupon programs ets.

The beauty of Metabase is that one doesn't need to know SQL to make reports. it has a logical report builder tool that steps you thorough it.

I want to see who if any are interested in this topic.

Veteran Member
Posts: 181
Joined: Fri Apr 18, 2003 11:50 am
Location: New Jersey

Re: Metabase Reporting tool

Postby H-Man » Wed Aug 07, 2019 11:38 am

How much does this cost?

Active Member
Posts: 45
Joined: Wed Jun 19, 2013 10:47 am

Re: Metabase Reporting tool

Postby crwheelr » Wed Aug 07, 2019 3:42 pm

H-Man,

There is no cost at this time. Its really just an investment in your time to get it all set up. I can provide some instructions.

Basically, there are two new fields in the POSPizza Orders table that need to get created. Those new fields get populated after each shift close event. Scott has graciously created a program that creates the new fields and populates them as needed. right now you have to run the program manually, but maybe Scott can make it automatic when cashiers close the shifts.

Besides that you need to load up the Metabase reporting tool on the PC. I can provide documentation for that if you wish.

Finally, i can pass you the Metabase reports that I have written so far. That's about it.

My hopes on this are as follows. Collaborate with participating shop owners to define and implement a comprehensive set of reports and graphs. All participating members will benefit from having the full report set at no charge. Once this thing is having substantial value, I will lock it down and productize it.

Your POS database is safe with this tool as its a read only reporting tool Only Scotts 2-new fields are things written to the database.

Regis

New Member
Posts: 2
Joined: Fri Jan 04, 2019 8:54 pm

Re: Metabase Reporting tool

Postby zedspizza1-2 » Sat Aug 17, 2019 4:11 pm

Hi
Im Intersted in setting something like this up at my store.

Active Member
Posts: 45
Joined: Wed Jun 19, 2013 10:47 am

Re: Metabase Reporting tool

Postby crwheelr » Sun Aug 18, 2019 2:33 pm

Hi H-Man and Zeds,

over the next week or so, I will put together an installation guide. Need to ask Scott if there is a place I can post files to.

If you want to get a head start though, here are the things that need to get installed for an operational Metabase.

1) Download Java version 8 here HTTPS://www.java.com/en/download . everyting default.
2) Create folder c:\Metabase . All Metabase content will be stored here.
3) Download latest Metabase.jar file to the folder you just created,
http://downloads.metabase.com/v0.33.0-p ... tabase.jar
4) Download nssm from here https://nssm.cc/download and place it in the windows folder
5) Open a CMD window and type in nssm install metabase
when the box appears, fill in the fields as follows and then press the install service button
path c:\ProgramData\Oracle\Java\javapath\javaw.exe
startup directory: c:\metabase
arguments: -jar c:\metabase\metabase.jar

you are there! I know this sequence is a bit cumbersome, but once its done, no need to ever repeat any of it. eventually, I'll have a script to handle all of this

If you have any problems let me know and I can help. Once complete let me know and I'll send you the canned reports i made up already.

Regis

Site Admin
User avatar
Posts: 2263
Joined: Wed Apr 16, 2003 4:35 pm
Location: Western PA

Re: Metabase Reporting tool

Postby Scott » Tue Aug 20, 2019 6:42 am

crwheelr wrote:Need to ask Scott if there is a place I can post files to.


You can post them in this form. I'd recommend creating a separate topic for that however.


Scott
Attachments
Hourly sales email.jpg
Hourly sales email.jpg (67.58 KiB) Viewed 354 times

Active Member
Posts: 45
Joined: Wed Jun 19, 2013 10:47 am

Re: Metabase Reporting tool

Postby crwheelr » Tue Aug 27, 2019 10:28 am

I posted two attachments. first is a screenshot of email sent hourly, showing sales, delivery count, sales by hour etc. Second one is an Outlook email item, this one is the end of day summary report which gets auto-emailed. more detail than the hourly email.

Regis

Member
Posts: 5
Joined: Mon Jan 21, 2019 1:59 pm

Re: Metabase Reporting tool

Postby swan » Wed Sep 04, 2019 8:44 am

Hi Regis

Do I need to install metabase reporting tool for each store? Can I install it on one server which can access all database?

Thanks for your help

Active Member
Posts: 45
Joined: Wed Jun 19, 2013 10:47 am

Re: Metabase Reporting tool

Postby crwheelr » Wed Sep 04, 2019 10:47 am

Swan,

It is possible to have a remote site running Metabase, and having it connected to your two server PCs through SSH tunnels. This gets a bit technical though as your site routers and POS server PCs need to open up a pass through to the database.

I didn't implement this way for my two stores. I have Metabase running locally on each POS PC. Metabase has a "pulse" feature that has scheduled email push function, so even though its a local install, I get the reports/charts remotely via email.

I also use tightVNC into my servers that I have used for Metabase adhoc stuff, although admittedly this is not optimal as the local Metabase browser occupies the server's screen when running VNC.

Regis

Active Member
Posts: 45
Joined: Wed Jun 19, 2013 10:47 am

Re: Metabase Reporting tool

Postby crwheelr » Wed Sep 04, 2019 10:50 am

By the way, that hourly sales chart above is not rendering correctly on PC/Windows. It is correct on smartphone though. I have notified the MB dev team of this bug. They maintain an active forum to get help, suggest features and report bugs. They are super responsive I have found.

Member
Posts: 5
Joined: Mon Jan 21, 2019 1:59 pm

Re: Metabase Reporting tool

Postby swan » Wed Sep 04, 2019 12:12 pm

Thanks I have it installed and up and running. Can you please send me canned reports?

New Member
Posts: 2
Joined: Fri Jan 04, 2019 8:54 pm

Re: Metabase Reporting tool

Postby zedspizza1-2 » Sat Sep 07, 2019 6:47 pm

Im setup would like the canned reports as well

Return to 3rd Party Reporting Tools

Who is online

Users browsing this forum: No registered users and 1 guest