The measure notes report demonstrates looping through the list of music_data records in a score, while saving data about the score according to pre-determined criteria.

The report procedure saves all non-tied notes at the beginning of each measure, including the measure number, and the measure's relative placement within the score. The report visualization lists all of the unique notes for each measure downbeat, in measure order.

Beethoven Symphony No. 4, 1st movement:

J. S. Bach Brandenburg Concerto No. 6:

For this report, I've chosen a table display that lists the unique pitches for each measure, in measure order.

To set up the report:

• Right click the project in the Navigator pane, select New -> Report, and name the report in the File Name box

To set up the data source:

• Double click the library in the Navigator pane
• Select the mysql data source in the Resource Explorer pane
• Double click the measure notes report in the Navigator pane
• Right click the data source in the Resource Explorer pane and select Add to Report

The visualization report is web-based and will have a prompt to enter the score ID.

To add the score ID prompt:

• Right select Report Parameters in the Data Explorer pane, and select New Parameter
• Enter the name as score_id, the Prompt Text as Score ID:, and the Data Type as Integer
• Make sure Is Required is selected

To set up the report query:

• Right click Data Sets in the Data Explorer pane and select New Data Set
• Verify the JDBC Data Source, and name the data set in the Data Set Name box
• Paste the Query Text from below; the query selects from the reports table populated in the stored procedure above. The query is also included in file reports_queries.sql in the zipped procedures file.

select m.measure_number, m.measure_number_count, group_concat(distinct pitch_label(pitch_number(n.step, n.pitch_alter, n.transpose)) separator ', ') as pitch, m.ordering from report_measure m, report_measure_notes n where m.id = n.report_measure_id and m.score_id = ? group by m.measure_number, m.measure_number_count, m.ordering order by m.ordering;

The query string contains a ? which is a query parameter.

• In the next window for creating the new data set, select Parameters
• Edit the listed parameter, giving the name score_id, Data Type Integer, Linked To Report Parameter score_id

To create the layout:

• Click the empty layout in the layout area. In the menu, select Insert -> Table
• Bind the columns in the table to measure_number and pitch

To run the report:

• Select the report in the Navigator pane
• In the menu, select Run -> View Report -> In Web Viewer

You can further refine the chart by right clicking the chart in the report area, and setting various formatting options.