Docstoc

building-the-approval-form-in-BIRT

Document Sample
building-the-approval-form-in-BIRT Powered By Docstoc
					building the approval form in
            BIRT
     using yesterday’s query
         to build a report
               today’s agenda
1. we will use yesterday’s query to build the
   approval form in birt
2. we’ll start with the report requiring the user to
   enter a loan system id
3. later we’ll replace 2. by letting the user select
   a. a center from a list of all centers
   b. then select a customer from all customers at the
      selected center
   c. then select a loan account for the selected customer
4. finally we’ll restrict the list of centers in a. to
   only the center that corresponds to the logged
   in user, if the user is a loan officer
            high-level steps
1.   create the database connection
2.   create the query
3.   layout the report elements
4.   format the report
5.   add user-input parameters
6.   refine
7.   upload into mifos
                 test frequently!
1.    create the database connection
2.    test
3.    create the query
4.    test
5.    layout the report elements
6.    test
7.    format the report
8.    test
9.    add user-input parameters
10.   test
11.   upload into mifos
12.   test
 1. create the database connection
• called a Data Source in BIRT
• uses a Java Database Connection, also
  called Connector J
• quick and easy wizard
            high-level steps
1.   create the database connection
2.   create the query
3.   layout the report elements
4.   format the report
5.   add user-input parameters
6.   refine
7.   upload into Mifos
           2. create the query
•    tells BIRT what data to pull from the
     database
     (quick overview of the data set wizard)
1.   paste our query from yesterday in the notes
2.   give useful alias names to the columns
3.   add computed columns, if any
4.   set cache preferences, for performance
5.   preview
         2. create the query
• i don’t use parameters, filters and property
  binding (yet)
• ‘refresh’ is your friend
       a word about architecture
• during development




              MySQL             report
                                design
                   SQL
 mifos db       data source
                              birt

                              web viewer
                  flat file
  flat file     data source
    a word about architecture II
• running within mifos


                                web viewer

              MySQL                         report
                              birt report   design
                                engine
                   SQL
 mifos db       data source
                                scripted mifos sw
                              data source

                  flat file
  flat file     data source
    a word about architecture III
• our long term plan will probably
  include a repository with re-usable
                                                  rptlibrary
  components
• reports will inherit continually from the       SQL
  library                                      data source
                                                      loan
                                                     data set
               MySQL                                product
                                      report        data set
                                      design     personnel
                    SQL
  mifos db       data source                       data set
                               birt            approval form
                                                  data set
                                                customer
                                                 data set
                   flat file
   flat file     data source                    master page
   a word about architecture IV
• performance...

  best for query              best at layout
 and processing              and presentation


              SQL
                                           report
                  SQL                      design
 mifos db      data source
                                   birt


                 flat file
  flat file    data source
            high-level steps
1.   create the database connection
2.   create the query
3.   layout the report elements
4.   format the report
5.   add user-input parameters
6.   refine
7.   upload into Mifos
  3. layout the report elements
(overview of the palette and windows)
• use
  – grid for structure
  – table for row & column presentation
  – labels for simple text
  – text for long text
  – data for database output & functions
    • bind first, then modify
• other elements... image! chart!
    data v. non-data elements
• (drop each and compare)
  – binding is the connection to the data set
  – which elements can bind to the data set?
  – expression builder for building logic
  – data type determines how the data can be
    used
  – hint: for headers, add rows to your table to
    use its binding:
  “Loan Report for “ +
    dataSetRow[“customer_name”]
3. layout the report elements III
• use master page for standard ‘look-and-
  feel’
  – ‘letterhead’ with logo
  – footer
  – page orientation: landscape v. portrait
         4. format the report
(intro to the property editor)
• choose your fonts, colors, column widths,
   etc.
• custom formating
  – Format DataTime
  – Format Number
  – Format String
  – data type determines what formating is used
• justify and padding for alignment
         4. format the report II
useful hints
• select multiple cells at one time
• format at the table or grid level when possible
• grid cells v. report elements
  – you can format the grid before adding elements
• preview often (cache the data set to save time)
• preview with scripted data source only works
  within mifos
  – so add scripted data source last
            high-level steps
1.   create the database connection
2.   create the query
3.   layout the report elements
4.   format the report
5.   add user-input parameters
6.   refine
7.   upload into Mifos
  5. add user-input parameters
• create a report parameter
• free text
  – string input ‘2008-MM-DD’ for date
• drop-down list, called combo
  – enter a query if you need data from the
    database
  5. add user-input parameters
• insert parameter into your query
  – add ‘?’ to query
  – add report parameter to data set
     • order of parameters matters
  – use a user variable to insert the same
    parameter many times in your query
     • (SELECT @MyVariable := ?)
     • set user variable at lowest subquery level
          more on user variables
 • let’s find unpaid installments as of a given report date
SELECT
  A.GLOBAL_ACCOUNT_NUM, LS.ACTION_DATE, LS.PAYMENT_DATE,
  LS.PRINCIPAL, LS.PRINCIPAL_PAID
FROM
  ACCOUNT A
  JOIN LOAN_SCHEDULE LS
  ON A.ACCOUNT_ID = LS.ACCOUNT_ID
WHERE
    /* ACCT IS ACTIVE OR WAS CLOSED AFTER RPT DATE */
 (A.ACCOUNT_STATE_ID IN (5,9) OR
 A.CLOSED_DATE > '2008-04-30') AND
    /* PAYMENT WAS DUE BEFORE RPT DATE */
 LS.ACTION_DATE < '2008-04-30' AND
 (LS.PAYMENT_DATE > '2008-04-30' /* WAS PAID AFTER RPT DATE*/
   OR
 LS.PRINCIPAL_PAID < LS.PRINCIPAL) /* WAS NOT PAID IN FULL */
ORDER BY A.ACCOUNT_ID, LS.ACTION_DATE
         more on user variables
SELECT
  A.GLOBAL_ACCOUNT_NUM, LS.ACTION_DATE, LS.PAYMENT_DATE,
  LS.PRINCIPAL, LS.PRINCIPAL_PAID
FROM
  ACCOUNT A
  JOIN LOAN_SCHEDULE LS
  ON A.ACCOUNT_ID = LS.ACCOUNT_ID
WHERE
    /* ACCT IS ACTIVE OR WAS CLOSED AFTER RPT DATE */
 (A.ACCOUNT_STATE_ID IN (5,9) OR
 A.CLOSED_DATE > (SELECT @ReportDate := „2008-04-30‟)) AND
    /* PAYMENT WAS DUE BEFORE RPT DATE */
 LS.ACTION_DATE < @ReportDate AND
 (LS.PAYMENT_DATE > @ReportDate /* WAS PAID AFTER RPT DATE*/
   OR
 LS.PRINCIPAL_PAID < LS.PRINCIPAL) /* WAS NOT PAID IN FULL */
ORDER BY A.ACCOUNT_ID, LS.ACTION_DATE
                  6. refine
• expect to iterate, refine, reiterate!
           7. upload to mifos
•   it’s a very simple process to upload
•   go to admin tab
•   do you have permission to upload reports?

is it a normal report?
1. create category
2. browse, name, upload, submit
3. go to report tab to run
4. set permissions under admin
           7. upload to mifos
administrative document?
• a new functionality in v1.1 to associate
  reports with workflow steps

1.   select account type & status
2.   browse, name, upload, submit
3.   go to account to run
4.   set permissions under admin
            high-level steps
1.   create the database connection
2.   create the query
3.   layout the report elements
4.   format the report
5.   add user-input parameters
6.   refine
7.   upload into Mifos
            any questions?
•   now we’ll add the parameter to let the
    user select the center, customer and
    loan account id, instead of typing in the
    id.
0. get userId from mifos
                                               1. user
                                                               cascading
      personnel              center
                                             selects from
                                              the list of      parameter
                                             centers that
    personnel_id         loan officer id    belong to that      & hidden
  where level id = 1       customer id       loan officer
                                                               parameter
                                                                               2. user
                                                              loan         selects from
               group                   member
                                                             account         the list of
                                                                           clients in the
           customer_id               customer_id          account_id
                                                                              selected
             parent                    parent                                  center
           customer id               customer id

                                                                   3. user
                                                 loan           selects from
                         account
                                                account           the list of
                                                                the loans of     4. passes
                       account_id            account_id
                                                                the selected    account_id
                       customer id                                  client      to big query
        cascading parameter
1. display all centers
  -> user selects a center
2. display all members (with loans) of that
   center
  -> user selects a member
3. display all loans of that member
  -> user selects a loan
4. pass loan account id to approval form
   query
         cascading parameter
         with hidden parameter
0. mifos provides user ID of current user
1. display all centers for that user ID
  -> user selects a center
2. display all members (with loans) of that center
  -> user selects a member
3. display all loans of that member
  -> user selects a loan
4. pass loan account id to approval form query
               write the queries
1. display all centers for that user ID

SELECT
  CUSTOMER_ID, DISPLAY_NAME
FROM
  CUSTOMER
WHERE
  DISCRIMINATOR = 'CENTER' AND
  STATUS_ID = '13' /* ACTIVE */
  /* AND
  IF((SELECT LEVEL_ID                      we’ll ignore
     FROM PERSONNEL                          the mifos
                                             user ID at
     WHERE PERSONNEL_ID =
                                          first, and add
     (SELECT @PersonnelID := ?)) = 1,          it later
  LOAN_OFFICER_ID = @PersonnelID,
  1) */
               write the queries
2. display all members (with loans) of that center

SELECT
  CMEM.CUSTOMER_ID, CMEM.DISPLAY_NAME
FROM
  CUSTOMER CMEM
  JOIN CUSTOMER CGRP
  ON CMEM.PARENT_CUSTOMER_ID = CGRP.CUSTOMER_ID
     JOIN CUSTOMER CCNTR
     ON CGRP.PARENT_CUSTOMER_ID = CCNTR.CUSTOMER_ID
       LEFT JOIN ACCOUNT A
       ON CMEM.CUSTOMER_ID = A.CUSTOMER_ID
WHERE
  A.ACCOUNT_TYPE_ID = 1
  AND
  CCNTR.CUSTOMER_ID = ?
            write the queries
3. display all loans of that member
SELECT
  A.ACCOUNT_ID, A.GLOBAL_ACCOUNT_NUM,
  LA.LOAN_AMOUNT, LA.DISBURSEMENT_DATE
FROM ACCOUNT A
  JOIN LOAN_ACCOUNT LA
  ON A.ACCOUNT_ID = LA.ACCOUNT_ID
WHERE
  A.ACCOUNT_TYPE_ID = 1 /* LOAN */
  AND
  A.CUSTOMER_ID = ?
       cascading parameters
• create 3 data sets using our 3 queries
• create cascading parameter with the 3
  data sets
• pass the result IDs from one query to the
  next
• add the final result ID to the approval form
  query
          hidden parameter
• copy the magic data source
• create the magic report parameter
• add the report parameter to the first query
  of the cascading parameter
                   testing
•   upload into mifos to verify
•   can’t test in birt because of scripted data
    source)
•   log in as different users to see the choice
    of centers
    questions?
•

				
DOCUMENT INFO