Report Examples within Excel

Since some users might face some difficulties by using PowerBI or they simply would like to create their report in Excel format, here we describe how to do this with concrete examples.

Load Data

1. Open the Microsoft Excel application to load data to a blank file.
2. Go to Data Tab -> Get Data (in the Get & Transform Data section).
3. Select From Database and then select "From SQL Server Database". See screen shot below:

   Figure 1: Get data into Excel file

4. Enter the Server name and the name of the database. You can find this information in the email with your access data. See screenshot below:

   Figure 2: Connect to SQL Server database from Excel file

   Note: Copy data from SQL Server since you do not have the option of Direct Query like in Power BI.

5. In the Navigator select all the tables you need to create your report and then click Load .

Only select the necessary tables to keep your file clear.

If you enter the name of the schema (for example processes.) in the search bar, only the tables of the regarding models are suggested. (Your access data sent in the email contains the name of the SQL Schema).

Note: There is also an option to check just the main table (here: TestCollection_Showcaseprocesses.fact_Processes) of a star schema and auto-select all related tables of this model. This option is on the bottom-left corner and is called Select Related Tables button. Make sure that you can select multiple items (see the check box on the upper left corner under search bar).

|![Connect to SQL Server Database](media/LoadODS2SchemaFromExcel.png)|
|:--:|
| <b>Figure 3: Select one of available star schemas</b>|

6. The selected star schema tables are loaded into Excel sheets and listed on the right side of the application under Queries & Connections .

   Figure 4: Loaded star schema tables within an Excel file

Create your first ODS2.0 report in Excel

Example 1

Creating a report in Excel is as easy as in Power BI. However, to project state of processes into a pie chart (for example) from our model, you need to create an intermediate level which is the pivot table to project the count of each state into the pivot table then create the required pie-chart using the following steps:

1. After loading data from SQL Server database into your Excel file, create a new worksheet.
2. Select the cell where you want to insert the pivot table. For this example, use Sheet1!$A$30 .
3. Go to Insert tab and click the PivotTable icon on the left side of Data ribbon.
4. Select the range from which to pivot data. For this example, choose the _State column from fact_processes table and the _CultureId column since this star schema has both English and German cultures. For further info, refer to the Star Schema Mode page.

   Figure 5: Create Pivot Table

   Description: In this figure the fact table was imported into 'TestCollectionShowcaseproces...' sheet (the name is truncated because Excel shortens the names of sheets automatically) and the range includes three columns from _CultureId (Column C), _Type (Column D) and _State (Column E).

5. Now select _CultureId and drag it into Columns list at the bottom. Then select _State and add it into Rows list, then make the values be as the count over the _State field (Count of _State). Finally insert a pie-chart from charts section in Insert ribbon and it will automatically set the chart for you. See Figure 6 below:

   Figure 6: Pie-chart referring to the count of processes based on their state

   Note: _CultureId is now displayed as a dropdown list so that you can choose the plot of English and German cultures separately.

Example 2

In this example, you want to plot the count of processes created each year since 2009. In this case you have to select the range that includes _CreatedOn Date within the fact_processes table and the Process field in the same fact table. Then in the pivot table you have to create a count over processes and drag the Years field into columns list. Finally insert a column-chart and it will automatically plot the results into a column-chart grafic. See Figure 7 below:

Figure 7: Plot count of processes created each year since 2009