Tutorial: Getting Started
This tutorial will outline the steps to create a basic XLCubed report.
You will first need to download and install XLCubed Excel Edition, and then open a new Excel workbook.
All XLCubed functionality is available through the XLCubed ribbon. (If the ribbon is not enabled, please read this article.)
First, connect to a data source by selecting the Connections ribbon menu item. In a new workbook, this dialog will initially be empty. Create a new connection by clicking the add button and then select the New Connection tab.
The left of this dialog lists all the possible data sources to connect to. Select the relevant one and fill in the details to connect to your data source.
After clicking OK, the Manage Connections dialog now has a new entry for the connection that was just created.
Creating a Grid
To bring the data into the workbook, create a new XLCubed Grid by selecting the Grid ribbon menu item.
This will display the Report Designer dialog. The left side of this dialog displays all the available hierarchies within the data source. These can be dragged and dropped on to the relevant areas in the middle of the dialog.
Find the date hierarchy and drag it to the filters section of the dialog. This will then populate the right side of the dialog where member selection can be made.
To filter the grid so that only data from the year 2017 is shown, drill down under the All Periods member, find the correct year and drag it over to the right (or double click) to select it.
We also need to define what measure the grid will be showing data for, so drag Measures on to filters too.
Selections can continue to be made in this manner in order to define the report. In this example, the following selections have been made:
- Measures - Reseller Sales Amount
- Date - CY 2017
- Geography - no member selected, so the default will be displayed (as defined in the cube)
- Product - no member selected, so the default will be displayed (as defined in the cube)
The new grid has now been inserted into the workbook. It is showing the data for Reseller Sales Amount in CY 2017 across Geographies and Products.
Because no members were selected for the Geography and Product hierarchy the grid is displaying the default 'All' member for both. These can be drilled into to display their child members. Simply double click on the parent member and it's children members are then displayed.
The layout of the grid can easily be rearranged by dragging and dropping the the hierarchy labels (the cells in grey in this example). This could be used, for example, to quickly swap the Geography and Product axes.
Editing a Grid
The grid design itself can be edited by going back into the report designer. This can be accessed by selecting a cell in the grid and then either
- selecting the right-click menu XLCubed > Design Grid...
- go to the Grid ribbon menu and selecting the Design menu item
Adding a Slicer
A slicer can be quickly added to a grid to allow for quick changes to member selections. Select one of the filter cells and right-click XLCubed > Add slicer. A warning will appear that there are existing member selections: click ok and a slicer will be created in the cell.
This can be moved, resized and edited through the right-click menu (read Dimension Slicers for more detail).
If needed, other grids in the workbook can also be connected to this slicer. Go into the report designer and in the member selector area, use the slicer option as the member selection.
Charts can be inserted using normal Excel functionality. However, XLCubed also comes with several extending charting and visualisation functionalities.
To insert a graph based on the data in the grid, right-click anyway in the grid area and select XLCubed > Grid Charts. You can then select Highest/Lowest/All members depending on how you want the chart to behave when the grid is drilled into.
Select which chart type you want to use and then the chart is automatically inserted into the workbook. It is connected to the grid, so after any drilling/drag and drop the chart will update.
The chart can be formatted etc. just as with any Excel chart.
For other charting and visualisation options, read this article.
To publish your report, select the Publish to Web ribbon menu item. Here you will need to connect to your web server, select a folder to publish to and give a name for the report. Click publish and a message will appear when this has been completed.
Navigate in your browser to the XLCubed Web site. The repository panel on the left displays all published reports. Select your report to load it. The grids and slicers remain interactive and are data-connected.