Business Objects BOBJ Introduction to Ad Hoc Reporting

Business Objects (BOBJ) Introduction to Ad Hoc Reporting February 26 -27, 2020 1

AGENDA • People First - Reports Tile • BOBJ Landing Page • BOBJ Applet Error • BOBJ Home Tab • BOBJ Documents Tab • BOBJ Launching Reporting Tool • Query Panel • Universe • Query Elements • Query Filters • Report Results • Report Toolbars • Column Header • Report Columns 2

AGENDA (Continued) • Filter and Sort • Calculations • Formulas and Variables • Dimensions and Measures • Cell and Column Formatting • Number and Date Formatting • Sections • Multiple Tabs • Conditional Formatting • Ranking • Share Report • Merge Dimensions • SQL Script • Schedule Report • Resources 3

PEOPLE FIRST – REPORTS TILE 4

PEOPLE FIRST – REPORTS TILE (Continued) My Reports Section • Access pre-built data warehouse and SAP (real-time) reports based on the user’s People First security role code. • Data warehouse pre-built reports are identified by an asterisk (*) at the end of the report title and contain data as of the previous day (not real-time). • Data Warehouse pre-built reports are launched using BOBJ and cannot be modified; these reports can only be exported (e. g. , Excel, PDF). Recent Reports Section • Shows the last 10 reports accessed. • Quick link to access and reprocess the report. This does not save your previous report results. 5

PEOPLE FIRST – REPORTS TILE (Continued) Resources Section • How to Run Reports in People First – Link to a video on how to run pre-built reports. • Reports Instructional Guide – Link to an instructional guide on how to run pre-built reports. • Data Warehouse Status – Visual indicator as to whether the nightly data warehouse refresh has been completed. The refresh is normally completed by 8 a. m. each day. o The “Data Warehouse Status” text displayed next to the DW status indicator is a link, and when selected will display the individual DW view names and date/time last updated to allow users to easily identify the view(s) that have not updated when the DW status indicator is a red “X”. o When a green checkmark is presented, the nightly data refresh has completed and data contained within the pre-built reports is updated. This means you can run data warehouse reports. o When a red “X” is presented, then the nightly data refresh has not completed and you should not run data warehouse reports (wait for the green checkmark), including pre-built and ad hoc reports. • Ad Hoc Reporting – Link to access BOBJ reporting tool to create ad hoc reports. 6

BOBJ – LANDING PAGE 7

BOBJ – APPLET ERROR • Applet Error – Occasionally, ad hoc report users may get the following error the first time they access BOBJ. If this happens, follow the steps below to resolve the issue. Resolution: • Click on Preferences in the menu header (right top hand corner) on BOBJ landing page. • In Web Intelligence section, under Modify (creating, editing and analyzing documents) change "Applet" to "HTML“. • Reload the page and check that now you are now able create a BOBJ report. 8

BOBJ – APPLET ERROR (Continued) 9

BOBJ – HOME TAB • When the Ad Hoc Reporting link is selected, the BOBJ landing page is presented. There are two tabs (Home Tab and Documents Tab) on the page that are used to navigate within BOBJ. • Home Tab – Contains the following sections: o My Recently Viewed Documents – Displays the last 10 ad hoc BOBJ reports viewed. o My Recently Run Documents – Displays reports that have been scheduled; hover the report name to see the date and status. o My Inbox – Displays reports that have been sent to the ad hoc user through BOBJ. The option to send reports to an outside email (e. g. , Outlook) is not currently available. o Unread Alerts – This option is not currently used. o My Applications – BOBJ reporting tool. 10

BOBJ – DOCUMENTS TAB • Documents Tab – Contains the following sections: • My Documents o My Favorites – User’s own folder where folders/subfolders can be created and ad hoc reports are saved. Only the user can access this folder. o Inbox – BOBJ Inbox where ad hoc users can retrieve reports that have been sent to them through BOBJ. • Folders o Ad Hoc Reports – Each agency has an agency folder that all ad hoc users within that agency can access. This is where a user can save a report to allow other agency users to access it. All agency ad hoc users can delete reports saved in the agency folder. It’s recommended that if you save reports to the agency folder that you also save a copy of the report to you own folder. o Data Warehouse Reports – The data warehouse pre-built reports can be accessed within BOBJ; same pre-built Data Warehouse reports that are available from the Reports tile. Note: SAP reports can only be accessed from the Reports Tile. 11

BOBJ – LAUNCHING REPORTING TOOL Step 1: Select Ad Hoc Reporting Link on Reports Tile Step 2: Select BOBJ Reporting Tool on BOBJ Home Tab Step 3: Select BOBJ Universe* *The BOBJ Universe is the data source used for compiling reports. 12

BOBJ – QUERY PANEL 13

BOBJ – UNIVERSE • Ad Hoc Universe (Universe) – The Universe is where People First data is stored and is organized by individual folders (views). The data is used to create ad hoc reports. o Folders can be expanded and collapsed to view the sub-folders and/or data fields contained within the folder. Do not double click on folders. Use the plus symbol to expand the folder. If you double click the folder, all data fields in the folder will be added in the Result Objects section. o Data fields can be added to the Result Objects section (query) by double clicking on the item or by clicking and dragging the item to the Result Objects section. o Data fields are searchable. o Data fields within a folder can be joined with other data fields in another folder(s), unless the folder is labeled as “Independent”. Data fields in folders labeled as “Independent” cannot be joined with any data fields in another folder. If you attempt to join fields in a folder with fields in another folder labeled “Independent”, the error “The query cannot run because it contains objects that reference incompatible tables. ” will be presented. 14

BOBJ – UNIVERSE (Continued) Main Universe Folders (Views) – There are two folders in the Universe that are utilized the most; DWSOF All Active and Position Active. Both of the folders are located within the DWSOF All Active folder. DWSOF All Active Contains employee and position data for filled positions. Position Active Contains position data for filled and vacant positions 15

BOBJ – QUERY ELEMENTS • Result Objects – Data fields that have been selected to be included in the report. • Query Filters – Filters that have been applied to the query. • Data Preview – A way to preview the data after the query has been executed. Only displays partial data, user accessed the report to view all data. 16

BOBJ – QUERY FILTERS • Query Filter – Used at the query level to limit data presented in report and allows you to restrict the information returned. o Equal to – Limits the data to only data that is equal to the value entered in the filter. o Not Equal to – Limits the data to only data does not equal the value entered in the filter. o In List – This is the query filter default. It limits the data to only data that matches the value entered in the filter. o Not In List – Limits the data to only data that is not in the list entered in the filter. 17

BOBJ – QUERY FILTERS (Continued) o Between – Limits the data to only data that falls between the value entered in the filter. o Not Between – Limits the data to only data that does not fall between the value entered in the filter. o Greater than – Limits the data to only data this is greater than the value entered in the filter. o Greater than or Equal to – Limits the data to only data that is greater than or equal to the value entered in the filter. o Less than – Limits the data to only data that is less than the value entered in the filter. 18

BOBJ – QUERY FILTERS (Continued) o Less than or Equal to – Limits the data to only data this is less than or equal to the value entered in the filter. o Matches Pattern – Wildcard query to search for partial values. Use the percent character % at the beginning or the end as the wildcard. o Different from Pattern – Wildcard query to search for partial values. Use the percent character % at the beginning or the end as the wildcard. o Is Null – Limits the records to only those that have a NULL value for the specified data. o Is Not Null – Limits the records to only those that do not have a NULL value for the specified data. 19

BOBJ – QUERY FILTERS (Continued) • Filter Types o Constant – This is the filter type default. It allows you to type in a desired value. o Value(s) from List – Retrieve a list of values to select from. o Prompt – Use to prompt the user to enter a value each time the report data is refreshed. 20

BOBJ – REPORT RESULTS • Reading Mode – Reading mode is view only. The user can refresh, save and print a report but there is no access to make modifications to the report. All data warehouse pre-built reports are in Reading mode and cannot be modified. • Design Mode – Design mode allows the user to modify the report. This is the default view when an ad hoc report is created. When a previously created ad hoc report is opened, it defaults to Reading mode and must be changed to Design mode to modify the report. 21

BOBJ – REPORT RESULTS (Continued) • Report Results – This is the query results and is the ad hoc report. • Available Objects Section – This section displays data fields, variables, etc. that are selected from the Universe and are available for use in the ad hoc report. o The Available Objects section is not displayed when an existing report is opened. To view the Available Objects section, select Design mode and then select the Available Objects icon (fifth tab) from the display panel. • Adding Data Fields – After a report is run for the first time, any new data fields added to the query will be shown in the Available Objects section. The new data fields do not automatically appear on the report, they have to be manually added. Data fields are added by dragging the field from the Available Objects section to 22 the report.

BOBJ – REPORT TOOLBARS • Edit Query – Edit query to add data fields, edit filters, add variables, etc. From within the report, click on Edit Data Provider. • Save – Save report to My Favorites or Agency Ad Hoc Report folder. o When saving a report, there is an option to select “Refresh On Open” (by default it is not selected). By selecting this option, the data within the report is automatically refreshed when the report is opened. o When the “Refresh On Open” option is not selected the data does not refresh when the report it opened, which means the data in the report is not current, and is at that point in time the report was last saved. The last time the data was refreshed is shown in the bottom right-hand corner of the report screen. 23

BOBJ – REPORT TOOLBARS (Continued) • Refresh – Select to refresh data in the report. This can be used to retrieve updated report data if the report was saved without selecting the “Refresh on Open” option. • Export – Report can be exported in PDF, Excel, CSV or text formats. • Undo/Redo – Undo/Redo previous action. 24

BOBJ – REPORT TOOLBARS (Continued) • Most report functions (e. g. , format, filters, sort) used with the BOBJ tool are accessed from the various toolbars. Report Elements Tab Formatting Tab Data Access Tab 25

BOBJ – REPORT TOOLBARS (Continued) Analysis Tab Page Setup Tab 26

BOBJ – COLUMN HEADER • Column Header – Use the following steps to repeat column header on each page of the report. Click on a cell in the column header of the report before beginning the steps. Step 1: Select Report Elements Tab Step 2: Select Behaviors Tab Step 3: Select Repeat Dropdown Step 4: Select Header on every page 27

BOBJ – REPORT COLUMNS • Rename Column – To rename a column, double click in the column cell and type the updated column name and hit enter on your keyboard. • Move Column – To move a column, click on a cell within the column and drag to the desired location. Release the column once the “Drop here to insert cell” message is displayed. • Swap Column – To swap a column, click on a cell within the column and drag to the desired location. Release the column once the “Drop here to swap columns or rows” message is displayed. • Add Column – To add a column, drag the field from the Available Objects section to the desired location. Release the column once the “Drop here to insert cell” message is displayed. Note: Once a report is run for the first time, any new data fields that have been added do not automatically appear on the report, they have to be manually added. • Delete Column – To delete the entire column from the report but not from the Available Objects screen, right click on a cell to highlight the column and select Delete. 28

BOBJ – FILTER AND SORT • Report Filter – Used at the report level to filter data that was brought into the report. o In the report, right click on data field and select Filter or click on Toolbar > Analysis Tab > Filters Tab and select Filter. • Sort – To sort, right click on data field and select Sort or click on Toolbar > Analysis Tab > Display Tab and select Sort. 29

BOBJ – CALCULATIONS • Calculation – Create a formula to calculate a value (e. g. , use base pay to calculate annual base). To create a formula, select the data field and click in the Formula Bar to enter the calculation (e. g. , =[Base Pay Amt]*12 or 26). 30

BOBJ – FORMULAS AND VARIABLES • Formulas / Variables – Customized calculations performed at the report level are formulas. Formulas and calculations (similar to Excel) are used to manipulate data. Formulas are defined as variables. Variables can be reused within the same report, but they cannot be used across different reports. o Right click on the Variables folder in the Available Objects section and select New. o Name the variable and if desired, add a description. The variable cannot be named the same as a data field in your report. o Create a formula for the variable by selecting from the list of available data fields (objects), functions and operators. The green checkmark in the Formula section is used to validate formula. o Once the variable is created, it appears under the Variables folder. 31

BOBJ – DIMENSIONS AND MEASURES • Data fields in the BOBJ universe are defined as a dimension or measure. o Dimension – Qualitative and do not total or sum. For example, Appt ID, Social Security Number, and Agency Code are dimensions. Although these fields are numbers, a mathematical function cannot be performed on them. Dimension is designated with an icon. o Measure – Numerical values that mathematical functions work on. For example, Annual Salary is a measure. A measure is designated with an icon. 32

BOBJ – CELL AND COLUMN FORMATTING • Format – Apply Formats to an individual column and/or multiple columns. To select multiple columns, hold down the Ctrl key and click on each column. o Right click in column and select Format Cell. o Formatting options: General, Alignment, Font, Border, Appearance. 33

BOBJ – NUMBER AND DATE FORMATTING • Format Numbers and Dates – There are two ways to format numbers and dates. You can either apply a predefined format or define a custom format. o To apply a format, right click on the data field and select Format Number. o Select the desired number/date format (e. g. , number, currency, date/time). o If needed, a custom number/date format can be created (e. g. , 01/20/2020) by selecting the Custom option. 34

BOBJ – SECTIONS • Sections – Creating a section splits the report information into smaller more manageable parts. o Right click the column that will be defined as a section and select Set as section. 35

BOBJ – MULTIPLE TABS • Multiple Tabs – Used to create a new report tab with an existing report. o To create a copy of the report, right click on the report tab (bottom of screen) and select Duplicate Report (report will copy to new tab). o To rename the copied report, right click on the report tab and select Rename Report. o Apply any filters/formatting to the new report. 36

BOBJ – CONDITIONAL FORMATTING • Conditional Formatting – Create custom formats on specific cells or groups of data to call attention to data using basic conditions or formulas. o Click on Toolbar > Analysis Tab > Conditional Tab and select New Rule. o Name the conditional format and if desired, add a description. o Enter the Condition (i. e. , cell, operator, operands). o Select Format to create formatting rules (e. g. , text, background, border). 37

BOBJ – RANKING • Ranking – Used to rank data within the report in a specific order. For example, can be used in a report to easily identify the top five annual salaries. o Right click on the data field and select Ranking > Add Ranking. o Select the ranking properties. o Apply Ranking. 38

BOBJ – SHARE REPORT • Share Report – Ad hoc users can share reports with other ad hoc report users. This includes sharing reports across agencies. Users can share a report with ad hoc users within own agency by placing the report in the Ad Hoc Reports folder. o From the My Favorites or Ad Hoc Reports folder right click on the report to be shared. o Select Send > BI Inbox. o Search for user by entering their Login ID in the Find Title field. o Click on Right Arrow icon to move ad hoc user to the Selected Recipients box. o Click Send button. 39

BOBJ – MERGE DIMENSIONS • Merge Dimensions – Create a report using data fields that do not join together to create a single report using multiple tabs. o Create first report. o To create a second report, click on Edit Data Provider while in the first report. o Click on Add Query (from Query Panel) and select From the Universe. 40

BOBJ – MERGE DIMENSIONS o Add data fields from a different folder than was used to create first report. o Run second (new) query. 41

BOBJ – MERGE DIMENSIONS (Continued) o Choose how to include data from new query. Default is “Insert table in a new report”. o At the bottom of Available Objects section, change the “Arranged by” preference to Arranged by Query. o Select the data field for Query 1, hold Ctrl key and select the data field from Query 2. o Right click on data field in Query 1 and select Merge. 42

BOBJ – MERGE DIMENSIONS (Continued) o Create a New Variable – right click on Variables folder (Available Objects section) and select New. o Name the Variable and if desired, add a description. o Change the Qualification to Detail and select the Associated Dimension (data field from Query 1). o Add a Formula (if applicable). o Drag the new Variable to Report 1. 43

BOBJ – SQL SCRIPT • BOBJ Query Script – Users have the option to view the SQL script that is created in the background when a BOBJ query is created. The SQL cannot be modified but can be used as a troubleshooting tool for those users familiar with SQL. o From the Query Panel, right click on the View Script icon. o View SQL script. 44

BOBJ – SCHEDULE REPORT • Schedule Report – Allows the user to automate the process of refreshing documents and can reduce the run time by allowing reports to run during off-peak periods. o From the My Favorites or Ad Hoc Reports folder right click on the report and select Schedule. o At a minimum, select the scheduling recurrence (e. g. , now, weekly, monthly – defaults to “now”) and report format (e. g. , Excel, PDF). o When all scheduling attributes have been completed, click Schedule. 45

BOBJ – RESOURCES • Resource Documents – BOBJ resource documents can be found on the People First Team BOBJ Website at: https: //www. dms. myflorida. com/workforce_operations/people_first/for_state_hr_practitioners/business_ objects_bobj o How To Run Reports in People First Video o Running Reports in People First Instructional Guide o Data Warehouse Business Objects (BOBJ) Ad Hoc Reporting User Guide 46

THANK YOU! People First - Data Warehouse Team Brenda Hough – 850 -488 -2277 Nikki Turlington – 850 -487 -3443 Cheri Holliday (Manager) – 850 -410 -3409 To ensure you are assisted in a timely manner, all BOBJ related email inquires should be sent to the Data Warehouse team email box at People. First. Data. Warehouse@dms. myflorida. com. 47