WIXLIB

End User Finance TrainingGuideCal Poly DataMarts UsingBrioQueryContact: Laurie Borello Lborello@calpoly.eduLast Date Revised 10/15/05Business Process Owner: BABSPage - 1 - of 23

Table of ContentsDataMart Overview. - 1 Field Definitions . - 2 Fields to Know . - 5 Accessing BrioQuery via Terminal Services. - 7 Build a Department Summary Query . - 8 Build a Department Actuals Transactions Query for Current Month . - 11 Build an Open Purchase Order Query. - 14 Build a Year to Date Budget Query by Scenario . - 16 Query Tips . - 18 Terminal Services Drives. - 21 -Contact: Laurie Borello Lborello@calpoly.eduLast Date Revised 10/15/05Business Process Owner: BABSPage - 2 - of 23

DataMart OverviewThe BrioQuery Financial Tables are used for ad hoc reporting. There are no financial repositoryqueries. The data is refreshed nightly from the PeopleSoft Finance database.The six tables for end user use are:FN SUMMARY MONTHLY TB (High level summary by Chart of Accounts by Accounting Period)FN SUMMARY YEARLY TB (High level summary by Chart of Accounts by Fiscal Year)FN TRANS ACTUALS TB (Detailed transactions for Expenditures)FN TRANS BUDGET TB (Detailed Budget transactions)FN TRANS ENCUMBRANCE TB (Detailed Purchase Order transactions)FN TRANS PRE ENCUMBRANCE TB (Detailed Requisition transactions)Please be aware, these tables only contain Revenue and Expense data.Contact: Laurie Borello Lborello@calpoly.eduBusiness Process Owner: BABS-1-10/27/05

Field DefinitionsSummary of Fields in Transaction TablesSummary of Fields in Summary TablesActual/Budget/Encumbrance/ PreencumbranceYearly/MonthlyGeneral DescriptionACCOUNTACCOUNTChartfieldACCOUNT DESCRACCOUNT DESCRChartfieldACCOUNT CSU GAAP ACCT DESCACCOUNT CSU GAAP ACCT DESCCSU Account AttributesACCOUNT CSU GAAP ACCT TYPEACCOUNT CSU GAAP ACCT TYPECSU Account AttributesACCOUNT CSU FIRMS OBJ CDACCOUNT CSU FIRMS OBJ CDCSU Account AttributesACCOUNT CSU NATURAL CLASSACCOUNT CSU NATURAL CLASSCSU Account AttributesACCOUNT GROUPACCOUNT GROUPAccount Group by Trans TypeACCOUNT GROUP DESCRACCOUNT GROUP DESCRAccount Group by Trans TypeACCOUNT TRANSACTION TYPEACCOUNT TRANSACTION TYPEAccount Group by Trans TypeACCOUNT TRANSACTION TYPE DESCRACCOUNT TRANSACTION TYPE DESCRAccount Group by Trans TypeACCOUNT TYPEACCOUNT TYPEExpense or RevenueACCOUNT TYPE DESCRACCOUNT TYPE DESCRExpense or RevenueACCOUNTING DTTransaction informationACCOUNTING PERIOD DESCRTransaction OnlyACCOUNTING PERIOD (Monthly TableOnly)ACCOUNTING PERIOD DESCR (MonthlyTable Only)Summary OnlyAMOUNT ACTUALSummary AmountsSummary OnlyAMOUNT BALANCE AVAILABLESummary AmountsSummary OnlyAMOUNT BUDGETSummary AmountsSummary OnlyAMOUNT ENCUMBRANCESummary AmountsSummary OnlyAMOUNT PRE ENCUMBRANCESummary AmountsSummary OnlyAMOUNT PRIOR YR ACTUALSummary AmountsSummary OnlyAMOUNT TOTAL ACTUALSummary AmountsAMOUNT (Budget, Enc, Pre, Budget)Transaction OnlyTransaction AmountsBALANCING LINETransaction OnlyTransaction informationBUSINESS UNITTransaction OnlyTransaction informationBUSINESS UNIT GLBUSINESS UNIT GLTransaction informationCLASS FLDCLASS FLDChartfieldCLASS DESCRCLASS DESCRChartfieldCURRENCY CDTransaction OnlyTransaction informationACCOUNTING PERIODAccounting PeriodAccounting PeriodDEPTIDDEPTIDChartfieldDEPTID DESCRDEPTID DESCRDEPTID LEVEL1DEPTID LEVEL1DEPTID LEVEL1 DESCRDEPTID LEVEL1 DESCRDEPTID LEVEL2DEPTID LEVEL2DEPTID LEVEL2 DESCRDEPTID LEVEL2 DESCRDEPTID LEVEL3DEPTID LEVEL3DEPTID LEVEL3 DESCRDEPTID LEVEL3 DESCRChartfieldDept ID by Roll-up level on CampusOrg ChartDept ID by Roll-up level on CampusOrg ChartDept ID by Roll-up level on CampusOrg ChartDept ID by Roll-up level on CampusOrg ChartDept ID by Roll-up level on CampusOrg ChartDept ID by Roll-up level on CampusOrg ChartDEPTID LEVEL4DEPTID LEVEL4Dept ID by Roll-up level on CampusContact: Laurie Borello Lborello@calpoly.eduBusiness Process Owner: BABS-2-10/27/05

Org ChartDEPTID LEVEL4 DESCRDEPTID LEVEL4 DESCRDEPTID LEVEL5DEPTID LEVEL5DEPTID LEVEL5 DESCRDEPTID LEVEL5 DESCRDept ID by Roll-up level on CampusOrg ChartDept ID by Roll-up level on CampusOrg ChartDept ID by Roll-up level on CampusOrg ChartFISCAL YEARFISCAL YEARTransaction informationFUND CODEFUND CODEChartfieldFUND DESCRFUND DESCRChartfieldFUND CSU APPROP REF NUMFUND CSU APPROP REF NUMCSU Fund AttributesFUND CSU APPROP REV DTFUND CSU APPROP REV DTCSU Fund AttributesFUND CSU APROP AVAIL TOFUND CSU APROP AVAIL TOCSU Fund AttributesFUND CSU APROP PURPFUND CSU APROP PURPCSU Fund AttributesFUND CSU APROP YR ENACTFUND CSU APROP YR ENACTCSU Fund AttributesFUND CSU BUDGET YEARFUND CSU BUDGET YEARCSU Fund AttributesFUND CSU FIRMS PROJECTFUND CSU FIRMS PROJECTCSU Fund AttributesFUND CSU FUND ATTR KEYFUND CSU FUND ATTR KEYCSU Fund AttributesFUND CSU NET ASSET CATFUND CSU NET ASSET CATCSU Fund AttributesFUND CSU FUND CODEFUND CSU FUND CODECSU Fund AttributesFUND CSU FUND DESCR60FUND CSU FUND DESCR60CSU Fund AttributesFUND CSU GAAP FUND TYPEFUND CSU GAAP FUND TYPECSU Fund AttributesFUND CSU LF GROUP CODEFUND CSU LF GROUP CODECSU Fund AttributesFUND CSU LF GROUP DESCRFUND CSU LF GROUP DESCRCSU Fund AttributesFUND CSU LF SOURCE CODEFUND CSU LF SOURCE CODECSU Fund AttributesFUND CSU LF TYPE DESCRFUND CSU LF TYPE CODECSU Fund AttributesFUND CSU LF TYPE CODEFUND CSU LF TYPE DESCRCSU Fund AttributesFUND CSU SCO ACCT TYP1FUND CSU SCO ACCT TYP1CSU Fund AttributesFUND CSU SCO ACCT TYP2FUND CSU SCO ACCT TYP2CSU Fund AttributesFUND CSU SCO CATEGORYFUND CSU SCO CATEGORYCSU Fund AttributesFUND CSU SCO CMPNTFUND CSU SCO CMPNTCSU Fund AttributesFUND CSU SCO ELEMENTFUND CSU SCO ELEMENTCSU Fund AttributesFUND CSU SCO FUNDFUND CSU SCO FUNDCSU Fund AttributesFUND CSU SCO FUND DESCR60FUND CSU SCO FUND DESCR60CSU Fund AttributesFUND CSU SCO PGM CDFUND CSU SCO PGM CDCSU Fund AttributesFUND CSU SCO RECON TYPEFUND CSU SCO RECON TYPECSU Fund AttributesFUND CSU SCO REV ACCTFUND CSU SCO REV ACCTCSU Fund AttributesFUND CSU SCO SUB FUNDFUND CSU SCO SUB FUNDCSU Fund AttributesFUND CSU SCO TASKFUND CSU SCO TASKCSU Fund AttributesINVOICETransaction OnlyTransaction informationJOURNAL DATETransaction OnlyTransaction informationJOURNAL IDTransaction OnlyTransaction informationJOURNAL LINETransaction OnlyTransaction informationJRNL LINE SOURCETransaction OnlyTransaction informationKK SOURCE TRANTransaction OnlyTransaction informationKK TRAN DTTransaction OnlyTransaction informationKK TRAN IDTransaction OnlyTransaction informationKK TRAN LNTransaction OnlyTransaction informationLEDGERTransaction OnlyTransaction informationLEDGER GROUPTransaction OnlyTransaction informationContact: Laurie Borello Lborello@calpoly.eduBusiness Process Owner: BABS-3-10/27/05

LINE DESCRTransaction OnlyTransaction informationLINE NBRTransaction OnlyTransaction informationPROGRAM CODEPROGRAM CODEChartfieldPROGRAM DESCRPROGRAM DESCRChartfieldPROJECT IDPROJECT IDChartfieldPROJECT DESCRPROJECT DESCRChartfieldRECEIPT DTTransaction OnlyTransaction informationRECEIVER IDTransaction OnlyTransaction informationRECV LN NBRTransaction OnlyTransaction informationRECV SHIP SEQ NBRTransaction OnlyTransaction informationSCENARIO (Budget Table only)Transaction OnlyTransaction informationSLO DOC LINE NBRTransaction OnlyDocument ReferenceSLO DOC SCHED NBRTransaction OnlyDocument ReferenceSLO DOC DIST LINETransaction OnlyDocument ReferenceSLO DOCUMENT IDTransaction OnlyDocument ReferenceSOURCETransaction OnlyTransaction informationSTATISTIC AMOUNTSTATISTIC AMOUNTFTESTATISTICS CODESTATISTICS CODEFTEVENDOR IDTransaction OnlyTransaction informationVENDOR NAMETransaction OnlyTransaction informationVENDOR NAME SHORTTransaction OnlyTransaction informationVENDOR SETIDTransaction OnlyTransaction informationVOUCHER IDTransaction OnlyTransaction informationVOUCHER LINE NUMTransaction OnlyTransaction informationUNPOST SEQTransaction OnlyTransaction informationMAINT DTMAINT DTSystem AssignedMAINT TIMEMAINT TIMESystem AssignedContact: Laurie Borello Lborello@calpoly.eduBusiness Process Owner: BABS-4-10/27/05

Fields to KnowRegardless of the table you select, always set a limit on the Business Unit GL field of SLCMP oryour query results will be not be correct.Account Transaction Type (values: 1-4)Account Transaction Type Descr (1 Salary & Wages, 2 Benefits, 3 Operating Expenseincluding student assistants, 4 Revenue)Account Group (first three numbers of an account which indicates account grouping)Account Group Descr (606 Misc Operating Expense,840 Budget, 601 Academic Salaries)SLO Document ID (dependant upon the type of transaction, it will be the journal Id, invoice #, POor Req Id)Dept ID Level (relationship on Dept Tree) – represents campus organization, ex. Level 1 –Provost, Level 2 – Dean, Level 3 – Dept. Select the appropriate level for the Dept Id(s) in thequeryNote: Although not displayed, some departments have Level 4 Dept IdsContact: Laurie Borello Lborello@calpoly.eduBusiness Process Owner: BABS-5-10/27/05

Level 1Level 2Level 3Fiscal Year – set limit on one or more (FY 2004 July 1, 2004 – June 30, 2005)Accounting Period - “as of” date; Period 1 July, Period 12 June (include numeric value,otherwise Accounting Period Descr displays in alphabetical order)Ignore all CSU fields. These fields are used by internal finance staff to report activity to theChancellor and the State.Contact: Laurie Borello Lborello@calpoly.eduBusiness Process Owner: BABS-6-10/27/05

Accessing BrioQuery via Terminal ServicesLog on to Terminal Services using the Terminal Services Icon on your desk top.Click on the BrioQuery-PolyData icon.From this window navigate to Data Model, Table Catalog.Enter your Brio Query User ID (same as PeopleSoft) and password.Contact: Laurie Borello Lborello@calpoly.eduBusiness Process Owner: BABS-7-10/27/05

The Financial Table Catalog displays.Build a Department Summary QuerySelect FN SUMMARY MONTHLY TB by double clickingThis table includes the accounting period field which the FN SUMMARY YEARLY TB does not.Set LimitsDouble click to limit on Business Unit GL: SLCMPSuggested limits:Deptid Level (appropriate level), Fiscal Year 2004, Fund Code: AD204 (except for Trust Funds)Select fields by adding them to the Request LineTo add fields to the Request Line use one of the following methods: Hold down the Ctrl key, highlight the fields and click Add Right click on each field and selecting Add Selected Items Click and drag the fields to the Request LineRequest the following fields:AccountContact: Laurie Borello Lborello@calpoly.eduBusiness Process Owner: BABS-8-10/27/05

Account DescrAccount GroupAccount Group DescrAccount Transaction TypeAccount Transaction Type DescrAccounting PeriodAccounting Period DescrAmount Balance AvailableAmount BudgetAmount EncumbranceAmount Pre EncumbranceAmount Total ActualClass FldClass DescrDeptidDeptid DescrDeptid Level (select appropriate Level)Fiscal YearFund CodeFund Code DescrProgram CodeProgram DescrProject IdProject DescrClickResults are displayed on the Results tab.Create PivotClick the Pivot tab to easily format results.Click OutlinerFor a high level report, drag the fields indicated in the example below, into the Outliner.Customize as needed.Indicate columnheadings hereThese fieldsdisplay onthe left sideof the reportContact: Laurie Borello Lborello@calpoly.eduBusiness Process Owner: BABSAmount fields arealways in thissection-9-10/27/05

Add column headers by selecting Format, Corner Labels, Both.Click the Save icon to save the query. For more information on Terminal Services Directories seepg.18.Contact: Laurie Borello Lborello@calpoly.eduBusiness Process Owner: BABS- 10 -10/27/05