COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Service\Service_Umsätze_test.imr
TITLE,Service_Umsätze_test.imr
BEGIN SQL
select distinct T1."G_L Account No_" as c1,
	   T1."Posting Date" as c2,
	   T1."Document Type" as c3,
	   T1."Document No_" as c4,
	   T1."Description" as c5,
	   (cast_float(T1."Amount")) * -1 as c6,
	   T1."Gen_ Bus_ Posting Group" as c7,
	   T1."Service Ledger Entry No_" as c8,
	   T1."Posting Date" as c9,
	   '1' as c10,
	   T1."Branch Code" as c11,
	   CASE WHEN ((T1."Branch Code" = 'LBS') or (T1."Branch Code" = '')) THEN ('10') WHEN (T1."Branch Code" = 'WLS') THEN ('20') ELSE null END as c12,
	   T1."Entry No_" as c13,
	   T2."Order No_" as c14,
	   T3."EBENE3" as c15,
	   (substring(T3."EBENE3" from 1 for 4)) as c16,
	   CASE WHEN (((substring(T3."EBENE3" from 1 for 4))) BETWEEN '6010' AND '6140') THEN (((cast_float(T1."Amount")) * -1)) ELSE (0) END as c17,
	   CASE WHEN (((substring(T3."EBENE3" from 1 for 4))) BETWEEN '6220' AND '6270') THEN (((cast_float(T1."Amount")) * -1)) ELSE (0) END as c18
from "DMS1"."dbo"."Automag GmbH$G_L Entry" T1,
	"DMS1"."dbo"."Automag GmbH$Service Ledger Entry" T2,
	QSS."C:\GAPS_BMW\Portal\System\IQD\Service\GCStruct.ims" T3
where ((T1."Document No_" = T2."Document No_") and (T1."Gen_ Bus_ Posting Group" = T2."Gen_ Bus_ Posting Group")) and (T1."G_L Account No_" = T3."KONTO_NR")
and ((T1."Posting Date" >= TIMESTAMP '2014-01-01 00:00:00.000') and (((substring(T3."EBENE3" from 1 for 4))) IN ('6010','6020','6030','6036','6037','6038','6170','6180','6190','6090','6100','6110','6040','6130','6140','6150','6050','6060','6070','6270','6240','6220','6250','6200','6230','6000','6035','6076','6077','6078')))

END SQL
COLUMN,0,G L Account No
COLUMN,1,Posting Date
COLUMN,2,Document Type
COLUMN,3,Document No
COLUMN,4,Description
COLUMN,5,Amount
COLUMN,6,Gen Bus Posting Group
COLUMN,7,Service Ledger Entry No
COLUMN,8,Invoice Date
COLUMN,9,Hauptbetrieb
COLUMN,10,Branch Code
COLUMN,11,Standort
COLUMN,12,Entry No
COLUMN,13,Order No
COLUMN,14,Ebene3
COLUMN,15,Zeile
COLUMN,16,Umsatz Lohn
COLUMN,17,Umsatz Sonstiges