Crosstab master detail

Hi All,
I'm Rani and new in BIRT.
Recently I created a dataset and based on this dataset I've created a crosstab.
The crosstab is very nice. But due to my limited knowledge I'm stuck:

my dataset results are like

ID Name value
1 NA 0
1 NA 1
1 NA 2
1 NA 3
2 BA 4
2 BA 0
2 BA 0

Above in my cross tab I want to show the info for Person with ID1 : So Name and ID and below the crosstab with the total value.

Like:
NAME: NA
ID : 1

Crosstab
Name Sum of Value

So like a master detail: in the master the info of one person and in the crosstab the values based on my groups.

Hope that I'm clear. I've tried everything with lists and table, but not succesful. Should I create another dataset for the master? I already have that data in my dataset. Any help would be highly appreciated/

Thank in advance.

Find more posts tagged with

Comments

mwilliams

Can you show me a small sample of data as it is in your dataSet and then show what you want the output to look like? That'd be really helpful to seeing exactly what you're trying to do. Also, let me know your BIRT version. Thanks!

RaniAg

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="107434" data-time="1343015752" data-date="22 July 2012 - 08:55 PM">
Can you show me a small sample of data as it is in your dataSet and then show what you want the output to look like? That'd be really helpful to seeing exactly what you're trying to do. Also, let me know your BIRT version. Thanks! </blockquote>
 
Attached you'll find a sample data of the dataset (I only have one dataset) 
Employees invoke different costs per day as they work. At the end we want to see per employee per day the costs that have incurred. Hope my data is easy to understand. 
My Birt version is 3.7.0 
 
Thank you very much!

RaniAg

<blockquote class='ipsBlockquote' data-author="'RaniAg'" data-cid="107443" data-time="1343044244" data-date="23 July 2012 - 04:50 AM">
Attached you'll find a sample data of the dataset (I only have one dataset) 
Employees invoke different costs per day as they work. At the end we want to see per employee per day the costs that have incurred. Hope my data is easy to understand. 
My Birt version is 3.7.0 
 
Thank you very much! </blockquote>
 
I forgot to attach the file.

mwilliams

Take a look at this report. Let me know if you have questions. ID is used as a dimension in the crosstab, so you can use it to filter. I removed the element from the crosstab, though and set the height of the cell to 0, so it's not visible. The crosstab is embedded into a table that is grouped on ID. The outer group's value is used to filter the crosstab data. Hope this helps get you started.

RaniAg

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="107496" data-time="1343148592" data-date="24 July 2012 - 09:49 AM">
Take a look at this report. Let me know if you have questions. ID is used as a dimension in the crosstab, so you can use it to filter. I removed the element from the crosstab, though and set the height of the cell to 0, so it's not visible. The crosstab is embedded into a table that is grouped on ID. The outer group's value is used to filter the crosstab data. Hope this helps get you started. </blockquote>
 
Hi Williams, 
This is the exact thing I discovered today! With a lot of trial and error I came up to this. 
Thanks for your time and explanation. 
I've one more question: 
How is it possible to pass one column of a dataset as parameter for another dataset? 
I tried with onFetch scripting to set the parameter to the value of the column in the first dataset. But on execution I get an error. 
 
params["PERS_ID"].value = row["ID"]; 
 
I use the PERS_ID report parameter in both datasets. Might that be an issue? 
 
Thanks in advance.

mwilliams

If you want to pass a value from one dataSet to another, you can put a table attached to the second dataSet into a grid or another table bound to the first dataSet. In the second dataSet, you'd use a dataSet parameter and in the table bound to the second dataSet, you'd go to the binding tab of the property editor and pass the value you want to pass from the outer dataSet through the dataSet parameter binding. Let me know.

RaniAg

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="107507" data-time="1343158688" data-date="24 July 2012 - 12:38 PM">
If you want to pass a value from one dataSet to another, you can put a table attached to the second dataSet into a grid or another table bound to the first dataSet. In the second dataSet, you'd use a dataSet parameter and in the table bound to the second dataSet, you'd go to the binding tab of the property editor and pass the value you want to pass from the outer dataSet through the dataSet parameter binding. Let me know. </blockquote>
Williams, 
I don't get it. 
I've a dataset that fetches all personal data for me 
select * 
from persons 
where sec_id = ? 
and id = nvl(?,colb); 
 
The second dataset is a function call: 
select * 
from (table functionx(?,?)) 
 
 
What I want to achieve is that based on the id of person in the first dataset I get the results for the second dataset. 
Is there any way to achieve this in the report design? 
 
I hope that I was clear. 
Thanks in advance.

mwilliams

<blockquote class='ipsBlockquote' data-author="'RaniAg'" data-cid="107513" data-time="1343164863" data-date="24 July 2012 - 02:21 PM">
Williams, 
I had one more question regarding this. 
If I want to display each cross tab for each employee on a new page, how should I do this? On which element I've to put a pagebreak? 
Thanks in advance! </blockquote>
 
If you select the outer table and go to the group tab in the property editor and double click on the group, you can set the page break after setting to always excluding last. That should do it. 
 
<blockquote class='ipsBlockquote' data-author="'RaniAg'" data-cid="107518" data-time="1343169685" data-date="24 July 2012 - 03:41 PM">
Williams, 
I don't get it. 
I've a dataset that fetches all personal data for me 
select * 
from persons 
where sec_id = ? 
and id = nvl(?,colb); 
 
The second dataset is a function call: 
select * 
from (table functionx(?,?)) 
 
 
What I want to achieve is that based on the id of person in the first dataset I get the results for the second dataset. 
Is there any way to achieve this in the report design? 
 
I hope that I was clear. 
Thanks in advance. </blockquote>
 
Put a table bound to dataSet 1 in your layout. Group it by ID. Drag your second dataSet into the group footer. Select the second table and go to the binding tab of the property editor. If you click on the dataSet parameter binding button, you can set it up to pass a value from your outer table through a dataSet parameter. If one of the two dataSet parameters in your query above are not for the ID, you'll need to add another one so that the sql is limited by the ID that you'll pass. Hopefully this makes more sense. I can make a simple example of using dataSet parameter binding, if you need. Let me know.

RaniAg

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="107551" data-time="1343233405" data-date="25 July 2012 - 09:23 AM">
If you select the outer table and go to the group tab in the property editor and double click on the group, you can set the page break after setting to always excluding last. That should do it. 
 
 
 
Put a table bound to dataSet 1 in your layout. Group it by ID. Drag your second dataSet into the group footer. Select the second table and go to the binding tab of the property editor. If you click on the dataSet parameter binding button, you can set it up to pass a value from your outer table through a dataSet parameter. If one of the two dataSet parameters in your query above are not for the ID, you'll need to add another one so that the sql is limited by the ID that you'll pass. Hopefully this makes more sense. I can make a simple example of using dataSet parameter binding, if you need. Let me know. </blockquote>
 
Thanks! But I'm using a crosstab for the second dataset. In the crosstab I don't get the parameter binding. Sorry for not telling that my second dataset is bound to a crosstab. Is there anyother option? Thanks!

mwilliams

Ah. For a crosstab, I think you have to just pass the outer value through a filter to limit the cube data. There is no dataSet parameter binding option.

RaniAg

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="107556" data-time="1343236256" data-date="25 July 2012 - 10:10 AM">
Ah. For a crosstab, I think you have to just pass the outer value through a filter to limit the cube data. There is no dataSet parameter binding option. </blockquote>
 
thanks. I already used a filter..but i've more to limit, which is not part of my datacube.So I should expand my datacube? 
The dataset is just a function call.... 
so there's no way to pass a value of a dataset to another dataset as a parameter? 
I tried with onfetch in the first dataset like: 
params["ReportParam1"].value = row["ID"]; 
params["ReportParam2"].value = row["COST_ID"]; 
 
But nothing happens....

mwilliams

I wouldn't expand the cube. I think I'd keep one dataSet for the cube, then make an identical dataSet to the one the cube uses and use it with dataSet parameter binding as I described above to get the other information that you want.

RaniAg

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="107559" data-time="1343238419" data-date="25 July 2012 - 10:46 AM">
I wouldn't expand the cube. I think I'd keep one dataSet for the cube, then make an identical dataSet to the one the cube uses and use it with dataSet parameter binding as I described above to get the other information that you want. </blockquote>
Thanks Williams! 
 
Another question, do you know the maximum number of rows that Birt viewer can handle? 
My dataset has 250K rows, which are being grouped in my datacube. 
At run time, the report crashes saying that outofmemoryerror cannot be cast to java.lang.exception...any idea how to tweak this?

mwilliams

Can you try changing the eclipse.ini to have -Xmx1024m instead of -Xmx512m and see if that fixes it?

RaniAg

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="107605" data-time="1343333543" data-date="26 July 2012 - 01:12 PM">
Can you try changing the eclipse.ini to have -Xmx1024m instead of -Xmx512m and see if that fixes it? </blockquote>
Thank you Williams. It helped. 
I should thank you for all your replies! I'm improving in BIRT and I should say that I like it! 
I've some other questions, but I'll make a new thread.

mwilliams

You're very welcome! I'm always glad to help! I'm also glad that you are learning and liking BIRT! Definitely let us know whenever you have questions!