Querying Quarterly Reports

[kalebpederson]

Hello,

We would like to be able to query quarterly reports in some sort of orderly fashion so that we can generate some good graphs and statistics from them. With the improved copy/paste support, we can easily copy the information to a spreadsheet, like excel, but since the information is sheet (quarter) oriented, we can't graph it directly. Obviously, we could move things around in Excel, but it would be nice if we could query the information directly in MLS.

I don't yet have a list of all the information that will be wanted, but some sort of reporting mechanism that might work with this would be nice.

But... as that feature isn't yet available, does anybody happen to have a quick way to make the information more useful?

A couple of things came to my mind:

1. Write a script which takes the copy/pasted data from the quarterly reports and dumps it into a database.
2. Use a JDBC connection to query the data directly from MLS

Obviously, both of the above are volatile depending on how the reports change and how the database schema changes. Are there better recommendations?

Thanks.

--kpederson

[hpaulsen]

I second the motion. I would love to be able to conveniently graph and analyze data from the quarterly reports, quarterly budget allowances, and monthly financial summaries that are sent to the stakes. I had been working on a program for this, but it was web-based and so I had to halt development once it became clear that it would probably violate current church policy if I went live. I think it would be helpful to have charting and some simple analysis tools built in to MLS - as long as they were powerful and flexible enough.

[kalebpederson]

I was talking to my Elder's Quorum President this week and he indicated that he really wanted to be able to generate additional home-teaching reports.

I presume there is other data that isn't queryable as well, so perhaps a comprehensive list of the data that can't yet be queried should be examined and carefully considered.

Thanks.

--kpederson

[darthwader-p40]

I have been tasked by our Stake President to provide some charts for each of the wards in our stake - using the data from the Quarterly Reports. I found that I can take a quarterly report and "Copy to file" - but that works only one quarter at a time.

I have copied these all out to a file that I plan on importing into Excel... I am hoping to come up with some macros to expedite this, because I don't like having to manually enter the data myself - especially when I have a lot of catch-up data to enter.

I would vote for some built in reports/charts or the ability to query and export the data as was requested in the first thread. The data is there in the system - let us at it!

[kalebpederson]

Actually, you can just do a copy and a paste right into excel and the data comes across very nicely. It works fine, but forces you to paste each quarter into a separate worksheet within the workbook, which makes it impossible to do graphs against without manipulating the data.

If I didn't have to manipulate the data to get good graphs out of it, it would work great. So, if somebody wants to take that output and create an automated way to make it usable, it would work much better than trying to do so by hand.

--kpederson

[james_francisco]

My stake has a spreadsheet that generates some very nice stake and ward level charts and statistics off of the unit statistics in MLS. I'll check with our stake clerk and see if he minds having a blank copy posted to this group.

[kalebpederson]

I'd love to see it, thanks for checking!

--kpederson

[whick-p40]

My stake has a spreadsheet that generates some very nice stake and ward level charts and statistics off of the unit statistics in MLS. I'll check with our stake clerk and see if he minds having a blank copy posted to this group.

James - did you find out how your stake generates thos charts & stats? It would be GREAT if you could share the details with us! Thanks!

[ericb]

It's interesting this topic was posted, as I was just running into the same question myself. It seems like the best solution would not involve having to export the information from MLS, but rather add charting functionality to the application.

There is an open source plug-in for Java Swing applications called jCharts (http://jcharts.sourceforge.net), which our company's development team has briefly looked into. I think a line chart would be ideal for presenting quarterly report information. I came up with a few charts in Excel that I'd like to run directly from MLS - I've attached a sample file, with identifying info removed to protect the guilty (refer to the worksheet tabs).

It would be nice if there could be selection-box type options available, to indicate the organizations to include in the report.

[Mikerowaved]

I actually do something similar in my monthly finance report to the Bishop. Using OpenOffice.org I generate financial charts showing YTD missionary fund donations, expenses, and balance, as well as Fast Offering fund donations vs expenses. It's very helpful for him to see the trends and make appropriate decisions. I pretty much just hand cut-n-paste the monthly numbers into the appropriate spreadsheet cells and plot a graph from there. I save it on the HD with a strong password so the following month's numbers can easily be added.

Yes, it would be nice to have some graphing built right into MLS, but I'm afraid we would all want so many different things graphed they wouldn't hear the end of it.