Access database office pl/sql reporting sub-reporting

Access reporting using sub-reports

Access isn’t well regarded for its reporting. In my experience a lot of users use Excel as the Access reporting tool. But there is one category where Access excels above Excel. (nice pun eh?). That is sub reporting. This is where two separate pieces of information must be brought together on a Access report.

An example is the personnel assignment report below:

In this report there are three different pieces of information brought together.

  1. Individual basic details
  2. The professional qualifications
  3. Individual assignment history

This normally cannot be done in Access. So how does on accomplish this. With sub reports.

Now look at the same report in design view:

Its a bit messy – working with sub-reports always is. But here you can see that for each person in the detail section are two sub-reports. One is their profile, skills, wage number, education. And second their work assignment history. These sub-reports are always stored as separate reports within Access.

This does help make it easier to layout the reports.

Lets take a look at how the sub-reports are linked to the main report. There must be a unique way to join the two. otherwise the sub-reports will display either no data or all data. We want the sub-report to only show the related data. Lets take a look at the assignment history sub report.

To do this you must have the subreport item selected. Don’t click on the top left as that it is the report. When you’ve got it right you’ll see small resize handles around the sides of the subreport.

In this case you can see the sub-report field resourceID it linked with the main report field ID. This ensures you are reporting on the right data.

And you can carry this forward with sub-reports containing sub-reports. I’m not sure how far it goes as I’ve only gone to the sub-sub-report level myself.

This is just a sample of the project that my colleagues and I at chameeya take on on a regular basis.

bulk load oracle pipelining pl/sql sql

The mighty Oracle pipeline clause

Several months ago I was working on a problem with one of my clients. This involved taking a blob of data and performing a transformation on it. (what else would you do with a database eh?) However the catch is that the transform is performed according to user defined rules which are dynamic. The user’s have the ability to add/remove/update these rules.

A rule consisted of several columns of sparse data. The first columns described the data pattern which caused a match and then the remained gave the split of the new data. Basically it took financial data and split it out by low level organisation. So a company can see how each element of cost/income was split over the business.

The rules matched based on a set of hierarchical ordering. The more pattern columns which contained data were matched first and you can determine that less columns matched later. This was controlled by a table which was used to sort the rules. More in detail below.

So how do you go about solving this problem? The first instinct is to simply perform a join across the data table and rules and sorting and then use a massive case to remove the duplicates and select the proper rule to insert into the result table. Fine yes that would work, but would consume vast amounts of temporary space as Oracle built the join and then sorted it and then selected the proper rule and row of data. In most instances several rows of data would be created (if not dozens) for each real data row and at most only one would pass through.

Still it seemed the fastest way to do it. The plan didn’t look too bad and so  tested it on some real data. It was slow, very slow. And occasionally it would cause an error. (OMG oracle has bugs!). So I had to find another way to get the job done, and fast.

I came upon an idea where I load the rules all into memory. Basically into a array with the rules sorted in the proper order. I could then take a sample of a a couple of columns of data – which were mandatory – and that would reduce the number of rules to compare down to a handful. I could then trim down to the single rule easily enough and return the rule id for further processing. As the rules were all in memory this should be really, really fast. Now to hook it up!

So I can load up the rules pretty fast using BULK LOAD facility of Oracle PL/SQL and keep them in memory for fast look up. But now how do I hook up that process with the data in the table and have it work as fast as possible. The secret here is two things. Pipelining and Parallel processing.

Pipelining is where a PL/SQL function can return results as each item is calculated and then Oracle can process them as part of a larger PL/SQL statement as it goes. Otherwise the DBMS would have to wait for the entire set of results to be returned before continuing. It sounds like perhaps in this situation this is not needed, but it is.

As the results of each function are independant of any other invocation having the function run in parallel could boost performance significantly. In my experience it does make a difference. You’ll need to get Oracle to use multiple cores or processors obviously for this to work in practise.

So, now I’ve got all the pieces and the PL/SQL looked something like this:

The SQL Statement:
insert into output( ENT_ID, VALUE_, RULE_ID )
      select /*+ parallel(data,2) */  aqsr.ent_id, data.value_*aqsr.ent_percent,
      from reslt_m data
        inner join
          table(GroupdData(CURSOR(select /*+ parallel(GD, 2) */ * from Groupd_Data GD ))) asm
          on data.cost_centre_id=asm.cost_centre_id and
           data.expense_type_id=asm.expense_type_id and data.measure=asm.measure_id and
           data.year_=asm.year_ and data.data_type_id=asm.data_type_id
        inner join rules_ratios aqsr on asm.rule_id=aqsr.UID_

and the pipelined function GroupdData

 function GroupdData(p_inpcur in ref_basedatacur_t) return G_GrpdData_tab
       PIPELINED PARALLEL_ENABLE (Partition p_inpcur by any)
       /* definitions left out */

            fetch p_inpcur bulk collect into l_groupify_rec limit 10000;
            exit when l_groupify_rec.count = 0;
            for l_idx in l_groupify_rec.first..l_groupify_rec.last
                  /* tedious code to check and find rules left out ..
                     fill in rule_id to return to query              */
                      l_rec.rule_id := rules(l_idx)(l_rul_idx).UID_;
                      pipe row(l_rec);
                    end if;
              end loop;
          end if;
          end loop;
      end loop;
      close p_inpcur;

And that’s all there is to it! Okay so I’ve left out some of the implementation nitty gritty details but you get the idea.

This code would appear to run slowly, but don’t be deceived it is very very fast. And I intend on using similar techniques in the future. That’s only if a single SQL statement is either too slow or problematic.