In this blog post we will create a detailed monthly Income Statement Report in Smart View.

To begin, open a new workbook in Excel. Under the Smart View ribbon, click Panel then click “Connect” to sign in to the environment. After successfully logging in, connect to the right cube.

Right-click on the Plan cube. Select Connect. Right click on the Plan cube again and click on Ad hoc analysis. This will prompt the POV pop up window. Drag and drop dimensions from the POV to the columns. To create a detailed Income Statement report, pivot all dimensions except Account from the rows to the column.

Select the “Account” cell. Navigate to the Planning Ad Hoc ribbon and select Member Selection. Choose the right member you would like to use from the left panel and bring it across to the right panel. Make sure only one member is on the right panel. Click OK.

Repeat the steps above to define the members in the other dimensions. Every dimension must be defined in order to bring data across. The member name can also be directly typed in to the cell, but it has to be the exact member name.

After all dimensions have been defined, click Refresh in the Planning Ad Hoc ribbon. This will pull in the data located in this specific intersection.

Please note all data displayed in this blog post is sample data.

This sheet is a great starting point that may be used as a template for creating detailed Income Statement reports.

Using the template that was created, copy and paste the template into a new sheet. In this way Ad-Hoc analysis can be done without having to drag and drop dimensions members.

The new sheet must be reconnected to the environment. Navigate to the Smart View panel, right-click the Plan cube and connect. Right click on the Plan cube again and select Ad-hoc analysis. A pop up will appear. Choose “Reuse sheet contents and POV.” This will retain the defined members for each dimension.This will reconnect the new sheet back to the environment while maintaining the POV that was set up.

Once the sheet has been reconnected, navigate towards the Smart View ribbon, drill down through the Income Statement Hierarchy. This can be done by double clicking on Income Statement. Another method is to navigate to Planning Ad Hoc ribbon, click on Zoom In, and choose the desired level.

Now double click on the Period member “Total.” This will expand the Period hiearchary and give a detailed monthly Income Statement report.

All members of the Net Income hierarchy should be displayed on the sheet.

Some members return as “#Missing” or with “0” meaning there is no data at the given intersection. These members can be removed to clean up the sheet. Navigate to the Smart View ribbon and select “Options.” The Options pop up will appear. Click on “Data Options.” Under “Suppress Rows,” check “No Data/Missing” and click “OK.” Click “Refresh” and the intersections with “#Missing” or “0” will be removed. The Income Statement Detail sheet will only show members with data.

To create an Expense Report by Department, begin by using the monthly report as a template and reconnect to the environment.

Find the account member “Total Expense.” In the Planning Ad Hoc Ribbon, select “Keep Only.” This removes all other members.

Click on the cell with “Total Department.” Navigate to the Planning Ad Hoc ribbon and click “Pivot.” This will bring Department to the rows.

Repeat the step with “Total Expense” to move the Account dimension from the rows to the columns. Select “Total Department” again and double click. This will drill through the Department hierarchy.  You can now view this report by Department by Account.

Smart View is a powerful tool to create detailed Income Statement reports. Having a template will create a base when producing various types of reports.

Want to Learn More?

If you would like a free consultation please contact Gerard at Redhill Business Analytics via email or the contact form below.