What Syntax do Use to create this report

V5 Question
41

Yes, i am sure Emre will be able to help with that because i learn how to prepare Ticket Counts and other tutorial from one of his Post

42

I have still not been able to solve my report issue in samba V4 using Custom Reports

43

What SQL have you tried? Can you paste what you have tried so far and explain what is not working?

44

Thanks Kendash, i want a simple report to show the total drinks sale per day in sambapos V4 using Custom Reports module

45

Not quite what kendash asked, he asked what you have tried


This SQL will give QTY and Value by MenuItemName for decreased inventory items

SELECT [MenuItemName], SUM([Quantity]) AS QuantitySold, SUM([Price]) AS ValueSold
  FROM [SambaPOS4].[dbo].[Orders]
  WHERE [DecreaseInventory] = 1
  GROUP BY [MenuItemName]

Does NOT take in to account portions


46

This shows portions;

SELECT [MenuItemName],[PortionName], SUM([Quantity]) AS QuantitySold, SUM([Price]*[Quantity]) AS ValueSold
  FROM [SambaPOS4].[dbo].[Orders]
  WHERE [DecreaseInventory] = 1
  GROUP BY [PortionName], [MenuItemName]

Also corrected price to price * quantity

47

This also shows GroupCode and has a GroupCode WHERE you can specify;

SELECT [MenuItems].[GroupCode], Orders.[MenuItemName],Orders.[PortionName], SUM(Orders.[Quantity]) AS QuantitySold, SUM(Orders.[Price]*Orders.[Quantity]) AS ValueSold
  FROM [SambaPOS4].[dbo].[Orders]
  JOIN [SambaPOS4].[dbo].[MenuItems] on [SambaPOS4].[dbo].[Orders].[MenuItemId]=[SambaPOS4].[dbo].[MenuItems].[Id]
  WHERE Orders.[DecreaseInventory] = 1 AND [MenuItems].[GroupCode] = 'Draught Beers'
  GROUP BY Orders.[PortionName], Orders.[MenuItemName], [MenuItems].[GroupCode]

Pasted image1226×359 25.5 KB

Anything else youll have to google SQL and work out for yourself.
This will not take in to account GIFT items as thats a state and not tried processing state JSON in SQL itself only ever in V5 scripts.

Google is your friend, I dont know this stuff off by heart, just google it myself gor the GROUP BY and to confirm how to use SUM()

1 Like
48

JTRTech Thanks, Currently trying to run the report script you just sent men

49

Show us screenshots and your report template as you go so we can help you if it does not work.

1 Like
50

I have only done this in SQL, not in samba, but shouldnt matter. HOWEVER database name would

I don’t usually have to put [SambaPOS4].[dbo]. but was just C&P from the SELECT TOP 1000

Also never used V4 reports so not sure if/what needs to be done in respect to start and end dates.

51

@winsoft its quite possible that report tags can build the report you want in v4. Most of us do not have v4 installed anymore and do not want to install v4 just to help build a report for forum.

I highly recommend you study these links here they explain in detail how to use Custom reports and all of the syntax is available in v4. V5 syntax was added after these guides were written.

Custom Report Templates:
https://www.sambapos.com/wiki/doku.php/custom_report_templates

Custom Report Tags:
https://www.sambapos.com/wiki/doku.php?id=custom_reporting_tags

Custom Reports with SQL:
https://www.sambapos.com/wiki/doku.php/creating_custom_reports_with_sql

Creating Accounting Reports with Custom Report Tags:
https://www.sambapos.com/wiki/doku.php/creating_accounting_reports_with_custom_reporting_tags

Pivot Tables:
https://www.sambapos.com/wiki/doku.php/custom_report_pivot_table_views

Table Views:
https://www.sambapos.com/wiki/doku.php/custom_report_table_views

If your curious here is some of the V5 ONLY syntax added:
https://www.sambapos.com/wiki/doku.php/custom_reporting_tags_for_v5

4 Likes
52

The guides above are what I used to go from 0 knowledge to being an expert with custom reports. I spent some time studying them and trying out various reports even ones I wouldn’t use. Now if I need a report for my businesses I can write any report I need which has been very valuable for me. I recommend you do the same.

I don’t mind helping you, I love building reports, I just noticed after 12 days you still have not solved your issue and I thought wow he could really benefit from reading the tutorials @Emre wrote so he doesn’t have to wait for community to solve his problems.

2 Likes
53

V5 reporting has matured allot in my opinion us V4 and has the auto complete/suggestion pop ups which is invaluable especially when learning.

54

Thanks Man, i will spent time on the tutorial recommended.

55

When i run the report this is the result

56

You using the original report posted?

57

Yes, or do i have to activate the Custom report module before it will work

58

Please share the report your using. That error means your report syntax is wrong and specifically ODI.

More than likely that is a V5 only syntax.

59

I thought it had been discussed that that report was from v5 and wouldnt work in v4?
Hence the need for the SQL you kept asking for?

60

That niffs me a little as suggests that after aprox 2 weeks you had not even tried the posted report


What did you try because looks like you didnt try one of the suggestions which you have now