Using DB Views in the Custom Scripts

V5 Question
1

Hi!

I just started working on Custom Reports on SambaPOS,
Been following this: [NEW!] How to use Custom Reports Module 1.0

Seems to work alright… Now to get into more specific things,
Im wondering if there is a way to make queries made on DB Views rather than the DB Tables.

I have created a few Views using SQL Management Studio that allow me to get a lot of relevant information tabled out nicely. How do i call these views in my Custom Scripts.

Following the first example, we have the query:
SELECT Convert(nvarchar(20),Date,103),
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 9 AND 11 THEN 1 ELSE NULL END),
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 12 AND 17 THEN 1 ELSE NULL END),
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 18 AND 20 THEN 1 ELSE NULL END)
FROM Tickets
WHERE Date > ‘{Start}’ and Date < ‘{End}’
GROUP BY Convert(nvarchar(20),Date,103)

Now instead of From Tickets table i need to to get from a Custom View.

Another thing i wanted to know is in the ‘Print Report’ type action, how to pass the start&end date in the Parameters ?

2

This is just plain SQL so you’ll read data from views like how you read data from view in a regular sql script.

Report Tool replaces {Start} {End} values with selected date ranges. While testing you can replace them hardcoded dates. After implementing your report you can change them back to tags so they’ll update dynamically.

1 Like