Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

...

  • SlideShare: Reporting On Your Xml Field Data
  • PowerPoint: Reporting On Your Xml Field Data
    Info
    titleChange your WHERE clause, not your JOINs

    With the Serensoft approach, the schema for all "parsed" forms is broken up into a handful of tables, such that the joins will be (basically) the same for all queries – the part that changes will be in your "where" clause, to limit it to certain worksites, certain matrices, certain forms, certain fields...
    Here's a sample join, when incorporating data-warehouse with "live" database tables:

    Code Block
    borderStylesolid
    titleSerensoft Reporting Gizmo -- Sample SQL
    borderStylesolid
    from
        `osp_review` OSP_REVIEW
            join
        `metaobj_form_def` METAOBJ
            on OSP_REVIEW.`review_device_id` = METAOBJ.`id`
            join
        `osptool_form_response` FORM_RESP
            on FORM_RESP.`reviewId` = OSP_REVIEW.`id`
            join
        `osptool_root_formitem_response` ROOT
            on ROOT.`responseId` = FORM_RESP.`id`
            join
        `osptool_formitem_response` FORMITEM
            on ROOT.`responseItemId` = FORMITEM.`parentId`
            join
        `osptool_long_formitem_response` LONGITEM
            on FORMITEM.`id` = LONGITEM.`responseItemId`
            and FORMITEM.`responseItemType` = 'LONG'
            join
        `SAKAI_USER` USER
            on USER.`USER_ID` = FORM_RESP.`userId`
            join
        `SAKAI_USER_ID_MAP` USERIDMAP
            on USER.`USER_ID` = USERIDMAP.`USER_ID`
            join
        `osptool_formitem_response` PARENT
            on PARENT.`id` = FORMITEM.`parentId`
    where
        METAOBJ.`description` = 'certain-form-name' or
        USERIDMAP.`EID` = 'certain-user-name' -- etc
    

    In this example, differen forms or different users can be reported upon, based on different values in the "where" clause... leaving the joins as-is. Very handy!

...