Deliverable Expectations Document Format



Ad Hoc Training ExercisesDate: 09/20/2018Table of Contents TOC \o "1-3" \h \z \u Table of Contents PAGEREF _Toc519888944 \h 21Exercise 1 – Create Web Intelligence Report PAGEREF _Toc519888945 \h 3Create Basic Web Intelligence Report PAGEREF _Toc519888946 \h 3Order Columns PAGEREF _Toc519888947 \h 5Sorting & Filtering PAGEREF _Toc519888948 \h 5Creating Formulas & Variables PAGEREF _Toc519888949 \h 62Exercise 2 – Formatting PAGEREF _Toc519888950 \h 8Formatting PAGEREF _Toc519888951 \h 8Sections & Breaks PAGEREF _Toc519888952 \h 8Multiple Tabs PAGEREF _Toc519888953 \h 9Saving & Sharing Report PAGEREF _Toc519888954 \h 103Exercise 3 – Conditional Formatting/Ranking PAGEREF _Toc519888955 \h 11Conditional Formatting PAGEREF _Toc519888956 \h 11Ranking PAGEREF _Toc519888957 \h 12Query Filter with User Prompt PAGEREF _Toc519888958 \h 134Exercise 4 – Input Controls PAGEREF _Toc519888959 \h 15Input Controls PAGEREF _Toc519888960 \h 155Exercise 5 – Multiple Queries/Combined Queries PAGEREF _Toc519888961 \h 16Add Multiple Queries PAGEREF _Toc519888962 \h 16Merging of Dimensions PAGEREF _Toc519888963 \h 18Combined Queries & Custom SQL PAGEREF _Toc519888964 \h 19Exercise 1 – Create Web Intelligence ReportCreate Basic Web Intelligence ReportSTEPS:Step DescriptionElementCreate a Basic WebI Query based on a universe1.1 Log in to BI Launchpad from People First portalLog into People First Portal. People First UAT: on the Reports tile. Under Resources, select Ad Hoc Reporting.1.2 Launch WebIApplications -> Web Intelligence or click on the WebI icon in the applications bar on the right-side of the screen1.3 Create new queryClick the “New” query icon on the toolbar1.4 Select Universe as data sourceSelect “Universe” as the data source for your new query1.5 Select Ad Hoc UniverseSelect “Adhoc Universe.unx” universe.1.6 Select class objectsDrag the following objects to the Result Objects panel:(Warning: Double clicking on folder adds all fields to Result Objects)Folder: Dwsof All Active >> Dwsof All ActiveAgency CodeAgency NameAppt IDClass CodeClass TitleClass Assign DateState Hire DateAgency Hire DateAppt FTEBase Pay AmtOrg CodeAppt DateFLAIR Org codeLast NameFirst NameMiddle NameEmployee Group Code Employee Sub Group CodePay Cycle CodeOrg NamePos NumFolder: Dwsof All Active >> Position ActiveBudget Entity CodeSPC Code11.7 Add Objects to the Query Filters panel with valuesAdd the following objects to the Query Filters panel:Folder: Dwsof All Active >> Dwsof All ActiveHover over the objects and it will tell you the folder and dimension nameObjectOperatorValuesEmployee TypeIn List[Enter your own Employee Type] example - 1;2Agency CodeIn List[Enter your own Agency Code] example - 2100;2200Class CodeIn List[Enter your own Class Code] example - 5109;5909;6900;6901Employee Types:1 = Included2 = Excluded1.8 Execute the queryClick on the “Run Query” button (Top-right of screen)Order ColumnsStep DescriptionElementOrdering Columns2.1 Move ColumnSingle click on the Column and drag next to a column will move the original to a new location on the report.Move Columns to Order:Agency CodeAgency NameOrg CodeOrg NameClass CodeClass Title2.2 Swap ColumnClick on column and drag over the top of another column will swap the data.Swap Columns:Appt ID – Appt DateState Hire Date – Agency Hire DateBase Pay Amt – Pay Cycle2.3 Deleting ColumnsDelete a column by clicking the column (data) and right click, select delete.Delete columns:Appt FTEPay Cycle2.4 Adding ColumnsAdd a column from the Available Objects drag to the desired location on the report:Pay CycleAppt FTE2.5Save Report.Click the Save button and navigate to the My Favorites folder and Save.Sorting & FilteringStep DescriptionElementSorting and Filtering3.1 Open Report from Previous Exercise3.1 SortingSelect column and set sort Employee Group (ascending)Employee Subgroup (ascending)Last Name (ascending)State Hire Date (descending)3.2 Add FiltersSelect column and set filter:Class Code: filter by values ‘5901’ and ‘5909’(Use Class Codes for your agency – as some agencies will not have these class codes)Creating Formulas & VariablesStep DescriptionElementFormulas & VariablesCreate Formula4.1 Create FormulaClick on the Base Pay Amt column (data) and click the formula button4.2 FormulaIn the Formula Editor window create formula.[Base Pay Amt] *12 or 26 depending on your agency’s pay cycle4.3 Validate FormulaClick the green check mark on the right-side of the formula box to validate the formula. If correct click ok.Create Variable4.4 New VariableIn the Available Objects pane on the left, right-click variables -> New4.5 Name VariableYearly Salary4.6 Change QualificationSelect “Measure” in the Qualification Dropdown4.7 Create FormulaDouble click [Base Pay Amt] from the “Available Objects window. Select the multiplication operator from the “Operators” window. Multiply by 12 or 26.=[Base Pay Amt]*12 or *26 depending on your agency’s pay cycle4.8 Validate FormulaClick the green check mark to the right of the formula pane to validate the formula. If correct click ok to close the variable screen4.9 Add Variable to ReportDrag the Variable “Yearly Salary” to the report. Place next to “Base Pay Amt”4.10Save Report.Click the Save button.Exercise 2 – FormattingFormattingStep DescriptionElementFormatting1.1 Rename ReportRight click on the Report tab and select “Rename Report”. EnterRename to “Formatted Report”1.2 Format Date columnsFormat Date columns to long date Month, Day, Year (e.g., September 11, 1974). Select date column(s), right click and select “Format Number”. Select Date/Time and click long date1.3 Format Currency columnsFormat Currency columns.Sections & BreaksStep DescriptionElementSections & Breaks2.1 Create a SectionCreate a Section on the “Org Name” column.Right click on the Org Name column (data) and select Set as Section2.2 Create BreakCreate a Break on the “Employee Group” column. Select the Employee Group column, navigate to the Analysis tab, Display subtab, and select “Break”Apply Break on Employee Subgroup2.3 Set Total currency columnsSelect the Yearly Salary column, navigate to the Analysis tab, Functions subtab. Select Sum function.Select the Base Pay Amt column and add Total (sum function)2.4 Set OutlineSet the Outline to quickly collapse different levels of data.Navigate to the Analysis tab, Interact subtab. Select Outline. 2.5 Collapse OutlineNotice there are several “layers” in the Outline available. Located in the bottom left corner of Window. Collapse Level 1. Observe Results. Un-collapse Level 1.Collapse Level 2. Observe Results. Un-collapse Level 2.2.6 Turn off Outline modeNavigate to the Analysis tab, Interact subtab. De-select Outline.2.7 Save ReportMultiple TabsStep DescriptionElementMultiple Tabs3.1 Add duplicate ReportCreate duplicate report by Right clicking on the report tab at the bottom of the window. Select “Duplicate Report”.3.2 Rename new tabRename the new report tab “Employees by Agency and Class Code”3.3 Edit FilterRight click on the Class Code column (data), and select Filter, Edit Filter.Change the values in the filter to “5901; 6901”(Use class codes for your agency – as some agencies will not have positions for these classes)3.4 Apply other desired formatsFind other desired formats and update the new report tab. Format Fonts, Cells, Numbers.Add Sums or CountsChange Breaks/Sections if desired.Saving & Sharing ReportStep DescriptionElementSaving & Sharing4.1 Save ReportClick on the Save drop down window. Select Save As…4.2 Create new folder in My Favorites locationClick the “New Folder” button.Name the new folder “Ad Hoc Training - <your initials>4.3 Save Report to the new folderCreate a name for the report. Save to the “Ad Hoc Training - your initials>” folder.Exercise 3 – Conditional Formatting/RankingConditional FormattingStep DescriptionElementConditional Formatting1.1 Create New Conditional FormatSelect the “Base Pay Amt” column (data). Select the Analysis tab, Conditional subtab and click “New Rule”1.2 Conditional Format ruleCreate Conditional Format to Highlight and Bold “Base Salary Amt” cells greater than $3,000 per pay cycle.1.3 Add name and description to Conditional FormatName: “High Salaries”Description: Base Salaries exceeding $3,0001.4 Add ConditionSelect “Base Pay Amt” object in the Filter object or cell1.5 OperatorSelect “Greater Than or Equal To” operator 1.6 OperandType the value for Operand.Example 100001.7 FormatSet the type of cell format desired.Click the Format button.Format example: Text = Red, Font Style = Bold, Background = Yellow1.8 Review Conditional FormatClick Ok and review the Conditional Format1.9 Create new Conditional FormatCreate new Conditional format.Base Pay Amt – Less Than or Equal To “4000”1.10 Save ReportRankingStep DescriptionElementRanking2.1 Create Duplicate ReportRight click the Report Tab and select “Duplicate Report”2.2 Create Ranking Select the Analysis Tab, Filters subtab. Click the “Ranking Button” button.Note – Rankings are only applied to measures2.3 Set Rank of Top 3 Yearly SalariesSelect the Top 2.Based on: Yearly Salary2.4 Review Ranking ResultsClick OK and review results now filtered.2.5 Add Bottom 3 Yearly SalariesEdit Ranking, add Bottom 3 Yearly Salaries2.6 Review results2.7 Remove RankAnalysis Tab > Filter subtabClick drop down arrow next to “Ranking” buttonSelect “Remove Ranking”2.8 Save ReportQuery Filter with User PromptStep DescriptionElementQuery Filter – User Prompt3.1 Edit Data ProviderClick the Edit Data Provider button.3.2 Locate Query Filters panelIn the Query Filters panel, locate Agency Code filter3.3 Define Filter TypeSelect the “Define Filter Type” Button3.4 Set to PromptSelect the “Prompt” value as Filter Type3.5 Show Prompt propertiesSelect the “Show Prompt Properties” button3.6 Set Prompt PropertiesSet Prompt Text (i.e. what will be displayed to users).Set checkbox for “Prompt with List of Values” and “Select only from List”Uncheck “Keep last value(s) selected” and “Optional Prompt”3.7 Default ValuesDetermine if Default value should be selected automatically3.8 Run QueryClick OK, then Run Query3.9 Review Agency Code PromptThe Prompt window is displayed. 3.10 Select Agency CodeSelect which Agency Code to execute the report3.11 Review ReportClick OK to execute report with selected Agency Code prompt3.12 Save ReportExercise 4 – Input ControlsInput ControlsStep DescriptionElement1.1 Open Report from Previous Exercise1.2 Locate Input Controls buttonClick on the Input Controls button on the Left Panel or under the Analysis Tab, Filters subtab.1.3 Select Org NameSelect the Org Name object from the list of available objects1.4 Pick Selection TypeSelect the List Box from the Multiple Selection block,1.5 Set to Input Control ParametersName: Org NameList of Values: From ReportFilter Option: In ListNumber of Lines 101.6 Assign Input Control LevelAssign Input Control to the “Entire document”1.7 FinishClick Finish1.8 Review Input ControlsReview input control options located on Left Panel1.9 Select Org NameSelect a few Org Names from the Input Controls list.Selection will depend on the associated Org Names2.0 Review ReportReview how the Input Controls change the data displayed on the report. Notice that the report is automatically filtered by the Org Names selected.2.1 Save ReportExercise 5 – Multiple Queries/Combined QueriesAdd Multiple QueriesStep DescriptionElementMultiple Queries1.1 Create New WebIClick on the New icon in the WebI window.1.2 Select the UniverseSelect the Adhoc Universe.1.3 Add Class ObjectsAdd the following Class Objects to the Query Results panel.Folder: Dwsof All Active >> Position ActiveAgency CodeAgency NameClass CodeClass TitleEmployee TypeFLAIR Org CodeFLSA Work WeekOrg CodePay PlanPos NumVacancy IndFolder: Dwsof All Active >> Dwsof All ActiveAppt IDFull Name1.4 Add the following Query FiltersDrag the following Class Objects to the Query Filters panel:Folder: Position ActiveObjectOperatorValueAgency CodeIn List[Enter your own Agency Code] example - 2100;2200Employee TypeEqual To[Enter a valid Employee Type for your agency] example – 1FLSA Work WeekIn List[Enter a valid FLSA Work Week for the Employee Type selected]example – 40 would be valid for Employee Type 1)Pay PlanIn list[Enter a valid Pay Plan for the Employee Type selected] example - 01Employee Types:1 = Included2 = Excluded4 = Included – OPS5 = Excluded - OPS1.5 Run QueryClick the Run Query Button1.6 Review ResultsNote: If you receive a message indicating, “No Data to Retrieve in Query” you will need to review and update your Query Filters. There may be an invalid combination (e.g., Employee Type and FLSA Work Week) in your Query Filters.1.7 Select Data ProviderClick the Edit Data Provider button.1.8 Duplicate Query 1Right click on the “Query 1” Tab at the bottom of the Query Panel. Select Duplicate1.9 Rename QueriesRight click “Query 1” tab and Rename to “Employee Type - 1”Right click “Query 1 (1)” tab and Rename to “Employee Type - 4”1.10 Change Query Filter in new QuerySelect the “Employee Type – 4” Query TabChange the Query Filter for Employee Type – Equal to 4 (Employee Type 4 is “Included – OPS”)Remove the Query Filter for Pay Plan or change the filter option from “In List” to “Not in List”1.11 Run QueryClick the Run Queries Button1.12 Insert new dataSelect the option to “Insert a table in a new report”. This option will create a new tab in the document.Options:Insert a table in a new reportInsert a table on current reportInclude the results in the document without generating a table.1.13 Rename Report TabsRename “Report 1” Tab to “Employee Type 1”Rename “Report 2” Tab to “Employee Type 4”1.14 Save ReportMerging of DimensionsStep DescriptionElementMerge Dimensions2.1 Observe objects in “Available Objects” window.Click on the Available Objects button to see objects from both “Employee Type – 1” and “Employee Type – 4” Query.2.2 Arrange by QueryAt the bottom of the Available Objects window, select the “Arrange by Query” option2.3 Merged DimensionsMerge the following dimensions:Click “Appt ID” (Employee Type – 1) and Ctrl+Click “Appt ID” (Employee Type – 4) and release Ctrl, Right Click and select Merge.Click “Employee Type” (Employee Type – 1) and Ctrl+Click “Employee Type” (Employee Type – 4) and release Ctrl, Right Click and select Merge.Continue merging the following dimensions:Class CodeFull NameAgency CodeAgency NameOrg CodePos Num2.4 Add new TabRight click on a Tab, and select “Add Report”2.5 Drag Merged Dimensions to New ReportDrag and Drop Merged Dimensions onto new Reports. 2.6 Observe Report with data from both QueriesData from both the Employee Type – 2 and Employee Type – 4 Queries are now displayed in a single table.2.7 Save ReportCombined Queries & Custom SQLStep DescriptionElementCombined Queries3.1 Review Combined QueriesClick the Edit Data Providers button3.2 Create Combined QuerySelect the Combined Queries button3.3 View new Query panelView the new Query Panel that opens under the Ad Hoc Universe. Union of Combined Query 1 and Combined Query 2 created. Double click the word Union to toggle through Union, Intersect, and Minus combined query type. Select Union for this bined Query 1 – has original query bined Query 2 – no query filters yet.3.4 Add Query FiltersAdd Query filters to Combined Query 2. Select Combined Query 2 and add Query FiltersObjectOperatorValueAgency CodeIn List[Enter your own Agency Code] example - 2100;2200Employee TypeEqual To[Enter a valid Employee Type for your agency] example - 1FLSA Work WeekIn List[Enter a valid FLSA Work Week for your agency and the Employee Type selected] example - 40Pay PlanNot in list[Enter a valid Pay Plan for your agency] example - 083.5 Run QueryClick the Run Queries Button 3.6 Observe changes to Data.Click on the first tab of the document. Observe where new data is added to the report. There are now Employee Types with the value 1.3.7 Save ReportCustom SQL3.8 View Custom SQLTo view the SQL statement created for the report, click the Edit Data Provider button.3.9 View Script ButtonClick the View Script button at the top menu bar of the Query Panel3.10 Observe SQL StatementCan view the standard SQL statement generated from the drag and drop of objects onto the Query Panel from the Universe.If you have SQL experience, you can select “Use custom query script radio button to create a custom SQL statement. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download