Querying Quarterly Reports

Discussions around using and interfacing with the Church MLS program.
kalebpederson
New Member
Posts: 32
Joined: Tue Jul 31, 2007 9:16 pm

Querying Quarterly Reports

Postby kalebpederson » Tue Aug 21, 2007 9:19 pm

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

User avatar
hpaulsen
Member
Posts: 109
Joined: Fri Mar 09, 2007 12:53 pm
Location: Barstow, CA
Contact:

Postby hpaulsen » Wed Aug 22, 2007 6:18 am

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
New Member
Posts: 32
Joined: Tue Jul 31, 2007 9:16 pm

Query home teaching statistics...

Postby kalebpederson » Tue Aug 28, 2007 9:06 pm

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

User avatar
darthwader-p40
New Member
Posts: 13
Joined: Tue Jan 23, 2007 2:24 pm
Location: Clinton, Utah

Need a better way to extract the data from the system!

Postby darthwader-p40 » Thu Aug 30, 2007 8:01 am

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!
Piliin ang tama... (CTR in Pilipino)

kalebpederson
New Member
Posts: 32
Joined: Tue Jul 31, 2007 9:16 pm

Copy and Paste quarterly report data to Excel

Postby kalebpederson » Thu Aug 30, 2007 5:54 pm

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
Member
Posts: 76
Joined: Thu Feb 08, 2007 9:42 am
Location: Arizona
Contact:

re: Querying quarterly reports

Postby james_francisco » Tue Sep 04, 2007 9:49 am

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
New Member
Posts: 32
Joined: Tue Jul 31, 2007 9:16 pm

Spreadsheet...

Postby kalebpederson » Tue Sep 04, 2007 7:50 pm

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

--kpederson

whick-p40
New Member
Posts: 3
Joined: Sat Dec 29, 2007 11:06 am

Postby whick-p40 » Sat Dec 29, 2007 6:13 pm

James_Francisco wrote: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!

User avatar
ericb
Member
Posts: 105
Joined: Fri Feb 23, 2007 8:51 am
Location: Vancouver USA

Postby ericb » Sun Jan 06, 2008 12:48 pm

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 :D (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.
Attachments
LDS Ward - Quarterly Report Graphs.xls
(90 KiB) Downloaded 175 times

User avatar
Mikerowaved
Community Moderators
Posts: 3132
Joined: Sun Dec 23, 2007 12:56 am
Location: Layton, UT

Postby Mikerowaved » Sun Jan 06, 2008 4:44 pm

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. ;)
So we can better help you, please edit your Profile to include your general location.


Return to “MLS Support, Help, and Feedback”

Who is online

Users browsing this forum: No registered users and 1 guest