Dear Colleagues,
In this blog post I want to share the customized AutoOpen.xls file that provides opportunity to export Query Manager reports from SAP Business One to Excel with the customized design. You can download the complete documentation and the customized excel workbook with an opened macros by the links below.
AutoOpen export is fired for an active form when you press the Excel icon in the tool bar. When the button is hit you are asked to specify the path to save a data file and B1 runs the special file called AutoOpen.XLS from your Excel path. This file implements Auto_Open() subroutine that is called when a file is opened.
The default AutoOpen.XLS checks the path to the saved file and just opens it. The algorithm schema is shown below.
The customized AutoOpen.XLS checks the path to the saved file and builds a report against the template if template name is provided and template exists. Otherwise it performs the default logic. The algorithm schema is shown below:
To create a template for your report you need to create an Excel template file. It might be any template file type: *.xlt, *.xlts, *.xltm etc. This file must be placed in the same directory as AutoOpen.XLS is. If this template is specified in the second column of the data file (first column is always row number: “#”) it will be instantiated (a new instance will be created, so it won’t affect the original template) and filled with data according to the special markup rules. These templates support multiple sheets but the data source is always the same data file for all sheets.
To create a template for your report you need to create an Excel template file. It might be any template file type: *.xlt, *.xlts, *.xltm etc. This file must be placed in the same directory as AutoOpen.XLS is. If this template is specified in the second column of the data file (first column is always row number: “#”) it will be instantiated (a new instance will be created, so it won’t affect the original template) and filled with data according to the special markup rules. These templates support multiple sheets but the data source is always the same data file for all sheets.
Templates support the following special words (all special words are case-insensitive):
Valid ConsolidationFunction values:
For better understanding of the functionality described above let’s have a look at 3 examples (1 workbook with 3 sheets). To try it yourself you need to install AutoOpen.XLS from the references section, copy the query text, execute it via query generator in any B1 company (this example is shown on the SBODEMOUS company), download the template from the references section and run Excel export.
Query text for SAP Business One version for SQL Server:
Query text for SAP Business One version for SAP HANA:
Template:
Result:
Template:
Result:
Template:
Result (I hope everyone likes cats ?):
This functionality might be very useful in case if you want to provide users a possibility to have well designed reports in Excel. Since Crystal Reports has problems with export to excel (especially if you need to have table borders in this reports) it might be a salvation when users disagree to have PDF or Word exported reports. Another advantage is that it’s really easy to create new and support existing reports. Everything you need to know is SQL to prepare the data selection statement and how to create an Excel sheet with formulas and special markup. Sometimes you can even describe how to change formatting for users so they can adapt report views for their needs.
Unfortunately, this approach works much slower then a standard one, so sometimes for really big reports it might be a problem. Also, you need to be sure that every user has access to the shared folder to use this functionality.
Complete documentation
Workbook with the customised macros
In this blog post I want to share the customized AutoOpen.xls file that provides opportunity to export Query Manager reports from SAP Business One to Excel with the customized design. You can download the complete documentation and the customized excel workbook with an opened macros by the links below.
Functionality overview
AutoOpen export is fired for an active form when you press the Excel icon in the tool bar. When the button is hit you are asked to specify the path to save a data file and B1 runs the special file called AutoOpen.XLS from your Excel path. This file implements Auto_Open() subroutine that is called when a file is opened.
The default AutoOpen.XLS checks the path to the saved file and just opens it. The algorithm schema is shown below.
The customized AutoOpen.XLS checks the path to the saved file and builds a report against the template if template name is provided and template exists. Otherwise it performs the default logic. The algorithm schema is shown below:
To create a template for your report you need to create an Excel template file. It might be any template file type: *.xlt, *.xlts, *.xltm etc. This file must be placed in the same directory as AutoOpen.XLS is. If this template is specified in the second column of the data file (first column is always row number: “#”) it will be instantiated (a new instance will be created, so it won’t affect the original template) and filled with data according to the special markup rules. These templates support multiple sheets but the data source is always the same data file for all sheets.
Report template preparation
To create a template for your report you need to create an Excel template file. It might be any template file type: *.xlt, *.xlts, *.xltm etc. This file must be placed in the same directory as AutoOpen.XLS is. If this template is specified in the second column of the data file (first column is always row number: “#”) it will be instantiated (a new instance will be created, so it won’t affect the original template) and filled with data according to the special markup rules. These templates support multiple sheets but the data source is always the same data file for all sheets.
Template special markup
Templates support the following special words (all special words are case-insensitive):
- <#ColumnName> - The main markup word. If you provide the column name surrounded by <# on the left-hand side and > on the right-hand side – this value will be replaced with the corresponding value from the data file. If this is located in the repeated area (described later) the value will be taken from the different rows, otherwise it will be always from the second row (the first row after the header).
- <RepeatStart> - the word that shows the beginning of the repeated area. Repeated area is the report table details. This markup word must be always placed in the first column. The next row after this word is reserved for column marking and always must be empty or with special words. After preparation the row with this word and the next one (empty or with special words as well) are removed.
- <PICTURE> - the word for repeated area column marking. This word must be placed in the next row after the <RepeatStart> row. If this word is specified for a column the values of that column will be replaced with pictures. Values must point to the corresponding pictures (either absolute path on the drive or URL).
- <GROUP|ConsolidationFunction> - the word for building subtotals. This word must always be used coupled with the word <MEASURE>. This word marks a column as a dimension for subtotals, when <MEASURE> marks columns as measures. Consolidation function is an aggregation function for measures.
Valid ConsolidationFunction values:
- AVG: <GROUP|AVG> – Average of values
- SUM: <GROUP|SUM> - Sum of values
- COUNT: <GROUP|COUNT> - Count of values
- COUNTN: <GROUP|COUNTN> - Count of numbers values
- DCOUNT: <GROUP|DCOUNT> - Count of distinct values
- MAX: <GROUP|MAX> - Max from values
- MIN: <GROUP|MIN> - Min from values
- PROD: <GROUP|PROD> - Product of values
- DEV: <GROUP|DEV> - Standard deviation of values
- DEVP: <GROUP|DEVP> - Standard deviation based on the whole population
- VAR: <GROUP|VAR> - Variation of values
- VARP: <GROUP|VARP> - Variation based on the whole population
- <MEASURE> - the word for building subtotals. If there is a <GROUP|*> word there always must be at least one <MEASURE> word.
- <RepeatEnd> - the word that shows the end of the repeated area. This markup word must always be placed in the first column. After preparation the row with this word is removed.
- <RepeatSummary> - the word that helps to apply standard excel formulas to the repeated are. This markup word must always be placed in the first column and in the next row after <RepeatEnd> word. In case you need to apply a formula (for example to calculate total of the repeater area in the footer) you must include all row from the <RepeatStart> row till the <RepeatSummary> row to this formula. Otherwise the result might be unexpected.
Report example
For better understanding of the functionality described above let’s have a look at 3 examples (1 workbook with 3 sheets). To try it yourself you need to install AutoOpen.XLS from the references section, copy the query text, execute it via query generator in any B1 company (this example is shown on the SBODEMOUS company), download the template from the references section and run Excel export.
Query text
Query text for SAP Business One version for SQL Server:
SELECT N'Example.xltm' AS N'TemplateName',
GETDATE() AS N'ReportDate',
OWHS.WhsName AS N'Warehouse',
OITM.ItemCode + ': ' + OITM.ItemName AS N'Item',
OITW.OnHand + OITW.OnOrder + OITW.IsCommited AS N'Available',
ISNULL(OITW.MinStock, OITM.MinLevel) AS N'MinStock',
ISNULL(OITW.MinStock, OITM.MinLevel) - (OITW.OnHand + OITW.OnOrder + OITW.IsCommited) AS N'Required',
'C:\Pictures\' + OITM.ItemCode + '.jpg' AS N'PicturePath'
FROM OITM
JOIN OITW ON OITM.ItemCode = OITW.ItemCode
JOIN OWHS ON OITW.WhsCode = OWHS.WhsCode
WHERE OITW.OnHand + OITW.OnOrder + OITW.IsCommited != 0
ORDER BY OWHS.WhsNameQuery text for SAP Business One version for SAP HANA:
SELECT 'Example.xltm' AS "TemplateName",
CURRENT_DATE AS "ReportDate",
OWHS."WhsName" AS "Warehouse",
OITM."ItemCode" || ': ' || OITM."ItemName" AS "Item",
OITW."OnHand" + OITW."OnOrder" + OITW."IsCommited" AS "Available",
IFNULL(OITW."MinStock", OITM."MinLevel") AS "MinStock",
IFNULL(OITW."MinStock", OITM."MinLevel") - (OITW."OnHand" + OITW."OnOrder" + OITW."IsCommited") AS "Required",
'C:\Pictures\' || OITM."ItemCode" || '.jpg' AS "PicturePath"
FROM OITM
JOIN OITW ON OITM."ItemCode" = OITW."ItemCode"
JOIN OWHS ON OITW."WhsCode" = OWHS."WhsCode"
WHERE OITW."OnHand" + OITW."OnOrder" + OITW."IsCommited" <> 0
ORDER BY OWHS."WhsName"Simple report
Template:
Result:
Report with subtotals
Template:
Result:
Report with pictures
Template:
Result (I hope everyone likes cats ?):
Functionality recap
This functionality might be very useful in case if you want to provide users a possibility to have well designed reports in Excel. Since Crystal Reports has problems with export to excel (especially if you need to have table borders in this reports) it might be a salvation when users disagree to have PDF or Word exported reports. Another advantage is that it’s really easy to create new and support existing reports. Everything you need to know is SQL to prepare the data selection statement and how to create an Excel sheet with formulas and special markup. Sometimes you can even describe how to change formatting for users so they can adapt report views for their needs.
Unfortunately, this approach works much slower then a standard one, so sometimes for really big reports it might be a problem. Also, you need to be sure that every user has access to the shared folder to use this functionality.
References
Complete documentation
Workbook with the customised macros
- SAP Managed Tags:
1 Comment
