nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

grouping tables in birt report

nithinurs

Hi,

I'm facing a situation where i need to display the table which displays data based on the for-loop. there are several tables which are attached to it.. i'm attaching a screenshot of the requirement along with the design i've done till now and xml file used. can i know which table should i use for grouping.
I'm using Birt - 4.2.0

Find more posts tagged with

Comments

kclark

nithinurs -- can you recreate the report with the sample DB for me? What about the table and the for loop are you having trouble with?

nithinurs

i've attached the sample of the report i need to generate..if i try to use the the tag depicted as 1 in the snapshot for grouping, only that table contents gets repeated properly. I've attached one pdf file. i need to generate report similar to that. report.doc contains the report which was generated with the xml file and the design file which is attached.

kclark

Could you please give me an exact output of what you expect with some other data? I'm having a hard time understanding the data in your report.

nithinurs

hi clark, sorry for the inconvenience. i've attached the snapshot which explains the requirement in very simple way. 
 
The contents of table 1, table2 are printed only once, whereas the contents of table 3, 4 , 5 , 6 are repeated. 
 
To give real world scenario as example, a patient takes few drugs and those details are noted down in the table. The patient name, age and weight should not be displayed every time in the report. It should be displayed only once. And other details like drug taken, side effects and prescribed by can contain more than one record and are listed down in the report. 
I would also like to add that the number of records in drugs and side-effects need not be equal all the time. Is it possible to do ?

kclark

Thanks for the explanation! If you put the above in one table you can then place table 1 and table 2 in the header. After you do that select the parent table > property editor > page break > uncheck repeat header and you should see table 1 and 2 only appear on the first page when you run it in the web viewer.

nithinurs

i'm unable to add any new tables to the header. Also if u look at the snapshot i'm repeating the table 1 and table 2 in 2nd page also, but the values within those table like age, weight, patient name are not printed.

Tubal

When you say "table" do you mean "cell"?

Where does your data come from? Does it come from several tables or just one query?

This would be fairly easy to do if it's all coming from one dataset. You would just arrange your data like you want it, create a row #, and if you're not on row #1, then you would hide your name, age and weight.

It seems odd that you wouldn't want that data displayed because you still have a space for it.

I've included a report with the sample database that is similar. Notice that the contact names disappear if it's not the first order.

nithinurs

hi tubal, ya i mean cell. I'm using xml as data-source. Problem is drug, side-effect and prescribed by all comes from 3 different data-sets. And patient weight, name, age comes from one data set. so i'm unable to find out on what basis i can create a group for the table.

Tubal

I think with the way you want to do it, it would be easiest to create a joint dataset or a scripted dataset from all 4 datasets, so you can key them all on the patient.

So you'd actually have to create 3 joint datasets:

Patient <-> Drug = Joint DataSet 1
Joint DataSet1 <-> Side Effect = Joint DataSet 2
Joint DataSet 2 <-> Prescribed By = Joint DataSet 3

And then you can report and group on Joint DataSet 3, with all your data available in 1 dataset.

nithinurs

hi tubal.. thanks for the reply.
But still i'm unable to do the grouping properly. I made one single dataset like u told.
When i tried to group using the drug tag, all the reaction gets listed first, followed by patient details like age, weight and then all the drugs gets listed.

If i try to group using the reaction tag, then all the drug gets listed first, followed by patient details like age, weight and then all the reaction gets listed.

I'm unable to group in the way i had posted in the previous snapshot. Is it possible to do group based on both drug and reaction ?
i'm attaching the design file i tried out by joining datasets.

nithinurs

Is it possible to do in the way i posted in the previous reply.. i didnt get any replies further. can anybody help me out on this ?

nithinurs

Is it possible to do in the way i posted in the previous reply.. i didnt get any replies further. can anybody help me out on this ?

Tubal

I'm not exactly sure what you're trying to get, but it seems like your joint data sets aren't set up correctly.

I can't tell how your tables are set up, because the field names aren't descriptive.

What field are your tables keyed on? (i.e. what field is the same in all three tables?)

I think the first table would be your patient table. Then you'd do a left join to your drug table, joining them on your key field (patient name?). This would create your first joint dataset. This is just a dataset that combines the data from the patient/drug tables.

Now we need to join this combined table with your reaction table. So you'd do a left join between your JDS-1 and your reaction table, joining them on the key field again. This would create JDS-2, and give you all of your data in one dataset.

nithinurs

problem is there are no fields which are common across the tables.

Hans_vd

Hi Nithinurs, 
 
I'm not sure how you want your report to look like. 
Is this more or less the layout you want: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>patient name: pat name age: 21
Height: 175 weight: 78

drug taken: crocin
metacin

side-effects: bodyache
headache
pain</pre>

nithinurs

hi hans,
no its not the way i want.. i've posted the snapshot of the report i need in my previous replies

Hans_vd

Hi Nithinurs, 
 
I'm sorry, I asked just to be sure. 
 
Now I have another question. 
You write: 
<blockquote class='ipsBlockquote' >I would also like to add that the number of records in drugs and side-effects need not be equal all the time</blockquote>
What do you mean by that? In your XML I see 2 drug records and 3 side-effect records? So how do you want that to be in the report 
 
Also, you could add patient name to any of the data sets, since they're all based on the same XML. But that wouldn't solve the problem, as there seems to be no way to join drugs and side-effects together. As long as that is not possible, you are not going to be able to get the report that you want. 
 
Regards 
Hans

nithinurs

hi Hans,
just for running the sample i gave 2 drug and 3 reaction records, in realtime scenario it can vary. Like there can be 10 drugs and 60 reaction records and so on..
If i add patient name tag to drug and and reaction tags and later join them can it solve my problem ? Also which kind of joining i can use.. ? i tried full outer join, but it didnt work out..

And also while grouping tables how can i group them ?

Hans_vd

Hi Nithinurs, 
 
<blockquote class='ipsBlockquote' >just for running the sample i gave 2 drug and 3 reaction records, in realtime scenario it can vary. Like there can be 10 drugs and 60 reaction records and so on..</blockquote>
I understand that, but since in an earlier comment you wrote "the number of records in drugs and side-effects need not be equal all the time", I wonder: what do you mean by that? 
 
Now about joining: 
If I look at the data in the sample xml I see: 
1 patient: 
- pat'name 
2 drugs: 
- crocin 
- metacin 
3 side-effects: 
- bodyache 
- headache 
- pain 
 
Let's say all data sets have patient in it, you could then inner join the patient data set and the drugs data set. The result would be two records: 
- pat'name, crocin 
- pat'name, metacin 
 
That is good. But if you try to join the side-affects data set to this new joint dat set, the result will be six records, as there is no relation between drug and side-effect. It would look like this: 
- pat'name, crocin, bodyache 
- pat'name, crocin, headache 
- pat'name, crocin, pain 
- pat'name, metacin, bodyache 
- pat'name, metacin, headache 
- pat'name, metacin, pain 
 
That's not good. 
 
As long as the data structure remains the same (no relation between drug and side-effect), you will not be able to solve your problem using joint data sets. 
I'm also very curious to know how drugs and side-effects should be combined. 
 
Regards 
Hans

nithinurs

Hi hans,
i had joined the datasets in the way u told, ya it shows so many records like u told.
regarding ur q'n relating how drug and side-effects are related, <PMS2-1> is the tag which contains patient details , drug and side-effects.
so for each <PMS2-1> tag we create one new patient record.
But there is no direct relation b/w drug and side-effect.
I can say that patient name is the unique thing that can contain drug and side-effects information.

Hans_vd

Hi Nithinurs,

I suppose that for patient "pat'name" you want three records, am I right?
Based on the sample xml you provided, how would you like the drug and side-effects to appear on your report?

nithinurs

considering the xml i had posted, which contains single patient information as of now, i want the report to print 2 drugs and 3 side-effects records.
like in the snapshot i had posted, based on how many characters page can contain, i want patient info like age, name to be printed only once, followed by 1st drug and 1st side-effect, 2nd drug and 2nd side-effect, and 3rd -effect.

If u look at the snapshot in accordance to the xml, page 1, page 2 tables contain 2 drug records and 2 side-effect records, whereas the page 3 table doesnt contain any drug record but contains the 3rd reaction record.

It would be fine if page break happens automatically rather than only 1 record per page, but the drug and side-effects should be printed as described in snapshot

Hans_vd

There are no drugs "aaa" or "bbb" in your sample xml, but I conclude that the combination of a drug and a side-effect is ad random.

I've been thinking about how the data set that will be the base for your report can be build, but at this moment I have no idea.

I'll think about it a bit more and get back to you when I have something.

Regards
Hans

nithinurs

sorry hans i should have corrected it, drugs were crocin and metacin
Thank you for the quick replies u gave, hope i get a solution for this