XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Difference between revisions of "Formatting Grids"
Line 1: Line 1:
−
==General Overview==
+
{{VersionDisambiguation|pageVersion=|disambiguationPage=Formatting Grids/Versions}}
 +
{{:Formatting Grids/Version 7.0}}
  
−
XLCubed Grid formatting is now controlled through the XLCubedformat sheet.  
+
<!--I've transcluded the appropriate version above so that we don't have to repeat the information.-->
  
−
(For a grid which is static in shape, you can choose to manage formatting natively in Excel, and turn off XLCubed formatting. This is done by right clicking on the grid and in the properties menu, on the appearance tab, unchecking ‘Apply Formatting’.)
+
In v7 formatting is available on XLCubed right-click menu as option, Format This Member.
−
 
+
[[Image:FG1.png|350px|centre]]
−
When the first XLCubed grid is inserted in a new workbook, the format sheet is copied in from the master format workbook.  The format sheet can then be customised as required within this workbook. The default format sheet is shown below.
 
−
 
 
−
[[Image:FormatGrids1.png|750px|centre]]
 
−
 
 
−
The layout is primarily that of a simple grid (C6:D21), with descriptions of each cell alongside. The format set in the Excel cell within this range is then applied to the corresponding area of the XLCubed grid when it is refreshed.
 
−
 
 
−
Where there is a requirement to format different grids in different ways, this is handled by the range A24:Exx.
 
−
 
 
−
Most common formatting requirements can be handled through the right click menu directly on an XLCubed grid, without any need to make changes directly on the format sheet. More complex requirements can be handled directly on the format sheet. When applying formatting through the right click menus, this will actually make the appropriate changes in the format sheet.
 
−
 
 
−
==Right Click Formatting==
 
−
 
 
−
The right click options available vary with whether you have selected a value, or a member name.
 
−
 
 
−
To apply a default numeric format to a grid:
 
−
Use Excel to format the number as required, then right click the number and choose XLCubed – Formatting – Apply Format to Data. This sets the format of the selected cell as the default for all Measures (D11 on the XLCubedFormats sheet).  
 
−
 
 
−
To apply differing formats to several measures:
 
−
Open the member selector for ‘Measures’ (by double clicking the Measures hierarchy label, or right clicking the grid and choosing ‘Design Grid’ to open the report designer). Select the measures you wish to format, and then use the formatting button, highlighted below to set the Excel numeric format required for each measure.
 
−
 
 
−
[[Image:FormatGrids2.png|centre]]
 
−
 
 
−
This will apply the selected formats to the format sheet as shown below
 
−
 
−
[[Image:FormatGrids3.png|600px|centre]]
 
−
 
 
−
Additional formats / background colours as required can then be applied to the member name and data format cells directly on the format sheet.
 
−
 
 
−
To format a Slice:
 
−
 
 
−
With a Dimension Member highlighted the following options are available, on the XLCubed – Formatting right click menu
 
−
 
 
−
• Apply Format to This Member – Choose this having first set the Excel cell formatting as required. The format is then applied to the member name and the data for this member.
 
−
 
−
• Select Number Format for This Member – This opens an Excel dialog to specify the number format for the data for the member selected.
 
−
 
 
−
• Apply Format to Column/Row Members - Choose this having first set the Excel cell formatting as required, and the format is then applied to all member names on rows or columns.
 
−
 
 
−
Each of these settings will add or amend a row in the format sheet from A25 onward, which can then be additionally changed as required directly on the format sheet.
 
−
 
 
−
==Formatting directly through Format Sheet==
 
−
More complex formatting scenarios or grid specific formatting are handled directly on the format sheet. Any of the cells on the format sheet can be formatted directly in Excel, and will then be applied to the appropriate are of the grid when the grid is next refreshed. The main body of the grid is contained in C6:D21, and the data cells, column and row member names, and the filter area can all be set directly here.
 
−
 
 
−
'''Alternate Rows:'''
 
−
Alternate row formatting can be used by setting the drop down selector in E13 to ‘True’ and then applying the format required in C and D13.
 
−
'''
 
−
Writeback – Leaf Level:'''
 
−
The grid can display the lowest level data and members in a specific format where required, by setting E15:21 to true as required.
 
−
 
 
−
'''Conditional Formatting:'''
 
−
Conditional formatting on the grid data can be applied by setting the conditions on the default cell format (D11). This is currently only available at a grid level, and will become available at a slice level in version 6.1.
 
−
 
 
−
'''Grid Specific Formatting:'''
 
−
Where multiple grids exist in a workbook, there may be a requirement to format a slice of data in a specific way in grid A, but not in grid B. Using the right click menus will apply the selected format to that slice of data in all grids. To make this grid specific, edit the format sheet and supply the grid name for the appropriate slice in column B. The example below in B26 is restricting the Bold formatting on CY 2004 to the ‘Sales’ grid, whereas the formatting on Germany is not restricted and will be applied on all grids.
 
−
 
 
−
When using this approach you must first name the grid, which is done in grid properties, on the appearance tab. Note that it’s also possible to use wildcards in the GridName field, so that for example all grids starting with Sales would apply this format (Sales*).
 
  
 +
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.
 +
[[Image:FG2.png|350px|centre]]
 
   
 
   
−
[[Image:FormatGrids4.png|750px|centre]]
+
We will choose Fill, select a colour and OK.  The refreshed grid now looks like this:
−
 
+
[[Image:FG3.png|350px|centre]]
−
 
 
−
 
 
−
'''Slicer Formatting:'''
 
−
XLCubed button slicers can be formatted as required in I5:Nxx.The font and button background can all be customised as required, and this can be achieved at an individual slicer level.  
 
−
 
 
−
   
 
−
[[Image:FormatGrids5.png|centre]]
 
−
 
 
−
[[Image:FormatGrids6.png|centre]]
 
−
 
 
−
 
 
−
The image above shows the definition area in the format sheet, and a slicer which is formatted using this, in this case using the default format which had been set (1st row) rather than the custom entry for the GeographySlicer.
 
−
 
 
−
The formatting areas are:
 
−
 
 
−
Slicer Title:  Only applicable when the ‘Show Title bar’ is checked in ‘Edit Slicer’ – settings tab. This applies back colour and font settings to the title.
 
−
 
 
−
Slicer Button:  This applies back colour and font settings to the buttons, when they are in an unselected state
 
−
 
 
−
Slicer Button Selected:  This applies back colour and font settings to the buttons, when they are in a selected state
 
−
 
 
−
Slicer Hover: This applies back colour and font settings to the buttons, when the mouse hovers over them.
 
−
 
 
−
Slicer Name: Populate this with the name of the slicer to create a customised format for a specific slicer. To name a slicer, go to Edit Slicer – settings tab.
 
−
 
−
===Level based Grid Formatting===
 
−
 
 
−
Please see [[Level_based_formatting|this article]] for information on how fo achieve level based formatting.
 
−
 
 
−
 
 
−
==Format Sheet Location and resetting==
 
−
The default format sheet is specified under the Options menu on the ribbon XLCubed Options
 
−
 
 
−
 
−
[[Image:FormatGrids7.png|centre]]
 
−
 
 
−
 
 
−
 
 
−
This can be changed as required, for example to a network location to share customised corporate formats.
 
−
 
 
−
Should the Format Sheet need to be reset; use the Workbook Options to reset the Format Sheet in the current workbook to that held in the default path
 
−
 
 
 
   
 
   
 +
The lower half of the XLCubedFormats sheet shows that both Member Accessories and associated row data will be filled in pink.
 +
[[Image:FG4.png|350px|centre]]
  
−
[[Image:FormatGrids8.png|750px|centre]]
+
If you do not want the data to be filled go to the Data Format cell for Accessories and remove the fill colour.
 +
[[Image:FG5.png|350px|centre]]
  
  
−
==Hiding the FormatSheet==
 
  
−
By default, the format sheet will be visible in Excel. To hide the format sheet by default, use the XLCubed Options.
 
  
 
[[Category:Grid Reporting]]
 
[[Category:Grid Reporting]]

Revision as of 13:38, 30 April 2012

This article refers to the latest XLCubed version. For other versions, see Formatting Grids/Versions.


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

Format1.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 6 to 11 defines 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.

Row 13 controls whether or not you want to apply alternate row formatting. For example, to set the alternate row format you need to toggle the selector to TRUE. The format in cell C13 will be applied to the member names and D13 will then be applied to the data.

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

Row 24 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.

Note that 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 20+ are slice or measure specific).


Format2.png



Format3.png


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


Format4.png


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

Format5.png


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

Format11.png


Measure formatting

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

Format6.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

In v7 formatting is available on XLCubed right-click menu as 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

In v7.2 we now have the ability to 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:

Row2.png

Change the format sheet to ROWS as below:

Row3a.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

New to v7.2, 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.

Format by Member Property

New to v8.1, 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


See Also


In v7 formatting is available on XLCubed right-click menu as 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 Data Format cell for Accessories and remove the fill colour.

FG5.png
Retrieved from ‘https://help.xlcubed.com/index.php?title=Formatting_Grids&oldid=4680’