¶ MySQL Adhoc Reports Configuration
¶ Services
| Service | Workflow(s) | Purpose | Status |
|---|---|---|---|
| sqlReportServiceCreateSubqueries | Aggregate / Filter Views, Union Views, Join Views | Create the config files for subqueries | Working |
| sqlReportServiceCreateSQLModelConfig | SQL Views | Create SQL Model config file | Working |
| sqlReportServicePublishViews | Sql Publish views | Publish the SQL models by creating a taxonomy containing the view ids and inserting it into the app config | Working |
| sqlReportServiceGenerateQuery | SQL View Generation | Call the module (sqlQueryGenerator) to generate the query from the SQL Model config file and then execute the query. If successful, update sqlPublishedViews_1_Community_{communityId} taxonomy to contain all generated views | Working |
| sqlQueryGenerator | SQL Views, Aggregate / Filter Views, Union Views, Join Views, SQL View Generation | Use details in the SQL Model config file to generate the sql query | Working |
| sqlReportAutofillRepeat | Report setup | Autofill the field select repeat with all available fields for the selected view | Working |
| sqlReportCreateReportObject | Report setup | Generate a data object for the report to be used in dashboards | Working |
¶ The flow
- Create the app we are working on in app config app on the staging server
- Complete the application registration workflow
- Complete the workflow for all Data Objects
- Complete the Application Categories workflow
- Complete the Roles workflow
- Complete the Workflow Processes workflow
- If appropriate, complete the Subprofile Process workflow
- Community level
- Move this into the app
- Generate basic views in CouchDB ( To be reviewed )
- Return to app profile and open SQL models sub profile category
- Create a sub profile instance for each view
- Select the views to join
- Service will generate config file for the view
- SQL publish views workflow at profile level
- Create view taxonomy with references to models
- Moved into app config
-
Adopt app to a community
-
Build to production ( To be reviewed)
-
Go community hub ( To be reviewed)
-
Open community model definition ( To be reviewed)
- generate views
¶ Note of what needs to be added
-
Automatic versioning based on sdo changes or major view changes
-
In the reportsv4 app change the label for the report type from "my sql view" to "MySql adhoc reports"
-
Review data models to refine labels etc. and update
-
Integrate view generation into the build process
¶ Use cases
| ID | Use case | Status | Comment |
|---|---|---|---|
| 1 | View containing no subqueries | Complete | No issues |
| 2 | View containing a single aggregate subquery | Complete | No issues |
| 3 | View containing a nested subquery. So that we create an aggregate subquery of a join subquery | Complete | No issues |
| 4 | View containing a nested subquery. So that we create a join subquery of an aggregate subquery | Complete | No issues |
¶ Simple use case (01)
The first simple use case will involve just a single join. The 2 views joined will be.
- cesSurveyCycle
- cesSurveyResponse
The fields required for the view will be as followed:
cesSurveyCycle
- _communityId (Community Id)
- _profileId (Profile UUID)
- targetArea (Target area)
- locLevel00.code (Country code)
- locLevel00.label (Country)
- locLevel01.code (Provice code)
- locLevel01.label (Province)
- locLevel02.code (District code)
- locLevel02.label (District)
cesSurveyResponse
- _communityId (Community Id)
- _profileId (Profile UUID)
- interviewDate (Interview date)
- gender.code (Gender code)
- gender.label (Gender)
- ageRange.code (Age range code)
- ageRange.label (Age range)
These views will be joined on _profileId
The expected query should look like this.
SELECT 1.G_COMMUNITYID, 1.G_PROFILEUUID, 1.TARGETAREA, 1.LOCLEVEL00_CODE, 1.LOCLEVEL00, 1.LOCLEVEL01_CODE, 1.LOCLEVEL01, 1.LOCLEVEL02_CODE, 1.LOCLEVEL02, 2.INTERVIEWDATE, 2.GENDER_CODE, 2.GENDER, 2.AGERANGE_CODE, 2.AGERANGE
FROM ces_surveycycle 1
JOIN ces_surveyresponse 2 ON ces_surveyresponse 2.G_PROFILEUUID = 1.G_PROFILEUUID
On the form, the following fields show the Model ID which is the subprofileId of the created model.
Model Name: Test case
Model ID: f1c5bbc6-821a-4b31-ca49-796eb5ac8718
From the data in this form a config file is generated. This file can be found in the config bucket kwantu_configs. The filename is constructed from the Model Id + ":" + version. So the full file location is: kwantu_configs/f1c5bbc6-821a-4b31-ca49-796eb5ac8718:2 for example.
The config file generated looks like this:
{
"_id": "f1c5bbc6-821a-4b31-ca49-796eb5ac8718:2",
"_rev": "1-0a9fe8d138b0e15e061c87897183b481",
"where": [],
"joins": [
{
"leftViewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyCycle1",
"leftField": "_profileId",
"isLeftSubquery": false,
"type": "full",
"isRightSubquery": false,
"rightViewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyResponse1",
"rightField": "_profileId",
"seq": 1
}
],
"published": false,
"views": [
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyCycle1",
"viewAlias": "Test-case:1",
"isSubquery": false,
"seq": 1
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyResponse1",
"viewAlias": "Test-case:2",
"isSubquery": false,
"seq": 2
}
],
"coreViews": [
{
"version": "1",
"uuid": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyCycle1"
},
{
"version": "1",
"uuid": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyResponse1"
}
],
"version": 2,
"title": "Test case",
"select": [
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyCycle1",
"header": "G_COMMUNITYID",
"agregationDo": "None",
"field": "_communityId",
"type": "",
"viewSeq": 1,
"seq": 1
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyCycle1",
"header": "G_PROFILEUUID",
"agregationDo": "None",
"field": "_profileId",
"type": "",
"viewSeq": 1,
"seq": 2
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyCycle1",
"header": "Country code",
"agregationDo": "None",
"field": "locLevel00.code",
"type": "search",
"viewSeq": 1,
"seq": 3
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyCycle1",
"header": "Country",
"agregationDo": "None",
"field": "locLevel00.label",
"type": "search",
"viewSeq": 1,
"seq": 4
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyCycle1",
"header": "Province code",
"agregationDo": "None",
"field": "locLevel01.code",
"type": "search",
"viewSeq": 1,
"seq": 5
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyCycle1",
"header": "Province",
"agregationDo": "None",
"field": "locLevel01.label",
"type": "search",
"viewSeq": 1,
"seq": 6
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyCycle1",
"header": "District code",
"agregationDo": "None",
"field": "locLevel02.code",
"type": "search",
"viewSeq": 1,
"seq": 7
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyCycle1",
"header": "District",
"agregationDo": "None",
"field": "locLevel02.label",
"type": "search",
"viewSeq": 1,
"seq": 8
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyResponse1",
"header": "Interview date",
"agregationDo": "None",
"field": "interviewDate",
"type": "",
"viewSeq": 2,
"seq": 9
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyResponse1",
"header": "Gender code",
"agregationDo": "None",
"field": "gender.code",
"type": "search",
"viewSeq": 2,
"seq": 10
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyResponse1",
"header": "Gender",
"agregationDo": "None",
"field": "gender.label",
"type": "search",
"viewSeq": 2,
"seq": 11
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyResponse1",
"header": "Age range code",
"agregationDo": "None",
"field": "ageRange.code",
"type": "search",
"viewSeq": 2,
"seq": 12
},
{
"viewId": "ViewConfig-29000ea37d1fe-ef1c-4c00-8201-e6ac6e6e7039cesSurveyResponse1",
"header": "Age range",
"agregationDo": "None",
"field": "ageRange.label",
"type": "search",
"viewSeq": 2,
"seq": 13
}
],
"Group": []
}
The key fields in this config file are as follows:
where
joins
published
views
coreViews
version
title
select
group
The expected result is to produce a view that looks like this:
| Community Id | Profile Id | Target Area | Country code | Country | Province code | Province | District code | District | Interview date | Gender code | Gender | Age range code | Age range |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
¶ single subquery use case (02)
The second use case will involve a single subquery. For this example we will use an aggregate subquery.
¶ Survey response questions aggregate
The purpose of this aggregate is so we can provide a count of interruptions. The view we will use for this is cesSurveyResponse-questions. The fields we need to select are the following:
- _communityId (G_COMMUNITYID)
- _profileId (G_PROFILEUUID)
- interuptionReason.code (Count of interuptions)
We also need to select the aggregation for "Count" for the interuptionReason.code
This query will also require a group by and a where clause
The where clause will check that the field interuptionReason.code contains Yes.
The group by will group on the field _profileId
The subquery should end up looking like this:
SELECT G_COMMUNITYID, G_PROFILEUUID, COUNT(INTERUPTIONREASON_CODE) `Count of interptions`
FROM `ces_surveyresponse_questions`
WHERE INTERUPTIONREASON_CODE LIKE %Yes%
GROUP BY G_PROFILEUUID
¶ The model
The final model will now join together following views:
- cesSurveyCycle
- Survery response questions aggregate
The fields required for the view will be as followed:
cesSurveyCycle
- _communityId (Community Id)
- _profileId (Profile UUID)
- targetArea (Target area)
- locLevel00.code (Country code)
- locLevel00.label (Country)
- locLevel01.code (Provice code)
- locLevel01.label (Province)
- locLevel02.code (District code)
- locLevel02.label (District)
Survery response questions aggregate
- Count of interuptions (Count of interuptions)
These views will be joined on _profileId
The expected query should look like this.
SELECT `1`.`G_COMMUNITYID`, `1`.`G_PROFILEUUID`, `1`.`TARGETAREA`, `1`.`LOCLEVEL00_CODE`, `1`.`LOCLEVEL00`, `1`.`LOCLEVEL01_CODE`, `1`.`LOCLEVEL01`, `1`.`LOCLEVEL02_CODE`, `1`.`LOCLEVEL02`, `2`.`Count of interuptions`
FROM ces_surveycycle `1`
JOIN (
SELECT `G_COMMUNITYID`, `G_PROFILEUUID`, `COUNT(INTERUPTIONREASON_CODE)` `Count of interptions`
FROM `ces_surveyresponse_questions`
WHERE `INTERUPTIONREASON_CODE` LIKE %Yes%
GROUP BY `G_PROFILEUUID`
) `2` on `2`.`G_PROFILEUUID` = `1`.`G_PROFILEUUID`
¶ nested subqueries use case (03)
The third use case will involve a nested subquery. For this example we will use an aggregate subquery and a join subquery.
¶ Survey response join
The purpose of this join is so we can join together a view with it's repeat. The following views will be joined together using G_UUID
- cesSurveyResponses
- cesSurveyResponse-questions
¶ Survey response join aggregate
The purpose of this aggregate is so we can provide a count of interruptions. The view we will use for this is Survey response join. The fields we need to select are the following:
- _communityId (G_COMMUNITYID)
- _profileId (G_PROFILEUUID)
- interuptionReason.code (Count of interuptions)
We also need to select the aggregation for "Count" for the interuptionReason.code
This query will also require a group by and a where clause
The where clause will check that the field interuptionReason.code contains Yes.
The group by will group on the field _profileId
The subquery should end up looking like this:
SELECT G_COMMUNITYID, G_PROFILEUUID, COUNT(INTERUPTIONREASON_CODE) `Count of interuptions`
FROM (
SELECT * FROM `ms_ces_surveyresponse` `1`
JOIN `ms_ces_surveyresponse_questions` `2` on `2`.`G_UUID` = `1`.`G_UUID`
)
WHERE INTERUPTIONREASON_CODE LIKE %Yes%
GROUP BY G_PROFILEUUID
¶ The model
The final model will now join together following views:
- cesSurveyCycle
- Survey response join aggregate
The fields required for the view will be as followed:
cesSurveyCycle
- _communityId (Community Id)
- _profileId (Profile UUID)
- targetArea (Target area)
- locLevel00.code (Country code)
- locLevel00.label (Country)
- locLevel01.code (Provice code)
- locLevel01.label (Province)
- locLevel02.code (District code)
- locLevel02.label (District)
Survey response join aggregate
- Count of interuptions (Count of interuptions)
These views will be joined on _profileId
The expected query should look like this.
SELECT `1`.`G_COMMUNITYID`, `1`.`G_PROFILEUUID`, `1`.`TARGETAREA`, `1`.`LOCLEVEL00_CODE`, `1`.`LOCLEVEL00`, `1`.`LOCLEVEL01_CODE`, `1`.`LOCLEVEL01`, `1`.`LOCLEVEL02_CODE`, `1`.`LOCLEVEL02`, `2`.`Count of interuptions`
FROM ces_surveycycle `1`
JOIN (
SELECT `G_COMMUNITYID`, `G_PROFILEUUID`, `COUNT(INTERUPTIONREASON_CODE)` `Count of interptions`
FROM (
SELECT * FROM `ms_ces_surveyresponse` `1`
JOIN `ms_ces_surveyresponse_questions` `2` on `2`.`G_UUID` = `1`.`G_UUID`
)
WHERE `INTERUPTIONREASON_CODE` LIKE %Yes%
GROUP BY `G_PROFILEUUID`
) `2` on `2`.`G_PROFILEUUID` = `1`.`G_PROFILEUUID`