Creating a SAP BusinessObjects Analysis workbook 

Now, let's learn how to create a SAP BusinessObjects Analysis workbook based on an existing SAP HANA calculation view. You can use an existing BEx query as well instead of a SAP HANA calculation view.

Here, I'll show the step-by-step procedure of creating a workbook using an existing SAP HANA calculation view and then I'll explain how to do it with a BEx query.

Follow these steps (we'll use the eFashion dataset and HANA view that we used in Chapter 1, Overview of SAP BusinessObject Business Intelligence 4.2. You can also refer to this link for more information on eFashion: https://wiki.scn.sap.com/wiki/display/BOBJ/eFashion+on+HANA

1. Start SAP BusinessObjects Analysis, the Microsoft Excel edition, and then, in the options, mark Preferred Platform as Selectable. This will allow us to choose either NetWeaver or the BI platform so that we can save our workbooks:
   
   1. Click on the Windows key on your keyboard and, on the top-right of your screen, search for Analysis for Microsoft Excel
   2. Create a new worksheet based on a blank template
   3. If the Preferred Platform setting isn't set to Selectable, then choose File and then Setting and select the Preferred Platform option and change it to Selectable

1. Insert the SAP HANA calculation view, AN_SALES, starting at cell A1:
   1. Click on Analysis | Insert | Select Data Source.
   2. It will ask for SAP BusinessObjects BI platform credentials; enter the details and log on, as displayed here:
      

1. 1. In the Select Data Source dialog box, set Show Connections to All and double-click the HANA_HTTP connection. Then, enter the necessary credentials, as displayed here:

1. 1. Choose the Area tab and select the cube radio button; expand the eFashion folder and double-click the view AN_SALES.
   2. The query results are displayed in cell A1. However, the view includes all Measures and no attributes:

1. As there was no attribute in the default layout from the SAP HANA view, we'll add STATE, STORE, and CATEGORY (keep the same order):
   1. Drag the STATE, STORE, and CATEGORY attributes from the DATA SOURCE pane into the ROWS pane:

1. 1. Select the cell containing the words: Store in the body of the workbook. Drag this to the left of the STATE cell to swap COLUMNS.

1. Remove all of Measures from the reports and just keep AMOUNT_SOLD and QUALITY_SOLD: 
   1. In the COLUMNS panel, select the Measures node and then right-click on COLOUR_CODE and select Remove:

1. 1. Follow the same steps to remove all of the other Measures
   2. Drag AMOUNT_SOLD under QUALITY_SOLD to change the order of Measures:

1. Restore the initial order of Measures and characteristics:
   1. Before we  complete this step, we need to make sure that the report isn't getting refreshed after each action, so we need to choose Pause Refresh in the Analysis tab.

1. 1. In the Measures panel, change the order of the measure again. Make sure that the display in the spreadsheet doesn't change.
   2. In the ROWS panel, change the order of STATE | STORE. Again, make sure the display in the spreadsheet doesn't change.
   3. Choose Pause Refresh again.
   4. All changes should be reflected in the spreadsheet.
2. Remove the STORE attribute from ROWS and define the filters setting for STATE as follows: 
   1. Display California and Florida:
   2. In the ROWS panel, right-click the STORE attribute and choose Remove.
   3. In Analysis, go to A1 | Filter | Filter By Member:

1. 1. In the STATE | Filter By Member dialog box, deselect the Select All checkbox.
   2. Select the California and Florida checkboxes and choose OK.
   3. To display all again, right-click the STATE attribute in the ROWS panel and choose Select All members:

1. 1. To display all except California, you can select the cell containing the word California and position your cursor on the edge of this cell so that the crosslines icon appears. Then, drag it to the blank cell area of the workbook to remove it.

1. In our AN_SALES view, we have a defined level hierarchy for the articles that are organized into categories and families. Therefore, we'll have to remove CATEGORY from ROWS. To do so, we follow these steps: 
   1. Go to DATA SOURCE and then ARTICLE:

1. 1. In the ROWS panel, right-click STATE and choose Remove
   2. Drag HIER_FAM_CAT_ARTICLE to the ROWS pane
   3. In the workbook, expand the + icon under the ARTICLE column several times to navigate and expand the hierarchy

1. (Optional) you can also create a restricted measure on the fly from BusinessObjects Analysis. Let's see how can we do this: 
   1. Choose the cell that's called, AMOUNT_SOLD.
   2. Choose Calculations | Add Restriction:

1. 1. Enter the name, Restricted Sales_YYYY (or a name that you want to enter).
   2. In the drop-down list, select AMOUNT_SOLD. In the selections box, use the drop-down list and choose the YR dimension:

1. 1. Choose Open Value Help, select the year YYYY, deselect all other checkboxes, and click OK:

1. 1. The new column is added and now we can add the measure into the DATA SOURCE panel by expanding the measure node and dragging the year Restricted Sales_YYYY into the COLUMNS panel:

1. Save the view by selecting Save View and give it a name, Analysis_view_HANA.
2. Save the workbook in the BusinessObjects platform, choose File | Analysis, and save the workbook to a platform. Save the workbook to the SAP BusinessObjects BI platform:

1. Reopen and refresh the workbook:
   1. Choose File | Analysis. Open the workbook from the platform
   2. Choose the Analysis_view_HANA view that we saved previously
   3. In Analysis, choose Refresh All

With these steps, we have created an SAP BusinessObjects Analysis workbook based on an existing SAP HANA calculation view. When you click on Refresh All, it'll update the corresponding data and redraw the crosstab with the newly created workbook based on the SAP HANA calculation view.