Customer purchase history by ticket

@Jesse - From something I read on an old forum post from Emre, it seemed that you were something to do with the way in how the @List functionality works and how the looping works, so I am not sure if you can shed any light on this question?

You have ventured into confusing territory… I have been there too, and it’s a mindbender. :stuck_out_tongue_winking_eye:

This might help (maybe not):

Report

[Employee Earnings $entityName:1,1,1,1,1, 1, 1, 1, 1, 1]
>EmployeeName|StartDate|EndDate|DT|H|Hours|HOL|OT|REG|EARNED
@@TC_EmployeeEarnings:$1,$2,$3,$4,$entityName
     can we use a $namedVariable  ^^^^^^^^^^^ to be referenced in SQL script

SQL

-- PARM for Employee Entity Type
declare @entityType varchar(20)  = 'Employees' --'@1'
-- PARM for Employee Name
declare @entityName varchar(20)  = '@2'
-- PARM for Date Filter Start
declare @StartDateIn varchar(25) = '@3'
-- PARM for Date Filter End
declare @EndDateIn varchar(25)   = '@4'
-- PARM for Employee Name via GQL
declare @entityNameGQL varchar(20)  = '@entityName'
--$namedVariable from Report referenced here ^^^^^

You can use “named variables” as parameters instead of using only the $1 / @1 variables.

The next question is: how to set the value for “named variables”?

GraphQL

GraphQL can do it using the parameters parameter with an array of name/value pairs:

getCustomReport(
name:"TC Employee Hours"
,user:"Q"
,dateFilter:"This Month"
,parameters:[{name:"entityType",value:"Employees"},{name:"entityName",value:"Ovania"},{name:"startDate",value:"2016-07-01"},{name:"endDate",value:"2016-08-01"}]
)

Actions

These Actions can do it using the parameters parameter:

Print Report

image

Specify parameters as:

<varName>=<varValue>,<varName2>=<varValue2>,...
entityType=Employees,entityName=Ovania

Refresh Custom Report Widget

image

Specify parameters as:

<varName>=<varValue>,<varName2>=<varValue2>,...
entityType=Employees,entityName=Ovania

Report Widget

And the Report Widget can do it using the parameters parameter in Widget Settings:

image

Specify parameters as:

<varName>=<varValue>,<varName2>=<varValue2>,...
entityType=Employees,entityName=Ovania

:bulb: NOTE: if you don’t specify the varName, and use only a comma-separated list of varValues, then the varNames will automatically become: $1,$2,$3,...

For example:

Parameters: Employees,Ovania

… is like doing this:

Parameters: 1=Employees,2=Ovania

Thanks @QMcKay - Yes I am starting to feel like that!

Thanks so much for your reply, it sounds like it would be the solution, but i am having no luck whatsoever…

I have setup this test report:

[$entityname Purchase History:1,4,1]
$entityname $1 $2

And I have setup the report widget in the following way…
image

But the report displays as…

$entityname Purchase History
$entityname entityname=matt something=else

So as you can see, this is not being treated like and Name+Value pair of named parameters, but instead two parameters which set the values of $1 and $2.

Any idea what I am doing wrong (on 5.2.3)? I have also tried refreshing and setting the parameters via an Action, but I get exactly the same result…

So the way he programed parameters to work is specific. It only supports two and if you use it in the report name it gets screwy. You can’t define a third parameter and use $3

It’s better to use SQL in a script and call it in the template.

Ah, thanks. That explains why the third was not working correctly.

I am still stumped as to why named variables is not working as @QMcKay described in his example…?

Maybe because it just doesn’t. It works with GraphQL getCustomReport(), so I just assumed the Actions and Widget would work that way as well (did not test). A shame if that isn’t the case.

What? Only 2? Since when?

Most of my Reports of this type have 5 parameters: $1,$2,$3,$4,$5

I decided early on a “standard” for my self to not get confused:

$1 : entityType (most of the time, but can be whatever)
$2 : entityName (most of the time, but can be whatever)
$3 : dateBeg (ALWAYS)
$4 : dateEnd (ALWAYS)
$5 : other stuff (rarely used)

Even if some vars are not used in the Report (ie. empty string placeholders), I always know that $3 and $4 contain dates (if applicable).

I’m not talking about SQL I’m specifically talking about using @ parameters in report syntax

So you mean Reports only support 2x @lists ? News to me.

Yes that’s what I’m saying. Long ago when implemented it started with just a single one. He expanded it to two.

I’m pretty sure it’s a beta topic but I’d have to dig it up.

1 Like

So it was not beta it was v4 forum when we first started with custom reports. Originally it only supported one but he expanded it to two. Also if you use lists before the report title to use them in the title itself it kinda breaks them for rest of report. I asked him about that long ago and it was not something he could just fix. Something about how the parser reads stuff after [] brackets.

Been a long time about 3 years ago since we discussed it.

It does seem to support more than 2 @lists… Take a look at this…

[Test:1,1,1]
@red,green,blue
@dave,john,phil
@pizza,burger,drink
$1 $2 $3

Gives the result of:

red dave pizza
red dave burger
red dave drink
red john pizza
red john burger
etc...

The main thing I am confused about is why these names variables are not working for me, but they seem to work for @QMcKay

2 Likes

Ok so I stand corrected.

You must have missed my reply:

Ah - Yes. I missed that. I will check it out with GQL. A while back you showed me how to use GQL to set the Ticket Note value, so I will try to use the same logic for this…

Unfortunately, it won’t be the same at all. The query getCustomReport() returns JSON-formatted data which you need to cycle through and build the display yourself. It doesn’t just return a “formatted report” like you see in Report Explorer or in the Report Widget…

{
  "data": {
    "report": {
      "name": "VOIDS",
      "header": "",
      "startDate": "04/04/2018 10:17:13",
      "endDate": "05/07/2018 10:55:03",
      "tables": [
        {
          "name": "Void Items List",
          "maxHeight": 500.0,
          "columns": [
            {
              "header": "Ticket No"
            },
            {
              "header": "Order No"
            },
            {
              "header": "Void Item Name"
            }
          ],
          "rows": [
            {
              "cells": [
                "15281",
                "18018",
                "Barena"
              ]
            },
            {
              "cells": [
                "15282",
                "18019",
                "Barena"
              ]
            },
            {
              "cells": [
                "15286",
                "18023",
                "Barena"
              ]
            },
            {
              "cells": [
                "15287",
                "18024",
                "Barena"
              ]
            },
            {
              "cells": [
                "15288",
                "18025",
                "Barena"
              ]
            },
            {
              "cells": [
                "15361",
                "18098",
                "Barena"
              ]
            },
            {
              "cells": [
                "15363",
                "18100",
                "Barena"
              ]
            },
            {
              "cells": [
                "15364",
                "18104",
                "Imperial"
              ]
            }
          ]
        }
      ]
    }
  },
  "errors": null
}

P.S. I am a bit surprised that the Actions and Widget don’t support named variables. I just assumed when the named-variable feature was introduced in GQL, it would be implemented in other SambaPOS areas (because that’s how @emre tends to do things).

I wish I could confirm this with him; instead I need to run some tests to prove it to myself one way or the other.

Thanks for all your efforts. Instead of using getCustomReport() is there any GQL for manipulating Entity Screens or Report Widgets?

Or, do you know an effective way of running these inner and outer loops within SQL? It’s not really what SQL is designed for though, right? - That sort of thing is normally done at the presentation level rather than the DB level.

Of course maybe another option if for me to learn more about this whole GQL stuff and build a web page which displays the info instead of relying on an Entity Screen and report widget?

No.

Not sure what you are referring to.

That’s what I did in QMX to replace the Report Explorer, and my Timeclock (Reports) - the Reports are still defined in SambaPOS, but they are fetched using GQL and display is formatted as HTML using the returned JSON.

I still use Report Explorer, but rarely.

And I still use Report Widgets for some things, because those things are already done, they work, and building a GQL-equivalent is somewhat more trouble than it’s worth and would be very specific to the purpose.

1 Like

That would be your best option. You get full control and can do much more.

2 Likes

Cannot get it to work in Report nor in Action “Parameters” parameter, nor in the Report Widget. Too bad. Oh well.

1 Like