Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
Crosstab in a subreport
megabri
I'm having issues passing parameters to a cross tab that's in a sub report. In my sub report I try the following in the open method:
subDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
subDataSet.open();
var sqlText = new String();
sqlText = "select siteid, location, worktype, status, wonum, workorderid from workorder"
+ " where istask = 0 and rownum < 10 and historyflag = 0 and siteid = '" + rows[0]["siteid"] + "'"
;
subDataSet.setQuery(sqlText);
The rows[0]["siteid"] should get the siteid from the parent query but it doesn't seem to be working properly. Has anyone had this issue?
Find more posts tagged with
Comments
mwilliams
Which BIRT version are you using? I'll take a look, but I was always under the impression that cubes weren't recomputed for each group, like a dataSet can be. Instead, a filter would need to be used to limit the data for each group. That's how I've always done it, anyways. I could be wrong, and I'll take a look.
megabri
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="112571" data-time="1355895032" data-date="18 December 2012 - 10:30 PM"><p>
Which BIRT version are you using? I'll take a look, but I was always under the impression that cubes weren't recomputed for each group, like a dataSet can be. Instead, a filter would need to be used to limit the data for each group. That's how I've always done it, anyways. I could be wrong, and I'll take a look.<br /></p></blockquote>
I'm currently using 2.3.2. I'm not sure how a filter works on a crosstab either, do you think that might solve my problem?
mwilliams
If I'm understanding you correctly, yes. What you'd do for a filter is to simply create a dataSet that brings in all of your data and use this for the cube. Then, you'd build your crosstab subreport, like normal, only now, you'll go to the filter tab in the property editor and simply filter the crosstab by the outer table's value. I'm not using Maximo, so I won't be able to do the dataSet exactly like you, but I can show you an example of a crosstab as a subreport, if you'd like. Let me know.
megabri
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="112594" data-time="1355935893" data-date="19 December 2012 - 09:51 AM"><p>
If I'm understanding you correctly, yes. What you'd do for a filter is to simply create a dataSet that brings in all of your data and use this for the cube. Then, you'd build your crosstab subreport, like normal, only now, you'll go to the filter tab in the property editor and simply filter the crosstab by the outer table's value. I'm not using Maximo, so I won't be able to do the dataSet exactly like you, but I can show you an example of a crosstab as a subreport, if you'd like. Let me know.<br /></p></blockquote>
I think I do use a filter. When I click on my crosstab then go to filters it says data._outer["siteid"], which is the field it should be narrowing it down by. Attach you'll find the rptdesign file and a pdf of the output. I have 2 sites (AW and ES). You'll see in the output the crosstab is only bringing back records for AW.
mwilliams
My guess is, the reason yours is only picking up the AW sub report is because that's the only time the cube is executed. With the first group. The cube is not re-executed each time. What you'll need to do is take the parameter out of the sub-dataSet, unless you use it with a normal binding element, like a table or list. If you need it as is, you'll need to create another dataSet that brings in all of the sub data for use in your cube.
megabri
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="112608" data-time="1355954031" data-date="19 December 2012 - 02:53 PM"><p>
My guess is, the reason yours is only picking up the AW sub report is because that's the only time the cube is executed. With the first group. The cube is not re-executed each time. What you'll need to do is take the parameter out of the sub-dataSet, unless you use it with a normal binding element, like a table or list. If you need it as is, you'll need to create another dataSet that brings in all of the sub data for use in your cube.<br /></p></blockquote>
I'm not sure I follow. I don't have the subDataSet attached to any table or list. How can I make the cross tab work?
mwilliams
Since you don't use it in any list, and only use it for the cube. Just write your query so that you bring in all of the data from both of your groups. Don't parameterize the sub query with the outer group's value.
megabri
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="112650" data-time="1356027496" data-date="20 December 2012 - 11:18 AM"><p>
Since you don't use it in any list, and only use it for the cube. Just write your query so that you bring in all of the data from both of your groups. Don't parameterize the sub query with the outer group's value.<br /></p></blockquote>
Ok I'll try that. For future reference there's no way to pass parameter from a parent dataset to a child dataset and print it in a cross tab?
mwilliams
I've not tested it, since talking to you, but I'm pretty sure that's the case, especially based on what you're reporting seeing. The cube is created the one time, so any dataSet changes don't change the cube.
