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

generate birt report with excel 'group' function

XFabien

Hello,

When we generate the birt report in excel format, is it possible to add something that we can get the result report like the following :

Find more posts tagged with

Comments

ashish13

<blockquote class='ipsBlockquote' data-author="'XFabien'" data-cid="99241" data-time="1334756738" data-date="18 April 2012 - 06:45 AM">
Hello, 
 
When we generate the birt report in excel format, is it possible to add something that we can get the result report like the following :

XFabien

Thank you Ashish.

But it can only work with 3.7 or later.
My birt version is 2.6.2 and not going to upgrade...

Yaytay

<blockquote class='ipsBlockquote' data-author="'XFabien'" data-cid="99251" data-time="1334762864" data-date="18 April 2012 - 08:27 AM">
Thank you Ashish. 
 
But it can only work with 3.7 or later. 
My birt version is 2.6.2 and not going to upgrade... </blockquote>
How urgent is your need? 
 
If you hassle me on <a class='bbc_url' href='https://bitbucket.org/yaytay/spudsoft-birt-excel-emitters/issue/11/emitters-only-work-in-birt-37'>BitBucket</a> I'll see about getting my emitters working properly with earlier versions. 
Last time I tried it the emitters worked correctly, I just couldn't work out a decent way of testing them on 2.6 whilst developing with 3.7. 
 
Jim

XFabien

Hi Jim,

I posted my question on the BitBucket, so you can check the problem out.

Thank you

Yaytay

<blockquote class='ipsBlockquote' data-author="'XFabien'" data-cid="99291" data-time="1334821373" data-date="19 April 2012 - 12:42 AM">
I posted my question on the BitBucket, so you can check the problem out. </blockquote>
Thanks. 
I've run that report, but I'm responding here 'cos the image of what I think you want is here. 
 
I think the problem is that Excel groups don't work with a header row (which is what you've got), they only work with a footer row. 
So in your image the rows 2,3,4,5,6 & 7 all belong to Austria, not Australia. 
Where this would matter, from Excel's point of view, is where you ask it to perform the subtotals - Austria would say 6246 and Australia would be blank. 
Given that all your data is static this wouldn't actually affect you, but I can't change the grouping so it works the wrong way. 
 
Or, I may have misunderstood the problem completely

Let me know. 
 
Jim

XFabien

Hi Jim,

The image that I posted here is generated by the birt spreadsheet designer. So it works exactly the same as in excel.

If I undertood well, when we create the group in excel, we just need to select all the detail's lines except for the header of the group.

So I think it's difficult to change the grouping logic in your emitter or maybe I can work it around in the other way?

Yao

Yaytay

<blockquote class='ipsBlockquote' data-author="'XFabien'" data-cid="99301" data-time="1334829307" data-date="19 April 2012 - 02:55 AM">
Hi Jim, 
 
The image that I posted here is generated by the birt spreadsheet designer. So it works exactly the same as in excel. 
 
If I undertood well, when we create the group in excel, we just need to select all the detail's lines except for the header of the group. 
 
So I think it's difficult to change the grouping logic in your emitter or maybe I can work it around in the other way? 
 
Yao </blockquote>
 
Ah! 
There is a sheet level option in Excel (and POI) that controls whether groups have headers or footers. 
 
So, the output from birt spreadsheet designer is wrong*, but I can do it right

The result will be:

XFabien

Yeah, that is exactly the result that I want to see.

I appreciate very much your help. ^_^

Yao

XFabien

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="99303" data-time="1334830531" data-date="19 April 2012 - 03:15 AM">
Ah! 
There is a sheet level option in Excel (and POI) that controls whether groups have headers or footers. 
 
So, the output from birt spreadsheet designer is wrong*, but I can do it right

The result will be:

Yaytay

<blockquote class='ipsBlockquote' data-author="'XFabien'" data-cid="99405" data-time="1334859768" data-date="19 April 2012 - 11:22 AM">
Exceuse me Jim, 
 
Is it possible for you to tell me how to modify the emitter so that it can work like your post above or you are going to send me your modification? 
I am sorry that I need it to generate a lot of report with a little urgence. 
 
Thank you very much. 
 
Yao </blockquote>
 
The image I posted was me just seeing what Excel could do

However I have now changed the emitters so they support doing this - just set the RenderOption called "ExcelEmitter.GroupSummaryHeader" to TRUE and your groups will have a summary header instead of a footer. 
 
You need v0.8.0 of the emitters, which I have just uploaded to BitBucket. 
 
I also tweaked the manifest so it might work in BIRT 2.6 right away - please let me know if it does. 
 
Jim

XFabien

Thanks Jim.
I'll try it tomorrow.

Yao

XFabien

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="99419" data-time="1334867350" data-date="19 April 2012 - 01:29 PM">
The image I posted was me just seeing what Excel could do

Yaytay

<blockquote class='ipsBlockquote' data-author="'XFabien'" data-cid="99438" data-time="1334910292" data-date="20 April 2012 - 01:24 AM">
You see that if I have 2 groups' level and one detail. The emitter works well just like the result above for the group 'G1'. But for the group 'G2', it has only 1 group level and one detail, in the case the emitter doesn't work well. 
The left image is the result that I need, the right one is what the emitter does. </blockquote>
 
What's the value for the sub group binding for the sub group of G2? 
When you output to a different format do you get a blank entry (rather than no entry) for the sub group header? 
 
Try also setting the RenderOption "ExcelEmitter.RemoveBlankRows" to FALSE. 
 
There is definitely something wrong there, but I can't quite work out what it is yet

Jim

XFabien

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="99446" data-time="1334916175" data-date="20 April 2012 - 03:02 AM">
What's the value for the sub group binding for the sub group of G2? 
When you output to a different format do you get a blank entry (rather than no entry) for the sub group header? 
 
Try also setting the RenderOption "ExcelEmitter.RemoveBlankRows" to FALSE. 
 
There is definitely something wrong there, but I can't quite work out what it is yet

Jim </blockquote>
 
Actually, for the sub group of G2, there is a blank entry, so I hided it with the visibility expression. 
 
 
Yao

Yaytay

Yaytay

Attached here are three Excel spreadsheets that demonstrate the problem. 
 
<ul class='bbc'><li>Issue55GroupHierarchy0.xlsx has headers and has summary row above.</li><li>Issue55GroupHierarchyBelow0.xlsx has footers and groups has summary row below.</li><li>Issue55GroupHierarchy1.xlsx has headers and summary row below, and comes out badly.</li></ul>

XFabien

Thanks Jim ^_^

I'll replace the SubG2 with the same data as the G2, so that it will not look so weird.

I have tried to deploy to my sever, but I always the same error that it told me it could not find the EmitterID <the ID I took from the plugin.xml file>. I'll try again next week, hope I can figure it out.

Thanks again for the help.

Yao

XFabien

Hi Jim,

After having deployed the emitter to my server, I launched the report, but it returned the error message to me :
org.eclipse.birt.report.engine.api.EngineException: EmitterID uk.co.spudsoft.birt.emitters.excel.XlsEmitter for render option is invalid

I have added all the jars to the lib folder then it did not work always.

Do you have any ideas for this? Or the EmitterID is "uk.co.spudsoft.birt.emitters.excel" but not "uk.co.spudsoft.birt.emitters.excel.XlsEmitter" ?

Thanks
Yao

Yaytay

<blockquote class='ipsBlockquote' data-author="'XFabien'" data-cid="99540" data-time="1335192688" data-date="23 April 2012 - 07:51 AM">
Hi Jim, 
 
After having deployed the emitter to my server, I launched the report, but it returned the error message to me : 
org.eclipse.birt.report.engine.api.EngineException: EmitterID uk.co.spudsoft.birt.emitters.excel.XlsEmitter for render option is invalid 
 
I have added all the jars to the lib folder then it did not work always. 
 
Do you have any ideas for this? Or the EmitterID is "uk.co.spudsoft.birt.emitters.excel" but not "uk.co.spudsoft.birt.emitters.excel.XlsEmitter" ? 
 
 
Thanks 
Yao </blockquote>
 
The emitter ID is "uk.co.spudsoft.birt.emitters.excel.XlsEmitter", the normal reason for getting that error is that something is wrong with the installation. 
 
As a thought, if you've just changed to a 0.8.0 build have you updated the POI libraries? 
 
Jim

XFabien

<blockquote class='ipsBlockquote' data-author="'Yaytay'" data-cid="99541" data-time="1335192944" data-date="23 April 2012 - 07:55 AM">
The emitter ID is "uk.co.spudsoft.birt.emitters.excel.XlsEmitter", the normal reason for getting that error is that something is wrong with the installation. 
 
As a thought, if you've just changed to a 0.8.0 build have you updated the POI libraries? 
 
Jim </blockquote>
 
 
It works well with the genReport.bat after having add the emitter in the plugin folder and the jars to the lib folder. 
 
The POI libraries will be the three jars in the lib folder? 
 
 
Yao

Yaytay

<blockquote class='ipsBlockquote' data-author="'XFabien'" data-cid="99544" data-time="1335196241" data-date="23 April 2012 - 08:50 AM">
It works well with the genReport.bat after having add the emitter in the plugin folder and the jars to the lib folder. 
 
The POI libraries will be the three jars in the lib folder? 
 
Yao </blockquote>
Yes, the full set it needs are: 
<ul class='bbc'><li>commons-codec-1.5.jar</li><li>dom4j-1.6.1.jar</li><li>poi-3.8-20120326.jar</li><li>poi-ooxml-3.8-20120326.jar</li><li>poi-ooxml-schemas-3.8-20120326.jar</li><li>slf4j-api-1.6.2.jar</li><li>stax-api-1.0.1.jar</li><li>xmlbeans-2.3.0.jar</li></ul>
 
Jim

XFabien

hi Jim,

I found the where does the problem come from. It's because my app server uses java5, but your emitter is developed with java6.

So I think I am very unlucky that I can't use your emitter.

Yao