is it possible if you can share the updated version with sql report
I’ll try to post it in the next few days, when I get a chance.
1 Like
My new report does not use the date filter. I will show all tickets in the report. I was surprised how quick it ran the report, that I did not go any further to use the date filter. For our use, it was easier to run the whole report then use the search to find the item (search can be enabled in the “View Editor” and searching for “search”).
I had to add another field to the Customers report, Entity Id as another field. The Customers report had to be redone on the entity screen (right click on the customers report and choose “Reset Layout”. A Entity search widget should work in-place of the Customers report.
Here is the Customers report:
[#Customers:1,1,1,1,1,1,1,1,1]
>Name|Organization|Phone|City|Address|State|Zip Code|Email|Id
{REPORT ENTITY DETAILS:EC.First Name,EC.Last Name,EC.Organization,E.Name,EC.City,EC.Address 1,EC.State,EC.Zip Code,EC.Email,E.Id:(ET=Customers):{0} {1}|{2}|[=F(TN('{3}'),'(###) ###-####')]|{4}|{5}|{6}|{7}|{8}|{9}}
On the Entity screen, the for the Customers report a setting needs to be added in the “Settings Mappings” (right click → Settings (Custom Report Viewer)) STG_CPH-Id=Customers.9.
It should look like this:
STG_CPH-Customer=Customers.1
STG_CPH-Organization=Customers.2
STG_CPH-Phone=Customers.3
STG_CPH-City=Customers.4
STG_CPH-Address=Customers.5
STG_CPH-State=Customers.6
STG_CPH-Zip Code=Customers.7
STG_CPH-Email=Customers.8
STG_CPH-Id=Customers.9
Here is the Customers Purchase History SQL report:
[#Customers Purchase History2:1,1,1,1,1,1,1]
>Date|Tkt Number|Ticket ID|[N]Quantity|Item|[C]Price|[C]Ext Price
{REPORT SQL DETAILS:SELECT Date,
TicketNumber,
TicketTypes.Name,
TotalAmount,
Tickets.Id,
Orders.Quantity,
Orders.MenuItemName,
Orders.Price
FROM Tickets
JOIN Orders ON Orders.TicketId = Tickets.Id
JOIN TicketTypes ON Tickets.TicketTypeId = TicketTypes.Id
JOIN TicketEntities ON TicketEntities.Ticket_Id = Tickets.Id
WHERE TicketEntities.EntityId = '$1' AND
TotalAmount > 0:
F.Date,F.TicketNumber,F.Name,F.TotalAmount,F.Id,F.Quantity,F.MenuItemName,F.Price::[=FD('{0}','yyyy/MM/dd ddd')] Total\: $ {3}|{1} Ticket Type\: {2}|{4}|{5}|{6}|$ {7}|[=F({5}*{7},'0.00')]}
Of course this report can be edited to your needs.
The report layout will also need to be redone also (right click → Reset Layout).
What I did was to put the Date and Ticket Total into on column and the Ticket Number and Ticket Type into another combined column. I then grouped by the Date and Ticket number column. That gave me the stacked effect. Again, you can adjust to your needs.
In rules I removed the Date Filter and used {LOCAL SETTING:STG_CPH-Id} as the parameter.
I think those are all the changes I made to make the Customer Purchase History report to work.
3 Likes
how do i go about in using Customer Purchase report2 to use date filter, i changed the customer purchase report to the sql one but its not filtering…is there something i need to change on the report or report parameter
When I was redoing the report I did not have time to explore that completely ( I was satisfied at that time with the how the report was working). Hopefully someone smarter than me can post how to do that. It will be a few weeks before I am able to have time to explore adding the start & end dates.
@QMcKay had also posted a Customer Purchase History tutorial. Although he used a script to generate the report. Maybe that will be of some help:
the {LOCAL SETTING:CPH_DateBEG} is giving output of datetime for example 17/05/2024 12:00:00am and that makes the report not work if i have to filter it by date.I tried formatting it with this …CAST(P.Date AS DATE) BETWEEN CONVERT(DATE, '{LOCAL SETTING:CPH_DateBEG}', 103) AND CONVERT(DATE, '{LOCAL SETTING:CPH_DateEND}', 103).. but still it ddnt work…
I got it working by making a script that changes the output setting to only date format dd/MM/yyy , that made it easier for me not to even use the date filter in Refresh custom
report widget action
and this is the script…
function formatDate(input) {
var parts = input.split('/');
var day = parseInt(parts[0], 10);
var month = parseInt(parts[1], 10) - 1; // Months are zero-based in JavaScript's Date object
var year = parseInt(parts[2], 10);
var date = new Date(year, month, day);
var formattedDate = ("0" + day).slice(-2) + "/" +
("0" + (month + 1)).slice(-2) + "/" +
year;
return formattedDate;
}
so when i run the report it updates the settings again by calling the script
{CALL:fd.formatDate(‘{LOCAL SETTING:CPH_DateBEG}’)}
now my sql report filters by date
2 Likes
Congratulations, you are now officially smarter than me! 
Awesome! Thank you, you save me a ton of time!! I am going to implement that into my setup!
1 Like
actually you are the smarter one for helping in implementing the purchase history report…thank you so much 
1 Like
That looks amazing! I like the credit card representation. Clever!
Is the “Credit Card” number anything significate (…##00 7129)?
Thanks for sharing, I enjoying seeing other people solutions to inspire me to up my game!
1 Like
its actually the EntityId number…i just used this format [=F({6},‘00 0000’)] to output the last digits of the card
1 Like
I have followed all the steps in this tutorial but it’s not loading the tickets and it’s not asking me and question when I click the run report button
Can you show your Settings for the report viewers…Particularly the Settings Mappings.
It should show this type of information in the box:
Make sure this column number matches the Ticket ID column in the original report, not the column on the entity screen if columns have been rearranged or hidden. Count the columns in the Reports editor.
If the above is correct, then post the automation command buttons settings for both buttons. And post the rules for those buttons.
This is the report that I am using, it looks a bit different from yours. I checked the column number it seems okay, let me attach the buttons settings and rules
[#Customers Purchase History:1,1,1,1,1]
>Date/Qty x Items|Ticket #/Price|Tkt Type/Ext|Tkt Total|Ticket ID
@{REPORT TICKET DETAILS:T.TicketNumber:(TEN.Customers={LOCAL SETTING:STG_CPH-Primary})::,}
{REPORT TICKET DETAILS:T.Date,T.TicketNumber,T.TicketType,T.TotalAmount,T.Id:T.TicketNumber="$1":[=FD('{0}','MM/dd/yyy - ddd')]|{1}|{2}|${3}|{4}}
{REPORT ORDER DETAILS:O.Quantity,O.MenuItemName,O.Price,O.ExactTotal,T.Id:T.TicketNumber="$1": {0} x {1}| $ {2}| $ {3}||{4}}
[= '{REPORT TICKET DETAILS:T.TicketNumber:(TEN.Customers={LOCAL SETTING:STG_CPH-Primary})::,}' == '' ? '' : ' ------------------------------ | ---------- | ---------- | ---------- |' ]
{REPORT TICKET DETAILS:T.ItemCount.sum,T.TicketAmount.count,T.TotalAmount.sum:(TEN.Customers={LOCAL SETTING:STG_CPH-Primary}):Items\: [=F('{0}','0')] Ticket Count\: {1}<!rn>Avg Itm\: $[=F(TN('{2}')/TN('{0}'),'0.00')] Avg Tkt\: $[=F(TN('{2}')/TN('{1}'),'0.00')]|||Total\: $ {2}}
{REPORT TICKET DETAILS:EC.First Name,EC.Last Name,EC.Organization,EN.Customers.asc:(TEN.Customers={LOCAL SETTING:STG_CPH-Primary}):{0} {1}}
{REPORT TICKET DETAILS:EC.Organization.asc:(TEN.Customers={LOCAL SETTING:STG_CPH-Primary})}
