Creating Reports in Web Edition
Connecting to the XLCubed web server in a browser window shows a display similar as described here.
To create a new report in Web edition, click on Adhoc Grid and you will see the following display:
You will see a list of the servers, databases and cubes to which you have connected previously. To create a new connection, enter the name of the server in the lower half of the window and then click connect. You will then be presented with a full list of databases on that server. Select a database and on the right-hand side you will see the cubes on that database to which you have access.
Creating a report
This example uses the Adventure Works cube on the Adventure Works Demo database.
After connecting, select a cube and you will be presented with the following screen (the Report Designer screen):
The Report Designer is separated into 5 main areas:
- Available Hierarchies
This lists the available dimensions and hierarchies within the active cube connection. Expand the dimension to view the hierarchies which it contains. Hierarchies can be dragged from this area into Headers, Rows or Columns to be used within the report.
The Header area, (or filter area), provides a set of hierarchies for user selections, which will be displayed at the top of the grid. User selections made within hierarchies in the Header area of the grid will determine the numbers displayed in the report body.
The Rows area is a holder for hierarchies to be positioned on rows in the reports
The Columns area is a holder for hierarchies to be positioned on columns in the reports
- Member Selector
This displays the content of the selected hierarchy. The hierarchy tree can be expanded and members are selected by dragging to the right. Selections can also be based on Excel cells, Slicers or specified as related sets of data (children / descendants etc). This is covered in detail in the Member Selector Selection
Drag items from the Available Hierarchies list to the Headers, Columns and Rows. Next, double-click each member and in the right-hand area of the window (the Member Selector area) you can drill-down to select the member to display. In this example I have double-clicked on Measures. This then expanded in the Member Selector Area allowing me to select the measure, Reseller Sales Amount. I can double-click or drag across to the right-hand side to select.
You can use 'SHIFT and click' or 'CTRL and click' as normal to multi-select members.
Having positioned and made selections within the hierarchies as required, press ‘OK’ to insert the report.
Double-click on entries in the grid to toggle between drill-down to expand and drill-up again. In the example above double-clicking on Australia will expand to show the rows beneath (at state level). Double-clicking on Australia again will return to just showing one row for Australia.
You can change the layout of the report by clicking and dragging the hierarchy labels. For example, in the above report you can drag Geography from rows to columns or drag it to the Headers area. You can do this with any of the hierarchies being displayed.
This report shows sales of products model categories across sales territories. Right-click on the grid and you will see a menu as below:
- Grid Properties: Behaviour, Appearance, Mdx
These three tabs allow you to change certain aspects of the grid report.
- Design Grid
This takes you into Report Designer as detailed previously.
- Number format
This allows you to format the cell values.
This option allows you to take a subset of the grid report and display in more detail. In this example we can set up the breakout options as below:
This result of the breakout shows the 10 highest models in the Product Model Categories:
- Apply: Sort, Rank, Filter
This option allows you to sort (ascending or descending), rank or filter the grid report.
The screenshot below shows the above report sorted in ascending order on the measure Reseller Sales Amount.
- New Chart: All
By selecting New Chart > All, you can quickly create a chart of the grid report – see example below.