How can i generate Excel Reports in business process with App

Dear Juan Cruz, 



We recommend you to contact bpmonlinelabs@bpmonline.com on this particular request as they are developers of this marketplace application.



Thank you. 

Hi Juan, How are you?

It may not be the best way but we made it work as follows:

1.  Create a new source code
   1. Copy & paste IntExcelReportService
   2. Rename the class with our custom name
   3.  Add own code in the method we need to override in this case GetExportFilteredData

    var insert = new Insert(UserConnection).Into("AccountFile")

                .Set("Name", Column.Parameter(reportName))

                .Set("Data", Column.Parameter(buffer))

                .Set("Typeid", Column.Parameter("529bc2f8-0ee0-df11-971b-001d60e938c6"))

                .Set("Accountid", Column.Parameter("accountid");

            insert.Execute();

2.  Using RestSharp we made a process with a scrit task that atuhenticate in bpm and calls this new service in its 2 methods:

GetExportFiltersKey

GetExportFilteredData 

Regards

Hi Uriel,

We manage to develop the script task that stores the excel report in our custom object attachments detail, following the steps you describe above.

Since one of the params required to generate the report is the "EsqString" and this param changes everytime we update the excel report structure.

We would like to know how can we get the most updated EsqString from a specific report in order to use it in our script task?

Thanks in Advance.

Best Regards,

Pedro Pinheiro

Pedro Pinheiro,

Hi,

Here is an example of two EsqStrings from two test calls of the GetExportFiltersKey service for the same contact using the same report:

EsqString: "{\"rootSchemaName\":\"Contact\",\"operationType\":0,\"includeProcessExecutionData\":true,\"filters\":{\"items\":{\"a3cef0ef-1871-4dfe-ac72-f6f322b1be57\":{\"filterType\":1,\"comparisonType\":3,\"isEnabled\":true,\"trimDateTimeParameterToDate\":false,\"leftExpression\":{\"expressionType\":0,\"columnPath\":\"Id\"},\"rightExpression\":{\"expressionType\":2,\"parameter\":{\"dataValueType\":1,\"value\":\"c4ed336c-3e9b-40fe-8b82-5632476472b4\"}}}},\"logicalOperation\":0,\"isEnabled\":true,\"filterType\":6},\"columns\":{\"items\":{\"Full name\":{\"caption\":\"Full name\",\"orderDirection\":0,\"orderPosition\":-1,\"isVisible\":true,\"expression\":{\"expressionType\":0,\"columnPath\":\"Name\"}},\"Age\":{\"caption\":\"Age\",\"orderDirection\":0,\"orderPosition\":-1,\"isVisible\":true,\"expression\":{\"expressionType\":0,\"columnPath\":\"Age\"}},\"Email\":{\"caption\":\"Email\",\"orderDirection\":0,\"orderPosition\":-1,\"isVisible\":true,\"expression\":{\"expressionType\":0,\"columnPath\":\"Email\"}}}},\"isDistinct\":false,\"rowCount\":-1,\"rowsOffset\":-1,\"isPageable\":false,\"allColumns\":false,\"useLocalization\":true,\"useRecordDeactivation\":false,\"serverESQCacheParameters\":{\"cacheLevel\":0,\"cacheGroup\":\"\",\"cacheItemName\":\"\"},\"queryOptimize\":false,\"useMetrics\":false,\"adminUnitRoleSources\":0,\"querySource\":0,\"ignoreDisplayValues\":false,\"isHierarchical\":false}"

EsqString: "{\"rootSchemaName\":\"Contact\",\"operationType\":0,\"includeProcessExecutionData\":true,\"filters\":{\"items\":{\"aed2f24c-bb67-4a1d-9504-33e9bb3010e5\":{\"filterType\":1,\"comparisonType\":3,\"isEnabled\":true,\"trimDateTimeParameterToDate\":false,\"leftExpression\":{\"expressionType\":0,\"columnPath\":\"Id\"},\"rightExpression\":{\"expressionType\":2,\"parameter\":{\"dataValueType\":1,\"value\":\"c4ed336c-3e9b-40fe-8b82-5632476472b4\"}}}},\"logicalOperation\":0,\"isEnabled\":true,\"filterType\":6},\"columns\":{\"items\":{\"Full name\":{\"caption\":\"Full name\",\"orderDirection\":0,\"orderPosition\":-1,\"isVisible\":true,\"expression\":{\"expressionType\":0,\"columnPath\":\"Name\"}},\"Age\":{\"caption\":\"Age\",\"orderDirection\":0,\"orderPosition\":-1,\"isVisible\":true,\"expression\":{\"expressionType\":0,\"columnPath\":\"Age\"}},\"Email\":{\"caption\":\"Email\",\"orderDirection\":0,\"orderPosition\":-1,\"isVisible\":true,\"expression\":{\"expressionType\":0,\"columnPath\":\"Email\"}}}},\"isDistinct\":false,\"rowCount\":-1,\"rowsOffset\":-1,\"isPageable\":false,\"allColumns\":false,\"useLocalization\":true,\"useRecordDeactivation\":false,\"serverESQCacheParameters\":{\"cacheLevel\":0,\"cacheGroup\":\"\",\"cacheItemName\":\"\"},\"queryOptimize\":false,\"useMetrics\":false,\"adminUnitRoleSources\":0,\"querySource\":0,\"ignoreDisplayValues\":false,\"isHierarchical\":false}"

The only thing that is modified here is an Id of the "items" parameter in the "filters" part. I guess that you can generate a random GUID here using Guid.NewGuid(). Other key parameters here are:

1) \"rootSchemaName\":\"Contact\" - report on which section record should be generated

2) "value\":\"c4ed336c-3e9b-40fe-8b82-5632476472b4\" - Id of the record itself

3) \"columns\" - list of columns of the report, can remain static for each report, but should be updated in case the columns set in the report setup is modified.

Please test the following approach of adding the random GUID to the filter items and let us know in case you face some issues with it.

Best regards,

Oscar

Hi Oscar Dylan,

Thank you for the response.

Our problem is related with the \"columns\" list.

The Script Task receive the reportId from the process parameter. This parameter is changed depending on which report we want to store in the attachments.

The main goal is to update the \"columns\" list based on the reportId received, because each report has its own structure. Finaly, we generate and store the correct report.

This whole process cannot have user interaction, we need to find a way to get the columns list from a specific report inside the process.

If you have any questions feel free to ask.

Thanks in Advance.

Best Regards,

Pedro Pinheiro

Pedro Pinheiro,

Hi,

Then you need to check the IntExcelreportMixin. The columns list and the EsqString is formed inside getIntExcelReport function that receives esq as an argument:

			var requestData = {
				EsqString: esq.serialize(),
				ReportId: reportId,
				RecordCollection: recordCollection
			};

and esq itself is formed in two methods: addCustomProfileColumns and prepareProfileColumns:

prepareProfileColumns: function(profileSettings) {
			var profileColumns = [];
			var profile = profileSettings && profileSettings.DataGrid;
			if (!profile) {
				return profileColumns;
			}
			var gridsColumnsConfig = profile.isTiled ? profile.tiledConfig : profile.listedConfig;
			if (gridsColumnsConfig) {
				var columnsConfig = this.Ext.decode(gridsColumnsConfig);
				this.Terrasoft.each(columnsConfig.items, function(item) {
					profileColumns.push({
						aggregationType: item.aggregationType,
						caption: item.caption,
						dataValueType: item.dataValueType,
						path: item.path || item.bindTo,
						subFilters: this.Terrasoft.deserialize(item.serializedFilter),
						type: item.type || this.Terrasoft.GridCellType.TEXT
					});
				}, this);
			}
			return profileColumns;
		},
		addCustomProfileColumns: function(esq, profile) {
			var allColumns = Boolean(profile && profile.DataGrid && profile.DataGrid.allColumns);
			esq.allColumns = allColumns;
			if (allColumns) {
				return;
			}
			var profileColumns = this.prepareProfileColumns(profile);
			this.Terrasoft.each(profileColumns, function(column) {
				var columnName = column.caption;
				if (!esq.columns.contains(columnName)) {
					if (column.aggregationType) {
						this.addProfileAggregationColumn(esq, column, columnName);
					} else {
						esq.addColumn(column.path, columnName);
					}
					var newColumn = esq.columns.get(columnName);
					newColumn.caption = column.caption;
				}
			}, this);
		},

And once esq is formed and serialized it becomes a correct Esq.String that we need:

So you need to copy the logic of the mixin using C#.

Best regards,

Oscar

Oscar Dylan,

I'm trying to implement the logic of the mixin on my script task as you said. But I'm having trouble understanding the profile argument. Can you please explain me how do I get/generate the profile data that is being used by addCustomProfileColumns method as argument  on my script task?

Thanks in Advance.

Best Regards,

Pedro Pinheiro

Oscar Dylan,

I think I found the solution to my problem. The object IntExcelReport contains a specific column that has the ESQ String as you can see in the image bellow.

I've check the values and it matches the query inside the requestData. Now I can get this value with a simple db query and use it to generate my ExcelReport.

Best Regards,

Pedro Pinheiro

Did anyone actually manage to get this to work?

Thanks,