Need to avoid displaying the grouping columns when we export to excel.

Sharath_Chakali
edited February 11, 2022 in Analytics #1

Hi All,

Please find the attached screenshot where the grouping columns are displaying as highlighted(in Black).Please suggest how to avoid displaying that part in excel.

Best Answers

  • jfranken
    #2 Answer ✓

    I tested with the BIRT XLSX emitter and the SpudSoft XLSX emitter. Both emitters automatically generate a grouped outline when exporting to XLSX. The grouped outline creates the symbols you circled. There are no settings I could find to change the default behavior. I found three options for dealing with this issue:

    1. In the Excel Options editor, one of the settings on the Advanced tab is "Show outline symbols if an outline is applied". Unchecking the box will prevent the symbols from displaying again. All users will need to do this. Alternatively, Ctrl+8 will hide the symbols just for the current sheet.
    2. Switch to commercial BIRT. The emitter in commercial BIRT does not create a grouped outline.
    3. Edit the report replacing the groups with nested tables. This will have a negative impact on performance.
    Warning No formatter is installed for the format ipb
  • jfranken
    #3 Answer ✓

    I have attached my report. The text that is set to wrap in Excel is highlighted in red. I also included a screenshot showing the property setting. Be sure the data element is selected in the layout before trying to access its properties.

    Warning No formatter is installed for the format ipb
  • jfranken
    #4 Answer ✓

    Here is an example that I found. The instructions are on the report. This only works with the open source Excel emitter.

    Warning No formatter is installed for the format ipb
  • jfranken
    edited October 15, 2018 #5 Answer ✓

    The "multiplier" should eliminate the trailing 0's on the Y-axis label. It works for the example I created. Since it did not work for you, here is a different method to try.

    In the editor where you set the multiplier, set the formatting to "none" so that the multiplier is no longer active, then do following as shown in the screenshot below:

    1. Go to the Select Data tab of the chart editor.
    2. Click the Data Binding button.
    3. Edit the column binding for the column that supplies the Y-axis values.
    4. Make sure a numeric Data Type like Float is selected and click the "fx" icon to edit the expression.
    5. Divide the column value by one billion and call the toFixed(0) method to remove digits after the decimal.

    If the Y-axis labels have not changed, check your data to make sure the numbers are in tens of billions. Be sure to include any aggregating of data that the chart is doing.

    Warning No formatter is installed for the format ipb

Answers

  • jfranken
    #6 Answer ✓

    I tested with the BIRT XLSX emitter and the SpudSoft XLSX emitter. Both emitters automatically generate a grouped outline when exporting to XLSX. The grouped outline creates the symbols you circled. There are no settings I could find to change the default behavior. I found three options for dealing with this issue:

    1. In the Excel Options editor, one of the settings on the Advanced tab is "Show outline symbols if an outline is applied". Unchecking the box will prevent the symbols from displaying again. All users will need to do this. Alternatively, Ctrl+8 will hide the symbols just for the current sheet.
    2. Switch to commercial BIRT. The emitter in commercial BIRT does not create a grouped outline.
    3. Edit the report replacing the groups with nested tables. This will have a negative impact on performance.
    Warning No formatter is installed for the format ipb
  • Thank you

    Also,Could you please let me know how to use "Text wrap " so that the text width will be adjusted based on the text length in Excel Birt Report.

  • jfranken
    edited August 7, 2018 #8

    If you go to General properties for the element, there is a radio button in the properties editor to set the way Whitespace is handled. When I choose "Preformatted" and export to Excel, the element text wraps. There is also a "No Wrapping" setting to prevent text wrapping.

    Warning No formatter is installed for the format ipb
  • Hi,

    Thank you

    I have tried this way,but no luck.Still facing the same issue,Text wrapping is not happening.Could you please suggest any other way to resolve this.

  • jfranken
    #10 Answer ✓

    I have attached my report. The text that is set to wrap in Excel is highlighted in red. I also included a screenshot showing the property setting. Be sure the data element is selected in the layout before trying to access its properties.

    Warning No formatter is installed for the format ipb
  • Thank you.Text Wrap worked well.

    How can we Freeze the header in Excel Report? Please suggest on this

  • jfranken
    #12 Answer ✓

    Here is an example that I found. The instructions are on the report. This only works with the open source Excel emitter.

    Warning No formatter is installed for the format ipb
  • Hi ,

    Could you please let me know how to get the Y-Axis of the Histogram in the below highlighted format(In Billions Qar) in BIRT Eclipse.

  • jfranken
    edited October 10, 2018 #14

    One quick comment before I attempt an answer. If you have more questions in the future, please start a new thread. I understand why you might want all of your questions answered in one thread, but for others this thread is getting difficult to read and search. Thank you.

    Regarding your question, it is not clear to me what you mean by "highlighted format". Also, you did not specify the version or the type of chart (SVG, HTML5, etc.). I will provide general instructions for formatting the text you have circled.

    The easiest way to illustrate is with an image. To format the Y-axis title, follow steps 1 through 4 shown in the image.
    1. Go to the Format Chart tab in the chart editor.
    2. Select Y-Axis on the left.
    3. Click the "Title" button.
    4. Click the "A" icon to modify the formatting of the text. The "Edit Font" window will appear.
    5. To modify the formatting of the Y-axis labels, click the "A" icon highlighted in purple.

    Warning No formatter is installed for the format ipb
  • Hi ,

    Thanks for the reply.

    Type of the chart is SVG

    I need the Y Axis in the same format as highlighted in the previous screenshot (QAR 0, QAR 2 ...) QAR Currency. As of now,I am getting as in below screenshot.Also ,the total cost from the dataset is populating in the below format .Please suggest

  • Thanks for the explanation. Try the following two steps:

    1. Add this script to the onRender event of the chart:
      function beforeDrawAxisLabel( axis, label, icsc )
      {
      if (axis.getTitle().getCaption().getValue() == "Billions") {
      label.getCaption().setValue("QAR " + label.getCaption().getValue())
      }
      }

    2. On the Format Chart tab, select Y-axis on the left and add an advanced number format as shown below:

    Warning No formatter is installed for the format ipb
  • Hi ,

    Thanks for the reply.

    I have followed the instructions suggested by you but was not able to render in the expected format. I need the format (QAR 0,QAR 2,QAR 4,....) in billions as mentioned in the below screenshot.Could you please suggest here.

    Need Y-Axis in the below format.

    I followed the instructions suggested by you but getting data as below .

  • jfranken
    edited October 15, 2018 #18 Answer ✓

    The "multiplier" should eliminate the trailing 0's on the Y-axis label. It works for the example I created. Since it did not work for you, here is a different method to try.

    In the editor where you set the multiplier, set the formatting to "none" so that the multiplier is no longer active, then do following as shown in the screenshot below:

    1. Go to the Select Data tab of the chart editor.
    2. Click the Data Binding button.
    3. Edit the column binding for the column that supplies the Y-axis values.
    4. Make sure a numeric Data Type like Float is selected and click the "fx" icon to edit the expression.
    5. Divide the column value by one billion and call the toFixed(0) method to remove digits after the decimal.

    If the Y-axis labels have not changed, check your data to make sure the numbers are in tens of billions. Be sure to include any aggregating of data that the chart is doing.

    Warning No formatter is installed for the format ipb
  • Hi Jeff,

    Thanks a lot for the solution.It worked as expected.

    Please let me know how to provide space/ gap between the series.