Dynamický dotaz - CRM - Report Průměrná tržba dle NACE (za firmu)

ID:

VRRARQCU52S4J4HENLC1Q3GITG

Datasety

MAIN

Popis:

Obor činnosti NACE

SQL definice:

SELECT {FIELDS},
(COALESCE(A.Code,A.Code,'      ') ||
 COALESCE(B.Code,B.Code,'      ') ||
 COALESCE(C.Code,C.Code,'      ') ||
 COALESCE(D.Code,D.Code,'      ') ||
 COALESCE(E.Code,E.Code,'      ')) AS CodeKey
FROM NACE A
  {JOIN}
WHERE (A.Parent_ID = '0000000000') OR (A.Parent_ID IS NULL)
  and (E.Hidden = 'N' or E.Hidden is null)
  and (D.Hidden = 'N' or D.Hidden is null) and (C.Hidden = 'N' or C.Hidden is null)
  and (B.Hidden = 'N' or B.Hidden is null) and (A.Hidden = 'N' or A.Hidden is null)
AND
(
  (({$NACESelID}
  = ''
  )
  OR
  (A.ID in
  (SELECT Bx.ID FROM NACE Bx WHERE Bx.Hidden = 'N' AND
    ( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})) OR
    (({$NACEWithChilds} in ('1', 'A')) and
    (Bx.ID in (select ID from Sys$NACE2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})))))
  )))
OR
  (({$NACESelID}
  = ''
  )
  OR
  (B.ID in
  (SELECT Bx.ID FROM NACE Bx WHERE Bx.Hidden = 'N' AND
    ( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})) OR
    (({$NACEWithChilds} in ('1', 'A')) and
    (Bx.ID in (select ID from Sys$NACE2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})))))
  )))
OR
  (({$NACESelID}
  = ''
  )
  OR
  (C.ID in
  (SELECT Bx.ID FROM NACE Bx WHERE Bx.Hidden = 'N' AND
    ( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})) OR
    (({$NACEWithChilds} in ('1', 'A')) and
    (Bx.ID in (select ID from Sys$NACE2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})))))
  )))
OR
  (({$NACESelID}
  = ''
  )
  OR
  (D.ID in
  (SELECT Bx.ID FROM NACE Bx WHERE Bx.Hidden = 'N' AND
    ( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})) OR
    (({$NACEWithChilds} in ('1', 'A')) and
    (Bx.ID in (select ID from Sys$NACE2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})))))
  )))
OR
  (({$NACESelID}
  = ''
  )
  OR
  (E.ID in
  (SELECT Bx.ID FROM NACE Bx WHERE Bx.Hidden = 'N' AND
    ( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})) OR
    (({$NACEWithChilds} in ('1', 'A')) and
    (Bx.ID in (select ID from Sys$NACE2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = {$NACESelID})))))
  )))
)
{ANDWHERE}
ORDER BY CodeKey

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	GEPU3UY3U2POB4VWN4FIPOAQEG	Level_1
B	GEPU3UY3U2POB4VWN4FIPOAQEG	Level_2	NACE kód
C	GEPU3UY3U2POB4VWN4FIPOAQEG	Level_3	NACE kód
D	GEPU3UY3U2POB4VWN4FIPOAQEG	Level_4	NACE kód
E	GEPU3UY3U2POB4VWN4FIPOAQEG	Level_5	NACE kód

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
L1Amount	dtFloat	15	(select TotalAmount from FirmNACETurnoverTotalCounts(A.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Tržba
L1AmountWithoutVAT	dtFloat	15	(select TotalAmountWithoutVAT from FirmNACETurnoverTotalCounts(A.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Tržba bez DPH
L1FirmCount	dtInteger	10	(select FirmCount from FirmNACETurnoverTotalCounts(A.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Počet firem
L2Amount	dtFloat	10	(select TotalAmount from FirmNACETurnoverTotalCounts(B.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Tržba
L2AmountWithoutVAT	dtFloat	15	(select TotalAmountWithoutVAT from FirmNACETurnoverTotalCounts(B.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Tržba bez DPH
L2FirmCount	dtInteger	10	(select FirmCount from FirmNACETurnoverTotalCounts(B.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Počet firem
L3Amount	dtFloat	10	(select TotalAmount from FirmNACETurnoverTotalCounts(C.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Tržba
L3AmountWithoutVAT	dtFloat	15	(select TotalAmountWithoutVAT from FirmNACETurnoverTotalCounts(C.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Tržba bez DPH
L3FirmCount	dtInteger	10	(select FirmCount from FirmNACETurnoverTotalCounts(C.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Počet firem
L4Amount	dtFloat	10	(select TotalAmount from FirmNACETurnoverTotalCounts(D.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Tržba
L4AmountWithoutVAT	dtFloat	15	(select TotalAmountWithoutVAT from FirmNACETurnoverTotalCounts(D.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Tržba bez DPH
L4FirmCount	dtInteger	10	(select FirmCount from FirmNACETurnoverTotalCounts(D.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Počet firem
L5Amount	dtFloat	10	(select TotalAmount from FirmNACETurnoverTotalCounts(E.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Tržba
L5AmountWithoutVAT	dtFloat	15	(select TotalAmountWithoutVAT from FirmNACETurnoverTotalCounts(E.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Tržba bez DPH
L5FirmCount	dtInteger	10	(select FirmCount from FirmNACETurnoverTotalCounts(E.ID, {$NACESelID}, {$NACEWithChilds}, {$DocDateUsed},{$DocDateFrom},{$DocDateTo}, {$DueDateUsed},{$DueDateFrom},{$DueDateTo}, {$DivisionsSelID},{$DivisionsWithChilds}, {$BusOrdersSelID},{$BusOrdersWithChilds}, {$BusTransactionsSelID},{$BusTransactionsWithChilds}, {$BusProjectsSelID},{$BusProjectsWithChilds}, {$FirmSelID},{$IssInvoiceType},{$CashRecType}, 'N', 1) )		Počet firem

Podmínky

ID

Šablona:

ID

Dataset:

MAIN

Speciální parametry:

CLASSID=GEPU3UY3U2POB4VWN4FIPOAQEG

Code

Šablona:

Code

Dataset:

MAIN

Speciální parametry:

Hidden=True

Name

Šablona:

Name

Dataset:

MAIN

Speciální parametry:

Hidden=True

NotZero

Popiska:

Jen nenulové hodnoty

Typ/Subtyp:

ctBoolean/0

ckSingle:

(
({:Value} = 'A')
OR
({:Value} = 'N')
)

Dataset:

MAIN

NACE_ID

Šablona:

NACE_ID (Replacement link)

Dataset:

MAIN

IssInvoiceType

Popiska:

Faktury vydané

Typ/Subtyp:

ctSpecial/1

ckSingle:

<field> = {:VALUE}

Dataset:

MAIN

Speciální parametry:

AlwaysUsed=True
ReplacementLink=True
{$IssInvoiceType}=VALUE;0
Names=Snížené o dobropisy;Nesnížené o dobropisy;Nezahrnout
Values=0;1;2

CashRecType

Popiska:

Pokladní příjem

Typ/Subtyp:

ctSpecial/1

ckSingle:

<field> = {:VALUE}

ckList:

<field> IN ({:LIST})

Dataset:

MAIN

Speciální parametry:

AlwaysUsed=True
ReplacementLink=True
{$CashRecType}=VALUE;0
Names=Snížené o Vrácení příjmu;Nesnížené o vrácení;Nezahrnout
Values=0;1;2

DocDate

Šablona:

DocDate

Dataset:

MAIN

Speciální parametry:

ReplacementLink=True
{$DocDateFrom}=VALUE;0
{$DocDateTo}=VALUEHIGH;0
{$DocDateUsed}=USED;'N'

DueDate

Šablona:

DueDate

Dataset:

MAIN

Speciální parametry:

ReplacementLink=True
{$DueDateFrom}=VALUE;0
{$DueDateTo}=VALUEHIGH;0
{$DueDateUsed}=USED;'N'

Division_ID

Šablona:

Division_ID (Replacement Link)

Dataset:

MAIN

BusOrder_ID

Šablona:

BusOrder_ID (Replacement Link)

Dataset:

MAIN

BusTransaction_ID

Šablona:

BusTransaction_ID (Replacement Link)

Dataset:

MAIN

BusProject_ID

Šablona:

BusProject_ID (Replacement Link)

Dataset:

MAIN

Firm_ID

Šablona:

Firm_ID (Replacement Link)

Dataset:

MAIN

Generated by ABRA Software a.s. 27.10.2021 16:36