• Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Register
    • Login

    Auswertung Lieder - Reporting

    Fragen
    15
    34
    5.9k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • U
      Uhu1
      last edited by

      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?

      R 1 Reply Last reply Reply Quote 0
      • R
        rschi @Uhu1
        last edited by 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?

        Hosting bei church.tools

        1 Reply Last reply Reply Quote 0
        • U
          Uhu1
          last edited by

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

          JDJ 1 Reply Last reply Reply Quote 0
          • JDJ
            JD
            last edited by

            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.

            1 Reply Last reply Reply Quote 0
            • JDJ
              JD @Uhu1
              last edited by 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:
              0_1513860927261_Bildschirmfoto 2017-12-21 um 13.54.37.png
              0_1513860946149_Bildschirmfoto 2017-12-21 um 13.55.32.png

              Dann hat es zumindest bei mir funktioniert.

              R 1 Reply Last reply Reply Quote 0
              • R
                rschi @JD
                last edited by

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

                Gruss Robin

                Hosting bei church.tools

                1 Reply Last reply Reply Quote 0
                • U
                  Uhu1
                  last edited by

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

                  1 Reply Last reply Reply Quote 2
                  • T
                    tollestool @mhuber
                    last edited by

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

                    R 1 Reply Last reply Reply Quote 0
                    • R
                      rschi @tollestool
                      last edited by

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

                      Hosting bei church.tools

                      T 1 Reply Last reply Reply Quote 0
                      • T
                        tollestool @rschi
                        last edited by

                        @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

                        1 Reply Last reply Reply Quote 0
                        • J
                          Janno Both
                          last edited by

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

                          1 Reply Last reply Reply Quote 0
                          • H
                            hjtappe
                            last edited by 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
                            
                            1 Reply Last reply Reply Quote 0
                            • xMRiX
                              xMRi
                              last edited by 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.

                              "A well-written program is its own heaven; a poorly written program is its own hell!" The Tao of Programming
                              Hosting bei Churchtools.de

                              AndyA 1 Reply Last reply Reply Quote 0
                              • AndyA
                                Andy admin @xMRi
                                last edited by

                                @xMRi Schau mal bei den AdHoc-Reports.

                                1 Reply Last reply Reply Quote 1
                                • xMRiX
                                  xMRi
                                  last edited by

                                  Danke Andy!
                                  Ist alles doch etwas verschachtelt und versteckt 😉

                                  "A well-written program is its own heaven; a poorly written program is its own hell!" The Tao of Programming
                                  Hosting bei Churchtools.de

                                  AndyA 1 Reply Last reply Reply Quote 0
                                  • AndyA
                                    Andy admin @xMRi
                                    last edited by

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

                                    1 Reply Last reply Reply Quote 1
                                    • simonruehlS
                                      simonruehl
                                      last edited by

                                      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 1 Reply Last reply Reply Quote 5
                                      • J
                                        j.gross @simonruehl
                                        last edited by j.gross

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

                                        1 Reply Last reply Reply Quote 0
                                        • jmrauenJ
                                          jmrauen ChurchToolsMitarbeiter
                                          last edited by

                                          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:

                                          bab3abf0-6da6-4c3d-9238-2d30bb48e847-grafik.png

                                          BerndB 1 Reply Last reply Reply Quote 3
                                          • BerndB
                                            Bernd @jmrauen
                                            last edited by

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

                                            Grüße
                                            Bernd

                                            hosting bei churchtools.de

                                            AndyA 1 Reply Last reply Reply Quote 0
                                            • First post
                                              Last post