hiding an inner row

I have a massive report that is, quite honestly, psychotic and a bit unwieldy. The run time is crazy, based on the existing SQL query. 15+ left joins. So I'm giving it an overhaul. 
 
One of the things I noticed is that several of the joins are designed to do counts to verify if a later dataset result needs to be shown. I'm quite certain there are better ways. Unfortunately, I don't know them. 
 
I stumbled across this post and it looks like what I'm attempting to do, at least in regards to hiding an inner table call. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/org/forum/index.php/topic/24501-hiding-a-row-if-an-inner-table-contains-no-rows/page__s__ee550ee56ae7ec9b057d573cb997e493'>http://www.birt-exchange.org/org/forum/index.php/topic/24501-hiding-a-row-if-an-inner-table-contains-no-rows/page__s__ee550ee56ae7ec9b057d573cb997e493</a> 
 
I am utilizing BIRT 2.3.2. Based on that article, and assuming it's what I should be doing, how do I put in an onCreate / onRender ... and what type of code am I looking at? If there's a tutorial or something, that's fine too. 
 
I appreciate your time, 
Shadraq

Find more posts tagged with

Comments

mwilliams

I'm not certain if the linked post will help you, or not. Just hiding an element doesn't make it not run, it just makes it hidden. This may not help your report creation time, at all. The only way to keep an element from running is to drop it in an early event, like the beforeFactory. Maybe I'm misunderstanding. Where are you doing your joins? In your query or with joint dataSets, in BIRT? Can you also describe your report, in more detail, or recreate a similar scenario, using the sample database, if possible?

TheShadraq

Mike, I am, actually, looking just to hide it. The rest is good stuff, and I'd like to look into that in the future to make this a quicker-loading/running report, but in the meantime, I need to simply hide the section.

mwilliams

Using the onCreate to set a persistent global variable that you can recall in the onRender of another element is a way to hide, like this. To hide, you'd just check the variable in the onRender. If its value tells you to hide the second element, you'd just set the display property to none, like:

this.getStyle().display = "NONE";

You will then need to make sure you run the report using separate run and render tasks. If using the viewer, you'd need to call the URL with frameset. If you need more info, please let me know where you're stuck.

TheShadraq

Mike, Yes, I'm going to need a bit more info, thanks. I'm afraid onCreate isn't something I've ever messed with. Can it point to a dataSet or run a query, etc?

TheShadraq

how do i add an onCreate method?

kclark

TheShadrq, 
If you select the element you want to work with (hide in this case) then select the "script" tab you can then select from the drop down and the top of your script onCreate then add 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
this.getStyle().display = "NONE";
</pre>
 
After you've done this go ahead and preview it and everything should be fine now.

TheShadraq

kclark, thanks for the help. That worked well.

Followup question - can I call, in onCreate, to hide the inner section if a row being built in that section returns null/0/etc. ?

TheShadraq

as an example, this is what I want to do. I know this is incorrect.

onCreate:

var hidethissection = false;
if(row["hasmaterial"] == 0)
{
hidethissection = true;
}

return hidethissection;

onRender:

if(hidethissection)
{
this.getStyle().display = "NONE";
}

mwilliams

In the detail row, of the inner table, put this in the onCreate:

reportContext.setPersistentGlobalVariable(row["ID"], "true");

Then, in the onRender of the outer table's row, put:

if(reportContext.getPersistentGlobalVariable(row["ID"]) == null){
this.getStyle.display = "NONE";
}

Where "ID" is the field that links the inner and outer tables.

That should do it. You'll just need to make sure you're using the web viewer to test this, from the designer, because it's the only output that uses separate run and render tasks.

TheShadraq

Mike, 
 
Thanks for the help. I believe I'm following the code now, and the basic idea. Question - when you say where "id" is the field that links the inner & outer tables ... what exactly does that mean? In the below Pic, I'm putting the onCreate code on the grid (blue) which is bound to a query outputting a count() variable. If the count() = 0, I want the row (Red) to fully disappear. the onRender code is on the row.

mwilliams

True has absolutely nothing to do with it, besides giving it a non null value. That is why I check for null. If there is no data in the inner table, the PGV wouldn't be set because there would be no detail rows, so in the outer table, the value of the PGV would be null. As for what I mean by the field that links the two tables. Usually, when you embed a table into another, it's to have a subreport for a group. Say you have employees in the outer table and employee tasks in the inner table. You would either filter the inner table or use dataSet parameter binding to limit the data on the inner table with the outer table's value. So, whatever field that exists in both is the linking field. Using this field to name the PGV allows you to set it and access it by the correct name, on the inner and outer tables. If this is just a one time situation that is not repeated by a group or section, you wouldn't need a dynamic name, like that, you could just use "hidethissection" as the name. Hope this helps. If you'd like an example, let me know. I'll make you one, real quick.

TheShadraq

Thanks for the explanation. This is a one-off variable that only exists in the particular section. Yes, I would like an example of a use of a one-off. I'm a visual learner

Thanks for the offer (and the patience).

mwilliams

Look at this report. Run it in the web viewer with the default parameter value. Then, run it again with 160000.

TheShadraq

I'm still not seeing where hidePayments is actually being set to some sort of value though.

mwilliams

Look at the script in the onCreate of the detail row in the inner table. It sets a value in the PGV, if there is a value. If there are no values in the inner table, the detail row script never gets called. Therefore, the value of your PGV would be null. If there's a value, the grid is shown and the label is hidden. If the value is null, this means the table is empty, so the grid is hidden and the label is shown. Hope this helps. You can always view where there is script in a report by going to the script section of the outline view.

TheShadraq

Thanks for the explanation. Yes I did see all that. I just didn't follow the logic that well. I get it now, though. Thanks for the help.

mwilliams

No problem. Let me know if you have any other questions on this.

TheShadraq

Ok, so I followed your example and I'm still having the same results. Looking at my above screenshot with the Blue and the Red: 
 
I've got the below in the onRender Script of the red row 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>if(reportContext.getPersistentGlobalVariable("hidetoolsection") == null){
this.getStyle().display = "NONE";
}</pre>
 
On the blue grid, not the detail row, but on the grid itself, in the onCreate, the code is this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.setPersistentGlobalVariable("hidetoolsection","false");</pre>
 
Utilizing the View Report in Web Viewer option I still have it displaying...and my query that is bound to that grid is returning null. Did I miss a step?

mwilliams

You situation is slightly different then. Before you were talking about checking if a row value was 0. Set the pgv to that value, then in the onRender, check the pgv for the value rather than just checking for null. Slight change, but without the detail row script, the variable will be created each time, so it'll always show.

TheShadraq

Ah yes, that did it. Thanks for this marathon of help. I certainly appreciate it. 
 
In case anyone comes along needing a resolution, below is the final code based on the above schematic, where the SQL query is returning either a 1 or a 0 : 
 
Red: OnRender 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>if(reportContext.getPersistentGlobalVariable("hidetoolsection") == 0){
this.getStyle().display = "NONE";
}</pre>
 
Blue: onCreate 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.setPersistentGlobalVariable("hidetoolsection",row["hastool"]);</pre>

mwilliams

Not a problem.

TheShadraq

Upon further testing (previewing with Web Viewer), this works great if I'm searching for a 1 to 1 relationship: record = '123'. But if I'm searching for multiple, this doesn't seem to work: record in('123,'456') 
 
For Example: 
<pre class='_prettyXprint _lang-auto _linenums:0'>record 123 / hastool returns 1
record 456 / hastool returns 0</pre>
 
The report will look through twice, creating 5 or so pages (expected), but in the section that we did the above code for, hastool will hide for both cycles, regardless of what it returns in the cycles separately. 
 
I tested this where all records had a hastool returning 1 and the report ran as expected. I also tested this where multiple returned 1, but one returned 0. In this test case, the section hid for all instances. 
 
Is this because we are setting a global variable in the section?

mwilliams

Can you create a flat file with a small sample of data how it looks in your dataSet, then build a report that uses this data an shows me your issue. This way, we can work with the same report and with your data. It'll make it easier for me to see exactly what you're doing, too.

TheShadraq

So it looks like what is happening is that when I ask for multiple records that return 1,1,0 respectively, the code will hide the section for all records...simply because one == 0. 
 
I added a second entry in the onCreate, and onRender sections to check the record number as well, and only hide if the record number is the same. 
 
Red: OnRender 
<pre class='_prettyXprint _lang-auto _linenums:0'>if(row["wonum"] == reportContext.getPersistentGlobalVariable("toolrecord"))
{
if(reportContext.getPersistentGlobalVariable("hidetoolsection") == 0){
this.getStyle().display = "NONE";
}
}</pre>
 
Blue: onCreate 
<pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.setPersistentGlobalVariable("hidetoolsection",row["hastool"]);

reportContext.setPersistentGlobalVariable("toolrecord",row._outer["wonum"]);</pre>
 
So it now checks to ensure that the record it is hiding is in fact the record it is looking at. Seems a bit unnecessary, but it works great now. I'd welcome any thoughts you have on this.

mwilliams

If I'm understanding correctly, you're getting back into what I was talking about earlier, with naming the pgv by a field in the table. In your case, row["wonum"]. So, if in your onCreate, you did something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
reportContext.setPersistentGlobalVariable(row._outer["wonum"],row["hastool"]);
</pre>
 
and in your onRender did this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if(reportContext.getPersistentGlobalVariable(row["wonum"]) == 0){
this.getStyle().display = "NONE";
}
</pre>
 
You wouldn't need the extra variable and check.

TheShadraq

Looks like that code makes more sense than mine. I tried it. But I'm still getting the issue where the report, when ran in more records than just 1, has the same problem.

It is almost as if the params get overwritten by whatever happens to be called last.

If record 1 and record 2 have a result =1, but result 3 = 0, then in all 3 records, the section will not display.

mwilliams

Can you set up a report with flat files of data that represent what the data looks like in your dataSets? This way I can see the report and test on it? Or if you can recreate the issue with the sample database, that'd work, too.