Define a Report in Smart View with the Query Designer

This example shows how to define a report using the Query Designer in Smart View.

Defining a Report with the Query Designer

1. Open Excel and navigate to the Smart View ribbon. Click Panel.

2. Click Shared Connections.

3. Sign in with your user name and password.

4. Click the Select Server to proceed drop-down list.

5. Select Oracle Essbase.

6. Expand the Essbase_FA_Cluster and expand USChartofAccounts.

7. Highlight db. Don't expand db.

8. Click Connect.

9. Click the analysis link.

10. Navigate to the Essbase ribbon and click Point of View.

11. In the spreadsheet, click cell A2.

12. On the Essbase ribbon, click Query and select Query Designer. A new sheet named, Sheet1-Query is created and the Query Designer displays.

13. On the Point of View panel, move:

    1. Account to the Rows section.

    2. Accounting Period to the Columns section.

    3. Ledger to the Point of View section.

    4. Company to the Rows section, as the first dimension.

    Note: You can also select the dimensions cells on the worksheet and use the Pivot command on the Essbase ribbon to perform these steps.

14. Open the Amount Type dimension by clicking the drop-down list and selecting the Member Selection link. Remove the default placeholder using the Remove icon. Select the period-to-date value and add it to the panel of selected values using the Add icon.

15. On the Member Selection window, click the drop-down list next to the Amount Type dimension to toggle to each Point of View dimension. Complete the fields, as shown in this table.

    Field	Value
    Ledger	US Ledger Set > [US Ledger Set].[Us Primary Ledger]
    Line of Business	All Line of Business Values
    Cost Center	All Cost Center Values
    Product	All Product Values
    Intercompany	All Intercompany Values
    Scenario	Actual
    Balance Amount	Period Activity
    Currency	USD
    Currency Type	Total

16. From the POV Sheet1-Query_1 panel, click AccountingPeriod to open the Member Selection window. You can also open the Member Selection window from the worksheet by right-clicking the AccountingPeriod cell, and selecting Smart View, Member Selection.

17. Remove the default selection. Expand 2016. Select all four quarters and add the quarters to the selected panel.

18. Click OK.

    Note: The four quarters become separate columns on the worksheet.

19. Click the Company dimension.

20. Expand All Company Values.

21. Select [All Company Values].[101] and [Company]@[941] and add them. Remove the default value.

22. Click OK.

23. On the POV Sheet1-Query_1 panel, click Account in the Rows section.

24. Expand All Corporate Accounts-V1 > Account@[T] > 95001 - Net Income Current Year.

25. Expand 95011-Revenue and 95021-Expenses.

26. Remove the default value. Select the following values and add them to the selected panel:

    • 95011-Revenue

    • 40000-Revenues

    • 95021-Expenses

    • 50000-Material and Goods

    • 60000-Operating Expenses

    • 70000-Extra Charges and Tax

27. Click OK.

28. On the POV Sheet1-Query_1 panel, click Apply Query.

29. Save your report to the desktop as your initials Smart View Financial Report.

30. Insert 7 rows at the beginning of the spreadsheet.

31. Click in cell A3. Click the Insert tab and select Picture.

32. Select WINNT (D) > Labs > Fin_reporting > Vision Logo.

33. Click Refresh.

    Note: You must refresh periodically to maintain the connection to the database.

34. From the Insert tab, click Text Box.

35. Click cell C4 and type your initials Income Statement for Companies 101 and 941. Use excel formatting to format the text however you want.

36. Click Refresh.

37. Click Save.

38. Highlight all the cells that contain amounts.

39. Navigate to the Home ribbon, and from the Number section, select dollar sign.

40. On the Home ribbon, in the Cells section, select Format > AutoFit Column Width.

41. Navigate to the Essbase ribbon and select Preserve Format.

42. Click Refresh.

43. Click Save.

44. Insert three rows between the data for company 101 and 941.

45. On the first new blank row in Column B, type Total Income.

46. Add the following formula for the first column with data Qtr1-14: =C10+C15 where C10 is 95001-Revenue and C15 is 95021-Expenses.

47. Copy and paste that formula to the other columns.

48. Do the same formula for totals of Company 941.

49. Format all the total cells as Accounting. On the Essbase ribbon, select Preserve Format.

50. Click Save.

51. Insert 10 more rows between the data for Company 101 and 941.

52. Highlight all four rows of the expense data for all four quarters, including Material and Goods.

53. Navigate to the Insert ribbon and select the Line chart from the Chart section.

54. Select the first 2-D Line chart layout.

55. On the line chart, click the Legend that reads Series1, Series2, Series3, and Series4.

56. From the Chart Tools ribbon, select Select Data.

57. In the Legend Entries (Series) pane, select Series2 and click Remove. Series2 is a total of expenses and not appropriate for this chart.

58. In the Legend Entries (Series) pane, select each series individually and click Edit. Complete the series and names fields, as shown in this table.

    Series	Name
    Series1	Material and Goods
    Series3	Operating Expenses
    Series4	Extra Charges and Tax

59. In the Horizontal (Category) Axis Labels panel click the first one and click Edit.

60. Click the icon next to the Axis label range: field.

61. On the spreadsheet, highlight the four quarter headers and click the icon next to the Axis Labels field.

62. Click OK twice.

63. Move and resize the line chart to fit inside the blank lines.

64. Click Save.

65. Navigate to: Financial Reporting Center > Tasks panel tab > Open Workspace for Financial Reports.

66. Click Navigate > Applications > BI Catalog.

67. Expand Shared Folders > Custom > highlight Financial Reports.

68. From the Tasks panel, select Upload.

69. Click Browse and from your Desktop, select your Smart View Financial Report.

70. Click Open.

71. Click OK.

72. Close the EPM Workspace tab.

73. From the Financial Reporting Center, click the Search icon.

74. Enter Smart and click Search.

    Note: Your report may not be found immediately. Wait a few minutes and try again.