[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 :

 Image 1.jpg (170.31K)
Number of downloads: 3


I mean with the excel 'group' function.
I knew that the birt e.spreadsheet designer can do that, but my problem is that I have kind a lot of this report to generate, so I need the automation with the birt runtime engine.

Thank you very much for your help.

[ashish13]

XFabien, on 18 April 2012 - 05:45 AM, said:

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 :

Image 1.jpg


I mean with the excel 'group' function.
I knew that the birt e.spreadsheet designer can do that, but my problem is that I have kind a lot of this report to generate, so I need the automation with the birt runtime engine.

Thank you very much for your help.

Hi,

You can get this feature using spudsoft excel emitter.

Regards,
Ashish

[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]

XFabien, on 18 April 2012 - 08:27 AM, said:

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...

How urgent is your need?

If you hassle me on BitBucket 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]

XFabien, on 19 April 2012 - 12:42 AM, said:

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

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]

XFabien, on 19 April 2012 - 02:55 AM, said:

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

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:
 GroupWithHeader.png (12.08K)
Number of downloads: 8

I'll need to add an option to let you choose where the group summaries go.

Jim

* Disclaimer: I don't use birt spreadsheet designer, I have no reason to believe it can't do it right, but the image in the first post is wrong.

[XFabien]

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

I appreciate very much your help.


Yao

[XFabien]

Yaytay, on 19 April 2012 - 03:15 AM, said:

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:
GroupWithHeader.png

I'll need to add an option to let you choose where the group summaries go.

Jim

* Disclaimer: I don't use birt spreadsheet designer, I have no reason to believe it can't do it right, but the image in the first post is wrong.

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

[Yaytay]

XFabien, on 19 April 2012 - 11:22 AM, said:

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

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]

Yaytay, on 19 April 2012 - 01:29 PM, said:

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

Hi Jim,

There is still a little problem with your emitter.

 Image 1.jpg (95.64K)
Number of downloads: 2


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.

But I found that it worked well for the group footer before.

So, I think that there will be a little correction.


Yao

[Yaytay]

XFabien, on 20 April 2012 - 01:24 AM, said:

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.

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]

Yaytay, on 20 April 2012 - 03:02 AM, said:

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

Actually, for the sub group of G2, there is a blank entry, so I hided it with the visibility expression.


Yao

[Yaytay]

XFabien, on 20 April 2012 - 01:24 AM, said:

Image 1.jpg

Right, trying to make sense of this

Excel groups with a summary row above or below the data rows.
Each group has to have a distinct summary row - if you have two groups that try to end with the same summary row Excel represents them like the image on the right (you can still hide with the controls on the top of the grouping column, but not with the individual expand/collapse controls).

In order to make your image on the left I think you only did one level of grouping - but the emitter has to do what BIRT tells it to, which is two levels of groupings.
If you try doing it with two levels in Excel you'll get what the emitter generates.

Which means that I think my emitter is behaving correctly (within the limitation of Excel).
You can do one of three things:

• Give each group level a header row and set ExcelEmitter.GroupSummaryHeader.
  
• Give each group level a footer row and don't set ExcelEmitter.GroupSummaryHeader.
  
• Accept that you are going to get something odd

In your image you've got headers, but you are grouping by the footer.

In other words, you can have this:
 HierarchicalGroups.png (6.68K)
Number of downloads: 1
But you can't have what you seem to want without it going odd.

Does that help at all?

Jim

[Yaytay]

Attached here are three Excel spreadsheets that demonstrate the problem.

• Issue55GroupHierarchy0.xlsx has headers and has summary row above.
  
• Issue55GroupHierarchyBelow0.xlsx has footers and groups has summary row below.
  
• Issue55GroupHierarchy1.xlsx has headers and summary row below, and comes out badly.

Attached File(s)

•  Issue55GroupHierarchy0.xlsx (8.42K)
  Number of downloads: 5
•  Issue55GroupHierarchy1.xlsx (8.4K)
  Number of downloads: 1
•  Issue55GroupHierarchyBelow0.xlsx (8.38K)
  Number of downloads: 1

[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]

XFabien, on 23 April 2012 - 07:51 AM, said:

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

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