This help note will guide you through the process of adding and removing columns in a customised report template using the Report Designer. It is assumed that you are familiar with the Report Designer terminology and if not you should first read the Report Designer: General Concepts.


Please note that this support note will be exclusively using the Budget Summary report with the Percentage Variance template, as such there may be minor differences between this example and your report.


Adding Columns


In this example we will be adding a column in the Budget Summary report which will display the full year forecast field. The full year forecast is calculated as year-to-date actuals plus the budgets for the remainder of the financial year.

  1. Open the Budget Summary Percentage Variance report template in the Report Designer
  2. Right click on the Variance cell next to the [LastYearYTDPeriod] (YTD)Actuals cell and select Insert -> Column to Right

    add_step_2


  3. A new cell tableCell1 will be created and inserted to the right of the Variance cell. Resize the other header cells under the PageHeader band to allow more space for our newly created cell.

    Note – You can highlight multiple cells by selecting each cell while holding down the CTRL key. Then using the property grid set the cell width appropriately.

  4. Also resize the data cells associated with the header cells, these are located under the Detail3 band. You can resize these cells by selecting a cell and then click and drag a left or right edge toggle. When resizing data cells a pink guide line will inform you when you are aligned with the appropriate header cell. You report should look similar to this example:

    step_4


  5. Quickly right-click on tableCell1 twice to allow you to rename the cell. Rename tableCell1 to Forecast Full Year or something else that is appropriate. The cell probably looks like it has different formatting compared to the other header cells.

    add step 5


  6. Click on the Forecast Full Year cell and change the following properties in the cell’s Property Grid:
    - Change the Font field to Calibri, 10pt
    - Change the Text Alignment field to Middle Right
    - Change the Multiline field to Yes
    Your Forecast Full Year cell should now look similar to the following:

    step_6


  7. Right-click on the Variance data field cell in the Detail3 band and select Insert -> Column to Right

    step_7
     

  8. You should now have a new cell created next to the Variance data field cell. Resize it using the pink guide lines so that the new cell is aligned with the Forecast Full Year header cell. It should look similar to the following:

    step_8
     

  9. To bind data to the newly created label box, click on the Field List tab in the Report Explorer:

    step_9
     

  10. With the Field List tab open, expand the AccountsList item and select ForecastFullYear. Drag the ForecastFullYear item and drop it onto the tableCell2 box we created. After doing so, the name of your tableCell2 box should now look like this:

    step_10

    The Forecast Full Year data field will now be successfully bound to the label box we created. Now we simply need to format the data field cell so that it has the same formatting as the other data field cells.


  11. Select the data field cell containing the ForecastFullYear data binding and change the following properties in the Property Grid:
    - Change the Font field to Calibri, 10pt
    - Change the Text Alignment to Middle Right

  12. Also in the Property Grid with the cell selected, select the Formatting Rules field and click on the three dots to open the Formatting Rules Editor.

     step_12


  13. With the Formatting Rules Editor open, select each of the following items from the Rules available in a report and click on the add step 13 button to move each of the items to Rules applied to a control:
    - TotalAccountFont
    - ParentAccountShading
    - HeaderAccountHiding

  14. Your Formatting Rules Editor should now look like this:

    step_14

    Note – The TotalAccountFont formats the totals rows, the ParentAccountShading shades the parent accounts while the HeaderAccountHiding hides the values for the account level 1. We will now need to create a rule to format negative numbers.


  15. With the Formatting Rules Editor still open, click on the Edit Rule Sheet… button to open the following window:

    step_15 

     
  16. Click on the step_16a button to create a new formatting rule. The new formatting rule will display as formattingRule1 and you should select it. In the property section of the new formatting rule, change the following properties:
    - Change the (Name) field to ForecastFullYearNegative
    - Use the drop-down option in the Data Member field, expand the BudgetSummaryReportDataItems item and select AccountsList
    - Use the drop-down option in the Data Source field and select bindingSource1
    - Change the Foreground Color field to Red
    - Click on the Condition field and click on the button to open the Condition Editor and follow the advice in the next step.

    step_16b

     
  17. With the Condition Editor open:
    - Select the Fields button in the left-hand column and quickly double right-click on the ForecastFullYear item in the middle column
    - Click on the < button
    - Next to the < sign in the formula, type in -0.5
    Your Condition Editor screen should now look similar to the following:

    step_17


  18. Click on the OK button to continue.

  19. Click on the Close button on the Formatting Rule Sheet Editor. You will now be back on the Formatting Rules Editor window. Select the ForecastFullYearNegative rule from the Rules available in a report: list and click on the step_18a button to move it to the Rules applied to a control: list. When you have done that, click on the OK button.

    step_18b

     
  20. A final adjustment is needed to ensure that the numbers displayed in the report display in the correct format, for example, display negative numbers as (100) instead of -100 and define the number of decimal places. Select the Forecast Full Year data field cell and click on the little > button to open the Table Cell Tasks menu.

     step_19


  21. With the Table Cell Tasks window open, type {0:#,#;(#,#);0} into the Format String field.

    add_step_20

     
  22. Close the Report Designer and run the report to ensure that it looks correct.

     add_step_21

 

Removing Columns


In this example we will be removing a column from the Budget Summary report. We will be reoming the forecast Full Year Column that we added in the previous steps of this support note.

  1. Open the Budget Summary Percentage Variance report template in the Report Designer 
  2. Right-click on the Forecast Full Year header cell under the PageHeader band and click on the Delete button  

     remove_step_2


  3. Also right-click on the data field cell that contains the Forecast Full Year data binding under the Detail3 band and click on the Delete button

     remove_step_3


  4. As a result of deleting the cells from the report template, the report may look similar to the following:

    remove_step_4 

     
  5. To resolve the sizing issues, simply resize each of the cells, starting with the one on the end, so that each cell is the same width as each other. A simple way to do this is as follows:
    1.  Find the total width of all the cells together, an easy way to find this is to click on the remove_step_5a button in the top left hand corner of the cells under the PageHeader band

      remove_step_5b 

       
    2. With all the cells now selected, scroll down the Property Grid and find the Size field. It will probably say something like 2669, 131.77. The first number, 2669 refers to the width of all the cells in the PageHeader band. However, before we resize the cells we need to know how wide each cell needs to be. 
    3. To do this we will need to subtract the width of the empty cell from the far left-hand side under the PageHeader band from 2669. Click on the empty cell, scroll down the Property Grid and find the Width field. It should equal about 634.39. So the width of all the header cells minus the empty cell under the PageHeader band would equal something like 2669 – 634.39 which equals 2034.61.
    4. Next we will need to divide 2034.61 by the total number of columns (10) to identify how wide each cell needs to be (203.461).
    5. Finally, click on each cell (other than the empty one) under the PageHeader band and change their width in the Property Grid to 203.461. When you have completed that, do the same for the data field cells under the Detail3 band (with the exception of the [AccountsList.NameForReport] data cell).
    6. Your report template should now look appropriately sized.