SQL Based Reporting with Crystal Reports Ido Millet

SQL Based Reporting with Crystal Reports Ido Millet, Penn State Erie Sam & Irene Black School of Business www. Millet. Software. com Synergy Developer Conference June 6 -8, 2005

The 4 -Year Itch Slide 2 Copyright © 2004 Millet Software All rights reserved.

The 4 Year Itch Slide 3 Copyright © 2004 Millet Software All rights reserved.

The IF Technique Kary May Coagulates: 200 products, 100 sales representatives, 100, 000 stores STORE INVOICE LINE_ITEM Store_Code Invoice_Number Name Store_Code Catalog_Number Balance Invoice_Date Qty_Ordered City Sales_Rep_Code Unit_Price State Ship_Via Discount_% Store Size Date Needed Returned (Y/N) Date Shipped SALES_REP Slide 4 Terms PRODUCT Catalog_N Sales_Rep_Code Description Name Sell_Price Commission_% Our_Cost Gender Qty_On_Hand Quota ($) Qty_On_Order Hire_Date Product_Type Copyright © 2004 Millet Software All rights reserved.

The STOP&C Technique STORE INVOICE LINE_ITEM Store_Code Invoice_Number Name Store_Code Catalog_Number Balance Invoice_Date Qty_Ordered City Sales_Rep_Code Unit_Price State Ship_Via Discount_% Store Size Date Needed Returned (Y/N) Date Shipped SALES_REP Slide 5 Terms PRODUCT Catalog_N Sales_Rep_Code Description Name Sell_Price Commission_% Our_Cost Gender Qty_On_Hand Quota ($) Qty_On_Order Hire_Date Product_Type Copyright © 2004 Millet Software All rights reserved.

The (neglected) 3 rd Perspective 1. Status 2. Trend 3. …? 4. (Demo) Slide

Change View (& Some Techniques) Slide 7 Copyright © 2004 Millet Software All rights

Aliases, Outer Joins, and Cheesecakes 4/5 New (direct) approach F 2 to edit the

Slide 9 Copyright © 2004 Millet Software All rights reserved.

Drugs with & w/o Charges for a Given Patient Date: Tue, 05 Apr 2005 08: 11: 07 – 0400 From: "Jeremy Green" Jeremy. Green@hamot. org To: ixm 7@psu. edu Subject: Another Crystal Question I have a “Pharm. Src” (Drug type) table and a “Pharm. Chg” (Charges) table (showing drugs dispensed to patients). They want the report for a given patient to show all Drug types and the quantity, if actually charged. I created a left outer join: Drug type charges but when I set the record selection formula to a particular patient, it limits the records to those with actual charges. I am sure there must be a way to do this! Slide 10 Copyright © 2004 Millet Software All rights reserved.

Drugs with & w/o Charges for a Given Patient Q: WHY? A: Identify Cases where charges were omitted àSort by % Used Rather than Alphabetically by Drug Description àFlag gaps between Avg_Q and Actual Q Slide 11 Copyright © 2004 Millet Software All rights reserved.

UNION Soup • UNION versus UNION ALL • Child Care Center Case • Demo

Views Stored Procedures? Parsing (~), Parameters (!), Skill Set, Visibility, Debugging (!) “Dishes” versus

Subreport or Sandwich? ? Slide 14 Copyright © 2004 Millet Software All rights reserved.

Conditional Totals (in proportion) ? Slide 15 Copyright © 2004 Millet Software All rights

Conditional Totals (in proportion) 2/2 • Sum of 1 -or-0 is a Conditional ______ ? • Sum of Value-or-0 is a Conditional _____ ? • What about Proportions? __________ ? • Two Main Advantages over Running Totals! Slide 16 Copyright © 2004 Millet Software All rights reserved.

Normalization (& Replication) On The Fly Repeater Table approach (step 1) Slide 17 Copyright

Normalization (& Replication) On The Fly UNION approach Slide 18 8/10 SELECT INVOICE. Invoice_N, INVOICE. Proc 1 as PROC FROM INVOICE UNION ALL SELECT INVOICE. Invoice_N, INVOICE. Proc 2 as PROC FROM INVOICE UNION ALL SELECT INVOICE. Invoice_N, INVOICE. Proc 3 as PROC FROM INVOICE UNION ALL SELECT INVOICE. Invoice_N, INVOICE. Proc 4 as PROC FROM INVOICE UNION ALL SELECT INVOICE. Invoice_N, INVOICE. Proc 5 as PROC FROM INVOICE ORDER BY 1, 2 ; Copyright © 2004 Millet Software All rights reserved.

Normalization (& Replication) On The Fly Different case—same idea Slide 19 Copyright © 2004

Normalization (& Replication) On The Fly 10/10 Different Case -- Same Idea: Brad. W (IS/IT--Manageme) Sep 11, 2004 …I am working on a report for a lodging application. I have a hotel and need to know how many rooms were occupied each night. The tricky part is that guests check in and out on different days. I need to take a guest who checks in on 1/1 and out on 1/5 and tally one occupied room for each day that guest is in house… Slide 20 Copyright © 2004 Millet Software All rights reserved.

SELECT Statements in SQL Expressions Example: (select MAX(quantity) from My. Table) Requirements: 1. Statement Inside (…) 2. Single Value Expressions 3. Table DOESN’T have to be in Report! Slide 21 Copyright © 2004 Millet Software All rights reserved.

Complex SELECT Statements in SQL Expressions Example: (SELECT Sum(OUT. `amount`) FROM My. Table My. Alias WHERE My. Alias. `CUST_ID` = My. Table. `CUST_ID`) Requirements: 1. Statement Inside (…) 2. Single Value Expressions 3. Use an Alias Slide 22 Copyright © 2004 Millet Software All rights reserved.

$ALL Option in Parameters Good: {? Last_Name} = {employee. lname} OR {? Last_Name} =$

ALL Option in Parameters Good: {? Last_Name} = {employee. lname} OR {? Last_Name} = "ALL" Bad: {? Last_Name} = "ALL" OR {? Last_Name} = {employee. lname} Slide 23 Copyright © 2004 Millet Software All rights reserved.