Report: Gegenüberstellung Mitglieder zu Godi-Besucher


  • admin

    Mich würde eine Auswertung interessieren, in der ich die Anzahl der Mitglieder der Anzahl der Gottesdienstbesucher (z. B. monatlicher Durchschnitt) gegenüberstelle. Hat jemand sowas schon mal gemacht? Ich tue mich mit den Reports etwas schwer und würde mich über Unterstützung freuen. :-)


  • admin

    Hat keiner eine fertige Abfrage für mich? :D



  • Die Reports sind schwer zu bedienen, vorallem für die von CT gehosteten Instanzen, weil man keinen Einblick in die Datenbank hat und die Feldnamen nicht herausbekommt.

    Hast Du schon eine Lösung gefunden? Die würde mich nämlich auch interessieren. Sonst versuche ich es demnächst selbst einmal und poste dann hier wieder.



  • @Andy Ich experimentiere gerade mit UIPath RPA. Damit könnte es gehen. Da wir die Gottesdienstbesucher nicht zählen brauchen wir so einen Bericht aber nicht.

    Bei UIPath Data Scraping (Überschriftszeilen pro Tag) sieht es so aus:
    UIPath Export Fakten.JPG



  • @Andy Ich hab's nun mal versucht und einen Report erstellt, welcher deinen Wunsch erfüllen dürfte. Unten aufgeführt - step by step - die Einrichtung des Reports.

    SCHRITT 1 - Abfrage
    Als Erstes muss unter AdHoc-Reports -> Schraubenschlüssel -> Abfragen eine neue Abfrage erstellt werden.

    Bezeichnung: Vergleich Besucher - Mitglieder
    Datenbankabfrage (SQL):

    SELECT
    	SubBasis.Year AS Jahr,
    	SubBasis.Month AS Monat,
    	SubTyp.Typ AS Typ,
    CASE
    		SubTyp.Typ 
    		WHEN 'Besucher' THEN
    		SubBasis.Facts 
    		WHEN 'Mitglieder' THEN
    		SubBasis.Members 
    	END AS count 
    FROM
    	( SELECT 'Besucher' AS Typ UNION SELECT 'Mitglieder' AS Typ ) AS SubTyp,
    	(
    	SELECT
    		SubCompare.Year AS Year,
    		SubCompare.Month AS Month,
    		ROUND(AVG(SubCompare.Facts)) AS Facts,
    		ROUND(AVG(SubCompare.Members)) AS Members 
    	FROM
    		(
    		SELECT
    			SubVals.ID AS ID,
    			YEAR ( SubVals.EventDate ) AS Year,
    			MONTH ( SubVals.EventDate ) AS Month,
    			SubVals.Facts AS Facts,
    			SUM( SubVals.Members ) AS Members 
    		FROM
    			(
    			SELECT
    				e.id AS ID,
    				e.startdate AS EventDate,
    				f.Value AS Facts,
    				SubMembers.Value AS Members 
    			FROM
    				cs_event_fact AS f
    				INNER JOIN cs_event AS e ON f.event_id = e.id
    				INNER JOIN cc_cal AS c ON e.cc_cal_id = c.id
    				INNER JOIN cc_calcategory AS cat ON c.category_id = cat.id
    				LEFT JOIN (
    				SELECT COALESCE
    					(
    						eintrittsdatum,
    					STR_TO_DATE( '1900-01-01', '%Y-%c-%e' )) AS Entry,
    					COALESCE (
    						austrittsdatum,
    					STR_TO_DATE( '2099-12-31', '%Y-%c-%e' )) AS Leaving,
    					1 AS Value
    					
    				FROM
    					cdb_gemeindeperson 
    				WHERE station_id :filter_station AND
    					(eintrittsdatum IS NOT NULL 
    					OR austrittsdatum IS NOT NULL) 
    				) AS SubMembers ON e.startdate BETWEEN SubMembers.Entry 
    				AND SubMembers.Leaving 
    			WHERE
    				f.fact_id :filter_fact
    				AND cat.id :filter_cal
    			) AS SubVals 
    		GROUP BY
    			ID,
    			EventDate,
    			Facts 
    		) AS SubCompare 
    WHERE SubCompare.Year :filter_year
    	GROUP BY
    		SubCompare.Year,
    		SubCompare.Month 
    	) AS SubBasis 
    ORDER BY
    	SubBasis.Year,
    	SubBasis.Month,
    	SubTyp.Typ
    

    SCHRITT 2 - Filter Station
    Unter AdHoc-Reports -> Schraubenschlüssel -> Reportfilter einen neuen Filter erstellen.

    Abfrage: Vergleich Besucher - Mitglieder
    Platzhalter in SQL-Befehl: :filter_station
    Bezeichnung: Station
    Sortierung: 1
    Checkbox "Optional": Nein
    SQL zum Selektieren:

    SELECT
    	id,
    	bezeichnung 
    FROM
    	cdb_station 
    ORDER BY
    	sortkey, bezeichnung
    

    SCHRITT 3 - Filter Fakt
    Unter AdHoc-Reports -> Schraubenschlüssel -> Reportfilter einen neuen Filter erstellen.

    Abfrage: Vergleich Besucher - Mitglieder
    Platzhalter in SQL-Befehl: :filter_fact
    Bezeichnung: Fakt
    Sortierung: 2
    Checkbox "Optional": Nein
    SQL zum Selektieren:

    SELECT DISTINCT
    	id,
    	bezeichnung 
    FROM
    	cs_fact
    ORDER BY
    	bezeichnung
    

    SCHRITT 4 - Filter Kalender
    Unter AdHoc-Reports -> Schraubenschlüssel -> Reportfilter einen neuen Filter erstellen.

    Abfrage: Vergleich Besucher - Mitglieder
    Platzhalter in SQL-Befehl: :filter_cal
    Bezeichnung: Kalender
    Sortierung: 3
    Checkbox "Optional": Nein
    SQL zum Selektieren:

    SELECT DISTINCT
    	cat.id AS id,
    	CONCAT(cat.bezeichnung, CASE WHEN s.id IS NOT NULL THEN CONCAT(' (', s.bezeichnung, ')') END) AS bezeichnung
    FROM
    	cc_calcategory AS cat INNER JOIN cc_cal AS c ON cat.id = c.category_id INNER JOIN cs_event AS e ON c.id = e.cc_cal_id LEFT JOIN cdb_station AS s ON cat.station_id = s.id
    ORDER BY cat.oeffentlich_yn DESC, cat.sortkey, cat.bezeichnung
    

    SCHRITT 5 - Filter Jahr
    Unter AdHoc-Reports -> Schraubenschlüssel -> Reportfilter einen neuen Filter erstellen.

    Abfrage: Vergleich Besucher - Mitglieder
    Platzhalter in SQL-Befehl: :filter_year
    Bezeichnung: Jahr
    Sortierung: 4
    Checkbox "Optional": Ja
    SQL zum Selektieren:

    SELECT DISTINCT YEAR(startdate) AS id, YEAR(startdate) AS bezeichnung FROM cs_event AS e INNER JOIN cs_event_fact AS f ON e.id = f.event_id ORDER BY startdate
    

    SCHRITT 6 - Report erstellen
    Unter AdHoc-Reports -> Schraubenschlüssel -> Adhoc-Report einen neuen Report erstellen.

    Abfrage: Vergleich Besucher - Mitglieder
    Bezeichnung: Vergleich Besucher - Mitglieder
    Datenreihen: Typ
    Spaltenbezeichnung: Jahr,Monat
    Diagrammtyp: Table

    SCHRITT 7 - Report ausführen
    Nun kann der Report im Modul "Reports" unter "AdHoc-Reports" ausgewählt und ausgeführt werden. Das sieht dann in etwa so aus:
    2019-10-18_09-49-13.jpg

    Die Zeile/Spalte "Gesamt" ergibt in diesem Kontext keinen wirklichen Sinn. Ich weiss aber gerade nicht, wie diese ausgeblendet werden können.

    Hinweis: Den Report habe ich im Schnellschuss-Verfahren erstellt und lediglich stichprobenartig getestet - also keine Garantie für die inhaltliche Richtigkeit! ;-)

    Der Report dient als Inspiration und darf gerne weiterentwickelt werden... viel Spass!


  • admin

    @seetalchile wow, cool! Werde ich heute Nachmittag mal testen. Danke dir!!!
    Stationen sind in der Form jetzt natürlich nicht berücksichtigt ... muss ich mal gucken, ob ich das selber noch da rein bekomme. Die Auswertung soll nämlich nur für einen speziellen Standort gelten und nicht für alle.



  • @Andy Stimmt, das hatte ich vergessen zu später Stunde! ;-) Inzwischen habe ich den Filter für die Station ergänzt (siehe Beitrag oben).


  • admin

    @seetalchile du bist eine Granate! Werde berichten, ob es funktioniert.


  • admin

    Jaaaa ... @seetalchile ... aaaber: Eine wichtige Info von mir fehlte wohl: Wir haben sonntags zwei Gottesdienste an dem Standort. Also brauche ich eigentlich noch die Summe dieser beiden ... und erschwerend ... die Uhrzeiten haben sich vor einigen Jahren mal geändert. Also müsste ich eigentlich auch noch die Godi-Zeiten auswählen können und die müssten summiert werden.

    Vielleicht ist es dann wohl doch einfacher, wenn ich mir die Zahlen aus unterschiedlichen Abfragen nach LibreOffice Calc kopiere und dort dann die Gegenüberstellung mache.

    Deine Abfrage funktioniert sonst aber wunderbar und echt Wahnsinn, dass du mir das gebaut hast!


  • admin

    @seetalchile Diagramm ist fertig. Also keine weitere Arbeit reinstecken, wenn du nicht ohnehin Langeweile hast. ;-)



  • @Andy Freut mich, dass du den Report so gebrauchen kannst! ;-)


  • admin

    @seetalchile noch nicht wirklich, da die Zeiten nicht berücksichtigt sind. Habe es jetzt manuell gemacht.



  • @seetalchile Finde den Report auch super.
    Meinst Du dass es möglich ist anstelle der Zeile "Gesamt" den Mittelwert aus Besucher + Mitglieder
    bzw. Spalte "Gesamt" den Mittelwert aus Besucher bzw. Mitglieder darzustellen?


Log in to reply