# Daten

Abfrage:
SHAPE {Select Gesamt.Gesamt, GEW.Gewonnen As [right_format#,##0*00_Gewonnen], VERL.Verloren As [right_format#,##0*00_Verloren] From TBL_OPPORTUNITY O Inner Join TBL_STAGE On TBL_STAGE.STAGEID = O.STAGEID Inner Join TBL_PROCESS On TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID, (Select Sum(TBL_OPPORTUNITY.TOTALEXTENDEDAMT) As Gewonnen From TBL_OPPORTUNITY Inner Join TBL_STAGE On TBL_STAGE.STAGEID = TBL_OPPORTUNITY.STAGEID Inner Join TBL_PROCESS On TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID Where TBL_OPPORTUNITY.STATUSNUM = 1 And TBL_OPPORTUNITY.ACTUALCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_OPPORTUNITY.ACTUALCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%') GEW, (Select Sum(TBL_OPPORTUNITY.TOTALEXTENDEDAMT) As Verloren From TBL_OPPORTUNITY Inner Join TBL_STAGE On TBL_STAGE.STAGEID = TBL_OPPORTUNITY.STAGEID Inner Join TBL_PROCESS On TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID Where TBL_OPPORTUNITY.STATUSNUM = 2 And TBL_OPPORTUNITY.ACTUALCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_OPPORTUNITY.ACTUALCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%') VERL, (Select 'Gesamt' As Gesamt) Gesamt Where (O.STATUSNUM = 1 Or O.STATUSNUM = 2) And O.ACTUALCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And O.ACTUALCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' Group By Gesamt.Gesamt, GEW.Gewonnen, VERL.Verloren}  AS NullGesamt APPEND (( SHAPE {Select Gesamt.Gesamt As hide_Gesamt, O.MANAGEUSERID As hide_MANAGEUSERID, TBL_USER.USERLOGIN As Verwalter, GEW.Gewonnen As [right_format#,##0*00_Gewonnen], VERL.Verloren As [right_format#,##0*00_Verloren] From TBL_OPPORTUNITY O Inner Join TBL_USER On O.MANAGEUSERID = TBL_USER.USERID Left Join (Select Sum(TBL_OPPORTUNITY.TOTALEXTENDEDAMT) As Gewonnen, TBL_OPPORTUNITY.MANAGEUSERID From TBL_OPPORTUNITY Inner Join TBL_STAGE On TBL_STAGE.STAGEID = TBL_OPPORTUNITY.STAGEID Inner Join TBL_PROCESS On TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID Where TBL_OPPORTUNITY.STATUSNUM = 1 And TBL_OPPORTUNITY.ACTUALCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_OPPORTUNITY.ACTUALCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' Group By TBL_OPPORTUNITY.MANAGEUSERID) GEW On GEW.MANAGEUSERID = O.MANAGEUSERID Left Join (Select Sum(TBL_OPPORTUNITY.TOTALEXTENDEDAMT) As Verloren, TBL_OPPORTUNITY.MANAGEUSERID From TBL_OPPORTUNITY Inner Join TBL_STAGE On TBL_STAGE.STAGEID = TBL_OPPORTUNITY.STAGEID Inner Join TBL_PROCESS On TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID Where TBL_OPPORTUNITY.STATUSNUM = 2 And TBL_OPPORTUNITY.ACTUALCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_OPPORTUNITY.ACTUALCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' Group By TBL_OPPORTUNITY.MANAGEUSERID) VERL On VERL.MANAGEUSERID = O.MANAGEUSERID Inner Join TBL_STAGE On TBL_STAGE.STAGEID = O.STAGEID Inner Join TBL_PROCESS On TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID, (Select 'Gesamt' As Gesamt) Gesamt Where (O.STATUSNUM = 1 Or O.STATUSNUM = 2) And O.ACTUALCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And O.ACTUALCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' Group By Gesamt.Gesamt, O.MANAGEUSERID, TBL_USER.USERLOGIN, GEW.Gewonnen, VERL.Verloren Order By Verwalter}  AS EinsBenutzer APPEND (( SHAPE {Select Distinct Case O.STATUSNUM When 1 Then 'Gewonnen' When 2 Then 'Verloren' End As Status, O.MANAGEUSERID As hide_MANAGEUSERID, Sum(O.TOTALEXTENDEDAMT) As [right_format#,##0*00_Umsatz], O.STATUSNUM As hide_Status, Count(O.OPPORTUNITYID) As [right_format#,##0_Anzahl Vorgänge], Avg(O.TOTALEXTENDEDAMT) As [right_format#,##0*00_Durchschn.Umsatz], Avg(DateDiff(Day, O.OPENDATE, O.ACTUALCLOSEDATE)) As [right_format#,##0_Mittl. Akquisetage] From TBL_OPPORTUNITY O Inner Join TBL_STAGE On TBL_STAGE.STAGEID = O.STAGEID Inner Join TBL_PROCESS On TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID Where O.ACTUALCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And O.ACTUALCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' Group By O.MANAGEUSERID, O.STATUSNUM Having O.STATUSNUM = 1 Or O.STATUSNUM = 2}  AS Zwei_Status APPEND (( SHAPE {Select O.MANAGEUSERID As hide_MANAGEUSERID, (Select Top 1 UC.NAME From TBL_COMPANY_OPPORTUNITY UCO Inner Join TBL_COMPANY UC On UC.COMPANYID = UCO.COMPANYID Where UCO.OPPORTUNITYID = O.OPPORTUNITYID Order By UC.NAME) As Firma, O.OPPORTUNITYID As showactopportunity_Vorgang, O.ACTUALCLOSEDATE As [formatshortlocaldate_Abgeschlossen am], DateDiff(day, O.OPENDATE, O.ACTUALCLOSEDATE) As [right_Akquisitionsdauer Tage], O.TOTALEXTENDEDAMT As [right_format#,##0*00_Ums. Potent.], O.WEIGHTEDAMT As [right_format#,##0*00_Ums. realisiert], O.STATUSNUM As hide_Status From TBL_OPPORTUNITY O Inner Join TBL_STAGE On TBL_STAGE.STAGEID = O.STAGEID Inner Join TBL_PROCESS On TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID Where O.ACTUALCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And O.ACTUALCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And (O.STATUSNUM = 1 Or O.STATUSNUM = 2) And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' Order By [formatshortlocaldate_Abgeschlossen am] Desc}  AS DreiVerkaufschancen APPEND ({Select O.OPPORTUNITYID As hide_actopportunity, TBL_PRODUCTSERVICE.NAME As Artikelname, TBL_PRODUCTSERVICE.QUANTITY As [right_format#,##0*00_Menge], TBL_PRODUCTSERVICE.UNITPRICE As [right_format#,##0*00_Einzelpreis], TBL_PRODUCTSERVICE.DISCOUNTPRICE As [right_format#,##0*00_rabatt. Einzelpreis], TBL_PRODUCTSERVICE.EXTENDEDAMT As [right_format#,##0*00_rabatt. Positionsumsatz] From TBL_OPPORTUNITY O Inner Join TBL_OPPORTUNITY_PRODUCTSERVICE On O.OPPORTUNITYID = TBL_OPPORTUNITY_PRODUCTSERVICE.OPPORTUNITYID Inner Join TBL_PRODUCTSERVICE On TBL_PRODUCTSERVICE.PRODUCTSERVICEID = TBL_OPPORTUNITY_PRODUCTSERVICE.PRODUCTSERVICEID Inner Join TBL_STAGE On TBL_STAGE.STAGEID = O.STAGEID Inner Join TBL_PROCESS On TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID Where O.ACTUALCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And O.ACTUALCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And (O.STATUSNUM = 1 Or O.STATUSNUM = 2) And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%'}  AS Vier_Artikelposition RELATE 'showactopportunity_Vorgang' TO 'hide_actopportunity') AS Vier_Artikelposition) AS DreiVerkaufschancen RELATE 'hide_Status' TO 'hide_Status','hide_MANAGEUSERID' TO 'hide_MANAGEUSERID') AS DreiVerkaufschancen) AS Zwei_Status RELATE 'hide_MANAGEUSERID' TO 'hide_MANAGEUSERID') AS Zwei_Status) AS EinsBenutzer RELATE 'Gesamt' TO 'hide_Gesamt') AS EinsBenutzer


Abfrage:
SHAPE {Select Distinct 'Summe' As [Offener Gesamtforecast], Sum(TBL_OPPORTUNITY.TOTALEXTENDEDAMT) As [right_format#,##0*00_Forecast ungewichtet], Sum(TBL_OPPORTUNITY.WEIGHTEDAMT) As [right_format#,##0*00_Forecast gewichtet], Count(TBL_OPPORTUNITY.OPPORTUNITYID) As [right_format#,##0_Anz. VKChancen], Avg(TBL_OPPORTUNITY.TOTALEXTENDEDAMT) As [right_format#,##0*00_Durchschn. Objektwert], Sum(TBL_OPPORTUNITY.WEIGHTEDAMT) / (Sum(TBL_OPPORTUNITY.TOTALEXTENDEDAMT) / 100) As [right_format#,##0*00_%-Ant. Forecast gewichtet] From TBL_OPPORTUNITY Inner Join (Select Distinct TBL_COMPANY_OPPORTUNITY.OPPORTUNITYID From TBL_COMPANY_OPPORTUNITY) OPPID On OPPID.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID Inner Join TBL_STAGE On TBL_OPPORTUNITY.STAGEID = TBL_STAGE.STAGEID Inner Join TBL_PROCESS On TBL_STAGE.PROCESSID = TBL_PROCESS.PROCESSID Where TBL_OPPORTUNITY.ESTIMATEDCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_OPPORTUNITY.ESTIMATEDCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_OPPORTUNITY.STATUSNUM = 0 Group By TBL_PROCESS.NAME Having Count(TBL_OPPORTUNITY.OPPORTUNITYID) > 0 And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%'}  AS NullGesamtForecast APPEND (( SHAPE {Select Distinct 'Summe' As Hide_Summe, TBL_OPPORTUNITY.MANAGEUSERID As hide_MANAGEUSERID, TBL_CONTACT.LASTNAME As hide_Nachname, TBL_CONTACT.FULLNAME As Benutzer, Sum(TBL_OPPORTUNITY.TOTALEXTENDEDAMT) As [right_format#,##0*00_Forecast ungewichtet], Sum(TBL_OPPORTUNITY.WEIGHTEDAMT) As [right_format#,##0*00_Forecast gewichtet], Count(TBL_OPPORTUNITY.OPPORTUNITYID) As [right_format#,##0_Anz. VKChancen], Avg(TBL_OPPORTUNITY.TOTALEXTENDEDAMT) As [right_format#,##0*00_Durchschn. Objektwert], Sum(TBL_OPPORTUNITY.WEIGHTEDAMT) / Sum(TBL_OPPORTUNITY.TOTALEXTENDEDAMT) * 100 As [right_format#,##0*00_%-Ant. Forecast gewichtet] From TBL_OPPORTUNITY Inner Join TBL_USER On TBL_OPPORTUNITY.MANAGEUSERID = TBL_USER.USERID Inner Join TBL_CONTACT On TBL_USER.CONTACTID = TBL_CONTACT.CONTACTID Inner Join (Select Distinct TBL_COMPANY_OPPORTUNITY.OPPORTUNITYID From TBL_COMPANY_OPPORTUNITY) OPPID On OPPID.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID Inner Join TBL_STAGE On TBL_OPPORTUNITY.STAGEID = TBL_STAGE.STAGEID Inner Join TBL_PROCESS On TBL_STAGE.PROCESSID = TBL_PROCESS.PROCESSID Where TBL_OPPORTUNITY.ESTIMATEDCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_OPPORTUNITY.ESTIMATEDCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_OPPORTUNITY.STATUSNUM = 0 Group By TBL_OPPORTUNITY.MANAGEUSERID, TBL_CONTACT.LASTNAME, TBL_CONTACT.FULLNAME, TBL_PROCESS.NAME Having TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' Order By hide_Nachname}  AS Eins_Benutzer APPEND (( SHAPE {Select (Select Top 1 UC.NAME From TBL_COMPANY UC Inner Join TBL_COMPANY_OPPORTUNITY UCO On UC.COMPANYID = UCO.COMPANYID Where UCO.OPPORTUNITYID = O.OPPORTUNITYID) As Firma, O.OPPORTUNITYID As showactopportunity_Vorgang, O.OPENDATE As [formatshortlocaldate_Eröffnet am], O.ESTIMATEDCLOSEDATE As [formatshortlocaldate_Erw. Abschluß], O.MANAGEUSERID As hide_ManageUserID, (Select Top 1 U2A.STARTTIME From TBL_ACTIVITY U2A Inner Join TBL_OPPORTUNITY_ACTIVITY U2OA On U2A.ACTIVITYID = U2OA.ACTIVITYID Left Join TBL_ACCESSOR_ACTIVITY_CLEARED On TBL_ACCESSOR_ACTIVITY_CLEARED.ACTIVITYID = U2OA.ACTIVITYID Where U2OA.OPPORTUNITYID = O.OPPORTUNITYID And U2A.ISPRIVATE = 0 And U2A.ISDELETED = 0 And TBL_ACCESSOR_ACTIVITY_CLEARED.CLEARED_DATE Is Null) As [formatshortlocaldate_WVL am], DateDiff(day, O.OPENDATE, GetDate()) As [right_Tage seit Anlage], O.TOTALEXTENDEDAMT As [right_format#,##0*00_Ums. Potent.], O.PROBABILITYPCT As [right_%], O.WEIGHTEDAMT As [right_format#,##0*00_Ums. gew.] From TBL_OPPORTUNITY O Inner Join TBL_STAGE On O.STAGEID = TBL_STAGE.STAGEID Inner Join TBL_PROCESS On TBL_STAGE.PROCESSID = TBL_PROCESS.PROCESSID Where O.ESTIMATEDCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And O.ESTIMATEDCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And O.STATUSNUM = 0 And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' Order By [formatshortlocaldate_Erw. Abschluß]}  AS ZweiVerkaufschancen APPEND (( SHAPE {Select H.STARTTIME As formatshortlocaldate_Datum, H.STARTTIME As formatshortlocaltime_Uhrzeit, (Select Top 1 UC.FULLNAME From TBL_CONTACT_HISTORY UCH Inner Join TBL_CONTACT UC On UC.CONTACTID = UCH.CONTACTID Where UCH.HISTORYID = H.HISTORYID) As Kontakt, TBL_USER.USERLOGIN As Mitarbeiter, '' + TBL_ACTIVITYTYPE.NAME + '' As Typ, TBL_HISTORYTYPE.NAME As Ergebnis, H.REGARDING As Betreff, O.OPPORTUNITYID As hide_OpportunityID, H.HISTORYID As hide_HistoryID From TBL_OPPORTUNITY O Inner Join TBL_OPPORTUNITY_HISTORY OH On O.OPPORTUNITYID = OH.OPPORTUNITYID Inner Join TBL_HISTORY H On H.HISTORYID = OH.HISTORYID Inner Join TBL_USER On H.MANAGEUSERID = TBL_USER.CONTACTID Inner Join TBL_HISTORYTYPE On TBL_HISTORYTYPE.HISTORYTYPEID = H.HISTORYTYPEID Inner Join TBL_STAGE On O.STAGEID = TBL_STAGE.STAGEID Inner Join TBL_PROCESS On TBL_STAGE.PROCESSID = TBL_PROCESS.PROCESSID Left Outer Join TBL_ACTIVITYTYPE On TBL_ACTIVITYTYPE.ACTIVITYTYPEID = TBL_HISTORYTYPE.ACTIVITYTYPEID Where O.ESTIMATEDCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And O.ESTIMATEDCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And O.STATUSNUM = 0 And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' Order By formatshortlocaldate_Datum Desc, formatshortlocaltime_Uhrzeit Desc}  AS DreiProtokolle APPEND ({Select H.DETAILS As rtf2text_Details, H.HISTORYID As hide_HistoryID From TBL_OPPORTUNITY O Inner Join TBL_OPPORTUNITY_HISTORY OH On O.OPPORTUNITYID = OH.OPPORTUNITYID Inner Join TBL_HISTORY H On H.HISTORYID = OH.HISTORYID Inner Join TBL_STAGE On O.STAGEID = TBL_STAGE.STAGEID Inner Join TBL_PROCESS On TBL_STAGE.PROCESSID = TBL_PROCESS.PROCESSID Where O.ESTIMATEDCLOSEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And O.ESTIMATEDCLOSEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And O.STATUSNUM = 0 And Len(Convert(varchar(2),H.DETAILS)) > 1 And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%'}  AS VierDetails RELATE 'hide_HistoryID' TO 'hide_HistoryID') AS VierDetails) AS DreiProtokolle RELATE 'showactopportunity_Vorgang' TO 'hide_OpportunityID') AS DreiProtokolle) AS ZweiVerkaufschancen RELATE 'hide_MANAGEUSERID' TO 'hide_ManageUserID') AS ZweiVerkaufschancen) AS Eins_Benutzer RELATE 'Offener Gesamtforecast' TO 'Hide_Summe') AS Eins_Benutzer


Abfrage:
SHAPE {SELECT DISTINCT MIN('Summe') AS hide_Summe, SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) AS [right_format#,##0*00_Gesamt], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '0' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) AS [right_format#,##0*00_Offen], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '0' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) / (SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) / 100) AS [right_format\<b\>#,##0*00_%], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '1' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) AS [right_format#,##0*00_Gewonnen], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '1' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) / (SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) / 100) AS [right_format\<b\>#,##0*00_%], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '2' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) AS [right_format#,##0*00_Verloren], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '2' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) / (SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) / 100) AS [right_format\<b\>#,##0*00_%], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '3' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) AS [right_format#,##0*00_Inaktiv], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '3' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) / (SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) / 100) AS [right_format\<b\>#,##0*00_%], COUNT(DISTINCT TBL_COMPANY.COMPANYID) AS [right_Firmen Total] FROM TBL_OPPORTUNITY INNER JOIN TBL_OPPORTUNITY_PRODUCTSERVICE ON TBL_OPPORTUNITY.OPPORTUNITYID = TBL_OPPORTUNITY_PRODUCTSERVICE.OPPORTUNITYID INNER JOIN TBL_PRODUCTSERVICE ON TBL_PRODUCTSERVICE.PRODUCTSERVICEID = TBL_OPPORTUNITY_PRODUCTSERVICE.PRODUCTSERVICEID INNER JOIN (SELECT ALL CONVERT(uniqueidentifier,MIN(CONVERT(char(36),TBL_COMPANY_OPPORTUNITY.COMPANYID))) AS MIN_COMPANYID, TBL_COMPANY_OPPORTUNITY.OPPORTUNITYID FROM TBL_COMPANY_OPPORTUNITY GROUP BY TBL_COMPANY_OPPORTUNITY.OPPORTUNITYID) CO ON CO.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID INNER JOIN TBL_COMPANY ON CO.MIN_COMPANYID = TBL_COMPANY.COMPANYID INNER JOIN TBL_USER ON TBL_USER.USERID = TBL_OPPORTUNITY.MANAGEUSERID INNER JOIN TBL_CONTACT ON TBL_USER.CONTACTID = TBL_CONTACT.CONTACTID INNER JOIN TBL_STAGE ON TBL_STAGE.STAGEID = TBL_OPPORTUNITY.STAGEID INNER JOIN TBL_PROCESS ON TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID WHERE TBL_OPPORTUNITY.CREATEDATE  >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_OPPORTUNITY.CREATEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) AND TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' HAVING COUNT(TBL_OPPORTUNITY.OPPORTUNITYID) > 0}  AS Eins_Gesamt APPEND (( SHAPE {SELECT DISTINCT MIN('Summe') AS hide_Summe, TBL_OPPORTUNITY.MANAGEUSERID AS hide_MANAGEUSERID, TBL_CONTACT.FULLNAME AS Benutzer, SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) AS [right_format#,##0*00_Gesamt], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '0' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) AS [right_format#,##0*00_Offen], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '0' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) / (SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) / 100) AS [right_format\<b\>#,##0*00_%], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '1' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) AS [right_format#,##0*00_Gewonnen], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '1' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) / (SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) / 100) AS [right_format\<b\>#,##0*00_%], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '2' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) AS [right_format#,##0*00_Verloren], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '2' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) / (SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) / 100) AS [right_format\<b\>#,##0*00_%], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '3' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) AS [right_format#,##0*00_Inaktiv], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '3' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) / (SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) / 100) AS [right_format\<b\>#,##0*00_%], COUNT(DISTINCT TBL_COMPANY.COMPANYID) AS [right_Firmen Total] FROM TBL_OPPORTUNITY INNER JOIN TBL_OPPORTUNITY_PRODUCTSERVICE ON TBL_OPPORTUNITY.OPPORTUNITYID = TBL_OPPORTUNITY_PRODUCTSERVICE.OPPORTUNITYID INNER JOIN TBL_PRODUCTSERVICE ON TBL_PRODUCTSERVICE.PRODUCTSERVICEID = TBL_OPPORTUNITY_PRODUCTSERVICE.PRODUCTSERVICEID INNER JOIN (SELECT ALL CONVERT(uniqueidentifier,MIN(CONVERT(char(36),TBL_COMPANY_OPPORTUNITY.COMPANYID))) AS MIN_COMPANYID, TBL_COMPANY_OPPORTUNITY.OPPORTUNITYID FROM TBL_COMPANY_OPPORTUNITY GROUP BY TBL_COMPANY_OPPORTUNITY.OPPORTUNITYID) CO ON CO.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID INNER JOIN TBL_COMPANY ON CO.MIN_COMPANYID = TBL_COMPANY.COMPANYID INNER JOIN TBL_USER ON TBL_USER.USERID = TBL_OPPORTUNITY.MANAGEUSERID INNER JOIN TBL_CONTACT ON TBL_USER.CONTACTID = TBL_CONTACT.CONTACTID INNER JOIN TBL_STAGE ON TBL_STAGE.STAGEID = TBL_OPPORTUNITY.STAGEID INNER JOIN TBL_PROCESS ON TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID WHERE TBL_OPPORTUNITY.CREATEDATE  >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_OPPORTUNITY.CREATEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) AND TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' GROUP BY TBL_OPPORTUNITY.MANAGEUSERID, TBL_CONTACT.FULLNAME ORDER BY TBL_CONTACT.FULLNAME}  AS Zwei_Benutzer APPEND (( SHAPE {SELECT DISTINCT TBL_OPPORTUNITY.MANAGEUSERID AS hide_MANAGEUSERID, TBL_PRODUCTSERVICE.NAME AS Produktbereich, SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) AS [right_format#,##0*00_Gesamt], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '0' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) AS [right_format#,##0*00_Offen], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '0' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) / (SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) / 100) AS [right_format\<b\>#,##0*00_%], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '1' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) AS [right_format#,##0*00_Gewonnen], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '1' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) / (SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) / 100) AS [right_format\<b\>#,##0*00_%], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '2' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) AS [right_format#,##0*00_Verloren], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '2' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) / (SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) / 100) AS [right_format\<b\>#,##0*00_%], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '3' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) AS [right_format#,##0*00_Inaktiv], SUM(CASE TBL_OPPORTUNITY.STATUSNUM WHEN '3' THEN TBL_PRODUCTSERVICE.EXTENDEDAMT ELSE 0 END) / (SUM(TBL_PRODUCTSERVICE.EXTENDEDAMT) / 100) AS [right_format\<b\>#,##0*00_%] FROM TBL_OPPORTUNITY INNER JOIN TBL_OPPORTUNITY_PRODUCTSERVICE ON TBL_OPPORTUNITY.OPPORTUNITYID = TBL_OPPORTUNITY_PRODUCTSERVICE.OPPORTUNITYID INNER JOIN TBL_PRODUCTSERVICE ON TBL_PRODUCTSERVICE.PRODUCTSERVICEID = TBL_OPPORTUNITY_PRODUCTSERVICE.PRODUCTSERVICEID INNER JOIN (SELECT ALL CONVERT(uniqueidentifier,MIN(CONVERT(char(36),TBL_COMPANY_OPPORTUNITY.COMPANYID))) AS MIN_COMPANYID, TBL_COMPANY_OPPORTUNITY.OPPORTUNITYID FROM TBL_COMPANY_OPPORTUNITY GROUP BY TBL_COMPANY_OPPORTUNITY.OPPORTUNITYID) CO ON CO.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID INNER JOIN TBL_COMPANY ON CO.MIN_COMPANYID = TBL_COMPANY.COMPANYID INNER JOIN TBL_STAGE ON TBL_STAGE.STAGEID = TBL_OPPORTUNITY.STAGEID INNER JOIN TBL_PROCESS ON TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID WHERE TBL_OPPORTUNITY.CREATEDATE  >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_OPPORTUNITY.CREATEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) AND TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' GROUP BY TBL_OPPORTUNITY.MANAGEUSERID, TBL_PRODUCTSERVICE.NAME ORDER BY TBL_OPPORTUNITY.MANAGEUSERID, TBL_PRODUCTSERVICE.NAME}  AS Drei_Produktbereich APPEND ({Select Distinct TBL_OPPORTUNITY.MANAGEUSERID As hide_MANAGEUSERID, TBL_PRODUCTSERVICE.NAME As hide_Produktbereich, TBL_COMPANY.NAME As Firma, TBL_COMPANY.DESCRIPTION As Firmenzusatz, TBL_COMPANY.COMPANYID As showactcompany_Firma, TBL_PRODUCTSERVICE.EXTENDEDAMT As [right_format#,##0*00_Gesamt], Case TBL_OPPORTUNITY.STATUSNUM When '0' Then TBL_PRODUCTSERVICE.EXTENDEDAMT Else 0 End As [right_format#,##0*00_Offen], Case TBL_OPPORTUNITY.STATUSNUM When '1' Then TBL_PRODUCTSERVICE.EXTENDEDAMT Else 0 End As [right_format#,##0*00_Gewonnen], Case TBL_OPPORTUNITY.STATUSNUM When '2' Then TBL_PRODUCTSERVICE.EXTENDEDAMT Else 0 End As [right_format#,##0*00_Verloren], Case TBL_OPPORTUNITY.STATUSNUM When '3' Then TBL_PRODUCTSERVICE.EXTENDEDAMT Else 0 End As [right_format#,##0*00_Inaktiv] From TBL_OPPORTUNITY Inner Join TBL_OPPORTUNITY_PRODUCTSERVICE On TBL_OPPORTUNITY.OPPORTUNITYID = TBL_OPPORTUNITY_PRODUCTSERVICE.OPPORTUNITYID Inner Join TBL_PRODUCTSERVICE On TBL_PRODUCTSERVICE.PRODUCTSERVICEID = TBL_OPPORTUNITY_PRODUCTSERVICE.PRODUCTSERVICEID Inner Join (Select All Convert(uniqueidentifier,Min(Convert([Char](36),TBL_COMPANY_OPPORTUNITY.COMPANYID))) As MIN_COMPANYID, TBL_COMPANY_OPPORTUNITY.OPPORTUNITYID From TBL_COMPANY_OPPORTUNITY Group By TBL_COMPANY_OPPORTUNITY.OPPORTUNITYID) CO On CO.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID Inner Join TBL_COMPANY On CO.MIN_COMPANYID = TBL_COMPANY.COMPANYID Inner Join TBL_STAGE On TBL_STAGE.STAGEID = TBL_OPPORTUNITY.STAGEID Inner Join TBL_PROCESS On TBL_PROCESS.PROCESSID = TBL_STAGE.PROCESSID Where TBL_OPPORTUNITY.CREATEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_OPPORTUNITY.CREATEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_PROCESS.NAME Like N'{!RegistryValue, Suchbegriff01}%' Group By TBL_OPPORTUNITY.MANAGEUSERID, TBL_PRODUCTSERVICE.NAME, TBL_COMPANY.NAME, TBL_COMPANY.DESCRIPTION, TBL_COMPANY.COMPANYID, TBL_PRODUCTSERVICE.EXTENDEDAMT, Case TBL_OPPORTUNITY.STATUSNUM When '0' Then TBL_PRODUCTSERVICE.EXTENDEDAMT Else 0 End, Case TBL_OPPORTUNITY.STATUSNUM When '1' Then TBL_PRODUCTSERVICE.EXTENDEDAMT Else 0 End, Case TBL_OPPORTUNITY.STATUSNUM When '2' Then TBL_PRODUCTSERVICE.EXTENDEDAMT Else 0 End, Case TBL_OPPORTUNITY.STATUSNUM When '3' Then TBL_PRODUCTSERVICE.EXTENDEDAMT Else 0 End Order By hide_MANAGEUSERID, hide_Produktbereich, Firma}  AS Vier_Firmen RELATE 'hide_MANAGEUSERID' TO 'hide_MANAGEUSERID','Produktbereich' TO 'hide_Produktbereich') AS Vier_Firmen, COUNT(Vier_Firmen.'showactcompany_Firma') AS right_Firmen) AS Drei_Produktbereich RELATE 'hide_MANAGEUSERID' TO 'hide_MANAGEUSERID') AS Drei_Produktbereich) AS Zwei_Benutzer RELATE 'hide_Summe' TO 'hide_Summe') AS Zwei_Benutzer


# Rubriken

Bemerkung:
Zeitraum: '{!RegistryValue, WP_Start}'  -  '{!RegistryValue, WP_Ende}' / Prozessname beginnt mit: '{!RegistryValue, Suchbegriff01}'  /  Liefert eine Auflistung der geschlossenen Verkaufschancen mit Abschlußdatum im definierten Zeitraum. Für die Vorgabe des gefilterten Datumsbereiches verwenden Sie das AutoData-Script "SetKIParameter". Über den "Suchbegriff" im AutoData-Script "SetKIParameter" setzen Sie die ersten eindeutigen Zeichen des relevanten Prozessnamens, nach dem die Filterung erfolgen soll.


Bemerkung:
Zeitraum: '{!RegistryValue, WP_Start}'  -  '{!RegistryValue, WP_Ende}' / Prozessname beginnt mit: '{!RegistryValue, Suchbegriff01}'  /  Liefert eine Auflistung der offenen Verkaufschancen mit erwartetem Abschlußdatum im definierten Zeitraum. Für die Vorgabe des gefilterten Datumsbereiches verwenden Sie das AutoData-Script "SetKIParameter". Über den "Suchbegriff" im AutoData-Script "SetKIParameter" setzen Sie die ersten eindeutigen Zeichen des relevanten Prozessnamens, nach dem die Filterung erfolgen soll.


Bemerkung:
Zeitraum: '{!RegistryValue, WP_Start}'  -  '{!RegistryValue, WP_Ende}' / Prozessname beginnt mit: '{!RegistryValue, Suchbegriff01}'  /  Liefert eine Auflistung der neu angelegten Verkaufschancen mit Erstellungsdatum im definierten Zeitraum. Für die Vorgabe des gefilterten Datumsbereiches verwenden Sie das AutoData-Script "SetKIParameter". Über den "Suchbegriff" im AutoData-Script "SetKIParameter" setzen Sie die ersten eindeutigen Zeichen des relevanten Prozessnamens, nach dem die Filterung erfolgen soll.


