Re: [dabo-users] Reports

On Thursday, December 29, 2011 11:10:10 AM James Bull wrote:

> Wondering if I could get some pointers.

> I have tables setup as

> Parent(person)

> -ChildA(many: medications)

> -ChildB(many: allergies)

> The report I am trying to produce aims to have Parent details then below

> this 2 lists of childA and childB - to provide a summary report of

> medication one may be on and allergies. My problem is as I understand it,

> reportwriter needs denormalized dataset - which to me means each 'row'

> contains all the same data with one unique element; so in the example

> dataset below the first 3 'rows' are unique on meds(and medpkid) and retain

> same allergy etc:

> ( {'meds': 'medication-test1', 'pkid': 21, 'allergy': 'penicillin-test',

> 'medid': 465, 'ptid': 465, 'medpkid': 217}, {'meds': 'medication-test2',

> 'pkid': 21, 'allergy': 'penicillin-test', 'medid': 465, 'ptid': 465,

> 'medpkid': 471}, {'meds': 'drug-test', 'pkid': 21, 'allergy':

> 'penicillin-test', 'medid': 465, 'ptid': 465, 'medpkid': 472}, {'meds':

> 'medication-test1', 'pkid': 240, 'allergy': 'sulphur-test', 'medid': 465,

> 'ptid': 465, 'medpkid': 217}, {'meds': 'medication-test2', 'pkid': 240,

> 'allergy': 'sulphur-test', 'medid': 465, 'ptid': 465, 'medpkid': 471},

> {'meds': 'drug-test', 'pkid': 240, 'allergy': 'sulphur-test', 'medid': 465,

> 'ptid': 465, 'medpkid': 472} )

> So in report I am trying to list for each unique person(ptid) a list

> allergies: pencillin,sulphur and list meds: medication-test1 etc, instead

> as the rw Cursor iterates over each element in tuple I have long list of

> allergies essentially repeated in current example for each unique

> medication.

> Have tried grouping but still cannot achieve what I envisage.

> What am I doing wrong? Any help would be most appreciated.

> regards

> james

In general I create the sql manually. And in this case you will need to create

the joins with childA and childB.

tempCur = self.PrimaryBizojb.getTempCursor()

tempCur.execute("select nameofFields from ParentTable join childATable a on PK

= a.FK join childBTable b on PK = b.FK")

MyDataSet = tempCur.getDataSet()

IOW's create the sql needed in advance that meets your need. I'm not

completely sure what is needed in your case - but SQL is very powerful and you

should be able to get the data in the right format.

Johnf

_______________________________________________

Post Messages to: Dabo-users@leafe.com