Advanced Reporting with SQL and Access Projects

1. Advanced Reportingwith SQL and Access Projects 2007 PPM User Conference David Eades

2. Agenda • Overview • Report Layer Documentation • Access Projects • Advanced Reporting Exercises

3. PowerBusiness Reporting PPM DigitalDashboard Executive Reporting OLAP Reporting Excel OLAPViewer CentralReporting ReportLayer AccessProjects Single logonReporting portal AdvancedReporting 1. Overview

4. Report Layer External feeder systems • Raw data is brought into PPM from your feeder systems • Report Layer has a standard structure and naming of data tables • Designed for performance and space optimisation PPM ReportLayer

5. Advanced Reporting PPM • Advanced Reporting is an exact replica of the Report Layer database using views • The structure of the database is designed for ease of use • Query the data, save queries and other users can view the queries ReportLayer AdvancedReporting

6. AdvancedReporting AccessProjects Connecting to the Advanced Reporting database with Access Projects

7. 2. Report Layer Documentation

8. InstallingReport Layer Documentation • Available on request from PowerHealth Solutions • Can be installed on your client PC • Use the install file PHS_PPM_ReportLayerDocs_X.X.build_AUS.exe

9. Power Health Solutions Power Performance Management Report Layer Report Layer Documentation Starting upReport Layer Documentation

10. Click on the menu items on the left to navigate

11. Select Reporting Layer Standard • A list of tables or views displays

12. Select a table. The table details display with: • a brief description • a link to any table that is related to that object

13. Section Review • Report Layer Documentation gives the user a better understanding of the relationships between tables • How to navigate the HTML Documentation pages

14. 3. Access Projects

15. What is a View? • It is a saved query SELECT * FROM PatCost_Episode_Category • Each query will display the exact data that is in the Report Layer table and can be queried as if it were the table itself

16. Why use databases with views instead of the database with tables? • Data integrity is maintained • You can save queries in the database without fear of losing the queries when Report Layer is upgraded • Other users can view your queries from the view databases

17. Why Access Projects? • Query is conducted on the Server and not on the local PC • Can add queries to the database • Share queries with other staff • Familiar interface as it is within Microsoft Access Access Projects can access data from: • Advanced Reporting database • PPM View database

18. Create an Access Project • Start Access and select File > New

19. Create ADP file • Select Project using existing database • The File New Database window displays • Specify filename AdvancedReporting_demo.adp and click Create

20. Connect to database • Select the Server where advanced_reporting is located • Set login parameters • User name = reportlayeruser • Password = dailyreport • Select Allow saving password • Select the advanced_reporting database on the server from the drop down box

21. Click Test Connection. If the connection is successful, a message displays. Click OK. • The Microsoft Access Project window displays the tables and views from the advanced_reporting database.

22. Review Access Projects We just covered: • Creating an Access Project • Connecting to Advanced Reporting via Data Link • Viewing Advanced Reporting views in Access Projects

23. 4. Advanced Reporting Exercises

24. What do the exercises cover? • GROUP BY • MIN • MAX • COUNT • SUM • ORDER BY(ascending, descending)

25. Exercise 1 • Create query: __MaxDate • Return: most recent separation date • Use: MAX function, SELECT statement • Hint: PatCost_Episode_Category

26. Exercise 2 • Create query: __MaxDateForNonAcute • Return: most recent separation date for Separation Status of ‘Change to NHT/Non-Acute’ • Use: MAX function, WHERE and GROUP BY options • Hint: PatCost_Episode_Category, PatCost_Reference_SeparationStatus

27. Exercise 3 • Create query: __HighCostOutputs • Return: a list of Cost Outputs with their total costs, and order them by highest cost to lowest cost • Use: MAX function, GROUP BY and ORDER BY options • Hint: PatCost_EpisodeCostOuptut_Results, PatCost_Reference_CostOutput

28. Exercise 4 • Create query: __LowAdmissionTypeALOS • Returns: a list of Admission Types and their Average Length of Stay. • Also: reduce the results by only returning the Admission Types who have an Average Length of Stay less than 30. • Use: AVG function, GROUP BY and HAVING options • Hint: PatCost_Episode_Category, PatCost_Episode_Results, PatCost_Reference_AdmissionType

29. Exercise 5 • Create query: __DrgDetail • Returns: DRG Code, DRG Description, Average Cost, Average Length of Stay and a count of episodes for each DRG. Order by highest to lowest cost. • Use: AVG and COUNT functions, with the GROUP BY and ORDER BY options • Hint: PatCost_Episode_Category, PatCost_Reference_Drg, PatCost_Episode_Results

30. Exercise 6 • Populate with • P66A, 1370, 21 • B03A, 3500, 25 • H01A, 622, 24 • D01Z, 845, 28 • L61Z, 420, 18 • Create table Drg_Benchmark • Column 1Column Name = Drg_CodeData type = varcharLength = 10Allow Nulls = Unticked • Column 2Name = BenchmarkCostData type = intLength = 4Allow Nulls = Ticked • Column 3Name = BenchmarkALOSData type = intLength = 4Allow Nulls = ticked • Primary key = Drg_Code

31. Exercise 7 • Create query: __DrgDetailBenchmark • Return: Drg_Code, Average Total Cost, Average LOS, BenchmarkCost, BenchmarkALOS by joining objects created in Exercises 5 and 6. • Use: INNER JOIN option • Hint: __DrgDetail, Drg_Benchmark

32. Exercise 8 • Create query: __ServiceCodeDetails • Return: each unique service code, along with the Average cost and the number of services for that Service Code • Use: AVG and COUNT functions and INNER JOIN option • Hint: PatCost_Service_Category, PatCost_Service_Results, PatCost_Reference_ServiceCode

33. Exercise 9 • Create query: __PatCost_AreaMappingDetail • Return: Area Mapping and the total cost from within Patient Costing. Get the total cost from the Service level. • Use: the SUM function, GROUP BY and INNER JOIN options • Hint: PatCost_Service_Category, PatCost_Service_Results, PatCost_Reference_ServiceCode, PatCost_Reference_AreaMapping

34. Exercise 10 • Create query: __GenLedg_AreaMappingDetail • Return: Area Mapping and the total cost from within General Ledger. Get the total cost from the Product Fractions level. • Use: SUM function, GROUP BY and INNER JOIN options • Hint:GenLedg_CostAllocationProductFractions_Results GenLedg_CostCentre_Category GenLedg_Reference_AreaMapping

35. Exercise 11 • Create query: __PatCost_vs_GenLedg • Objective: Join __GenLedg_AreaMappingDetail and __PatCost_AreaMappingDetail on Area Mapping. The query should return area mapping and the costs from each query. • Use: INNER JOIN option • Hint: __PatCost_AreaMappingDetail __GenLedg_AreaMappingDetail

36. Exercise 12 • Create query: __DeadEndDollars • Return: Account Rollup 1 and Costs at the end of the first GL process as a well as the costs as they were when imported into the GL. Filter these results for only those with a Cost Input of None • Use: SUM function, GROUP BY, INNER JOIN and WHERE options • Hint:GenLedg_CostAllocationAudit_Results GenLedg_Reference_AccountRollup1 GenLedg_Reference_CostInput GenLedg_Account_Category

37. Thank You 2007 PPM User Conference David Eades