Advanced Grouping

[codeshark]

 hi All,

i'm new to IReport. i need to do a report with 3 sections (Detail Bands). 1 of the sections should contain grouping (actually 3 group levels).

i started playing with tool and reading online and from what i see (please correct me if i'm wrong) i can add as many report groups as i want but i can only add detail bands inside of the innermost  group. there isn't an ability to add additional detail bands before or after the outermost group.

from what i read i guess this can be done using tables or/and subreports.

i had difficulty creating 3 group levels in a table. i saw something called column group but couldn't work with it. maybe it's possible but i don't know how and it seems much more complex than the report groups.

i started trying with subreports but decided to stop and write this post to see if i'm on the right track and hear from experienced people what's the best way to go about this scenario.

so please tell me what you think. if the solution is indeed tables or/and subreports i would really appreciate some jrxml samples or/and useful information. by the way my data source is a single xml file.

Thanks in Advance, 

Amit. 

[cbarlow3]

I use groups routinely, and my initial thought is that if you think you need detail bands outside of your innermost group, you're probably not thinking about groups the same way that JasperSoft is, or else you're using different terminology and maybe your needs would be met by the group headers and group footers.  If you're able to mock up an example of what you think you want your report to look like, I might be able to better imagine what you're attempting and come up with a solution.  For example:

Group Header: All these accounts are in Branch A

  Group Header:  All these accounts are Savings Accounts

    Group Header:  All these accounts are open

      Detail (repeatable):  Account number, description, person's name, current balance

    Group Footer:  subtotals for open accounts

    Group Header:  All these accounts are closed

      Detail (repeatable):  Account number, description, person's name, current balance ($0.00)

    Group Footer:  subtotals for closed accounts

  Group Footer:  subtotals for savings accounts (open and closed)

  Group Header:  All these accounts are Checking Accounts

  ....etc.

Group Footer:  Subotals for all accounts in Branch A

Group Header:  All these accounts are in Branch B

...etc.

When I use groups this way, I use all kinds of tricks to make the report easier to use:  I usually check the box for the group headers to repeat on every page, I usually put my column header labels at the bottom of my innermost group so it's right next to the detail data instead of leaving it in the column header band, where it only prints at the top of the page before any group headers.  I also make two overlapping fields in my group header that have the information that's driving the group (open vs. closed, savings, checking, etc., branch A, branch B, etc.), and I give them mutually exclusive Print When conditions.  The first version gets a condition like $V{BranchGroup_COUNT}==0 and I make the font bold and I create a hyperlink where the Anchor Name Expression is the filed that controls the group and the Bookmark level depends on what level of group it is...my innermost gets 1, the next one out I assign level 2, and the outermost one is level 3...these create SUPER useful PDF bookmarks if you commonly export your reports to PDF.  The other version of the field I set the condition to $V{BranchGroup_COUNT}>0, I leave it UNbold, I don't set a hyperlink, but I change the expression to be the field that controls the group followed  by " (continued)".  If I'm doing three groups like this, I might also take at least the outermost group and specify that there should be a page break every time the group changes.  Otherwise, I just rely on some blank space at the bottom of my group footer to help separate the groups.

And of course, you can print subtotals in your group footers as well.  Just these tricks alone make groups much more readable in my mind:  the top of every page tells you where you are and reminds you whether you're at the beginning of a group or continuing, the column headers are repeated at the innermost level right before the details, you get PDF bookmarks so you can expand the trees of these different levels and jump right to the section of the report you're interested in, etc.  As long as there's not a privacy issue, I frequently suggest now that where clients used to run separate reports for different branches, etc. that they just run a single report and use the PDF bookmarks to help each branch manager, etc. find the section they're interested in quickly.

Hope that helps, but as I say...please feel free to put a rough outline of what you're trying to accomplish so I can picture it better.

Carl

[codeshark]

hi Carl,

Thanks a lot for the useful information. didn't see these tips and tricks in the book i read.

so regarding the report i'm creating. it's a cell phone invoice report. i have a single invoice xml file.

i need to display 3 bands / sections. the first section is summary of all charges. i take these values from a certain invoice xml section / tag (let's call it section A). the second section is a list of aggregated charges. i take these values from a different invoice xml section / tag (let's call it section B). no need to do grouping here since the records in the invoice xml section i'm using are already aggregated. the third section is the details of the calls made (grouped first by the calling number, then by the charge type and then by the calls' destination zone / area). i take these values from a different invoice xml section / tag (let's call it section C).

so basically i have 3 seperate sections / bands. the first 2 have no grouping and the last has grouping, so i need the first 2 sections to be in details bands before the groups that i need for displaying the "calls made" section, and it seems that the ireport tool doesn't have this ability. at least without using tables or/and subreports. hope this is a better explanation.

if i can still do that without tables or/and subreports i'd like to know how. if not then maybe you can explain how to do it using tables or/and subreports. i perfer tables cause this way i will have / manage only a single jrxml file.

by the way is there some book or sites you can advise for learning ireport. esspecially with tricks and tips like you mentioned, or is the case that you learned these tricks by playing with the tool. 

Thanks,

Amit. 

[cbarlow3]

Well, a report with three different sections like that does suggest using a subdata set for each, either through a List or Table or through a SubReport.  I'm still using Professional version 3.7, so I have Lists, but not Tables, although I think the basic plumbing is identical.  I've tried to avoid using subreports for most things, although they can have more flexibility than Lists, just because (a) they can be slower, and (b) I think someday we'll start taking over some of the tasks that we use JasperReports Server for now, and although I've written some test Java code that can check a jrxml/jasper for what parameters are required and can call the report, pass the parameters, and also export to PDF and stream the result back to a screen or to a file, I'm not at all confident that I could get subreports to work as well.

One time I was tasked with a report that was really essentially SEVEN reports that they wanted in a single report and that had very similar views:  each detail was some category breakdown and a count and/or amount or something like that.  I TOTALLY cheated on that one and did most of the work in the SQL itself:  I essentially did a UNION ALL on seven different queries that sliced the information in the database slightly differently, and the main four fields I returned in every row were something like SECTION_SORT, SECTION_TITLE, ROW_LABEL, ROW_CONTENT.  SECTION_TITLE was hardcoded differently for each section of the query and was something like "Loans by Interest Rate" or "Loans by DueDate" or "Loans by Balance Range", etc.  I also hardcoded a different SECTION_SORT value for each section of the query to 0, 1, 2, etc. so I could specify the order of my results without having to be stuck with the order of my section titles.  ROW_LABEL was different for each section but was related to the title:  it would be an interest rate or duedate or balance range, etc., and ROW_CONTENT was the actual value for that row.  It was a little more complicated, but that was the general idea.  Then I made an outer query that sorted the contents of the UNION based on SECTION_SORT (I think the internal sections were already sorted by other criteria...can't remember how I retained that order within sections) and I made an iReport group that was also based on that SECTION_SORT (although the value I printed in my group header was obviously the SECTION_TITLE).

In your specific case, it sounds like maybe what you need is two List or Table components in your Title band to handle the first two parts of your report, and then do the third part of your report with the main query so you can take maximum advantage of the groupings.  You might even just try to get the third part of your report working first so you can save that version and put that task behind you.  Put static text in the Title band that says "contents of section 1 and 2 go here" and then just forget that for a while and concentrate on section 3.  When that looks just the way you want, then let's focus on a List, Table, or SubReport going into the Title band for each of the other two sections.

I learned most of what I know from trying out new things as I get requests for various reports or think of changes that would make them better or my life easier and from looking for ideas on this forum.  I also learned a lot while doing some editing reviews for Dave Jarvis (a contributer to this forum) for his book "Indispensable", and I learned from the iReport ultimate guide.  As for this forum, I easily learn as much by trying to come up with answers to problems I almost know the answer to as I have from reading answers that someone else puts out there.  As soon as you picture something a specific way, it's hard to shake that image, and you usually learn a lot of little details trying to accomplish that exact image, even if you later decide that some of those techniques are overkill (do I really NEED that field to be flashing on the screen???).

Carl

[codeshark]

 hi,

thanks again. so let me see i've got it. the report's query will be tag / section C (of the invoice xml) and i'll use this data in the report groups (with 1 detail band inside the innermost group) and the report's title will hold the other 2 sections.

let's say i'll create a dataset whose query will be tag / section B (of the invoice xml) and then have a table (that uses this dataset) in the title band. now what's not clear to me is how to display the first section (charges summary). the problem i have here is that a table isn't suitable for this section. i need more of a layout that i can place static texts and text fields on wherever i want. if the report's query was tag / section A (of the invoice xml) or even the root tag of the invoice xml i guess i could have done it but since the report's query will be tag C of the invoice xml i guess it will not work.. do you have an idea how to make it work ?

also i see i can't add detail bands to the report's title. is this a disadvantage or i can still present the 2 sections in the report's title nicely using static texts, text fields, tables, etc... that are placed on the report's title.

BR,

Amit.

 

[cbarlow3]

How about a new strategy...write all three as completely separate reports first.  Then write a master report that has NO detail records and instead just calls the three subreports in the Summary band.  It sounds like all three of your sections would benefit from the full capability of treating data as full-fledged detail, so I think that may be your best bet after all.  After you get each subreport working individually, you need to keep track of what the input parameters for each report are so that when you create the master report, you know  what know what parameters you'll have to pass those subparameters (which in this case would probably be based on master report input parameters rather than on any detail coming back from the database).

Carl

[codeshark]

hi,

sounds good. thanks a lot. i'll try the subreports solution. i have a few more questions:

1.  why put the subreports in the summary band and not just have 3 detail bands. each band will show a different subreport.

2.  eventually i'll run the report using java code and i wanted to know if having subreports can be a problem in such case or if i'll mention only the main report in the java code and assuming the subreports are in the same directory as the main report it will still work.

3. regarding grouping. i wanted to know if the displayed names of 2 groups can be in the same line. currently when i have 2 groups it looks like this:

national calls
        destination: america
               record 1
               record 2

international calls
         destination: america
                record 3    
                record 4

but i want it to look like this (and i can't seem to do it):

national calls. destination: america
             record 1
             record 2

international calls. destination: america
             record 3    
             record 4

so is it doable and if so how do i do it.

Thanks,
Amit.

[cbarlow3]

1. You normally only put something in a detail band if you want it to be repeated.  This is a common problem I see on this forum is someone trying to put a chart into a detail record, and suddenly they see hundreds of copies of the chart (with the data accumulating with each version).  If you make some sort of fake query (which you might need to do anyway) that will only return ONE result set row, then perhaps you could define a Detail1, Detail2, and Detail3 band and put your subreports there, but you wouldn't want to put subreports in the detail band if the report had a normal query, unless you legitimately want the subreport to run one time for each result row returned in the query (which is entirely possible, by the way but doesn't sound like it applies in your case).

2. Running the report from within a Java program instead of from within iReport or JasperServer is my main concern with subreports.  I've never done it, and I don't know for sure if it's possible or not.

3. Yes, you can absolutely put $F{CallType} and $F{Destination} together in the group header for your DestinationGroup and skip the group header for CallTypeGroup.  If you want combined headers and aren't interested in subtotals by CallType, you could even just define one group instead of two, where that group would be controlled by $F{CallType}+$F{Destination}, so that every time the combination of the two of them changed, you would get a new group.  Some of my earlier recommendations don't apply to this method:  you wouldn't have a two-level tree of PDF bookmarks, for example.

Carl

[codeshark]

hi,

thanks for 1 & 3. i tried 2 groups ln one line like you suggested and it works (didn't work before cause tried to put both in the CallTypeGroup). now regarding problem #2 do you think it would help if i'll open another post in this forum regarding this issue or will it be useless (seeing as you answer most questions).

i also have another question (sorry. i have more questions as i further unfold this task). i'm working with some other people that create the invoice xml file. i was able to present the "calls made" section (the one with 3 groups) well by having the following section / tag (called it section C earlier) in the invoice xml:

<cdr>

<call_type type="X">National</call_type>

<a_number type="X">123456</a_number>

<destination type="X">America</destination>

<duration name="duration" type="l">2</duration>

 <b_Number name="b_Number" type="X">123456</b_Number>

</cdr>

<cdr>

<call_type type="X">National</call_type>

<a_number type="X">123456</a_number>

<destination type="X">America</destination>

<duration name="duration" type="l">3</duration>

 <b_Number name="b_Number" type="X">111111</b_Number>

</cdr>

<cdr>

<call_type type="X">International</call_type>

<a_number type="X">123456</a_number>

<destination type="X">America</destination>

<duration name="duration" type="l">1</duration>

 <b_Number name="b_Number" type="X">44444444</b_Number>

</cdr>

<cdr>

<call_type type="X">International</call_type>

<a_number type="X">123456</a_number>

<destination type="X">America</destination>

<duration name="duration" type="l">4</duration>

 <b_Number name="b_Number" type="X">5555555555</b_Number>

</cdr>

the report query was this cdr tag so actually i had 4 records that i can easily create groups on. the problem is that for the people creating the invoice xml file it's easier to create the calls section that's in the xml file like this:

<a_number type="X" value="123456">

<call_type type="X" value="National">  

 <destination type="X" value="America">

   <cdr>

     <duration name="duration" type="l">2</duration>

     <b_Number name="b_Number" type="X">123456</b_Number>

   </cdr>

   <cdr>

     <duration name="duration" type="l">3</duration>

     <b_Number name="b_Number" type="X">111111</b_Number>

   </cdr>    

 </destination>

</call_type>

<call_type type="X" value="International">  

 <destination type="X" value="America">

   <cdr>

     <duration name="duration" type="l">1</duration>

     <b_Number name="b_Number" type="X">44444444</b_Number>

   </cdr>

   <cdr>

     <duration name="duration" type="l">4</duration>

     <b_Number name="b_Number" type="X">5555555555</b_Number>

   </cdr>    

 </destination>

</call_type>

</a_number>

but i can't seem to get the same result from this tag format. is there a way to get the same result from this second format or only from the first format. maybe by changing the second format a bit this can be done.

Thanks,

Amit.

 

[cbarlow3]

I would post another question with the specific subject "Using Java to call a report that has subreports" or something like that.  You should probably post it in this forum and maybe in the "JasperReports Library" forum as well, since I'm not sure the topic is limited to iReport.

As for the xml question, I'm very used to writing Java code that uses xml files as input, but not experienced in using xml as a datasource in iReport, so I can't help you there.  Sorry.

Good luck!

Carl

[codeshark]

 hi Carl,

i'm still working on the invoice report and have few more questions. mainy in grouping but also other small ones non related to grouping so decided to ask them in this post and not open another one. hopefully it's ok.

1. i've noticed that if the XML records (that return from the report's query) aren't ordered by the group's field the grouping doesn't work properly unless i specify sorting by the group's field in the report's query editor. maybe you can explain why this occurs. it looks to me like a bug. also is using sorting in the report's query editor the only way to get the grouping work correctly (assuming the xml records aren't sorted) ? also what about 3 groups like in my case. should i sort by the 3 groups' fields in the report's query editor for all grouping to be ok ?

2. i'm displaying in 1 line some static texts and text field boxes. something like this:

call type: <call type> destination: <destination>

the issue i have is that the <call type> field can have a really long or really short value while the text field box has a fixed width as far as i know (i only saw Stretch with Overflow option but the box 's height just grows on overflow). if the value is short there will be a big gap between this field and the destination label / static text and if it's very long it will stretch vertically if i check that checkbox (which is better than the value's right side getting trimmed but it's not ideal). so my question is what can be done or what do you do in such case.

3. i've noticed that when i have a text field box whose expression is a timestamp object (i.e. new java.sql.Timestamp(Long.valueOf($F{start_time})*1000) ) that the displayed date is the date resulted in the expression evaluation but after being adjusted according to the computer's time zone. i wanted to know if there's a way to not have that time zone adjustment.

Thanks,

Amit.

[cbarlow3]

Amit,

1. Yes, you would nearly always want to have your groups reflected in your ORDER BY clause.  If you have two levels of groups based on CALL_TYPE and DESTINATION (sorry, I can't remember your third group and whether it's at a higher or lower level than these two), then I would expect your ORDER BY to start with those two values in that order.  I think it's a common mistake in iReport to assume that the iReport groups reorganize the result rows...they do not.  The report just takes them in the same order the SQL query returns them, and then a new group total band is output and some system-defined variables re-initialized and a new group header band is output every time the value that controls the group changes.  It's conceivable that you could want to see data in one order (chronological, let's say) and still start a new group every time some other value changes, just to draw attention to it or because it doesn't change very often, or because there is some sort of correlation between time and this other value.  But in a vast majority of cases, the decision to create a group and the decision to ORDER BY are related decisions:  you can certainly do an ORDER BY without creating a matching group, but the other way around is rare.

2. If your data in the first field varies in length so much that you can't reserve it enough room without sometimes crowding your second label and field, you could put them all together...that is, create a single field that is "call type: "+$F{CALL_TYPE}+"   destination: "+$F{DESTINATION}.  If either of those fields was previously formatted as a date or money, it's a little bit harder, because you have to know how to do that formatting yourself:  "MyDate: " + new SimpleDateFormat("MM/dd/yyyy").format($F{MY_DATE}), for example.  Furthermore, if you want to still bold the labels even when they're combined with unbolded content, see my suggestions and example at this post: http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=90885&topid=90892

3. There are some settings regarding timezone, but they confused me, and I could never really get the effect I was after.  I'm lucky that most of my reports don't care about time, only date, and the date they use is one that is stored as a date in the database and that has nothing to do with the operating system time.  I feel for you on this one...the only time I tried dealing with this, it was a mess.

Carl

[codeshark]

 hi,

thanks for 1 & 2. regarding 1: ok i now understand why it works like that so i guess it's not a bug but maybe it would be nice to have an option / property for the group so that the groups are determined by the group's expression regardless of the order of records. afterall that's the expected result for most people / cases. regarding 2: yep it works. guess just didn't think outside the box. the styling information is also very useful. i'll create a seperate post for issue #3.

Amit. 

[codeshark]

 hi Carl,

i have another question. it's not directly about grouping but relates to some of the stuff you & i mentioned so i decided to ask it here and not open another post. i've created a subreport and added last page footer band since i need something displayed in the footer of the last page of that subreport. when i run the subreport directly all is ok but when i run it from the main report (when the subreport is in the main report's detail band) that footer text doesn't appear in the footer of the last page of the subreport but just after the summary band of the subreport (which can be in the middle of the page).

i know you recommended placing the subreports in the summary band of the main report but when i do that the first and second reports are displayed on the same page. actually having the subreports in the detail bands is ok. i didn't have any problems with it besides this last page footer issue.

please tell me what i'm doing wrong in the main report summary band that the subreports are displayed in the same page, and also whether you know of a solution to the last page footer issue i've mentioned.

Thanks,

Amit.

[martin.clarke]

I started to read the first answer and I KNEW this was you Carl : )

[adinugroho]

hai, i'm very new in ireport...

i have a question, if it should make a 3 grandtotal in the report so i must make group in sql or in  ireport??

thx all