Alio Intelligence Advanced Session 1

1. Alio IntelligenceAdvanced Session 1 Kathleen Alexander

2. Agenda • User Controls (Filter Bar, Input Controls, Element Linking, Outline, Freeze) • Groupings (Sections, Breaks) • Sorting (Simple Sorting, Custom Sorting) • Ranking • Linking (Document Linking, Website Linking) • Graphs • Subqueries

3. Create a Simple Query

4. Create a Simple Query • First, let’s create a query… • From the Alio Human Resources universe, drag the following over to the Result Objects section of the Query Panel • From Employees folder: Employee Name; Employee No; Employment Status • From Employees> Dates folder: Hire Date • From Employees > Jobs folder: Contract Year; Job Code Description; Primary Job Flag • From Employees > Jobs > Pays > Actual FTE; Contract Salary • Select Run Query to view the results • We will be using multiple tabs and the look of the table does not really matter at this time – this is just so you can play along if you’d like….

5. Create a Simple Query

6. User Controls

7. User Controls • Filter Bar • Can be added and used by end users in Reading Mode • Input Controls • Report designer adds them, but end users can interact with them in Reading mode • Element Linking • Report designer adds them, but end users can interact with them in Reading mode • Outline • Can be added and used by end users in Reading Mode • Freeze • Added only in Reading Mode

8. Filter Bar • Allows user to interact with the report while in Reading mode • End users may also be able to add Filter Bars themselves • Easy to add and maintain • Simple ‘equal to’ filter • Had give the user a quick view of the records on the report • Can add multiple filters across the top report • Automatically filters the entire report tab

9. Filter Bar • Show the Filter Bar • In Design mode, select Filter Bar under Analysis, Interact tab • In Reading mode, select Filter Bar at the top of the report viewer • Select icon to add the object you want to appear in the Filter Bar • Only Dimensions and Detail Objects may be selected • Once the object is added to the Filter Bar, use the pulldown to the right of Contract Year (All values) to see the list of values • The label will always match the object name. Can also add Variables that are not measures to the Filter Bar • Select data to filter the report • To stop filtering, select Contract Year (All Values) again • To remove the filter completely from the report, select (Remove)

10. Filter Bar • Report filters based on the selection

11. Input Controls • Many options to filter data in reports • Allows the end user to interact with the report while in Reading mode without refreshing the data • Can be used with dimensions, measures, details, variables, and report blocks • Can be used to change variable values • Can filter specific blocks within a report tab

12. Input Controls • While in Design mode, select the icon to the far left of the report to open the Input Controls panel • Select • Select the report object on which you want to filter and click Next > • In our example, we will choose Contract Year • Dimensions, Detail and Measure objects may be used for Input Controls • Some Input Control types can only be used with certain object types

13. Input Controls • Choose the Control Type • Simple Selections allow you to filter on one item • Multiple Selections allow you to filter on multiple items at once • For our example, select Combo Box under Simple Selection section • Leave Input Control Properties with their default values • Depending on the Control Type choses, there are different Input Control Properties that can be used to customize the filter • Click Next >

14. Input Controls • By default, the entire report tab will be checked in the Assign Report Elements pane • You may choose to only filer on one block for instance • In SAP BI 4.2, there is an option to add the Input Control for the entire document • Leave all checkboxes checked • Select Finish

15. Input Controls • Select a value to filter the report • Select All Values to stop the filtering • More tips • Select the wrench icon to make changes to the Input Control properties • Select the x icon to remove the Input Control from the Input Controls pane • Input Controls can be moved up and down in the Input Controls pane

16. Input Controls – Simple Selection Data on the report is filtered based on Input Control Selections Entry Field Combo Box Radio Buttons List Box Calendar Spinner Simple slider

17. Input Controls – Multiple Selections Check Box List Box Double Slider

18. Input Controls – What If Scenarios • Add a Variable called “Original Salary” with the following formula • On a new report tab, add a new horizontal table with columns for Job Code Description and Contract Salary • Change the Header of Contract Salary to “Original Salary” • Add 3 new variables • Increase % • Qualification: Dimension • Formula: =100 • Salary Increase • Qualification: Measure • Formula: =[Contract Salary]*([Increase %]/100) • New Salary • Qualification: Measure • Formula: =[Salary Increase]+[Total Contract Salary]

19. Input Controls – What If Scenarios • Add Salary Increase and New Salary to new columns on the table • Filter the report tab to only one Contract Year using the Filter Bar or a simple Input Control • Add a Simple Slider Input Control for Increase % • Set the properties as in the screenshot • Click Finish • Use the new Simple Slider Input Control to change the Salary Increase and the New Salary based on the Increase % chosen

20. Input Controls – What If Scenarios • More Tips • Change the header cell for the Salary Increase column to reflect the Increase % chosen with this formula: =[Increase %]+"% Increase“ • Add Graphs to the report that can also be filtered by Input Controls

21. Input Controls • New to SAP BI 4.2: Grouping of Input Controls • For example, could add Job Code Description and Employee Name to an Input Control Group; when choosing Job Code Description “203 DAY SECRETARY’, only Contract Years that exist for that Job Code Description on the report appear in the Contract Year selection options – similar to a cascading prompt No Group With Group 2008, 2014-2016, and 2026 do not appear when grouped All Contract Years from the report query appear

22. Element Linking • Filters a report element based on a selection from a separate report element • Can be set up on tables or Graphs • Can filter specific blocks within a report tab

23. Element Linking • For our example, add a new report tab to the existing document • Right click on a report tab at the bottom of report and choose Add Report • Add at least two report elements to a report tab • Table 1 • Add a two column table for Contract Year and Employee Count • Drag Contract Year to the Contract Year column • For the Employee Count column, use the following formula • =Count([Employee No]) • Add a Sum to the Employee Count column • Rightclick on the table and choose Format Table… and change the Name on the General pane to “Filter”. Click OK • This is not required but it helps to keep everything organized

24. Element Linking • Table 2 • Add a new table to the report tab • Add Contract Year, Employee No, Employee Name, Job Code Description, Actual FTE,and Contract Salary to the table columns • Right click on the second table and choose Format Table… and change the Name on the General pane to “Details” – click Apply • Go to the Layout section on the left of the Format Table pane and set the Position as follows then Click OK

25. Element Linking • Set up the Element Linking • Right click on any value in the Contract Year column of the Filter table (Table 1) • Select Linking > Add Element Link… • Select Contract Year and click Next > • Click Next > again • You could also rename the block\table using the Name field here before clicking Next > if it had not been done earlier • Check Detailsunder Assign Report Elements and click Finish

26. Element Linking • Now select a row in the Filter block (Table 1) and see how the Details block (Table 2) filters to that Contract Year Additional tips: • In the top right of the Filter table, right click on the icon to Edit, Highlight Dependencies, Reset, Disable or Remove the Element Linking on the table • Can also go to the Input Controls pane to Clear Filters

27. Element Linking To learn more about Element Linking… Attend the Interactive Dashboard in Reports session coming up next!

28. Outline • The Outline function can be used to hide specific elements of a report • The Outline function can be used in conjunction with • Sections • Breaks • Entire Blocks/Tables

29. Outline • Lets go back again to the original query we used for User controls way back at the beginning (page 3)…

30. Outline • Start with a table that looks like this • Now add a Break on Job Code Description with totals on the Actual FTE and the Contract Salary • Right click on a Job Code Description value and choose Break > Add Break • Right click on the Actual FTE column and choose Insert > Sum. Do the same for Contract Salary

31. Outline • In Design mode, select from the Analysis, Interact tool bar • In Reading mode, select from the top • Now, when you select an item within the table, an Outline area will appear on the left of the table

32. Outline • In our case, there is a level for the total and a level for the single Break we are using • Select to fold the section • Select to unfold a section previously folded • When you click away from the table, the Outline area will disappear • Click on the table again to see the Outline area again

33. Outline • This is how it would look if used with a Section instead of a Break • There is a column for each Section • Can also be used for table with no Breaks or Sections • Only the first level column would appear • Could have multiple tables on the same report tab folded and unfolded differently from each other

34. Freeze • In Reading Mode only: select the table and click Freeze buttons – can select the Header Rows and up to the first 5 columns on the left Tip: Freeze is only available in Quick Page view which can be changed at the bottom right of the report

35. Groupings

36. Groupings • Sections • Sections can be added to a report • Can have more than one section in a table • Can have a section on a table that already had a Break • Breaks • Breaks are great for subtotals and grand totals • Can have many Breaks within a table • Breaks can look better than Sections when exported to Excel • Can wrap a table including a Break within a Section

37. Groupings • Lets go back again to the original query we used for User Controls back at the beginning (page 3)…

38. Groupings • We’ll begin with a simple table like this

39. Sections • We are going to create a Section on Contract Year • Right click on any data in the Contract Year column and select Set as section • Format the Contract Year cell that appears however you would like • To create an additional section below Contract Year, right click on any data in the Job Code Description column and select Set as section again Tip: Sometimes it’s helpful to go to Design > Structure only at the top right of the report when you want to arrange data within multiple sections

40. Breaks • From the original table, right click on any data in the Contract Year column and select Break > Add Break • To create an additional section below Contract Year, right click on any data in the Job Code Description column and select Break > Add Break Tip: This can also be done by going to Analysis, Display tab and selecting Break • Optionally add totals to the Actual FTE and the Contract Salary • Right click on data in each column and select Insert > Sum • Totals will automatically appear in the footer of each Break, and in footer for grand totals at the end of the table

41. Breaks • Go to Manage Breaks… under Analysis, Display tab then the pulldown beside Break to see more formatting options • Can do the same by right clicking on any data value in the table and selecting Break > Manage Breaks…

42. Sorting

43. Sorting • Sorting can be done for any report element containing more than one value • Sections, within Breaks, any values within Tables and Graphs • Can have multiple Sorts • Can create custom Sorts

44. Simple Sorting • When a query is run for the first time, the new table is automatically sorted by the first column’s data • To quickly sort by a value in a column, select and choose Sort on the Analysis, Display tab • The first item sorted remains the first Sort unless you remove the Sort, or move it up or down in Sort > Advanced… • To sort in descending order, select Descending in the pulldown beside Sort • All Sort options are also available when you Right click on a value and select Sort Advanced pane

45. Custom Sorting • Go to Advanced… under Sort • Select the object you want to have a Custom Sort • Under Custom Order: section, select Values… • Use the up and down Arrows to made the values sort the way you want • In our example, we’ll move 2016 to the top Tip: a new value may also be added to the Custom Sort if it does not currently appear on the report • Click OK in the Custom Sort, and click OK

46. Custom Sorting • Notice how Contract Year 2016 falls before 2008 after the Custom Sort was applied Tip: By default in the CMC, there is a maximum sort size of 100 entries for Custom Sorting so, in this example, there are 199 Job Code Descriptions. If we would try to apply a Custom Sort on Job Code Descriptions we would receive an error. 2016 comes before 2008; then 2009 is next

47. Ranking

48. Ranking • Ranking allows you to show only the top and/or bottom records in a table • Ranks are based on measure objects • Rankings can use a count or a percent; or use a cumulative sum or percent

49. Ranking • For our examples, add a table to a new report tab using the existing query • Add Contract Year, Job Code Description, and Actual FTE to the table • Add a filter for the Contract Year and filter to the most recent year • Can add a report filter, filter bar filter or an Input Control

50. Ranking • Select the table or a column that you want to use for the ranking and select Ranking from the Analysis, Filters tab • Fill in the Ranking pane that appears