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; } } }

One table, multiple datasets

DMUK

Hi,

I am using Eclipse and am trying to output an invoice. ordinarily this would be no problem, but what I am trying to do is pull in a single row via a query, but then a set of multiple rows to represent the invoice line items.

In a single table I seem prohibited form doing this, and I'd rather avoid using 3 tables to achieve it (single row header, single row footer and multiple row middle as the columns seem to jump around a lot and the invoice is fairly complex.

Any ideas out there?

Thanks

Find more posts tagged with

Comments

mwilliams

Hi DMUK,

If you have a common key to join on, you should be able to use a joint dataSet to use in the table.

DMUK

<blockquote class='ipsBlockquote' data-author="mwilliams">Hi DMUK, 
 
If you have a common key to join on, you should be able to use a joint dataSet to use in the table.</blockquote>
 
Thanks for the response. I thought about a join, but would that work when I want to join columns multiple times from multiple rows in the database. 
 
If I joined on a common key (invoice id) how would multiple rows (the line items) come back and be represented in a single row from the DB? 
 
I have only done simple joins before, so it may be possible. 
 
Thanks, 
 
DMUK

mwilliams

DMUK,

As long as there's a key and you pick the correct kind of join, it should show all records. Say you had two dataSets, invoice information and invoice detail. Detail has multiple lines associated with an individual invoice line from invoice information. The invoice information row would be repeated for each line in the invoice detail dataSet that was associated with that invoice number(key). Let me know if you have any issues with the join.

bhanley

If you have two tables, products and orders, the join is one to many. In a one-to-many join, your singular items (Like Product Line) would appear on each row of the joined data set; the value repeated as necessary.

There are "right", "left", "inner" and "outer" joins. You would need to look at each and determine which type gives you the result you are looking for in your specific case. BIRT can support any and all of them.

DMUK

Thanks for both of your replies. 
 
I think I could handle the join okay, in fact even the single invoice row is a series of about 5 left joins. 
 
My problem is that if I have, say 4 rows representing one invoice, and the invoice ID and other constant data repeated in each of those rows, then wouldn't BIRT, by default, try and loop through each of the four rows in the table and print out the invoice ID, the customers name ect, four times? 
 
I would want the table to do: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>

Name
Address

Item 1
Item 2
Item 3
Item 4

Total amount due

</pre>
 
Not: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>

Name
Name
Name
Name
Address
Address
Address
Address

Item 1
Item 2
Item 3
Item 4

Total amount due
Total amount due
Total amount due
Total amount due

</pre>
 
Does that make sense? I have a feeling I have a fairly 2D view of how BIRT can work, and that the desired outcome above is absolutely possible, but all the examples I have used to date (tutorials) loop through multiple rows or have multiple tables to achieve what I want. 
 
I did try the latter option, but as feared the tables all come out with differing widths and column widths no matter how much I try and constrain it. 
 
As an aside, I am really struggle to get Korean text to wrap, regardless of whether I use a label, text etc. In Eclipse, the text wraps, but when output to PDF, it doesn't wrap at all. 
 
Thanks, 
 
DMUK

mwilliams

DMUK,

Once you had all the data in your dataSet with the join to be able to put in a table and you set up up like the following:

header:
detail:Name
detail:Address
detail:Item
footer

You'd end up with this:

Name
Address
Item1
Name
Address
Item2
...
etc.

If you grouped your table on Name like the following setup:

header:
group(Name)header:Name
group(Name)header:Address
detail:Item
goup(Name)footer:
footer:

You'd get the following output:

Name1
Address1
Item1
Item2
Item3

Name2
Address2
Item4
Item5
Item6
...
etc.

Hope this helps.

DMUK

That makes sense theoretically, but where is the grouping applied? In the query, in the expression for the relevant table cell or is there somewhere else I can do it in Eclipse.

Thanks for your patience - I have already crammed more learning into this one report via these forums than I have in the previous 10 reports I designed.

mwilliams

DMUK,

If you right click on the detail row tab (to the left of the table with the table selected) of the table you have dropped into the report design and choose insert group, you'd group on "name" there. Or, you could just select the table and go to the gouping tab in the property editor and add the group there. This will add a group header and footer for name that you will use to get your expected output. To add another header row like I have shown in the example above, you'd select the table, then right click on the group header tab and choose insert -> row -> below.

Let me know if you have any questions on this.

DMUK

Thanks MWilliams, I'll give it a go.

I had done some snooping and found that Group function in Eclipse, but from the reading I did it sounds as though it would Group rows on fields in the same way sorting does. I think I probably misunderstood.

DMUK

Okay I have tried this and I think I am missing a fundamental concept

When I click on the detail tab, and add a group, it just adds another data cell above the detail row, which duplicates the value. If I add more groups, it adds another row.

I have attached a screenshot, and also a crude output of what the output will look like in Excel (the actual RPT is in Korean).

Be grateful if you could tell me what I am doing wrong, don't be afraid to dumb it down

Thanks

UPDATE

Been playing around a bit more and found another example of an invoice which seems to do what I am looking for. Problem is that applying it doesn;t seem to work.

If I select the detail row which I want to loop through (the line items) and hit insert group, the grouping bounds default to the top and bottom of the entire table, which is not what I want to do. RPT attached. Row that is supposed to loop is the 3rd row from the bottom. I don't seem to have a bonafide "header" and "footer" row already which may be the problem.

mwilliams

DMUK,

The grouping bounds to the top and bottom of that group only. Only the detail lines associated with that group will be in that section, then, the group footer happens and another "group table" is started. So, if you're doing a bunch of invoices, you could have an outer group of "country" or whatever you want them ordered by, then a group by, say, "city", then have a group on "customer". This would group all your customers by the same country and city together. All of the data you include in the detail would be repeated as individual reports for each customer. I hope this explains grouping a little better. Let me know if you still have questions.

DMUK

Thanks Michael, 
 
In this case, only a single invoice will be generated at a time. An invoice ID is being passed into the BIRT report by the parent app, and the query is constructed so only a single invoice is returned. 
 
Problem being that multiple detail rows for each single invoice are present. So I am not sure that the group feature as I understand it anyway, will help in this case? 
 
Or I could be doing something fundamentally wrong, but whatever I try it will not produce the desired output: 
 
Dataset (all columns but Line Item 1 have identical field content): 
 
First | Last | Address | Invoice Total | Tax Amount | Payment Terms | Line Item 1 
First | Last | Address | Invoice Total | Tax Amount | Payment Terms | Line Item 2 
First | Last | Address | Invoice Total | Tax Amount | Payment Terms | Line Item 3 
First | Last | Address | Invoice Total | Tax Amount | Payment Terms | Line Item 4 
 
Invoice 
 
First: John. Address: 111 Any Street 
Last: Doe . Total: £1,000. Tax Amount £150 
Line Item 1 
Line Item 2 
Line Item 3 
Line Item 4 
Payment terms: 30 days. 
 
So details I only want written once, appear over more than just a single row, and then the unique items (line items) need to repeat. Then it is likely to need more standard terms. 
 
Not sure if it is me not understanding, not explaining it well enough, or if it is just not possible. 
 
This is the example I found, but it does not seem to be constructred the same way I have it - <a class='bbc_url' href='http://www.eclipse.org/birt/examples/solution/SalesInvoice.html'>http://www.eclipse.org/birt/examples/solution/SalesInvoice.html</a> / <a class='bbc_url' href='http://www.eclipse.org/birt/examples/solution/SalesInvoice.rptdesign'>http://www.eclipse.org/birt/examples/solution/SalesInvoice.rptdesign</a> 
 
Thanks

mwilliams

DMUK,

What version of BIRT are you using? I'll make a quick example with your data.

DMUK

<blockquote class='ipsBlockquote' data-author="mwilliams">DMUK, 
 
What version of BIRT are you using? I'll make a quick example with your data.</blockquote>
 
Thanks, I really appreciate the help - 2.3.1 is the version I am working with in Eclipse.

mwilliams

DMUK,

Take a look at this report. Let me know if you have questions. Be sure to change the location of the flat file in the dataSource to where you save this text file.

DMUK

<blockquote class='ipsBlockquote' data-author="mwilliams">DMUK, 
 
Take a look at this report. Let me know if you have questions. Be sure to change the location of the flat file in the dataSource to where you save this text file.</blockquote>
 
Wow, that's great.

It makes perfect sense in the flesh. Any row containing constant data across multiple rows are grouped and you have grouped on First + Last as a unique key in each case. 
 
Is this something I am only going to be able to do if I start my table from scratch and build with these practices in mind? If I could monopolise you one further time, if you open the rpt attached to my earlier post, it's a bit of a merge fest in order to get the design I need. Any attempts to shoehorn in the grouping required doesn't seem to work. I am wondering if the table is too complex for this to work? 
 
Thanks again, 
 
DMUK

mwilliams

DMUK,

Most of the cells in the report you posted are just squares for me. I'm guessing the korean font you're using, I don't have. Without knowing what you want just shown once for each customer and what you need to repeat. If you're just bringing in one invoice, you won't even need to do the grouping that I did in that other report. That type of setup would be needed only if you're bringing in multiple invoices for one or more companies. If you're just bringing in the one invoice, as I see you have a parameter for invoice number, you can just use the standard table header for the non-"line items" of the invoice (the things you don't want repeated or listed - only shown once). Then, you'd put the "line items" in the detail row so that the table will step through the different rows in your dataSet and display the items you need to see from each row. Then, in the table footer, you'd put the things that you only want to see once below the rows of data. Hopefully this helps!

If you're bringing in multiple invoices by a single customer or from multiple customers, you'd use the grouping in the appropriate way to group them how you want.

Let me know if you have any questions.

DMUK

<blockquote class='ipsBlockquote' data-author="mwilliams">DMUK, 
 
Most of the cells in the report you posted are just squares for me. I'm guessing the korean font you're using, I don't have. Without knowing what you want just shown once for each customer and what you need to repeat. If you're just bringing in one invoice, you won't even need to do the grouping that I did in that other report. That type of setup would be needed only if you're bringing in multiple invoices for one or more companies. If you're just bringing in the one invoice, as I see you have a parameter for invoice number, you can just use the standard table header for the non-"line items" of the invoice (the things you don't want repeated or listed - only shown once). Then, you'd put the "line items" in the detail row so that the table will step through the different rows in your dataSet and display the items you need to see from each row. Then, in the table footer, you'd put the things that you only want to see once below the rows of data. Hopefully this helps!

If you're bringing in multiple invoices by a single customer or from multiple customers, you'd use the grouping in the appropriate way to group them how you want. 
 
Let me know if you have any questions.</blockquote>
 
Oh - okay, in that case I apologise for leading you on a bit of goose chase with the grouping solution when it was not required. I really appreciate all your very patient help - it's been very educational and I am sure the grouping knowledge will come in handy for me later or someone else reading this thread. 
 
Eclipse doesn't seem to want to let me create multiple header rows, so am I right in guessing that I need to created a grid within the header row? 
 
I also don't think I will get away with being able to simply drag the existing detail row arrangement in as Eclipse doesn't seem to want to let me copy it...

mwilliams

DMUK,

BIRT should allow you to use multiple header rows. In the designer, all you'd do is right click on the existing header row and choose insert row below. If for some reason you can't get this to work, using a grid inside the header would work for your purposes too, I would think.

DMUK

<blockquote class='ipsBlockquote' data-author="mwilliams">DMUK, 
 
BIRT should allow you to use multiple header rows. In the designer, all you'd do is right click on the existing header row and choose insert row below. If for some reason you can't get this to work, using a grid inside the header would work for your purposes too, I would think.

</blockquote>
 
Perfect. 
 
Anyway to get cell structure and cell content from detail rows into the header for a perfect score? ;-)

mwilliams

DMUK,

On the existing table, if you right click on a detail row tab and go to insert, you should be able to add the header and footer back to the table. Then, you'll be able to keep the structure of the detail rows. You'll have to redo the structure for the detail rows that you want to turn into header and footer rows and drag the items from the detail row into your newly structured header or footer row. I don't think there's any way around that.

DMUK

<blockquote class='ipsBlockquote' data-author="mwilliams">DMUK, 
 
On the existing table, if you right click on a detail row tab and go to insert, you should be able to add the header and footer back to the table. Then, you'll be able to keep the structure of the detail rows. You'll have to redo the structure for the detail rows that you want to turn into header and footer rows and drag the items from the detail row into your newly structured header or footer row. I don't think there's any way around that.</blockquote>
 
Yep - (after upgrading to Eclipse 3.5 and Birt 2.5.0...) I rebuilt the table, and everything works as it should. 
 
I am finding out that my computed columns have a habit of renaming themselves to things like ORIGINAL_NAME_26 or ORIGINAL_NAME_26_27_26 which breaks the dataset links. it's almost as if they are finding themselves already in the result dataset and renaming themselves something unique, but that's another matter. 
 
Thanks for all your help!

mwilliams

DMUK,

I have not heard of that happening before. Sounds like a good topic for a new thread!