Auswertung Lieder - Reporting

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!

tollestool

@mhuber
Besteht auch die Möglichkeit die CCLI-Nummer mit ausgeben zu lassen?
Liebe Grüße und Gottes Segen für euren Dienst.
Wolfgang

rschi

@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?

tollestool

@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

Janno Both

Ich antworte hier mal, weil es jetzt wieder für viele interessant sein könnte!

hjtappe

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 &lt= 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 &lt= CURDATE()
ORDER BY
  bezeichnung DESC