Auswertung Lieder - Reporting

Janno Both

Moin,

kennt sich hier jemand mit Reporting-Funktionen aus?
Wir spielen im Jahresabschluss-Gottesdienst immer die Top 3 Lieder des vergangenen Jahres und ich würde das gerne automatisiert auswerten. Hat jemand dafür schon mal etwas geschrieben? Die Monatsübersicht in der Songübersicht ist dafür meiner Meinung nach nicht besonders hilfreich.

Vielen Dank,
Janno

mhuber

Moin,

so eine Auswertung kann über das Reports-Modul realisiert werden.

Dazu im Reports-Modul auf den Schraubenschlüssel klicken, dann bei "Abfrage" eine neue Abfrage "Songhäufigkeit" erstellen mit folgender SQL-Query:

SELECT s.bezeichnung Liedtitel, COUNT(CONCAT(s.bezeichnung, YEAR(e.startdate))) count, YEAR(e.startdate) Jahr FROM cs_song s, cs_item i, cs_song_arrangement arr, cs_event_agenda a, cs_event e WHERE e.id=a.event_id AND i.agenda_id=a.agenda_id AND s.id=arr.song_id AND arr.id=i.arrangement_id GROUP BY CONCAT(s.bezeichnung, YEAR(e.startdate)) ORDER BY count DESC;

Anschließend einen neuen Bericht erstellen mit folgenden Einträgen:
0_1513186511225_Bildschirmfoto 2017-12-13 um 18.32.20.png

Wenn der Bericht dann ausgeführt wird, werden alles Songs aufgelistet, die in dem jeweiligen Jahr gespielt wurden, für alle Jahre in der Datenbank. Das sieht dann etwa so aus:
0_1513186598131_Bildschirmfoto 2017-12-13 um 18.36.16.png

JD

Diese Abfrage ist super zum melden von Liedern! Ich hatte dies bis jetzt über eigene API Abfragen gelöst (getSongStatistic und AllSongs)

Ich kenne leider die dahinterliegenden leider Datenstrukturen nicht, wäre es möglich die Datenspalte noch auf den Kalender und die Station des Kalenders zu erweitern?

Dies würde unserer Gemeinde gut weiterhelfen die Entwicklung von Liedern im Gottesdienst (ohne Lobpreisabende und Jugendarbeit) zu analysieren.

mhuber

Um auch noch den Kalender und die Station anzuzeigen, sowie filtern zu können, muß die Datenbank-Abfrage angepaßt werden:

SELECT s.bezeichnung Liedtitel, COUNT(CONCAT(s.bezeichnung, YEAR(e.startdate), IFNULL(cc.station_id,'-'),cc.bezeichnung)) count, YEAR(e.startdate) Jahr, case when cc.station_id is null then cc.bezeichnung else concat(cc.bezeichnung, ' (', (select bezeichnung from cdb_station st where st.id = cc.station_id), ')') end as Kalender, cc.station_id Station FROM cs_song s, cs_item i, cs_song_arrangement arr, cs_event_agenda a, cs_event e, cc_cal c, cc_calcategory cc WHERE e.id=a.event_id AND i.agenda_id=a.agenda_id AND s.id=arr.song_id AND arr.id=i.arrangement_id AND c.id=e.cc_cal_id AND c.category_id=cc.id AND IFNULL(cc.station_id,-2) :station_id AND cc.id :calcategory_id GROUP BY CONCAT(s.bezeichnung, YEAR(e.startdate),IFNULL(cc.station_id,'-'),cc.bezeichnung) ORDER BY count DESC;

Außerdem beim Bericht bei "Datenreihen" folgendes eintragen: Kalender,Liedtitel.

Zuletzt müssen noch zwei Filter angelegt werden, einmal für "Kalender" und einmal für "Station". Hier können einfach die Filter von der "Fakten-Auswertung" 1:1 nachgebildet werden (leider gibt es dafür noch keine Klon-Funktion). Bei "Abfrage" muß dann die jeweilige Abfrage ausgewählt werden. So sollte es später aussehen:
0_1513860927261_Bildschirmfoto 2017-12-21 um 13.54.37.png
0_1513860946149_Bildschirmfoto 2017-12-21 um 13.55.32.png

Alternativ können die Filter auch von z.B. "Fakten-Auswertung" verwendet werden, indem einfach die "Abfrage" verändert wird. Dann funktioniert allerdings die Fakten-Auswertung nicht mehr. Das sollte man also nur tun, wenn auf die "Fakten-Auswertung" komplett verzichtet werden kann.

Am Ende sollte es dann so ähnlich aussehen:
0_1513861265420_Bildschirmfoto 2017-12-21 um 14.00.33.png

Leider erfolgt die Sortierung automatisch nach den Namen der Lieder, nicht nach Häufigkeit. Das läßt sich aktuell auch nicht einstellen. Wenn man aber als Anzeige nicht "Table" sondern "TSV-Export" wählt, werden die Daten Tab-separiert anzeigt. Das kann man markieren, in eine Datei abspeichern und in Excel laden (via CSV-Import), um es dort zu sortieren.

Chrigu

Super, genau das habe ich gesucht zum melden der Lieder. Nun ist mir aber aufgefallen, dass der Churchreport und Churchservice nicht einig sind. Wenn ich im Churchservice unter "Songs" nachzähle, dann stimmt die Summe von Churchreport nicht (siehe Bilder). Was ist hier falsch? 1_1513867438257_Songhäufigkeit2.JPG 0_1513867438254_Songhäufigkeit1.JPG

mhuber

Doch, das stimmt. Ein Strich in ChurchService ist ein Monat. Wenn ich jetzt von rechts nach links 12 Striche zähle (Dezember 2017 - Januar 2017) komme ich genau auf die gleichen Zahlen wie in dem ChurchReport-Screenshot.
Aber: warum fehlen bei Dir die Zahlen für 2016? Hast Du möglicherweise bei dem Dropdown "Jahr" (oberhalb der Liedtitel) nur 2017 ausgewählt?

rschi

@mhuber sagte in Auswertung Lieder - Reporting:

Moin,

so eine Auswertung kann über das Reports-Modul realisiert werden.

Eine doofe Frage: Wo finde ich das "Reports-Modul"?

daniel_

@rschi CT Admin-Einstellungen - Allgemeine Einstellungen und dann dem "churchreport" - Modul einen Namen geben und speichern.

Andy

@rschi sagte in Auswertung Lieder - Reporting:

Eine doofe Frage: Wo finde ich das "Reports-Modul"?

Moin Robin,

wenn es in den Admin-Einstellungen aktiviert ist ...

0_1514819571536_Bildschirmfoto vom 2018-01-01 16:10:44.png

... dann sollte es im "Hamburger-Menü" auswählbar sein:

rschi

Ich habe das mal so probiert, also die erste Variante von @mhuber (ohne Kalender und Station. Leider bekomme ich aber immer einen Fehler, wenn ich den Report generiere:

Beschreibung: [200] parsererror Fehler: SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data

Warning: strlen() expects parameter 1 to be string, array given in /opt/churchtools_application/versions/churchtools-3.25.3/system/churchcore/classes/CTAbstractModule.class.php on line 215
{"status":"success","data":null}

Weiss jemand, was das die Ursache ist?

mhuber

@rschi : Der Fehler ist bekannt und wird in der nächsten Version behoben sein. Der Report sollte trotzdem erstellt werden können, das Fenster mit der Fehlermeldung kann einfach geschlossen werden.

rschi

@mhuber Super, vielen Dank!
Ist schon ein Release-Datum bekannt?

mhuber

@rschi : wenn alles wie geplant läuft, am 22.01.

Uhu1

Dank dieser Anleitung hier habe ich schon mal grundstätzlich eine Song-Statistik hinbekommen. Vielen Dank! Kann mir jemand weiterhelfen, wie der SQL-Query lauten muss, damit ich die Statistik nicht jährlich habe, sondern monatlich? Das wäre nett.

Ausserdem habe ich festgestellt, dass Songs erfasst werden, sobald sie erfasst werden. Wurden sie falsch erfasst, werden sie trotzdem schon mitgezählt. Aber das lässt sich wohl nicht vermeiden?

rschi

Hallo @Uhu1

Versuch's mal mit diesem:

SELECT s.bezeichnung Liedtitel, COUNT(CONCAT(s.bezeichnung, YEAR(e.startdate),"-", MONTH(e.startdate), IFNULL(cc.station_id,'-'),cc.bezeichnung)) count, CONCAT(YEAR(e.startdate),"-",MONTH(e.startdate)) Monat, case when cc.station_id is null then cc.bezeichnung else concat(cc.bezeichnung, ' (', (select bezeichnung from cdb_station st where st.id = cc.station_id), ')') end as Kalender, cc.station_id Station 
FROM cs_song s, cs_item i, cs_song_arrangement arr, cs_event_agenda a, cs_event e, cc_cal c, cc_calcategory cc 
WHERE e.id=a.event_id AND i.agenda_id=a.agenda_id AND s.id=arr.song_id AND arr.id=i.arrangement_id AND c.id=e.cc_cal_id AND c.category_id=cc.id AND IFNULL(cc.station_id,-2) :station_id AND cc.id :calcategory_id GROUP BY CONCAT(s.bezeichnung, YEAR(e.startdate),"-", MONTH(e.startdate),IFNULL(cc.station_id,'-'),cc.bezeichnung) 
ORDER BY count DESC;

Beim Report musst du die Spaltenbezeichnung einfach noch von "Jahr" auf "Monat" ändern.

Klappt das so?

Uhu1

@rschi sagte in Auswertung Lieder - Reporting:

SELECT s.bezeichnung Liedtitel, COUNT(CONCAT(s.bezeichnung, YEAR(e.startdate),"-", MONTH(e.startdate), IFNULL(cc.station_id,'-'),cc.bezeichnung)) count, CONCAT(YEAR(e.startdate),"-",MONTH(e.startdate)) Monat, case when cc.station_id is null then cc.bezeichnung else concat(cc.bezeichnung, ' (', (select bezeichnung from cdb_station st where st.id = cc.station_id), ')') end as Kalender, cc.station_id Station
FROM cs_song s, cs_item i, cs_song_arrangement arr, cs_event_agenda a, cs_event e, cc_cal c, cc_calcategory cc
WHERE e.id=a.event_id AND i.agenda_id=a.agenda_id AND s.id=arr.song_id AND arr.id=i.arrangement_id AND c.id=e.cc_cal_id AND c.category_id=cc.id AND IFNULL(cc.station_id,-2) :station_id AND cc.id :calcategory_id GROUP BY CONCAT(s.bezeichnung, YEAR(e.startdate),"-", MONTH(e.startdate),IFNULL(cc.station_id,'-'),cc.bezeichnung)
ORDER BY count DESC;

Das klappt leider nicht: Fehler aufgetreten in churchreport. SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

Aber nun ist mir eine monatliche Statistik damit gelungen:

SELECT s.bezeichnung Liedtitel, COUNT(CONCAT(s.bezeichnung, MONTH(e.startdate))) count, MONTH(e.startdate) Monat FROM cs_song s, cs_item i, cs_song_arrangement arr, cs_event_agenda a, cs_event e WHERE e.id=a.event_id AND i.agenda_id=a.agenda_id AND s.id=arr.song_id AND arr.id=i.arrangement_id GROUP BY CONCAT(s.bezeichnung, MONTH(e.startdate)) ORDER BY count DESC;

Jetzt werden die Monate als 1, 2, 3 usw. einzeln angegeben. Aber ohne Jahrzahl dazu. Da weiss ich nicht, was dann Ende Jahr geschieht, wenn es wieder von vorne losgeht... Monat plus Jahrzahl dahinter weiss ich nicht wie... Aber immerhin ist das schon mal was...

JD

Du hast bei deiner Abfrage die Jahre gar nicht in betracht genommen. Versuchs mal mit folgender Abfrage:

SELECT
	s.bezeichnung Liedtitel, 
	COUNT(CONCAT(s.bezeichnung, YEAR(e.startdate), "-", MONTH(e.startdate))) count,
	CONCAT( YEAR(e.startdate), "-", MONTH(e.startdate)) Monat
FROM
	cs_song s, 
	cs_item i,
	cs_song_arrangement arr,
	cs_event_agenda a,
	cs_event e
WHERE
	e.id=a.event_id AND 
	i.agenda_id=a.agenda_id AND 
	s.id=arr.song_id AND 
	arr.id=i.arrangement_id AND
	YEAR(e.startdate) = 2017
GROUP BY 
	CONCAT(s.bezeichnung, YEAR(e.startdate), "-", MONTH(e.startdate))
ORDER BY 
	count DESC;

Wenn du AND YEAR(e.startdate) = 2017 weglässt bekommst du alle Monate der letzten Jahre angezeigt. Weil das bei uns zu viele Informationen waren habe ich das begrenzt. Alternativ würden auch folgendes funktionieren: AND YEAR(e.startdate) < 2016 um die Jahre 2017 und 2018 angezeigt zu bekommen. Alternativ kannst du auch alle Lieder die nach einem bestimmten Datum gespielt worden sind anzeigen: e.startdate > '2017-12-01 00:00:00' (ohne YEAR) und dies an euren Abrechnungszeitraum anpassen. (Du kannst natürlich auch einen Datumsbereich festlegen)

Die Sortierung nach Kalendern und Stationen fehlt zwar in diesem Falle, ist aber zum melden von Lieder egal.

JD

@uhu1 sagte in Auswertung Lieder - Reporting:

Das klappt leider nicht: Fehler aufgetreten in churchreport. SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

Du musst für die Abfrage die Reportfilter :station_id und :calcategory_id noch erstellen (wie von mhuber beschrieben:)

Zuletzt müssen noch zwei Filter angelegt werden, einmal für "Kalender" und einmal für "Station". Hier können einfach die Filter von der "Fakten-Auswertung" 1:1 nachgebildet werden (leider gibt es dafür noch keine Klon-Funktion). Bei "Abfrage" muß dann die jeweilige Abfrage ausgewählt werden. So sollte es später aussehen:

Dann hat es zumindest bei mir funktioniert.

rschi

@jd, @Uhu1
Ja genau, die Filter werden natürlich benötigt, wie auch im Beispiel von mhuber beschrieben.

Gruss Robin

Uhu1

Vielen Dank für die Hilfe! Nun habe ich eine übersichtliche Abfrage hinbekommen!