It would be nice if ADempiere could generate reports based on a defined Print Format and an attached Excel template file. When we hit the report button or we run a report process, ADempiere should fill the data into a sheet from provided excel report and return to client the filled excel file.
What do you think ?
Best regards,
Teo Sarca
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
>It would be nice if ADempiere could generate reports based on a defined Print Format and an attached Excel template >file. When we hit the report button or we run a report process, ADempiere should fill the data into a sheet from >provided excel report and return to client the filled excel file.
Sounds nice.
Do you have idea how to do it?
Kind regards,
Trifon
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
A draft idea would be:
On Print Format window, add a new field "LayoutEngine" (I=Internal, E=Excel). Then attach the excel template. When the report will run, ADempiere will fill the sheet called "Data" (for example)...
Best regards,
Teo Sarca
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
>A draft idea would be:
>On Print Format window, add a new field "LayoutEngine" (I=Internal, E=Excel). Then attach the excel template. When the >report will run, ADempiere will fill the sheet called "Data" (for example)...
So far so good.
But do you have idea how to genarate report based on Spreadsheet template?
Kind regards,
Trifon
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
such a report is nowadays a standard in ERPs. Customers ask for it not as a "nice-to-have" but as a "must" functionality.
Thus, a report based on Excel is more than welcome. It would be a feature decision makers would pay attention to (more than maybe other more important things), because they mostly see an ERP as a report generating information system.
Best regards,
Mario Calderon
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Teo,
I agree very strongly with Mario, In many ways Excel and OpenOffice are just another type of reporting tool that end-users are far more familiar with than JasperReport/iReport etc (which which I suggest is far beyond what end-users are capable of managing for themselves even if they had the system access needed which they won't)
May I add another suggestion related to this functionality. It would be helpful if Print Formats also stored:
... a default location for the file to be written out to
... a default filetype
... a tickbox to say "use output defaults"
With the default location and file type pre-defined the report should be able to be scheduled for overnight running.
In addition to being able to format the data in a defined Excel layout stored in the Print Format it would also then be possible to generate a datafile which could be connected to by Excel or any third party reporting tool (with all IDs resolved and security working). It can be very useful to have a spreadsheet containing a number of worksheets where each worksheet has a different Excel Pivot Table or Open Office DataPilot report but all reporting on the same data set which is refreshed by opening the spreadsheet.
There is an interesting article on using Excel for reporting here: http://www.exceluser.com/bi/greatbi.htm
The last sentence is an advertisement, the rest is good sense.
It says...
(a) "In many companies, if a manager needs a new report she creates a detailed spec then finds a sponsor for the project. She submits a Work Center Request to the Work Center Prioritization Committee that meets every other Tuesday. This committee discusses the request and assigns a project manager who eventually develops a budget, assigns resources, and manages the project queue. Then, three months after the need has passed, she receives a report that only resembles what she needed."
Instead, in smaller companies, she adds the report to a long list of other reports that she must create manually each week or month in Excel.
(b) On the other hand, an agile system allows users -- not programmers -- to change reports in a few minutes. It allows users to create new reports in a few minutes or hours. And it allows users to add virtually any new data in a few hours or days. But most important, it allows users to update the reports in seconds."
(a) sounds to me like .. create an iReport, create a Jasper Report, log in as System Administrator if you are allowed to, create a Report and Process, add it to the Menu etc etc..
(b) sounds to me like something we are lacking.
I do not dispute the value of having Jasper as a reporting solution but I do not think it it is a reporting solution that is in the hands of end-users, it will always be an IT department tool not a user tool. Any thing that lets users do their jobs seems a good improvement.
regards..
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
> May I add another suggestion related to this functionality. It would be helpful if Print Formats also stored:
> ... a default location for the file to be written out to
> ... a default filetype
> ... a tickbox to say "use output defaults"
> With the default location and file type pre-defined the report should be able to be scheduled for overnight running.
Good ideas ! To rephrase, we need:
AD_PrintFormat.OutputDir - a default location for the file to be written out to
AD_PrintFormat.FileType - default file type
AD_PrintFormat.ViewerType={Internal-internal report viewer will be used, External-an external viewer will be used, based on FileType}
To go forward, I think we will need another table, called AD_PrintFormat_Excel where we can store specific settings like on which sheet the data will be saved.
What do you think ?
PS1: i am waiting ideas and comments from entire community ;)
PS2: @adaxa , thanx for exceluser link... very interesting.
Best regards,
Teo Sarca
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
> I work with JExcelApi (http://www.andykhan.com/jexcelapi/) for integrate spredsheet into adempiere. It is very
> practical and simple.
Do you have any experience with both framework (POI and JExcelAPI). As I've read, POI is more feature rich, much widely accepted but it consumes more memory.
Also POI HSSF is currently integrated in ADempiere (from jasper integration).
What do you think ?
Best regards,
Teo Sarca
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Is the format in the propriety Excel format or is it a CSV or similar format? If it isn't Excel format then I suggest that it is called spreadsheet or CSV or something more generic.
Regards
Peter
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I hope we are talking about a real .xls file format rather than a text format readable by a spreadsheet.
A reason for enabling export as a .XLS file (which basically all spreadsheets can read and write anyway) is the Microsoft bug described in the following article after the heading "Modify the Schema, If Necessary" http://www.exceluser.com/explore/msquery2_2.htm
Exporting the data as an XLS avoids the problems that some tools have when trying to guess the data type of the columns in the CSV file. In my view it is a very worthwhile extra.
regards..
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi all, not contributing too much with my opinion here, but I think we must not do "Excel" specific solutions.
MS Office is the opposite of the open source world - even their OOXML was just a farce (IMHO) to stop ODF.
And we as an open source project MUST NOT promote the usage of privative software.
I'm not talking that we must not support xls exporting files - I'm talking that proposed solution must be wider, and xls just one of the family of "exporters" - even if the rest of exporters must be implemented in future.
Being said that - we must not call tables or fields as "Excel" - but as "Spreadsheet".
And if we are thinking on supporting .xls - then we must think on a format field that in future must support also ODF. ODS, etc. when somebody have the need and/or the time.
Regards,
Carlos Ruiz
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
What about using the ODS (openoffice spreadsheet documents), .ods file is just a zip xml file, but I think the creation of the .ods file using the openoffice API is a lot slower that those of the MS excel. Just a thought...
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Based on Adaxa's comment then yes it should be named with Excel in the module names etc. Thanks Adaxa for pointing that out.
Ivan, I think it would be better to create Excel format as everyone can read that (Excel, OpenOffice, etc), whereas using ODS it will only be a small subset of spreadsheet users that can read.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Carlos,
Excel does not open native Open Office spreadsheet format (and other open source spreadsheets)
To my knowledge, all the open source spreadsheets will open XLS files.
If we only support text files then we hit the problems mentioned above.
Until that situation changes perhaps we should (also) create XLS files .. whether it is called "XLS" or something else is not so critical. IMHO "XLS" is a much better choice than "Excel".
regards.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
> Excel does not open native Open Office spreadsheet format (and other open source spreadsheets)
> To my knowledge, all the open source spreadsheets will open XLS files.
> If we only support text files then we hit the problems mentioned above.
> Until that situation changes perhaps we should (also) create XLS files ..
> whether it is called "XLS" or something else is not so critical.
> IMHO "XLS" is a much better choice than "Excel".
> regards.
Yes, please don't misunderstand me.
I'm not talking about not supporting XLS
I'm talking about a better naming convention for the things.
We must not call tables or columns "Excel" or "XLS" - the solution must be generic to support other formats in future, and XLS is just one of the implementations - sure xls will be the first implementation - and possibly the only one if nobody else contribute ODF or ODS, but at least we're thinking wider, and not promoting shamelessly a software that doesn't deserve to be promoted (IMHO).
Rephrasing - YES from my side for this idea of supporting excel templates - but the design must cover other possible implementations (the class) and excel is just one implementation of that (the object).
Regards,
Carlos Ruiz
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I confirm jExcelAPI for integrate spreadsheet into adempiere. It is ease of use :D
Also think it would be great if adempiere supports CHART reports
I'm looking a good API for charting except jFreeChart do you have any idea ?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
It would be nice if ADempiere could generate reports based on a defined Print Format and an attached Excel template file. When we hit the report button or we run a report process, ADempiere should fill the data into a sheet from provided excel report and return to client the filled excel file.
What do you think ?
Best regards,
Teo Sarca
Hi Teo,,
>It would be nice if ADempiere could generate reports based on a defined Print Format and an attached Excel template >file. When we hit the report button or we run a report process, ADempiere should fill the data into a sheet from >provided excel report and return to client the filled excel file.
Sounds nice.
Do you have idea how to do it?
Kind regards,
Trifon
A draft idea would be:
On Print Format window, add a new field "LayoutEngine" (I=Internal, E=Excel). Then attach the excel template. When the report will run, ADempiere will fill the sheet called "Data" (for example)...
Best regards,
Teo Sarca
Hi Teo,
>A draft idea would be:
>On Print Format window, add a new field "LayoutEngine" (I=Internal, E=Excel). Then attach the excel template. When the >report will run, ADempiere will fill the sheet called "Data" (for example)...
So far so good.
But do you have idea how to genarate report based on Spreadsheet template?
Kind regards,
Trifon
> But do you have idea how to genarate report based on Spreadsheet template?
POI library. It's already included in ADempiere and i've used to export excel from different sources.
Best regards,
Teo Sarca
Hi Teo!
Do you have a test case showing this functionality? Sounds very interesting.
Regards!
Hi,
> Do you have a test case showing this functionality? Sounds very interesting.
Not at all. I's just a draft idea... Wish to discuss/collaborate on this with which is interested.
Best regards,
Teo Sarca
Hi Teo,
such a report is nowadays a standard in ERPs. Customers ask for it not as a "nice-to-have" but as a "must" functionality.
Thus, a report based on Excel is more than welcome. It would be a feature decision makers would pay attention to (more than maybe other more important things), because they mostly see an ERP as a report generating information system.
Best regards,
Mario Calderon
Hi Teo,
I agree very strongly with Mario, In many ways Excel and OpenOffice are just another type of reporting tool that end-users are far more familiar with than JasperReport/iReport etc (which which I suggest is far beyond what end-users are capable of managing for themselves even if they had the system access needed which they won't)
May I add another suggestion related to this functionality. It would be helpful if Print Formats also stored:
... a default location for the file to be written out to
... a default filetype
... a tickbox to say "use output defaults"
With the default location and file type pre-defined the report should be able to be scheduled for overnight running.
In addition to being able to format the data in a defined Excel layout stored in the Print Format it would also then be possible to generate a datafile which could be connected to by Excel or any third party reporting tool (with all IDs resolved and security working). It can be very useful to have a spreadsheet containing a number of worksheets where each worksheet has a different Excel Pivot Table or Open Office DataPilot report but all reporting on the same data set which is refreshed by opening the spreadsheet.
There is an interesting article on using Excel for reporting here:
http://www.exceluser.com/bi/greatbi.htm
The last sentence is an advertisement, the rest is good sense.
It says...
(a) "In many companies, if a manager needs a new report she creates a detailed spec then finds a sponsor for the project. She submits a Work Center Request to the Work Center Prioritization Committee that meets every other Tuesday. This committee discusses the request and assigns a project manager who eventually develops a budget, assigns resources, and manages the project queue. Then, three months after the need has passed, she receives a report that only resembles what she needed."
Instead, in smaller companies, she adds the report to a long list of other reports that she must create manually each week or month in Excel.
(b) On the other hand, an agile system allows users -- not programmers -- to change reports in a few minutes. It allows users to create new reports in a few minutes or hours. And it allows users to add virtually any new data in a few hours or days. But most important, it allows users to update the reports in seconds."
(a) sounds to me like .. create an iReport, create a Jasper Report, log in as System Administrator if you are allowed to, create a Report and Process, add it to the Menu etc etc..
(b) sounds to me like something we are lacking.
I do not dispute the value of having Jasper as a reporting solution but I do not think it it is a reporting solution that is in the hands of end-users, it will always be an IT department tool not a user tool. Any thing that lets users do their jobs seems a good improvement.
regards..
Hi all,
> May I add another suggestion related to this functionality. It would be helpful if Print Formats also stored:
> ... a default location for the file to be written out to
> ... a default filetype
> ... a tickbox to say "use output defaults"
> With the default location and file type pre-defined the report should be able to be scheduled for overnight running.
Good ideas ! To rephrase, we need:
AD_PrintFormat.OutputDir - a default location for the file to be written out to
AD_PrintFormat.FileType - default file type
AD_PrintFormat.ViewerType={Internal-internal report viewer will be used, External-an external viewer will be used, based on FileType}
To go forward, I think we will need another table, called AD_PrintFormat_Excel where we can store specific settings like on which sheet the data will be saved.
What do you think ?
PS1: i am waiting ideas and comments from entire community ;)
PS2: @adaxa , thanx for exceluser link... very interesting.
Best regards,
Teo Sarca
I work with JExcelApi (http://www.andykhan.com/jexcelapi/) for integrate spredsheet into adempiere. It is very practical and simple.
Hi,
> I work with JExcelApi (http://www.andykhan.com/jexcelapi/) for integrate spredsheet into adempiere. It is very
> practical and simple.
Do you have any experience with both framework (POI and JExcelAPI). As I've read, POI is more feature rich, much widely accepted but it consumes more memory.
Also POI HSSF is currently integrated in ADempiere (from jasper integration).
What do you think ?
Best regards,
Teo Sarca
Is the format in the propriety Excel format or is it a CSV or similar format? If it isn't Excel format then I suggest that it is called spreadsheet or CSV or something more generic.
Regards
Peter
I hope we are talking about a real .xls file format rather than a text format readable by a spreadsheet.
A reason for enabling export as a .XLS file (which basically all spreadsheets can read and write anyway) is the Microsoft bug described in the following article after the heading "Modify the Schema, If Necessary"
http://www.exceluser.com/explore/msquery2_2.htm
Exporting the data as an XLS avoids the problems that some tools have when trying to guess the data type of the columns in the CSV file. In my view it is a very worthwhile extra.
regards..
> I hope we are talking about a real .xls file format rather than a text format readable by a spreadsheet.
Obviously yes.
Best regards,
Teo Sarca
Hi all, not contributing too much with my opinion here, but I think we must not do "Excel" specific solutions.
MS Office is the opposite of the open source world - even their OOXML was just a farce (IMHO) to stop ODF.
And we as an open source project MUST NOT promote the usage of privative software.
I'm not talking that we must not support xls exporting files - I'm talking that proposed solution must be wider, and xls just one of the family of "exporters" - even if the rest of exporters must be implemented in future.
Being said that - we must not call tables or fields as "Excel" - but as "Spreadsheet".
And if we are thinking on supporting .xls - then we must think on a format field that in future must support also ODF. ODS, etc. when somebody have the need and/or the time.
Regards,
Carlos Ruiz
My thoughts EXACTLY!
colin
Hi,
What about using the ODS (openoffice spreadsheet documents), .ods file is just a zip xml file, but I think the creation of the .ods file using the openoffice API is a lot slower that those of the MS excel. Just a thought...
Based on Adaxa's comment then yes it should be named with Excel in the module names etc. Thanks Adaxa for pointing that out.
Ivan, I think it would be better to create Excel format as everyone can read that (Excel, OpenOffice, etc), whereas using ODS it will only be a small subset of spreadsheet users that can read.
Hi Carlos,
Excel does not open native Open Office spreadsheet format (and other open source spreadsheets)
To my knowledge, all the open source spreadsheets will open XLS files.
If we only support text files then we hit the problems mentioned above.
Until that situation changes perhaps we should (also) create XLS files .. whether it is called "XLS" or something else is not so critical. IMHO "XLS" is a much better choice than "Excel".
regards.
Hi Adaxa,
> Excel does not open native Open Office spreadsheet format (and other open source spreadsheets)
> To my knowledge, all the open source spreadsheets will open XLS files.
> If we only support text files then we hit the problems mentioned above.
> Until that situation changes perhaps we should (also) create XLS files ..
> whether it is called "XLS" or something else is not so critical.
> IMHO "XLS" is a much better choice than "Excel".
> regards.
Yes, please don't misunderstand me.
I'm not talking about not supporting XLS
I'm talking about a better naming convention for the things.
We must not call tables or columns "Excel" or "XLS" - the solution must be generic to support other formats in future, and XLS is just one of the implementations - sure xls will be the first implementation - and possibly the only one if nobody else contribute ODF or ODS, but at least we're thinking wider, and not promoting shamelessly a software that doesn't deserve to be promoted (IMHO).
Rephrasing - YES from my side for this idea of supporting excel templates - but the design must cover other possible implementations (the class) and excel is just one implementation of that (the object).
Regards,
Carlos Ruiz
I agree with carlos. It is much better to go with Open document. For microsoft office they could download plugin to open Open document files http://arstechnica.com/journals/linux.ars/2007/07/07/sun-releases-odf-plugin-1-0-for-microsoft-office. or just install open office in there pc. If we are going to support xls its like going backwards and its should be called spreedsheet. It's just my 2 cents.
dellph
Hi,
I confirm jExcelAPI for integrate spreadsheet into adempiere. It is ease of use :D
Also think it would be great if adempiere supports CHART reports
I'm looking a good API for charting except jFreeChart do you have any idea ?
>I'm looking a good API for charting except jFreeChart do you have any idea ?
Why not just use jFreeChart ?
Regards,
Low
>>Why not just use jFreeChart ?
cause jFreeChart Documentation is not free and i don't know how use it