Jump to: navigation, search
  • Main page
  • Recent changes
  • Random page
  • Formatting Grids


    Formatting in XLCubed is handled in 3 key areas:

    Format sheet: This controls the basic formatting for a grid, in terms of the overall colour scheme and fonts. It is also used to store user applied formatting for measures or slices, as detailed below.

    Measures formatting: Users can select a measure in the XLCubed member selector, and apply a numeric format to the selected measure using the standard Excel dialog.

    Formatting via XLCubed’s right-click menu: Users can right click on any member in the row or column area of a grid, choose XLCubed - Formatting and apply a format to that cell through the standard Excel dialog.

    Format sheet

    Format1v9.png


    This shows the default format settings for grids. These can be changed as required eg there may be corporate defaults for reports with particular preferred fonts and colours.

    Rows 5 to 16 define the basic formatting for a grid. By directly formatting the relevant cells here using native Excel, you can control the font style and colour and cell background of the labels, the grid header and row and column headers and members.

    Format2v9.png


    Row 47 onwards is for formatting applied to individual slices of a grid, normally when a user wants to format a particular member. This area will be updated automatically when formatting is applied using XLCubed’s right-click menu.

    If you have more than one grid in your workbook and you need to apply different formatting to each grid, you need to name the grid (through Grid Properties) and then refer to each specific grid in this area in column B. Please note: There is an empty row between each slice highlight, so that the borders do not conflict with each other.

    Any updates you make directly on the format sheet will be applied the next time the grid is refreshed

    The two screenshots below are of the standard format sheet, and a grid inserted using that format sheet.

    The second shows a format sheet which has been amended for custom formatting, and the grid that produces. The top area of the format sheet shown here covers the base layout of a grid. The lower area (rows 47+ are slice or measure specific).

    To Edit the format sheet you directly apply the required formatting changes to the relevant cells in the FormatSheet using normal Excel formatting.


    Format4v9.png


    This is the grid after all the formatting above has been applied:

    Format5v9.png


    Cells Q2 to V3 in the format sheet hold XLCubed slicer settings. Here you can change the display format for the slicer title, slicer buttons and slicer items.

    Format11v9.png

    Upgrading pre-v9 workbooks

    If you start a new workbook, you will automatically get the new format worksheet, workbooks built in older versions can be upgrading to the new formatting, by going to Workbook Options -> Reset Format sheet.

    You will be given an option to keep any customisation as part of the upgrade - once the upgrade has completed the old format sheet is renamed and can be safely deleted once you are happy that all the formatting has been saved correctly.

    Advanced Options

    You can click in cell A16 to toggle the visibility of the advanced options, from here you can alternate row formatting and other writeback/grid active cell formatting.

    Formatadvv9.png

    For example, to set the alternate row format you need to toggle the selector to "Alternate Rows - On". The formats for the members and cells will be applied for every other row.

    Row 20 is for the formatting of the Active cell

    Rows 22 to 28 are specific to writeback-enabled grids where you can write new values to the cube.

    Measure formatting

    Formatting can also be achieved through the Member Selector by clicking the highlighted button below:

    Format6v9.png


    The standard Excel format window is displayed and allows the user to format the number as required.

    Formatting via XLCubed’s right-click menu

    If you right-click on a grid row or column header and select Formatting from XLCubed’s menu you will be given the option to Format This Level or Format This Member.

    If you do the same on a grid cell you will be able to Format This Cell.

    All of these formatting options will write to the bottom area of the formats sheet.

    Here is an example grid:


    Format7.png


    Although there is indentation of grid members when you drill down it is a little difficult to differentiate between the different levels.


    Format8.png


    Let’s format the country level by right-clicking on a country and selecting Format This Level and selecting a fill colour of blue. As you can see each country now stands out.


    Format9.png


    You can see that the format sheet has been updated automatically:


    Format10.png


    If you wish to change this formatting in any way you can just right-click the entry in the format sheet and edit using standard Excel formatting.

    Format This Member

    The XLCubed right-click menu has an option, Format This Member.

    FG1.png

    So if we want to format Accessories row, we right-click on Accessories, XLCubed, Format This Member. We are presented with the standard Excel Format Cells window from which we can choose any of the usual settings to change.

    FG2.png

    We will choose Fill, select a colour and OK. The refreshed grid now looks like this:

    FG3.png

    The lower half of the XLCubedFormats sheet shows that both Member Accessories and associated row data will be filled in pink.

    FG4.png

    If you do not want the data to be filled go to the XLCubedFormats sheet, right-click the Data Format cell for Accessories and remove the fill colour by setting the backgound to 'No Colour'.

    FG6.png


    FG5.png

    Once refreshed, the grid now looks like:

    FG8.png

    Formatting Rows and Columns

    You can also set formatting on rows and/or columns for grid members.

    Here we have a simple report where we have set formatting at different levels:

    Row1.png

    The format sheet looks like this:

    Row2v9.png

    Change the format sheet to ROWS as below:

    Row3aV9.png

    This format will now be applied for whichever hierarchy is on rows. Let's put Geography on rows instead:


    Row6.png

    As you can see this is a very simple way to create standardised reporting within an organisation. This could easily be included in the master format sheet so that all grids created would have the same row format. The same applies to columns.

    Format this level

    Format this level allows the user to easily format all members at a particular hierarchy level and is available on XLCubed's right-click menu, Formatting, Format this level. You will be presented with the standard Excel Format Cells window from which you can choose any of the usual settings to change.

    You can find more advanced level formating instructions here.

    Format by Member Property

    Format by property allows you to select the affected slice by a member property value. You need the full name of the property, as shown below, and the property must be selected in the grid (although you can set the property display style in Grid Properties to member only so it is not displayed).

    Gridfmt memprop1.PNG
    Gridfmt memprop2.PNG

    Format InCell Charts

    From Version 9 you can set a highlight on an InCell Chart created in the member selector. To do this enter the hierarchy name as normal, and in the Member cell prefix the chart title name with "XLCubedInCellChart:", e.g. XLCubedInCellChart:InCell Chart.

    See Also