Auswertung Lieder - Reporting
-
@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.
-
@mhuber Super, vielen Dank!
Ist schon ein Release-Datum bekannt? -
@rschi : wenn alles wie geplant läuft, am 22.01.
-
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?
-
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?
-
@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...
-
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.
-
@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.
-
-
Vielen Dank für die Hilfe! Nun habe ich eine übersichtliche Abfrage hinbekommen!
-
@mhuber
Besteht auch die Möglichkeit die CCLI-Nummer mit ausgeben zu lassen?
Liebe Grüße und Gottes Segen für euren Dienst.
Wolfgang -
@tollestool Um die CCLI-Nummer in Klammern hinter der Lied-Bezeichnung mit auszugeben, kannst du in der SQL-Abfrage beim SELECT anstatt "s.bezeichnung" folgendes eingeben:
"CONCAT(s.bezeichnung, ' (', s.ccli, ' )')"Ist das so, wie du es dir vorstellst?
-
@rschi sagte in Auswertung Lieder - Reporting:
"CONCAT(s.bezeichnung, ' (', s.ccli, ' )')"
GENAU,
da wird sich unser Gemeindebüro freuen....
Vielen Dank und lieben Gruß
Wolfgang -
Ich antworte hier mal, weil es jetzt wieder für viele interessant sein könnte!
-
Da wir vom CCLI angeschrieben worden sind, jetzt mindestens einmal pro Monat berichten zu sollen, komme ich auch hier vorbei. Beim Lesen des Beitrags habe ich erst ein wenig gebraucht, weil die Doku, die ich finden konnte, nicht sehr umfänglich war. Daher hier noch die Zusammenfassung, wie es eingerichtet ist:
Unser "CCLI-Songreport" Ad-Hoc-Report besteht aus- Der Abfrage
- Einem Report und
- einem Filter für den Platzhalter ":filteryear"
Die Abfrage hat folgenden Inhalt, relativ nah an der o.g. Abfrage (aber ohne Station-Filter):
- Bezeichnung: CCLI-Songreport
- Sortierung: 0
- Datenbankabfrage:
SELECT CONCAT (s.bezeichnung, " (CCLI-Nr.: ", s.ccli, ")") AS Liedtitel, COUNT(CONCAT(s.bezeichnung, YEAR(e.startdate), "-", MONTH(e.startdate))) AS count, DATE_FORMAT(e.startdate, '%Y-%m') AS 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 e.startdate <= CURDATE() AND CONCAT(YEAR(e.startdate),"") :filteryear GROUP BY CONCAT(s.bezeichnung, YEAR(e.startdate), "-", MONTH(e.startdate)) ORDER BY count DESC;
Der Adhoc-Report sieht so aus:
- Abfrage (wie oben): CCLI-Songreport
- Bezeichnung: CCLI-Tabelle
- Sortierung: 0
- Datenreihen: Liedtitel
- Spaltenbezeichnung: Monat,count
- Vorauswahl: person.count
- Feldauswahl:
- Diagrammtyp: Heatmap
Der Reportfilter sieht so aus:
- Abfrage (wie oben): CCLI-Songreport
- Platzhalter im SQL-Befehl: :filteryear
- Bezeichnung: Jahr
- Optional: (leer)
- Sortierung: 0
- SQL:
SELECT DISTINCT YEAR(e.startdate) id, YEAR(e.startdate) bezeichnung 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 e.startdate <= CURDATE() ORDER BY bezeichnung DESC
-
Wenn ich auf einen Report gehe un den Schraubenschlüssel anklicke sehe ich nicht die Möglichkeit einen SQL Query anzulegen.
Kann es sein, dass dies in der gehosteten Version nicht möglich ist?
Ich sehe dort nur Auswertungsmöglichkeiten über Fakten...Danke für einen Schubser in die richtige Richtung.
-
@xMRi Schau mal bei den AdHoc-Reports.
-
Danke Andy!
Ist alles doch etwas verschachtelt und versteckt -
@xMRi Ja. Die Reports empfinde ich irgendwie noch als Baustelle ...
-
Kurze Info: hier tut sich bei uns etwas. Es gibt schon erste Entwürfe und Schritte für ein eigenes Feature zu CCLI Reporting.