Sage Intelligence Reporting Version 7.3 Beginner Exercises
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 1 of 22
Contents Welcome .................................................................................................................................................... 3 How to Use the Curriculum ..................................................................................................................... 3 Document Conventions .......................................................................................................................... 3 Sample Company Information ................................................................................................................ 3 Lesson Exercise 1: Overview of Sage Intelligence Reporting ..................................................................... 4 Lesson Exercise 2: Navigating within the Report Manager ......................................................................... 5 Lesson Exercise 4: Organizing Reports ...................................................................................................... 6 Lesson Exercise 5: Creating a Standard Report ......................................................................................... 8 Lesson Exercise 6: Saving Formatting Changes in Existing Reports ........................................................ 17 Lesson Exercise 7: Creating a Report That Includes Data From Multiple Reports .................................... 19 Lesson Exercise 9: Summarizing or Grouping Data in a Report ............................................................... 21
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 2 of 22
Welcome This book accompanies the Sage Intelligence Reporting Beginners Course manual and contains the exercises required to provide hands-on practice of the topics discussed in the lessons. How to Use the Curriculum In addition to this course been completed, an online assessment will be required to be passed in order to obtain your course certificate. The assessment can be found at www.sagealchemexacademy.com. Your login details will be provided to you on completion of the course. Document Conventions Sage Alchemex uses the Microsoft Manual of Style (MMOS), Third Edition, as its corporate authority for technical terminology and references to user interface elements as well as terms approved by the Sage Software’s Training Council or the CSC for references to specific training types, individual roles, certification terms, and specific elements of the curriculum. Sample Company Information The exercises have been created based on the sample company RKL Trading provided with Sage Intelligence Reporting software.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 3 of 22
Lesson Exercise 1: Overview of Sage Intelligence Reporting 1.
The Report Manager o Maintains the licenses installed for Sage Intelligence Reporting. o Provides an interface to create and modify reports. o Maintains the connectivity between Sage Intelligence Reporting and the accounting (or other) data
sources. o Controls the accessibility of Sage Intelligence Reporting reports by the various users.
2.
The Connector o Controls the accessibility of Sage Intelligence Reporting reports by the various users. o Maintains the licenses installed for Sage Intelligence Reporting. o Maintains the connectivity between Sage Intelligence Reporting and the accounting (or other) data
sources. o Provides an interface to create and modify reports.
3.
The Security Manager o Controls the accessibility of Sage Intelligence Reporting reports by the various users. o Maintains the licenses installed for Sage Intelligence Reporting. o Provides an interface to create and modify reports. o Maintains the connectivity between Sage Intelligence Reporting and the accounting (or other) data
sources.
4.
The License Manager o Controls the accessibility of Sage Intelligence Reporting reports by the various users. o Provides an interface to create and modify reports. o Maintains the connectivity between Sage Intelligence Reporting and the accounting (or other) data
sources. o Maintains the licenses installed for Sage Intelligence Reporting.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 4 of 22
Lesson Exercise 2: Navigating within the Report Manager Steps: 1.
Open the Sage Intelligence Reporting Report Manager.
2.
Expand the Demonstration folder. (Double-click)
3.
Click the Sales Details report.
4.
In the Properties window, click Show Advanced.
5.
Determine if Allow Report Viewer and External Access is clicked. Yes
6.
In the ribbon, on which tab is the option to Export Report. Help
7.
No
Home
Tools
Using the ribbon, launch the Help File. On the first page which appears, what is the last benefit listed as a benefit of using Sage Intelligence Reporting?
Extends Microsoft Excel skills rather than requiring learning of a new set of software skills
Empowers you thereby improving overall productivity
8.
Close the help file.
9.
Double-click on the Financial Reports Designer report. Name the second column listed in the columns list. Department
Parameters
AccountName
10. Using the right-click menu on the Demonstration folder. Name the second option on the menu. Delete
© 2014 Sage Intelligence Reporting
Add report
Rename
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 5 of 22
Lesson Exercise 4: Organizing Reports Objective: This exercise guides you through organizing reports within the Report Manager and includes adding a folder, copying a report, renaming a report and locking a report. Steps: Create a folder named Inventory Reports. 1.
Open the Report Manager.
2.
Click on Home on the Home tab (or right-click and then select Add Folder)..
3.
Click Add Folder.
4.
Type Inventory Reports and click OK. The folder appears in the list.
Copy the Stock Re-Order Levels report to the Inventory Reports folder. 1.
Click on the Stock Re-Order Levels report.
2.
On the Home tab, click Copy.
3.
Click on the Inventory Reports folder.
4.
On the Home tab, click Paste (or right-click and then click Paste). Your copied report is prefixed with Copy of followed by the original report name.
Rename the report to Stock Orders. 1.
Click the copied report
2.
On the Home tab click Rename (or right-click and then select Rename). This also gives you the option of renaming the template to match the report name.
3.
Type the name Stock Orders.
4.
Click OK.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 6 of 22
Lock the Report to prevent changes. 5.
Click on the report.
6.
On the Home tab, click Lock/Unlock.
7.
Enter an unlock password of 1234.
8.
Click OK.
9.
Confirm the password.
10. Type 1234 again. 11. Click OK.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 7 of 22
Lesson Exercise 5: Creating a Standard Report Objective: This exercise guides you through creating a standard report, adding filters and parameters, creating a basic PivotTable and saving the report template. Steps: 1.
Number the six basic steps in the right order to create a standard report:
Format the report ...................................... ________
Add a folder .............................................. ________
Define the properties of the report ............ ________
Save the Excel Template .......................... ________
Run the report........................................... ________
Add a report .............................................. ________
Add a folder. 1.
In Report Manager, create a Sales Reports folder.
Add a Report. 1.
Click the Sales Reports folder.
2.
Click Add Report.
3.
Click Standard Report and click OK.
4.
Type Commission Report for the name of the report, and click OK.
5.
Click Sales Details 2.0 (Demo) for the data container and click OK.
6.
Click the following fields to include in the report, then click OK:
CustomerName
Date
SalesPersonName
ProductCodeName
ProductID
TotalSale
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 8 of 22
7.
Click on the report named Commission Report, and observe the various tabs in the properties window. Notice, for example, the columns listed on the Column tab. (If you happen to be missing a column, or want to add another column, you can click the Add button and click additional columns.)
Define the properties of the report. 1.
Click on the Filters tab and click Add.
2.
Click SalesPersonName as a filter field.
3.
Click Equal To as the comparison method and click OK.
4.
Type DAVE as a comparison value.
5.
Click OK.
6.
Run the Commission Report report.
NOTE: The data in the report is only from transactions where Dave was the sales person.
7.
Close the report and return to the Report Manager.
8.
Navigate back to the Filters tab in the properties window of the report and Remove the filter we added.
9.
Click the Parameters tab and click Add.
10. To add a start date parameter, click Date in the Choose Filter Fields window and click OK. 11. Click Greater Than or Equal To as the comparison method and click OK.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 9 of 22
12. Leave the default for the Parameter blank. This will allow you to click the date at runtime. (You could also use a system variable as a parameter by clicking the @ button)
13. Click OK. 14. To add the end date, click Add. 15. Click Date as the Filter Field and click OK. 16. Click Less Than or Equal To as the comparison method (blank) and click OK. 17. You should now have the following two parameters.
Run the report. 1.
Run the Commission Report report. When prompted for the report parameters, enter the dates you’d like to run the report for. (example 1 September 2003 – 30 September 2003) The demo database RKL Trading has data for the years 2002 to 2003.
NOTE: Data for all sales persons were displayed but only between the dates you specified as parameters.
Format the Report. 1.
Click the Insert tab and click PivotTable in the Tables group.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 10 of 22
2.
The Create PivotTable window appears. In the text field for Table/Range, Press F3 and click the RawData range, or you can enter SourceData!Rawdata as the source of the data for the PivotTable.
3.
Select the location of the new PivotTable, usually Existing Worksheet, then browse to Sheet3 (or a blank worksheet), then click OK.
4.
Click OK. The following window will appear in Microsoft Excel.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 11 of 22
5.
Drag the CustomerName field to the Row Labels area in the PivotTable Field List.
6.
Drag the ProductCodeName field to the Row Labels area.
7.
Drag the SalesPersonName field to the Report Filter area.
8.
Drag the TotalSale field to the Values area. (remember to change the field value setting to sum if it is on count)
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 12 of 22
The resulting PivotTable will look like this:
9.
Right-click on the Sum of TotalSale field in the field list, and click Value Field Settings.
10. Click Number Format to change the format of the amounts showing in the PivotTable.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 13 of 22
11. In the list, click Number. Change Decimal places to 2 and click Use 1000 Separator.
12. Click OK. 13. Click in the Custom Name field, and change the name to Sale Amount.
14. Click OK. 15. Rename Sheet3 worksheet to Commission by SalesRep. © 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 14 of 22
Save the Excel Template in Report Manager: 1.
Return to the Report Manager, without closing Microsoft Excel.
2.
Click on the Commission Report in the Sales Reports folder.
3.
Click Save Excel Template.
4.
Click the correct workbook in the window, and click OK.
5.
Click Yes to allow the second worksheet to be cleared.
6.
(Click Yes to turn off the option to Save Data with Table Layout, as recommended, and click to overwrite the previous template.)
7.
In the Specify Template Name window, click OK.
8.
Upon completion, the following window appears stating that the template was created successfully and linked to the report. Click OK.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 15 of 22
9.
Go to Microsoft Excel and you’ll see the report is no longer open.
10. Return to the Report Manager and run the Commission Report using the default parameters. 11. Observe the worksheets, and the modifications made in this exercise. 12. Close Microsoft Excel without saving.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 16 of 22
Lesson Exercise 6: Saving Formatting Changes in Existing Reports Objective: This exercise guides you through saving report templates after making formatting changes in Microsoft Excel. 1.
In the Report Manager, under the Demonstration folder, copy the RKL Dashboard report.
2.
Run the Copy of RKL Dashboard report.
3.
In Microsoft Excel, click the Products Dashboard worksheet. This sheet uses Excel functionality to show the top 10 products. We will modify this to show top 5 products.
4.
Modify the title of the graph in row 30 to be TOP 5 Products.
5.
Unhide and click on the ChartData worksheet.
6.
In the second table TOP 10 Products, click the filter icon next to the ProductName field. Click the Value Filters option, then Top 10.
7.
Modify the filter to show the Top 5 Items by TotalSale. (Note that the “Items” means records or transactions, not inventory items.)
8.
Click OK, and you’ll see the grid change to reflect 5 customers.
9.
Click on the Products Dashboard worksheet and you’ll see the chart has changed to reflect 5 customers.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 17 of 22
Change the color of the chart. 1.
Click on the chart.
2.
On the Design tab, click Change colors.
3.
Click the colors of your choice.
Change the worksheet cell reference and save it: 1.
Click cell P23 and change the cell to refer to =IF(ISERROR(ChartData!E12),"",ChartData!E12)
2.
Return to the Report Manager, without closing Microsoft Excel.
3.
Click on the Copy of RKL Dashboard report.
4.
Click Save Excel Template.
5.
Click the Copy of RKL Dashboard workbook in the window, and click OK.
6.
Click Yes to allow the second worksheet to be cleared.
7.
Leave the template name as default and click OK.
8.
When it’s finished saving, a window appears stating that the template was created successfully and linked to the report. Click OK.
9.
If you go back to Microsoft Excel you’ll see the report is no longer open.
10. Return to the Report Manager and Run the Copy of RKL Dashboard report. 11. Look through the worksheets and you’ll see all of the changes you made in this exercise. 12. Close Microsoft Excel without saving.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 18 of 22
Lesson Exercise 7: Creating a report that includes data from multiple reports Objective: This exercise familiarizes you with creating a union report using two standard reports. Our demonstration data limits us so we won’t be able to show you the true potential of union reports but I’m sure you’ll get the general idea from doing this exercise. Create a union report. 1.
Open the Report Manager.
2.
Expand the Demonstration folder.
3.
Make a copy of the Sales Details Report.
4.
Paste it into the Sales folder.
5.
You’ll now have a report in the Sales folder named Copy of Sales Details. Remove the parameter from the Copy of Sales Details report.
6.
Right-click on the Sales folder and click Add Report.
7.
Click Union Report.
8.
Enter SalesReports for the report name.
9.
Click the Sales Details and Copy of Sales Details reports.
10. Click OK.
Change the order of the worksheets. 1.
Expand the new SalesReports report by double clicking, then click the Union Sub Report. In the properties window change Output Sheet Number text box, type 3.
2.
Click Apply.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 19 of 22
Hide the sub reports. 1.
From the object window, click the report.
2.
From the properties window, click Show Advanced.
3.
From the properties window, click Report Hidden.
4.
Click Apply.
5.
This report will not show in your list of available reports.
Run the report. 1.
Run the SalesReports report. Use the default date (1 September 2003).
2.
Review data in the various sheets (Sheet1 and Sheet3 will be populated with Raw Data/ Source data).
NOTE: The Copy of Sales Details report will include all transactions whereas the Sales Details report has a parameter so will only include transactions greater than 01 September 2003. 3.
Close the Microsoft Excel report without saving.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 20 of 22
Lesson Exercise 9: Summarizing or Grouping Data in a Report Objective: This exercise will revise previous concepts as well as familiarize you with how to add aggregate filters and, aggregate functions. Steps: 1.
In Report Manager, create a folder named Sales.
2.
Add a new Standard Report in the Sales folder.
3.
Name it Aggregate Sales.
4.
Click the Sales Details 2.0 container.
5.
Click the following fields for the report:
CustomerName
ProductName
ProductID
SalesPersonName
TotalSale
6.
On the Filters tab, add the filter: CustomerName Equal To SPICE POT.
7.
Copy the Aggregate Sales report and paste it in the Sales folder.
8.
Rename the copied report to Aggregate Function Sales.
9.
Copy the Aggregate Sales report again and paste it in the Sales folder.
10. Rename the copied report to Aggregate Filter Sales. 11. Click on the Aggregate Function Sales report. 12. Click on the Columns tab. 13. Right-click on TotalSale and click Apply Aggregate. 14. Select Sum and click OK. 15. Click on the Aggregate Filter Sales report. 16. Click on the Aggregate Filters tab. 17. Click Add.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 21 of 22
18. Click TotalSale. 19. Click Greater Than. 20. Type 1. 21. In the Sales folder, add a Union report. 22. Name it Aggregates. 23. Select the Aggregate Filter Sales, Aggregate Function Sales and Aggregate Sales reports. 24. Click OK. 25. Change the Aggregate Filter Sales report to output to worksheet 3. 26. Change the Aggregate Function Sales report to output to worksheet 4. 27. Run the Aggregates union report. 28. In worksheet 1 and 4 add a total to the TotalSale column.
Notice the same total appears for both, meaning that no transactions have been filtered out, but only summarized. In Worksheet 3 where we added an aggregate filter, only those transactions with more than one identical transaction appears. 29. Close Excel without saving.
© 2014 Sage Intelligence Reporting
Sage Intelligence 7.3 Reporting – Beginner Exercises
Page 22 of 22
