Auswertung Lieder - Reporting

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

xMRi

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.

Andy

@xMRi Schau mal bei den AdHoc-Reports.

xMRi

Danke Andy!
Ist alles doch etwas verschachtelt und versteckt

Andy

@xMRi Ja. Die Reports empfinde ich irgendwie noch als Baustelle ...

simonruehl

Kurze Info: hier tut sich bei uns etwas. Es gibt schon erste Entwürfe und Schritte für ein eigenes Feature zu CCLI Reporting.

j.gross

@simonruehl Das hört sich sehr gut an! Gibt es auch einen groben Zeitplan wann das ungefähr kommen soll?

jmrauen

In dem nächsten Release haben wir nun eine CCLI-Report Ansicht eingebaut, die anzeigt wie oft die Lieder im aktuellen Monat, im letzten und vorletzten Monat gespielt wurden.

Dazu ein nettes Schmankerl: Wir haben einen Filter ergänzt, wo man die Songs nun Verwendung filtern kann. Es ist sogar möglich zu filtern, welche Songs z.B. nur im Gottesdienst verwendet wurden:

Bernd

@jmrauen Ist der neue CCLI Report jetzt schon verfügbar?