GAPS/Portal/System/IQD/Planung/Umsatz_Einsatz_2018_GuV.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Planung_2012
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Planung\Umsatz_Einsatz_2018_GuV.imr
TITLE,Umsatz_Einsatz_2018_GuV.imr
BEGIN SQL
select T1."Art" as c1,
	   T1."Acct Nr_ori" as c2,
	   T1."Ledger Accts Name" as c3,
	   T1."Marke_1" as c4,
	   T1."Modell" as c5,
	   T1."Abnehmergruppe" as c6,
	   T1."Ebene1" as c7,
	   T1."Ebene2" as c8,
	   T1."Ebene3" as c9,
	   T1."Ebene4" as c10,
	   T1."Ebene5" as c11,
	   T1."Ebene6" as c12,
	   T1."Jahr" as c13,
	   T1."Mandant" as c14,
	   T1."Kürzel" as c15,
	   T1."KST" as c16,
	   T1."Marke" as c17,
	   T1."Plan" as c18,
	   T1."Stück" as c19,
	   CASE WHEN (T1."Kürzel" IN ('EW')) THEN (0) ELSE (T1."Stück") END as c20,
	   T1."Plan" / 12 as c21,
	   T1."Plan" / 12 as c22,
	   T1."Plan" / 12 as c23,
	   T1."Plan" / 12 as c24,
	   T1."Plan" / 12 as c25,
	   T1."Plan" / 12 as c26,
	   T1."Plan" / 12 as c27,
	   T1."Plan" / 12 as c28,
	   T1."Plan" / 12 as c29,
	   T1."Plan" / 12 as c30,
	   T1."Plan" / 12 as c31,
	   T1."Plan" / 12 as c32,
	   T1."Stück" / 12 as c33,
	   T1."Stück" / 12 as c34,
	   T1."Stück" / 12 as c35,
	   T1."Stück" / 12 as c36,
	   T1."Stück" / 12 as c37,
	   T1."Stück" / 12 as c38,
	   T1."Stück" / 12 as c39,
	   T1."Stück" / 12 as c40,
	   T1."Stück" / 12 as c41,
	   T1."Stück" / 12 as c42,
	   T1."Stück" / 12 as c43,
	   T1."Stück" / 12 as c44,
	   CASE WHEN (T1."Kürzel" IN ('VE')) THEN ('Umsatz') WHEN (T1."Kürzel" IN ('EW')) THEN ('Materialaufwand') ELSE ('Steuerung Ebene 31 fehlt') END as c45,
	   CASE WHEN (T1."KST" IN (1)) THEN ('Neuwagen') WHEN (T1."KST" IN (2)) THEN ('Gebrauchtwagen') ELSE ('Werkstatt & sonstige Erträge') END as c46,
	   CASE WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW Kunden EU','NW Kunden EU ID','NW Kunden EX','NW Kunden Kauf','NW Kunden Leasing'))) THEN ('NW Kunden') ELSE (CASE WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW Gewerbekunden EU ID','NW Gewerbekunden Kauf','NW Gewerbekunden Leasing'))) THEN ('NW Gewerbekunden') ELSE (CASE WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('ET BO Org. ET','NW Großkunden EU ID','NW Großkunden Kauf','NW Großkunden Leasing'))) THEN ('NW Großkunden') WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW Vermittler Kauf'))) THEN ('NW Vermittler') WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW Händlertausch'))) THEN ('NW Händlertausch') WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW Unterhändler'))) THEN ('NW Unterhändler') ELSE (CASE WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW verb. UN'))) THEN ('NW verb. UN') WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW Organschaften'))) THEN ('NW Organschaften') ELSE (CASE WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW CITOP','NW Bonus'))) THEN ('NW Bonus') ELSE (CASE WHEN (T1."KST" = 1) THEN ('NW Sonstige Erträge') ELSE ('kein NW') END) END) END) END) END) END as c47,
	   CASE WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Kunden EU','GW Kunden EU ID','GW Kunden EX','GW Kunden Kauf','GW Kunden Leasing','GW Kunden Nato'))) THEN ('GW Kunden') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('ET BO Org. ET','GW Gewerbekunden EU','GW Gewerbekunden EU ID','GW Gewerbekunden EX','GW Gewerbekunden Kauf','GW Gewerbekunden Leasing'))) THEN ('GW Gewerbekunden') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Großkunden EU','GW Großkunden EU ID','GW Großkunden EX','GW Großkunden Kauf','GW Großkunden Leasing','GW Großkunden Nato'))) THEN ('GW Großkunden') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Vermittler'))) THEN ('GW Vermittler') ELSE (CASE WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Rotationsfzg.'))) THEN ('GW Rotationsfzg.') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Händlertausch'))) THEN ('GW Händlertausch') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Unterhändler'))) THEN ('GW Unterhändler') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW verb. UN'))) THEN ('GW ver. UN') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Organschaften'))) THEN ('GW Organschaften') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Bonus'))) THEN ('GW Bonus') WHEN (T1."KST" = 2) THEN ('GW Sonstige Erträge') ELSE ('kein GW') END) END as c48,
	   CASE WHEN (T1."KST" IN (6)) THEN ('Teile') WHEN (T1."KST" IN (3,4,5)) THEN ('Service') WHEN (T1."KST" IN (7)) THEN ('Opel Rent') WHEN (T1."KST" IN (8)) THEN ('Tankstelle') ELSE (CASE WHEN (T1."KST" IN (9)) THEN ('Sonstige Erlöse') ELSE ('kein Werkst_Sonst') END) END as c49,
	   CASE WHEN (T1."KST" IN (1)) THEN ((CASE WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW Kunden EU','NW Kunden EU ID','NW Kunden EX','NW Kunden Kauf','NW Kunden Leasing'))) THEN ('NW Kunden') ELSE (CASE WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW Gewerbekunden EU ID','NW Gewerbekunden Kauf','NW Gewerbekunden Leasing'))) THEN ('NW Gewerbekunden') ELSE (CASE WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('ET BO Org. ET','NW Großkunden EU ID','NW Großkunden Kauf','NW Großkunden Leasing'))) THEN ('NW Großkunden') WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW Vermittler Kauf'))) THEN ('NW Vermittler') WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW Händlertausch'))) THEN ('NW Händlertausch') WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW Unterhändler'))) THEN ('NW Unterhändler') ELSE (CASE WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW verb. UN'))) THEN ('NW verb. UN') WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW Organschaften'))) THEN ('NW Organschaften') ELSE (CASE WHEN ((T1."KST" = 1) and (T1."Abnehmergruppe" IN ('NW CITOP','NW Bonus'))) THEN ('NW Bonus') ELSE (CASE WHEN (T1."KST" = 1) THEN ('NW Sonstige Erträge') ELSE ('kein NW') END) END) END) END) END) END)) WHEN (T1."KST" IN (2)) THEN ((CASE WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Kunden EU','GW Kunden EU ID','GW Kunden EX','GW Kunden Kauf','GW Kunden Leasing','GW Kunden Nato'))) THEN ('GW Kunden') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('ET BO Org. ET','GW Gewerbekunden EU','GW Gewerbekunden EU ID','GW Gewerbekunden EX','GW Gewerbekunden Kauf','GW Gewerbekunden Leasing'))) THEN ('GW Gewerbekunden') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Großkunden EU','GW Großkunden EU ID','GW Großkunden EX','GW Großkunden Kauf','GW Großkunden Leasing','GW Großkunden Nato'))) THEN ('GW Großkunden') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Vermittler'))) THEN ('GW Vermittler') ELSE (CASE WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Rotationsfzg.'))) THEN ('GW Rotationsfzg.') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Händlertausch'))) THEN ('GW Händlertausch') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Unterhändler'))) THEN ('GW Unterhändler') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW verb. UN'))) THEN ('GW ver. UN') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Organschaften'))) THEN ('GW Organschaften') WHEN ((T1."KST" = 2) and (T1."Abnehmergruppe" IN ('GW Bonus'))) THEN ('GW Bonus') WHEN (T1."KST" = 2) THEN ('GW Sonstige Erträge') ELSE ('kein GW') END) END)) ELSE ((CASE WHEN (T1."KST" IN (6)) THEN ('Teile') WHEN (T1."KST" IN (3,4,5)) THEN ('Service') WHEN (T1."KST" IN (7)) THEN ('Opel Rent') WHEN (T1."KST" IN (8)) THEN ('Tankstelle') ELSE (CASE WHEN (T1."KST" IN (9)) THEN ('Sonstige Erlöse') ELSE ('kein Werkst_Sonst') END) END)) END as c50
from "UE_EW_gesamt_2018" T1
where (T1."Art" IS NULL)

END SQL
COLUMN,0,Art
COLUMN,1,Acct Nr Ori
COLUMN,2,Ledger Accts Name
COLUMN,3,Marke 1_GuV
COLUMN,4,Modell_GuV
COLUMN,5,Abnehmergruppe_GuV
COLUMN,6,Ebene1_GuV
COLUMN,7,Ebene2_GuV
COLUMN,8,Ebene3_GuV
COLUMN,9,Ebene4_GuV
COLUMN,10,Ebene5_GuV
COLUMN,11,Ebene6_GuV
COLUMN,12,Jahr
COLUMN,13,Mandant
COLUMN,14,Kürzel
COLUMN,15,Kst
COLUMN,16,Marke
COLUMN,17,Plan
COLUMN,18,Stück_ori
COLUMN,19,Stück
COLUMN,20,Wert1_GuV
COLUMN,21,Wert2_GuV
COLUMN,22,Wert3_GuV
COLUMN,23,Wert4_GuV
COLUMN,24,Wert5_GuV
COLUMN,25,Wert6_GuV
COLUMN,26,Wert7_GuV
COLUMN,27,Wert8_GuV
COLUMN,28,Wert9_GuV
COLUMN,29,Wert10_GuV
COLUMN,30,Wert11_GuV
COLUMN,31,Wert12_GuV
COLUMN,32,Stk1_GuV
COLUMN,33,Stk2_GuV
COLUMN,34,Stk3_GuV
COLUMN,35,Stk4_GuV
COLUMN,36,Stk5_GuV
COLUMN,37,Stk6_GuV
COLUMN,38,Stk7_GuV
COLUMN,39,Stk8_GuV
COLUMN,40,Stk9_GuV
COLUMN,41,Stk10_GuV
COLUMN,42,Stk11_GuV
COLUMN,43,Stk12_GuV
COLUMN,44,Ebene31
COLUMN,45,Ebene32
COLUMN,46,Ebene33_NW
COLUMN,47,Ebene33_GW
COLUMN,48,Ebene33_Werkst_sonst
COLUMN,49,Ebene33