190 likes | 414 Views
Crystal Reports Learning the Basics. Part 1. Objectives. To provide information and direction to those who are responsible for FIS, HRIS and/or SIS reporting. To provide you with the basic skills to use existing report templates
E N D
Crystal Reports Learning the Basics Part 1
Objectives • To provide information and direction to those who are responsible for FIS, HRIS and/or SIS reporting. • To provide you with the basic skills to use existing report templates • To cover the theory behind the data and provide the necessary direction to make minor changes to templates already in circulation
What is a Data Warehouse? • Data warehouse is a generic term for a system for storing, retrieving and managing large amounts of any type of data. • The data warehouse is a central repository of data that provides the McGill community with integrated, up-to-date data from our various administrative systems. (FIS, HRIS, SIS)
Tables and Fields • There are over 500 separate tables in Banner containing information related to Finance • You would need to know every field and every table relationship to create reports
The solution: Views • A view is a collection of fields from different tables to make the reporting easy • Views are data fields stored in a table • Views are tables that have been joined together for you
Example of a Simple View DWH.FGU_Employee_Org_View
Inventory of data warehouse views • Finance views cover a wide range of Financial data relating to Budget details, FIS Transactions, Payroll details, Endowment and Basic FOAPAL Information • HRIS views provide Personal employee data, job and position information and related labor distributions • SIS views are student centric and provide personal, biographic, course, schedule and grade details
Finance HR Student
Data warehouse details –Important links • For more information about: • FIS data , refer to http://knowledgebase.mcgill.ca/fskb/easylink/article.html?id=5098 • Provides information about getting access and where to get additional help; • https://home.mcgill.ca/financialservices/reporting/ • Contains individual report help for each template available to you • SIS data, refer to http://www.mcgill.ca/es/dwh/ • Provides an overview of the data, how to get access and details about the views and the fields available to you • HRIS data, refer to http://www.mcgill.ca/files/hr/data_model_hr_general_user_w_data_dictionary.xls • This provide you details information of the fields available to you and where they are taken from in the Banner HR forms
Where to find existing reports • There are several report templates that have been designed for Financial Administration • Medicine Administration website – www.mcgill.ca/medadmin • Financial Services Reporting – www.mcgill.ca/financialservices/reporting • Tailored reports have been placed in each unit’s individual administration intranets…more coming soon
Using a Finance Template • Access Reports folder • Open report “Monthly Fund Details” • Click on Refresh icon • Connect to data warehouse • 1.Log into the Banner data warehouse • 2.Service: banrep • 3.Enter yourBanner User ID and Password • 4.Click on Finish • Enter the Parameter fields • Fiscal Year = 10 • Fiscal Period = 11 • Fund Code = Your Fund • Click OK
So what am I looking at? • Now that you have refreshed the report with data: • What am I looking at? • Where is the data coming from? • How can I verify what I am looking at? • How can I make changes to this report? • How can I change the parameters to include another field? • How can I get the names of the individuals paid from the 6 accounts? • Should I reconcile every single transaction? • Let’s go through each of these questions, one at a time!
My report does not balance to Banner…why??? • DWH is one day old • Use a closed month to verify your results • Budget Query vs Budget query for Fund holders • FGIBDST/FRIGITD • Revenue included in balance? • Is your fund setup for Auto Budget for Revenue? • You have documents in the pipeline not yet processed • FGIBAVL/View Pending document(s) • Salary distribution • Timing is important when looking at Payroll transactions and the related encumbrances • Salary distribution is only run on Wednesdays and the first working day of the new month
When should I use Crystal reports? • Open month reporting • To access and manipulate tailored reports designed by the Faculty/Central Offices • To access and manipulate tailored reports you designed • No relevant report exists
Minerva reports/web reports/Crystal reports /…what’s the difference? • Minerva Reports (Financial Statements) • Monthly statements which provide status reports and allows you to perform financial analysis for most funds. • They are delivered through the Minerva Login site. • All you have to do is log in and pick them up! • Web Reports • Canned reports, cannot be changed (WYSIWYG) • Fund/Org Secure • Data warehouse access is required • Crystal Reports • Used for manipulating reports • Data warehouse access required • Crystal Reports software must be installed
What’s next? • Crystal Reports – Designing a report from A-Z • Report Requests – send them to https://home.mcgill.ca/medadmin/reporting/request/ • Additional Support – call Karen Kirouac 398-6953 or email karen.kirouac@mcgill.ca