REPORT SQL comma delimted input list

V5 Question
#1

I just cannot seem to get this to work to product a list of Department ID’s?

{REPORT SQL DETAILS:SELECT Departments.Id FROM Departments:F.Id::{0},}

Here is the syntax that works:

[Sales:1, 1]
@1,2,3,4
{REPORT SQL DETAILS:SELECT Departments.Id FROM Departments:F.Id::{0},}
>Consolidated
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0}
|
>By Dept: $1
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0 && DepartmentID == $1}

Produces:

This will not work?

[Sales:1, 1]
1,2,3,4
@{REPORT SQL DETAILS:SELECT Departments.Id FROM Departments:F.Id::{0},}
>Consolidated
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0}
|
>By Dept: $1
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0 && DepartmentID == $1}

Produces:

Help, please…

#2

Also @QMcKay (sorry to call} but I am trying to use your explanation of using 2 parameters in a Report ‘$1’ and ‘$2’ created from a single {REPORT SQL DETAILS:x} command.

This syntax almost meets a departmentalised report:

[Sales:1, 1]
@1,2,3,4
{REPORT SQL DETAILS:SELECT Departments.Id FROM Departments:F.Id::{0},}
>Consolidated
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0}
|
>By Dept: {REPORT SQL DETAILS:SELECT [Name] FROM Departments WHERE Departments.Id = $1:F.Name::{0}}
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0 && DepartmentID == $1}

Results:

But this line in particular I need to issue a 2nd Report SQL command just to get the “Descriptive” Department Name!

>By Dept: {REPORT SQL DETAILS:SELECT [Name] FROM Departments WHERE Departments.Id = $1:F.Name::{0}}

I can do this but have not idea how to apply it - especially to the {REPORT TICKET TYPES} tag.

{REPORT SQL DETAILS:SELECT Departments.Id, Departments.Name FROM Departments:F.Id,F.Name::{0},{1}}

This is just the 1st section of the Work Periods report - finding it tough going :tired_face:

#3

So I am trapping the output from {REPORT SQL DETAILS:x} and not so sure if this should be as shown below?

What the :confused:

EDIT:
So I have consumed yet another Sunday trying to do work arounds. This now works:

@{CALL:Stripper.Text("{REPORT SQL DETAILS:SELECT Departments.Id FROM Departments:F.Id::{0},}")}

[Sales:1, 1]
>Consolidated
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0}
|
>By Dept: {REPORT SQL DETAILS:SELECT [Name] FROM Departments WHERE Departments.Id = $1:F.Name::{0}}
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0 && DepartmentId = $1}

I had to strip "!rn" from the output! So please tell me I do not have to do his through the whole Report…

#4

Delimiter should be the last section so it should be either

@{REPORT SQL DETAILS:SELECT Departments.Id FROM Departments:F.Id::,}

or

@{REPORT SQL DETAILS:SELECT Departments.Id FROM Departments:F.Id::{0}:,}

Seems like you defined both line format and value delimiter on last section and it thought you want to generate rows each ending with a comma.

PS {0} is not needed as you’re already fetching single value. In some cases we fetch 2 values, group by one of them (by using sum or distinct) and use the other value as report parameter… We use {0} (or {1} in some cases) to build a parameter list from that specific value (column). That is useful if your parameter list query returns non-distinct values.

1 Like