pentaho在导出到excel时修改并打印参数(pentaho modify and print a parameter while exporting to excel)

pentaho在导出到excel时修改并打印参数(pentaho modify and print a parameter while exporting to excel)

我使用pentaho报告设计师。 我的prpt文件中有businessdate参数。 它具有用于过滤sql查询的日期范围的值。 我能够在导出到html时处理和修改它但是我在导出到excel时遇到问题。

日期范围有以下格式：

- BETWEEN {d '2014-01-01'} AND {d '2014-01-31'} - IN ({d '2014-01-14'},{d '2014-01-15'}, {d '2014-01-19'} ,{d '2014-01-20'},{d '2014-01-21'})

我想找出最大和最小日期并显示它。 但是在这种情况下使用excel，我很高兴用逗号分隔显示它们，如下所示。

- 2014-01-01, 2014-01-31 - 2014-01-14, 2014-01-15, 2014-01-19, 2014-01-20, 2014-01-21

如果我使用下面的基本公式显示，它在excel中工作但是当我将它应用于pentaho报表设计器中的businessDate元素的excel - formula部分时它不起作用。

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"{d '",""), "BETWEEN", ""), "IN", ""),"'}",", "), " AND ", ""),")",""),"(",))

它不一定是这样的。 我很满意在打印到excel之前建议格式化此原始日期范围的任何方法。

先谢谢你。

I use pentaho report designer. I have got businessdate parameter in my prpt file. This has the value of the date range which is used to filter the sql query. I am able to handle and modify it while exporting to html however I have a problem in exporting to excel.

The date range comes in the formats below:

- BETWEEN {d '2014-01-01'} AND {d '2014-01-31'} - IN ({d '2014-01-14'},{d '2014-01-15'}, {d '2014-01-19'} ,{d '2014-01-20'},{d '2014-01-21'})

I like to find out max and min date and display it. However in this case with excel, I am happy with displaying them separated with commas like shown below.

- 2014-01-01, 2014-01-31 - 2014-01-14, 2014-01-15, 2014-01-19, 2014-01-20, 2014-01-21

If I use the basic formula show below, it works in in excel but it does not work when I apply it to excel - formula section of the businessDate element in pentaho report designer.

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"{d '",""), "BETWEEN", ""), "IN", ""),"'}",", "), " AND ", ""),")",""),"(",))

It does not have to be this way. I am happy with any method suggested to format this raw date range before printing to excel.

Thank you in advance.

最满意答案

在浪费了大量时间之后，我找到了一种适用于所有导出类型的方法。 正如我在我的问题中所说，我正在使用“结构选项卡” - >选择“主报告” - >“属性”选项卡 - > html - >“append-header”属性来修改html打印的日期。

<script type="text/javascript"> function init() { var tableList = document.getElementsByTagName("table"); var businessDate = document.getElementById("businessDatePeriodSelected"); businessDate.innerHTML = businessDate.innerHTML+"testDateModified"; } window.onload = init; </script>

这段代码完成了这项工作。 但只是为了HTML。 我需要想出一些新的东西。 我也在寻找excel和pdf出口的解决方案。

这是解决方案：单击右上方“结构”选项卡旁边的“数据”选项卡。 您将在树中看到“功能”。 右键单击并单击“添加功能”。 选择“脚本” - >“Bean-Scripting Framework（BSF）”。 选择在功能下创建的功能。 给它命名并将下面的代码添加到“表达式”部分。 [这不需要开始或结束标签]

/* businessdate is one of my parameters that I like to display on the report. dataRow is automatically recognized by the interpreter which can be used for calling parameter values. It seems like came out of nowhere.*/ String value = dataRow.get("businessdate"); value= value.replaceAll("[^0-9\\-\\{\\}]", ""); value= value.replaceAll("[\\{]", ""); // replace all { value= value.replaceAll("[\\}]", ","); // replace all } value= value.substring(0, value.length()-1); String[] dateArr = value.split(","); return dateArr[0] +" - "+dateArr[dateArr.length-1];

您需要做的最后一件事是将您的功能拖放到适合您报告的位置。 它将找到一个文本框，显示修改后的businessdate。

如果您想在pentaho报告上打印参数，则可以完成所有导出（html，pdf和excel）的工作。 您也可以在打印前对其进行修改。 此链接非常有用，因为语法在某些方面略有不同。

祝你好运。

After wasting a lot of time, I have found a way which works for all export types. As I said in my question, I was modifying date for html print by using "Structure Tab" --> Select "Master Report" --> "Attributes" Tab --> html -> "append-header" attribute.

<script type="text/javascript"> function init() { var tableList = document.getElementsByTagName("table"); var businessDate = document.getElementById("businessDatePeriodSelected"); businessDate.innerHTML = businessDate.innerHTML+"testDateModified"; } window.onload = init; </script>

This piece of code does the job. However just for html. I needed to come up with something new. I was looking for a solution for excel and pdf exports as well.

HERE IS THE SOLUTION: Click on "Data" tab next to the "Structure" tab on the right top side. You will see "Functions" in the tree. Right click and hit "Add functions". Select "Script" -->"Bean-Scripting Framework (BSF)". Select function created under Functions. Give it a name and add the code below to the "Expression" section. [This does not need a starting or ending tag]

/* businessdate is one of my parameters that I like to display on the report. dataRow is automatically recognized by the interpreter which can be used for calling parameter values. It seems like came out of nowhere.*/ String value = dataRow.get("businessdate"); value= value.replaceAll("[^0-9\\-\\{\\}]", ""); value= value.replaceAll("[\\{]", ""); // replace all { value= value.replaceAll("[\\}]", ","); // replace all } value= value.substring(0, value.length()-1); String[] dateArr = value.split(","); return dateArr[0] +" - "+dateArr[dateArr.length-1];

The last thing you need to do is drag and drop your function somewhere suitable on your report. It will locate a textbox which will display the modified businessdate.

If you like to print a parameter on your pentaho report, this does the job for all exports (html, pdf and excel). You can also modify it before printing. This link pretty helpful as the syntax is slightly different at some points.

good luck.

pentaho在导出到excel时修改并打印参数(pentaho modify and print a parameter while exporting to excel)

我使用pentaho报告设计师。 我的prpt文件中有businessdate参数。 它具有用于过滤sql查询的日期范围的值。 我能够在导出到html时处理和修改它但是我在导出到excel时遇到问题。

日期范围有以下格式：

- BETWEEN {d '2014-01-01'} AND {d '2014-01-31'} - IN ({d '2014-01-14'},{d '2014-01-15'}, {d '2014-01-19'} ,{d '2014-01-20'},{d '2014-01-21'})

我想找出最大和最小日期并显示它。 但是在这种情况下使用excel，我很高兴用逗号分隔显示它们，如下所示。

- 2014-01-01, 2014-01-31 - 2014-01-14, 2014-01-15, 2014-01-19, 2014-01-20, 2014-01-21

如果我使用下面的基本公式显示，它在excel中工作但是当我将它应用于pentaho报表设计器中的businessDate元素的excel - formula部分时它不起作用。

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"{d '",""), "BETWEEN", ""), "IN", ""),"'}",", "), " AND ", ""),")",""),"(",))

它不一定是这样的。 我很满意在打印到excel之前建议格式化此原始日期范围的任何方法。

先谢谢你。

I use pentaho report designer. I have got businessdate parameter in my prpt file. This has the value of the date range which is used to filter the sql query. I am able to handle and modify it while exporting to html however I have a problem in exporting to excel.

The date range comes in the formats below:

- BETWEEN {d '2014-01-01'} AND {d '2014-01-31'} - IN ({d '2014-01-14'},{d '2014-01-15'}, {d '2014-01-19'} ,{d '2014-01-20'},{d '2014-01-21'})

I like to find out max and min date and display it. However in this case with excel, I am happy with displaying them separated with commas like shown below.

- 2014-01-01, 2014-01-31 - 2014-01-14, 2014-01-15, 2014-01-19, 2014-01-20, 2014-01-21

If I use the basic formula show below, it works in in excel but it does not work when I apply it to excel - formula section of the businessDate element in pentaho report designer.

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"{d '",""), "BETWEEN", ""), "IN", ""),"'}",", "), " AND ", ""),")",""),"(",))

It does not have to be this way. I am happy with any method suggested to format this raw date range before printing to excel.

Thank you in advance.

最满意答案

在浪费了大量时间之后，我找到了一种适用于所有导出类型的方法。 正如我在我的问题中所说，我正在使用“结构选项卡” - >选择“主报告” - >“属性”选项卡 - > html - >“append-header”属性来修改html打印的日期。

<script type="text/javascript"> function init() { var tableList = document.getElementsByTagName("table"); var businessDate = document.getElementById("businessDatePeriodSelected"); businessDate.innerHTML = businessDate.innerHTML+"testDateModified"; } window.onload = init; </script>

这段代码完成了这项工作。 但只是为了HTML。 我需要想出一些新的东西。 我也在寻找excel和pdf出口的解决方案。

这是解决方案：单击右上方“结构”选项卡旁边的“数据”选项卡。 您将在树中看到“功能”。 右键单击并单击“添加功能”。 选择“脚本” - >“Bean-Scripting Framework（BSF）”。 选择在功能下创建的功能。 给它命名并将下面的代码添加到“表达式”部分。 [这不需要开始或结束标签]

/* businessdate is one of my parameters that I like to display on the report. dataRow is automatically recognized by the interpreter which can be used for calling parameter values. It seems like came out of nowhere.*/ String value = dataRow.get("businessdate"); value= value.replaceAll("[^0-9\\-\\{\\}]", ""); value= value.replaceAll("[\\{]", ""); // replace all { value= value.replaceAll("[\\}]", ","); // replace all } value= value.substring(0, value.length()-1); String[] dateArr = value.split(","); return dateArr[0] +" - "+dateArr[dateArr.length-1];

您需要做的最后一件事是将您的功能拖放到适合您报告的位置。 它将找到一个文本框，显示修改后的businessdate。

如果您想在pentaho报告上打印参数，则可以完成所有导出（html，pdf和excel）的工作。 您也可以在打印前对其进行修改。 此链接非常有用，因为语法在某些方面略有不同。

祝你好运。

After wasting a lot of time, I have found a way which works for all export types. As I said in my question, I was modifying date for html print by using "Structure Tab" --> Select "Master Report" --> "Attributes" Tab --> html -> "append-header" attribute.

<script type="text/javascript"> function init() { var tableList = document.getElementsByTagName("table"); var businessDate = document.getElementById("businessDatePeriodSelected"); businessDate.innerHTML = businessDate.innerHTML+"testDateModified"; } window.onload = init; </script>

This piece of code does the job. However just for html. I needed to come up with something new. I was looking for a solution for excel and pdf exports as well.

HERE IS THE SOLUTION: Click on "Data" tab next to the "Structure" tab on the right top side. You will see "Functions" in the tree. Right click and hit "Add functions". Select "Script" -->"Bean-Scripting Framework (BSF)". Select function created under Functions. Give it a name and add the code below to the "Expression" section. [This does not need a starting or ending tag]

/* businessdate is one of my parameters that I like to display on the report. dataRow is automatically recognized by the interpreter which can be used for calling parameter values. It seems like came out of nowhere.*/ String value = dataRow.get("businessdate"); value= value.replaceAll("[^0-9\\-\\{\\}]", ""); value= value.replaceAll("[\\{]", ""); // replace all { value= value.replaceAll("[\\}]", ","); // replace all } value= value.substring(0, value.length()-1); String[] dateArr = value.split(","); return dateArr[0] +" - "+dateArr[dateArr.length-1];

The last thing you need to do is drag and drop your function somewhere suitable on your report. It will locate a textbox which will display the modified businessdate.

If you like to print a parameter on your pentaho report, this does the job for all exports (html, pdf and excel). You can also modify it before printing. This link pretty helpful as the syntax is slightly different at some points.

good luck.