User Tools

Site Tools


customizing_reports
Building Custom Reports

The Churchinfo Query Engine

Churchinfo was built with a set of tables and pages that together form a query engine that can support about any query necessary. Clicking on “View Report Menu” in the left sidebar will display a list of queries available to the user. None of these are hard-coded into PHP—they are all dynamically rendered by the query engine.

The tables are..

Query_qry QueryParameters_qrp QueryParamterOptions_qpo

…and the pages are… QueryList.php QueryView.php

SQL queries (SELECT statements) are stored in a database table, along with a description of the information they return, parameters they can accept to refine their results, and options users can select for those parameters.

A user will call QueryList.php by selecting “View Report Menu” from the left navigation bar which will display the contents of the Query_qry table. This table stores all the queries available to the user. It stores the name of the query, the actual SQL required to run the query, and a description of the query. Selecting a query from this page will call QueryView.php for that query.

QueryView.php will first check if the requested query requires any parameters. If not, the page will simply render the results of the query. If the query does require one or more paramters, QueryView.php will request the parameters from the user. For each parameter, the page will either display a textbox or a drop-down list of options if the particular parameter has an options list. Once the user has entered the necessary parameters, QueryView.php will render the results of the query.

Building an Churchinfo Query, Part 1: Define the Query

For this example, we're going to build a query that returns a list of all family records for the state of South Dakota (great state that it is). To do this, we first determine the SQL necessary to display the results:

SELECT fam_Name AS Name FROM family_fam WHERE fam_State = 'SD'

Enter a record in the Query_qry table that looks like this:

qry_ID qry_SQL qry_Name qry_Description
1 SELECT fam_Name FROM family_fam WHERE fam_State = 'SD' Familes in South Dakota List of all families in the great state of South Dakota

Note: The qry_ID column will auto-increment; the value is simply entered here for illustration.

Calling the View Report Menu page from the left sidebar will render QueryList.php and display the list of all available queries, including the query we just entered. Selecting that query will display a list of all familes in the state of South Dakota.

That was the simple part…

Building an Churchinfo Query, Part 2: Defining Parameters

Say that we now want to give the users an option of specifying the state for their search. To do this, we need to change the SQL stored in the table to allow for a parameter where the state abbreviation would normally be. To do this, we put a placeholder in our SQL statement and give the placeholder an alias. A placeholder consists of the alias name surrounded by tildes (”~alias~”). So now our SQL statement looks like this:

SELECT fam_Name FROM family_fam WHERE fam_State = '~state~'

Let's update our record in the Query_qry table to look like this:

qry_ID qry_SQL qry_Name qry_Description
1 SELECT fam_Name FROM family_fam WHERE fam_State = '~state~' Familes by Specific State List of all families for a specified state

Since we now have a placeholder in our SQL we need to enter a paramater in the QueryParameter_qrp table to reference this placeholder. Enter this record in QueryParameters_qrp:

qrp_ID qrp_qry_ID qrp_Type qrp_Name qrp_Description qrp_Alias qrp_Required
1 1 0 State Abbreviation The two-digit state abbreviation state 1

Note: There are more columns in the table, but they can remain NULL. qrp_qry_ID is a Foreign Key to Query_qry; it should be the qry_ID value in that table.

Now, when you select the Families by Specific State link from the list of queries, you'll be prompted for a state abbreviation. Enter the abbreviation and click on “Execute Query” and the query will run with the placeholder replaced by the state abbreviation you entered.

Building an Churchinfo Query, Part 3: Defining Parameter Options

Say that now we want to limit the user to a set number of states in the five-state area: South Dakota, North Dakota, Minnesota, Iowa, and Nebraska. To do this, we will define parameter options for the “state” parameter we entered previously.

First we need to change the parameter type. In the queryparameter_qrp table, the qrp_Type column specifies how the engine should collect the parameter value, like so:

qrp_Type Meaning
0 Free-form text via an INPUT box
1 Drop-down list; options hard-coded by developer
2 Drop-down list; options rendered at run-time via a SQL statement

So, change the qrp_Type value in the QueryParameter_qrp table to a value of 1. This will cause the engine to look up parameter option values when requesting the value from the user for this parameter.

Next, enter five rows in the queryparameteroptions_qpo table, one for each of the five states we want to appear in the drop-down box. For each option, we enter the value for the OPTION tag and what we want to display to the user. Like this:

qpo_qrp_ID qpo_Display qpo_Value
1 South Dakota SD
1 North Dakota ND
1 Iowa IA
1 Minnesota MN
1 Nebraska NE

Note: qpo_qrp_ID is a Foreign Key back to the query parameter we entered in the QueryParameters_qrp table. Populate it with whatever that record ID is.

Now, selecting that query from the QueryList.php page will first prompt you with a drop-down list of the five states we entered. Selecting a state and clicking the “Execute Query” button will run the query using the option we selected.

Building an Churchinfo Query, Part 4: Using SQL to Define Parameter Options

Query Engine Management

Defining paramter options as we did in Part 3 is fine, so long as you know the options in advance and they won't change. In some cases, however, this is neither realistic nor convenient. To get around this, the InfoCentral Query Engine gives you the ability to define query parameter options at run-time using a SQL statement.

Our query as it stands is handy as long as all the families we want to return are located in one of the five states we specified in the query paramater options list. But if we have a family from, say, Indiana join the church, there wouldn't be an option for that state in the list. To do this, we're going to need to provide a SQL statement to the query engine that will return the options at run-time.

First, let's define the the SQL statement that will return what we need—one instance of every state present in the database. In this SQL statement, we need to alias the value of the OPTION tag as “Value” and the display of the OPTION tag as “Display” (in this case, they will be the same thing; we can't get to the full state name using SQL, so the user will just have to use the two-digit abbreviation). The SQL will end up looking like this:

SELECT DISTINCT(fam_State) AS Value, fam_State AS Display FROM Family_fam ORDER BY fam_State

This should give us two columns—”Value” and “Display”—both containing the two-digit state abbreviation for every state that appears in the database.

Now, let's change the qrp_Type field in the queryparameter_qrp table to a value of 2. This will tell the query engine to use the SQL statement provided to generate the parameter options. Our record in queryparemeters_qrp should now look like this:

qrp_ID qrp_qry_ID qrp_Type qrp_OptionSQL qrp_Name qrp_Description qrp_Alias qrp_Required
1 1 2 SELECT DISTINCT(fam_State) AS Value, fam_State AS Display FROM Family_fam ORDER BY fam_State State Abbreviation The two-digit state abbreviation state 1

Select this query from the menu. You'll see that the engine runs the SQL we provided and uses the results to generate the drop-down list for the parameter options. It shows the value aliased by “Display” to the user, and uses the value aliased by “Value” as the value to replace the placeholder with.

customizing_reports.txt · Last modified: 2014/06/01 20:35 by hermandr