[NEW!] How to use Custom Reports Module 1.0

pizzaeilat4 · August 16, 2014, 10:54pm

dear @emre,
How can I display the day of the week next to the date in a custom report?

THANKS.

emre · August 16, 2014, 11:06pm

Try datename and datepart functions…

SELECT 
  T.TicketNumber,
  datename(dw,T.Date) as WeekName,
  datepart(dw,T.Date) as DayNumber,
  dateName(month,T.Date) as MonthName,
  datepart(month,T.Date) as MonthNumber,
  convert(nvarchar,T.Date,105) as Date,    
  convert(nvarchar,T.Date,108) as Time,
  T.TotalAmount as TotalTicket
FROM Tickets AS T
ORDER BY Date,Time

pizzaeilat4 · August 17, 2014, 9:35am

@emre,
It works but how do I add just one line of weekday to an Existing report i have?
this is the script
SELECT
Convert(nvarchar(20),Date,103),
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 12 AND 18 THEN 1 ELSE NULL END),
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19 AND 24 THEN 1 ELSE NULL END),
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 00 AND 06 THEN 1 ELSE NULL END)
from Tickets
Where Date > ‘{Start}’ and Date < ‘{End}’
GROUP BY Convert(nvarchar(20),Date,103)
and this is the report stings
[Ticket Counts by Peak Hours:3,1,1,1]

תאריך|14-18|19-24|00-03
@@BETWEEN
@@BETWEENT
I need it here

d9f3af2823e0ffb8.png1275×629 22.2 KB

thenks.

emre · August 17, 2014, 9:57am

###Template

[Ticket Counts:2,2,1,1,1]
>Date|Weekday|12-18|19-24|00-06
@@TicketCountsByPeakHours
>@@TicketCountsByPeakHoursTotals

###@@TicketCountsByPeakHours

SELECT
Convert(nvarchar(20),Date,103),
min(datename(dw,[Date])) as WeekName,
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 12 AND 18 THEN 1 ELSE NULL END),
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19 AND 24 THEN 1 ELSE NULL END),
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 00 AND 06 THEN 1 ELSE NULL END)
from Tickets
Where Date > '{Start}' and Date < '{End}'
GROUP BY Convert(nvarchar(20),Date,103)

###@@TicketCountsByPeakHoursTotals

SELECT 'Totals',' ',
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 12 AND 18 THEN 1 ELSE NULL END),
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19 AND 24 THEN 1 ELSE NULL END),
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 00 AND 06 THEN 1 ELSE NULL END)
FROM Tickets
Where Date > '{Start}' and Date < '{End}'

###Result

pizzaeilat4 · August 17, 2014, 10:05am

thenks emre It works Great.

emre · August 17, 2014, 10:10am

We can extend it like…

###Template

[Ticket Counts:2,2,1,1,1]
>Date|Weekday|12-18|19-24|00-06
@@TicketCountsByPeakHours
>@@TicketCountsByPeakHoursTotals

[Ticket Amounts:2,2, 1, 1, 1]
>Date|Weekday|12-18|19-24|00-06
@@TicketAmountsByPeakHours
>@@TicketAmountsByPeakHoursTotals

###@@TicketAmountsByPeakHours

SELECT
Convert(nvarchar(20),Date,103),
min(datename(dw,[Date])) as WeekName,
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 12 AND 18 THEN TotalAmount ELSE 0 END),
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19 AND 24 THEN TotalAmount ELSE 0 END),
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 00 AND 06 THEN TotalAmount ELSE 0 END)
from Tickets
Where Date > '{Start}' and Date < '{End}'
GROUP BY Convert(nvarchar(20),Date,103)

###@@TicketAmountsByPeakHoursTotals

SELECT 'Totals',' ',
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 12 AND 18 THEN TotalAmount ELSE 0 END),
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19 AND 24 THEN TotalAmount ELSE 0 END),
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 00 AND 06 THEN TotalAmount ELSE 0 END)
FROM Tickets
Where Date > '{Start}' and Date < '{End}'

###Result

pizzaeilat4 · August 17, 2014, 10:20am

@emre,
Thank you
Scripting that’s not my strong side
To say the least

emre · August 17, 2014, 10:33am

So let me show another example… These reports will generate a row for each day for the given date range. We can also display weekday totals for the given range.

###Template
[Ticket Counts:2,2,1,1,1]
>Date|Weekday|12-18|19-24|00-06
@@TicketCountsByPeakHours
>@@TicketCountsByPeakHoursTotals

[Ticket Amounts:2,2, 1, 1, 1]
>Date|Weekday|12-18|19-24|00-06
@@TicketAmountsByPeakHours
>@@TicketAmountsByPeakHoursTotals

[Ticket Amounts by Week:2,1,1,1]
>Weekday|12-18|19-24|00-06
@@TicketAmountsByPeakHoursAndWeek
>@@TicketAmountsByPeakHoursAndWeekTotals

###@@TicketAmountsByPeakHoursAndWeek

SELECT
datename(dw,[Date]) as WeekName,
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 12 AND 18 THEN TotalAmount ELSE 0 END),
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19 AND 24 THEN TotalAmount ELSE 0 END),
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 00 AND 06 THEN TotalAmount ELSE 0 END)
from Tickets
Where Date > '{Start}' and Date < '{End}'
GROUP BY datename(dw,[Date]),datepart(dw,[Date])
order by datepart(dw,[Date])

###@@TicketAmountsByPeakHoursAndWeekTotals

SELECT 'Totals',
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 12 AND 18 THEN TotalAmount ELSE 0 END),
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19 AND 24 THEN TotalAmount ELSE 0 END),
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 00 AND 06 THEN TotalAmount ELSE 0 END)
FROM Tickets
Where Date > '{Start}' and Date < '{End}'

###Result

pizzaeilat4 · August 17, 2014, 10:34am

@emre,
One last thing (I hope)
It is possible to insert to the report an entity custom field?
For example Address.
thenks
also,I think it would be nice to post all kinds of examples of reports for people like me who do not know how to messed with scripting

emre · August 17, 2014, 12:47pm

Reading Custom Fields is a little tricky but possible with some effort For example we can create a customer report that lists customers that have account balances.

###Template

[Customers:3,2,3,2]
>Name|Phone|Address|Balance
@@CustomersWithAccountBalance

###@@CustomersWithAccountBalance

Select e.Name,

min(substring(cast(CustomData as nvarchar(4000)),
            CharIndex('{"Name":"Phone","Value":"',cast(CustomData as nvarchar(4000)))+26,
             CharIndex('"',cast(CustomData as nvarchar(4000)),
                CharIndex('{"Name":"Phone","Value":"',cast(CustomData as nvarchar(4000)))+26
              )- CharIndex('{"Name":"Phone","Value":"',cast(CustomData as nvarchar(4000)))-26) 
        ) as [Phone],
                    
min(substring(cast(CustomData as nvarchar(4000)),
            CharIndex('{"Name":"Address","Value":"',cast(CustomData as nvarchar(4000)))+27,              
             CharIndex('"',cast(CustomData as nvarchar(4000)),
                CharIndex('{"Name":"Address","Value":"',cast(CustomData as nvarchar(4000)))+27
              )- CharIndex('{"Name":"Address","Value":"',cast(CustomData as nvarchar(4000)))-27) 
        ) as [Address],
        
sum(AV.Debit-AV.Credit)

FROM Entities as e
  inner join AccountTransactionValues as AV on AV.AccountId = E.AccountId
Where EntityTypeId = 1 
Group by e.Name
HAVING sum(AV.debit-AV.credit) <> 0

###Result

Note: We can write better scripts for SQL Server but I’m trying to create samples that works for both SQL Express and SQL CE.

pizzaeilat4 · August 17, 2014, 1:14pm

@emre,
I tried and I get an error

-----------------------------
[General Info]

Application: SambaPOS
Version:     4.1.48
Region:      en
DB:          SQ
Machine:     PIZZAEILAT1
User:        איציק
Date:        17/08/2014
Time:        13:11

User Explanation:

איציק said ""
-----------------------------

[Exception Info 1]

Top-level Exception
Type:        System.Data.SqlClient.SqlException
Message:     Ambiguous column name 'Name'.
Source:      .Net SqlClient Data Provider
Stack Trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at Samba.Infrastructure.Data.SqlData.EFWorkspace.ExecSqlQuery(String sqlScript)
   at Samba.Addon.CustomReports.CustomReportViewerViewModel.UpdateLineSql(String line)
   at Samba.Addon.CustomReports.CustomReportViewerViewModel.AddContent(String line, SimpleReport simpleReport)
   at Samba.Addon.CustomReports.CustomReportViewerViewModel.ProcessLine(String line, SimpleReport simpleReport)
   at Samba.Addon.CustomReports.CustomReportViewerViewModel.CreateCustomReport(SimpleReport simpleReport)
   at Samba.Addon.CustomReports.CustomReportViewerViewModel.GetReport()
   at Samba.Presentation.Common.Reports.AbstractReport.<>c__DisplayClass8.<RefreshReport>b__2(Object param0, DoWorkEventArgs param1)
   at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

-----------------------------

[Assembly Info]

Samba.Services, Version=1.0.0.0
mscorlib, Version=4.0.0.0
System, Version=4.0.0.0
Microsoft.Practices.Prism, Version=4.0.0.0
WindowsBase, Version=4.0.0.0
Samba.Infrastructure, Version=1.0.0.0
Samba.Infrastructure.Data, Version=1.0.0.0
PresentationFramework, Version=4.0.0.0
System.Xaml, Version=4.0.0.0
Samba.Presentation.Services, Version=1.0.0.0
FluentValidation, Version=3.4.0.0
Samba.Domain, Version=1.0.0.0
DevExpress.Xpf.Grid.v14.1, Version=14.1.5.0
DevExpress.Xpf.Grid.v14.1.Core, Version=14.1.5.0
PresentationCore, Version=4.0.0.0
Stateless, Version=1.0.0.0
System.Core, Version=4.0.0.0
System.Drawing, Version=4.0.0.0
System.Windows.Forms, Version=4.0.0.0
System.ComponentModel.Composition, Version=4.0.0.0
Samba.Localization, Version=1.0.0.0
Microsoft.CSharp, Version=4.0.0.0
Microsoft.Practices.ServiceLocation, Version=1.0.0.0
Samba.Persistance, Version=1.0.0.0
Microsoft.Practices.Prism.MefExtensions, Version=4.0.0.0
DevExpress.Xpf.Core.v14.1, Version=14.1.5.0
PropertyTools, Version=2012.4.14.1

-----------------------------

[System Info]

Operating System
-Microsoft Windows 8 Pro
--CodeSet = 1255
--CSDVersion = 
--CurrentTimeZone = 180
--FreePhysicalMemory = 121396
--OSArchitecture = 32-bit
--OSLanguage = 1037
--ServicePackMajorVersion = 0
--ServicePackMinorVersion = 0
--Version = 6.2.9200

Machine
-PIZZAEILAT1
--Manufacturer = ASUSTeK Computer INC.
--Model = 1015PEM
--TotalPhysicalMemory = 1063374848
--UserName = pizzaeilat1\איציק

-----------------------------

emre · August 17, 2014, 1:31pm

OK. I’ve updated the query.

pizzaeilat4 · August 17, 2014, 2:25pm

@emre,
Sorry to bother you, it still does not work

-----------------------------
[General Info]

Application: SambaPOS
Version:     4.1.48
Region:      en
DB:          SQ
Machine:     PIZZAEILAT1
User:        איציק
Date:        17/08/2014
Time:        14:22

User Explanation:

איציק said ""
-----------------------------

[Exception Info 1]

Top-level Exception
Type:        System.Data.SqlClient.SqlException
Message:     Invalid length parameter passed to the LEFT or SUBSTRING function.
Source:      .Net SqlClient Data Provider
Stack Trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at System.Data.SqlClient.SqlDataReader.Read()
   at Samba.Infrastructure.Data.SqlData.EFWorkspace.<Read>d__a.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Samba.Infrastructure.Data.SqlData.EFWorkspace.ExecSqlQuery(String sqlScript)
   at Samba.Addon.CustomReports.CustomReportViewerViewModel.UpdateLineSql(String line)
   at Samba.Addon.CustomReports.CustomReportViewerViewModel.AddContent(String line, SimpleReport simpleReport)
   at Samba.Addon.CustomReports.CustomReportViewerViewModel.ProcessLine(String line, SimpleReport simpleReport)
   at Samba.Addon.CustomReports.CustomReportViewerViewModel.CreateCustomReport(SimpleReport simpleReport)
   at Samba.Addon.CustomReports.CustomReportViewerViewModel.GetReport()
   at Samba.Presentation.Common.Reports.AbstractReport.<>c__DisplayClass8.<RefreshReport>b__2(Object param0, DoWorkEventArgs param1)
   at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

-----------------------------

[Assembly Info]

Samba.Services, Version=1.0.0.0
mscorlib, Version=4.0.0.0
System, Version=4.0.0.0
Microsoft.Practices.Prism, Version=4.0.0.0
WindowsBase, Version=4.0.0.0
Samba.Infrastructure, Version=1.0.0.0
Samba.Infrastructure.Data, Version=1.0.0.0
PresentationFramework, Version=4.0.0.0
System.Xaml, Version=4.0.0.0
Samba.Presentation.Services, Version=1.0.0.0
FluentValidation, Version=3.4.0.0
Samba.Domain, Version=1.0.0.0
DevExpress.Xpf.Grid.v14.1, Version=14.1.5.0
DevExpress.Xpf.Grid.v14.1.Core, Version=14.1.5.0
PresentationCore, Version=4.0.0.0
Stateless, Version=1.0.0.0
System.Core, Version=4.0.0.0
System.Drawing, Version=4.0.0.0
System.Windows.Forms, Version=4.0.0.0
System.ComponentModel.Composition, Version=4.0.0.0
Samba.Localization, Version=1.0.0.0
Microsoft.CSharp, Version=4.0.0.0
Microsoft.Practices.ServiceLocation, Version=1.0.0.0
Samba.Persistance, Version=1.0.0.0
Microsoft.Practices.Prism.MefExtensions, Version=4.0.0.0
DevExpress.Xpf.Core.v14.1, Version=14.1.5.0
PropertyTools, Version=2012.4.14.1

-----------------------------

[System Info]

Operating System
-Microsoft Windows 8 Pro
--CodeSet = 1255
--CSDVersion = 
--CurrentTimeZone = 180
--FreePhysicalMemory = 377936
--OSArchitecture = 32-bit
--OSLanguage = 1037
--ServicePackMajorVersion = 0
--ServicePackMinorVersion = 0
--Version = 6.2.9200

Machine
-PIZZAEILAT1
--Manufacturer = ASUSTeK Computer INC.
--Model = 1015PEM
--TotalPhysicalMemory = 1063374848
--UserName = pizzaeilat1\איציק

-----------------------------

emre · August 17, 2014, 2:28pm

Did you updated query for your custom field names? Please note +26 is the lenght of {"Name":"Phone","Value":" part so you should also update this.

pizzaeilat4 · August 17, 2014, 2:58pm

@emre I can not do it
The name in this field is טלפון
The phone in this field is כתובת

emre · August 17, 2014, 9:07pm

OK nevermind. I’ll release an easier configuration for it on next updates.

emre · August 19, 2014, 8:30pm

###How to display Custom Field Values

We’re storing Entity Custom Field Values as JSON Format.

For example…

select Name,
       CustomData as Phone,
       CustomData as Address 
from Entities where EntityTypeId = 1

This SQL Scripts selects CustomData value twice as Phone and Address. However we’ll see Custom Field values not easily readable.

Let’s see what that data contains.

[{"Name":"Phone","Value":"2222222222"},
 {"Name":"Address","Value":"Viaport yanı. 6 cadde 22. sokak. İstanbul"}]

It says Phone field equals 2222222222. Instead of these values we need to display just Phone field value. What I need to do is telling SambaPOS to display Phone value here. I’ll change my template as…

[Customer List:1,1,1]
>Name|:Phone|:Address
@@CustomerList

I’ve added a Semi Colon character : before Phone and Address headers. That means it should display just Phone value instead of JSON data. So if your custom field name is Birthday you should type column header as :Birthday.

Our report magically becomes…

pizzaeilat4 · August 19, 2014, 9:49pm

@emre,
It seems to work but the report contains data from day one and it takes a lot of time and the software gets stuck
How to limit it to the current work period??

emre · August 20, 2014, 1:19pm

@pizzaeilat4 please let me know which report we are talking about. This is customer list and it only lists all customers. I just wanted to demonstrate how Custom Field reading works. What kind of a filter you are looking for? Customers only ordered in last work period?

pizzaeilat4 · August 20, 2014, 1:30pm

thenks @emre
I need one report that contains the following data
Ticket No,Date, time, phone, address, amount
And one report Per hour like you did thet include phone, address
my main field It’s Phone