How to sum data from dimension and fact tables in the same worksheet
413249Dec 4 2006 — edited Dec 4 2006Hi,
I have fact tables containing numbers of referrals to hospital, outpatient attendances, inpatient admissions. The fact tables are joined to dimension tables for GP practice, clinical specialty (e.g. cardiology, orthopaedics) and month. I've set up item classes for practice, specialty and month.
The GP practices are grouped into consortia, and I've set up an item class for consortium and a hierarchy of consortium to practice.
I can happily produce reports selecting consortium, practice (or 'all practices') and month with parameters and having the specialty as the row header, and the data coming from all the fact tables.
Now I need to calculate rates per thousand of the practice or consortium population. I've tried adding the population to the practice dimension table, but that will only work when I report for a specific practice - the population (and therefore the rate) shows as null if I try to do the report for 'all practices'. It seems Disco won't sum the populations from the dimensions table.
To get data from all the practices in the fact table, I do a decode in the condition, but this doesn't seem to work for the populations from the dimension table.
The decode I use in the condition is: (Shsurgeryname = DECODE(UPPER(:Practice),'ALL SURGERIES',Shsurgeryname,:Practice)), i.e. if the practice parameter is 'all surgeries', then it selects data whenever the folder item Shsurgeryname equals itself, otherwise it gets the data where Shsurgeryname equals the practice parameter.
As a quick fix, I've created separate data tables one with all the practices and their populations and one with the consortia and their populations. I've got seperate workbooks for consortium and practice reports, and have set up links. So I can get the report for a consortium and then drill to the linked practice-level report, select the practice and get my results.
Does anyone have a solution such that I can just have one worksheet and be able to sum the population from the population dimension table (or a seperate population fact table if necessary)?