pauln
June 23, 2016, 10:34am
1
Do we have a method to remove blank values in Reports?
I have this syntax:
[Net Payment Details:2, 1, 2]
>Consolidated Departments
@{TAX TYPE LIST}
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum}
{REPORT CALCULATION DETAILS:C.Name,C.X,C.CalculationAmount.Sum}
$1||{REPORT TICKET DETAILS:TX.$2.Sum}
|
@{REPORT SQL DETAILS:SELECT Departments.Name FROM Departments:F.Name::,}
@{TAX TYPE LIST}
>Dept: $1
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum:(DE=$1)}
{REPORT CALCULATION DETAILS:C.Name,C.X,C.CalculationAmount.Sum:(DE=$1)}
$2||{REPORT TICKET DETAILS:TX.$2.Sum:(DE=$1)}
It produces:
The report would be much shorter if we could?
emre
June 23, 2016, 12:37pm
2
[off topic] How consolidated GST can be zero if GST is 1.95 on Dept: Bar?
pauln
June 23, 2016, 12:44pm
3
Yes its wrong as still playing with the syntax. Trying to remove blank lines with ‘0’ values.
I was just going to post this image: @emre :
If we had access to Tax Name instead of having to use $2 at the beginning of the line I could possibly remove blank GST lines?
Latest Syntax:
[Net Payment Details:2, 1, 2]
>Consolidated Departments
@{TAX TYPE LIST}
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum}
{REPORT CALCULATION DETAILS:C.Name,C.X,C.CalculationAmount.Sum:C.CalculationAmount <> 0}
$1||{REPORT TICKET DETAILS:TX.$1.Sum}
|
@{REPORT SQL DETAILS:SELECT Departments.Name FROM Departments:F.Name::,}
@{TAX TYPE LIST}
>Dept: $1
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum:(DE=$1)}
{REPORT CALCULATION DETAILS:C.Name,C.X,C.CalculationAmount.Sum:(DE=$1) && C.CalculationAmount <> 0}
{REPORT TICKET DETAILS:TX.$2.Sum:(DE=$1)}
Just need to add the names below:
But using this syntax give me a blank lines with just GST? (As shown in the top image)
$2||{REPORT TICKET DETAILS:TX.$2.Sum:(DE=$1)}
Any suggestions Emre? Close now but just need to remove:
Departments with no data; and
GST lines with no Values.
EDIT:
{REPORT TICKET DETAILS:$2,TX.$2.Sum:(DE=$1)}
emre
June 23, 2016, 1:06pm
4
So how you define empty? Here you’re telling it to process all departments by sending all department names. You should either filter out departments by how you define empty or fetch departments from ticket details report so departments that doesn’t have a ticket won’t appear. You can try this one instead.
{REPORT TICKET DETAILS:T.Department,T.TotalAmount.sum::{0}:,}
pauln
June 23, 2016, 1:10pm
5
That is good Emre I will try that now.
So @emre what can we do about not showing the GST line if it sum is ‘0’? I assume you have preceded the line with a $3||{REPORT… in your original Work Period Custom Report because there was no access to Tax name in the template?
emre
June 23, 2016, 1:21pm
6
hmmm
So there is no prepare report and remove zero values feature. Any value can be zero and should appear in report depending in context so you need to define what you want at first place.
pauln
June 23, 2016, 1:35pm
7
Ok will accept that, probably good for them to know there was no GST (unlikely event).
I am having a problem getting your syntax to work to eliminate Departments with no data.
@{REPORT TICKET DETAILS:T.Department,T.TotalAmount.sum::{0}:,}
>Dept: $1
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0 && DepartmentId = $1}
I can no longer use && DepartmentId = $1 OR Department = $1 OR T.Department?
emre
June 23, 2016, 1:43pm
8
If you want department id’s instead of department names change it as …
{REPORT TICKET DETAILS:T.DepartmentId,T.TotalAmount.sum::{0}:,}
Isn’t it amazing?
pauln
June 23, 2016, 1:51pm
9
Did not see T.DepartmentId in the list! - But I need both?
I was going to ask that question how do is use $1 and $2 as:
{REPORT TICKET DETAILS:T.DepartmentId,:T.Department,T.TotalAmount.sum::{0}{1}:,}
Then reference them in:
@{REPORT TICKET DETAILS:T.Department,T.DepartmentId,T.TotalAmount.sum::{0}:,}
>Dept: $1
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0 && DepartmentId = $2}
Place Marker $1 → Show Department Name
Of course @emre the issue originally was that the syntax (DE=$1) is not supported on REPORT TICKET DETAILS.
EDIT:
@{REPORT TICKET DETAILS:T.Department,T.TotalAmount.sum::{0}:,}
@{REPORT TICKET DETAILS:T.DepartmentId,T.TotalAmount.sum::{0}:,}
>Dept: $1
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0 && DepartmentId = $2}
** I should say that does not work for anyone reading.
emre
June 23, 2016, 1:58pm
10
REPORT TICKET TYPES? lol when topic switched to that? This is not a custom report tag so may not work with parameters as expected.
pauln
June 23, 2016, 2:01pm
11
Yes, yes forgot the WP Report have 4 sections and I switched bac to the first section. I think your syntax might work for the rest.
pauln
June 23, 2016, 3:53pm
12
Thanks @emre - the END RESULT!:
Can’t put $ signs in the 1st Stand Tag sections but could parse that if I was desperate lol
As I’ve explained on my previous post you want to display all taxes by using @{TAX TYPE LIST}. I can’t write it from memory but you need to fetch tax names that used in tickets. Probably you’ll need some SQL here.
