Once MusicXML data is in the database, we can query the data to find information.

In the examples given here, I will query the relational database using MySQL procedures and functions to populate aggregated data about a piece of music into reports tables, and then use Business Intelligence (BI) software to query the reports tables and create visual reports.

These examples are intended as a starting point for demonstration purposes.

I'm using the MySQL database in the examples here, but each database type handled by the tasks application have their own versions of stored procedures, so the approach here is translatable.

Stored procedure code in this section is in a zip file here. The zip file includes scripts for both linux and Windows to load the procedures in the database.

The basic structure of a partwise score in the MusicXML database is a list of parts, each containing a list of measures, with each measure having a list of music data records. The view score_view obtains a score's music data list in part, measure, and music_data order.

Procedure score_report iterates through the score view, and on each iteration:

• Stores temporary data about the current state of the score: the current note, the previous note, placement of the note within the measure, etc.
• Calls an individual report procedure that queries the temporary data and populates a reports table that the BI software will use to generate a report.

I've used a temp table followed by a report procedure call that queries the temp table because MySQL procedures don't have a hash map data structure (unlike Oracle) that can be passed as an argument to the report procedure.

An individual report procedure is called from procedure score_report once for each music data record in the score.

The report procedure queries the score's temporary data. and generates score data in a table of its own.

The examples given here are:

• pitch_count_report: populates report table report_pitch_counts with a count of each pitch class in a score
• interval_count_report: populates report table report_interval_counts with a count of each interval in consecutive notes
• measure_notes_report: populates report table report_measure_notes with the pitch classes that occur at the beginning of each measure

BI software queries the reports data generated by an individual report procedure and creates a visual representation of the score's data.

I've chosen BIRT because it's free and does the job for the purposes here.