1 / 49

SoftReports The Unchartered Adventure

Always be prepared when embarking on an adventure. Learn about test environment, embedded vs independent queries, and the differences between SoftLAB 4.0 and 4.5 in report writing.

arthurjones
Download Presentation

SoftReports The Unchartered Adventure

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SoftReports The Unchartered Adventure Kim Schroeder, Tom Larson, Alicia Evans and Donna Boss

  2. Always be prepared when embarking on an adventure • Test Environment • Save often • Validate, Validate, Validate

  3. Embedded vs Independent Query • Embedded query is attached to the layout. It is created by creating a new Report. When a new report is created a layout is also created.

  4. Embedded Query

  5. One way of attaching an independent query to the layout of the report is to create a new report first

  6. Independent query is created by creating a new query and later attaching it to a report

  7. Open properties • Click on the Advanced tab • Choose Data Source • Component Name > SQLDataSource • Select the query under Value

  8. To modify the query, close the layout and click on Edit Query under Report properties window

  9. When to use embedded or independent query • Use embedded query when you have subreports. NOTE: reports with subreports may run a bit slower as each subreport will have its own query. • Use independent query if you want to reuse query for other reports

  10. WARNING!!!!!!!!! • NOTE: Do not modify an independent query that you want to use for a different report. Make a copy of the query, give it a different name and modify. • When using an independent query remember if you make a change to one report using the query then it will affect the other reports using the query.

  11. Differences Between 4.0 and 4.5From a Report Writing Perspective SoftLAB 4.0 • Tags are easily obtainable via comment fields • Flags are more difficult to obtain as they are ASCII and BITAND values • Specimen tracking is more simplistic but less information is available SoftLAB 4.5 • HIS numbers more easily accessible • No tag comments in the comment fields • Flags are mostly separate fields • Specimen tracking database structure was reworked and more complex

  12. COMMENTS field in 4.5 • Most COMMENTS field in 4.5 have CLOB (Character Large Object)data type

  13. Texts in Comments field • No custom function was created to parse out texts in comments field When a query includes DISTINCT keyword to eliminate duplicate rows it produces error:

  14. CLOB COMMENTS HAS TO BE CONVERTED USING ORACLE FUNCTIONS: • DBMS_LOB.SUBSTR(COMMENTS, 4000) OR • TO_CHAR(SUBSTR(COMMENTS, 1, 3999)) In SQL the maximum characters that can be converted from CLOB is 4000

  15. RichTextBoxcontrol has to be used in 4.5 instead of TextBox control in the layout to get comments to print correctly. NOTE: The RichTextBoxcontrol does not work well with excel reports. It will always create extra rows. The issue is on the Excel side and not SoftReports

  16. In 4.0 most comments field need custom function to print texts in one line Example: Free text (type Z) Order Comments

  17. SR_ORDCOMM(V_P_LAB_ORDER.AAID)

  18. Order comments are contained in one field

  19. Questions???

  20. Creating Reports using common tables

  21. SoftLab Linking – Initial Links

  22. SoftLab Linking – Correct Links

  23. Tests Per Month Report Linkage (4.0 & 4.5)

  24. Tests Per Month Report ** NOTE: In Versions 1.1.11 and lower The correct syntax for criteria is using % signs instead of : The correct syntax needs to change from :D1 to %D1% and from :D2 to %D2% New Syntax :Param Old Syntax '%Param%' A colon is now used in front of the parameter ** NOTE: In Versions 1.1.11 and lower Database Prefixes are displayed and required. In Version 1.1.12 and above they are hidden.

  25. Tests Per Month Report

  26. SoftLab Specimen Linking – Initial Links (4.0)

  27. SoftLab Specimen Linking – Correct Links (4.0)

  28. SoftLab Specimen Linking – Initial Links (4.5)

  29. SoftLab Specimen Linking – Correct Links (4.5)

  30. TAT Report Linkage

  31. TAT Report ** NOTE: In Versions 1.1.11 and lower The correct syntax for criteria is using % signs instead of : The correct syntax needs to change from :D1 to %D1% and from :D2 to %D2% SELECT DISTINCT o.COLLECT_DATE, o.COLLECT_TIME, tr.TEST_ID, st.CLINIC_ID, o.PRIORITY, TO_CHAR(o.COLLECT_DT, 'MM/DD/YYYY HH24:MI') AS ORDDT, TO_CHAR(sp.COLLECTION_DT, 'MM/DD/YYYY HH24:MI') AS COLLDT, TO_CHAR(sp.RECEIVE_DT, 'MM/DD/YYYY HH24:MI') AS RECDT, TO_CHAR(tr.VERIFIED_DT, 'MM/DD/YYYY HH24:MI') AS VERDT, Round((sp.COLLECTION_DT - o.COLLECT_DT) * 1440) AS Ord_Coll, Round((sp.RECEIVE_DT - sp.COLLECTION_DT) * 1440) AS Coll_Rec, Round((tr.VERIFIED_DT - sp.RECEIVE_DT) * 1440) AS Rec_Ver, Round((tr.VERIFIED_DT - o.COLLECT_DT) * 1440) AS Ord_Verify, tr.ORDERING_WORKSTATION_ID, stest.DEPARTMENT_ID, (CASE WHEN %SortBy% = 'TID' THEN (tr.TEST_ID) WHEN %SortBy% = 'WARD' THEN (st.CLINIC_ID) WHEN %SortBy% = 'DEPT' THEN (stest.DEPARTMENT_ID) WHEN %SortBy% = 'WID' THEN (tr.ORDERING_WORKSTATION_ID) END) FROM lab.lab.V_P_LAB_PATIENT p INNER JOIN lab.lab.V_P_LAB_STAYst ON st.PATIENT_AA_ID = p.AA_ID INNER JOIN lab.lab.V_P_LAB_ORDER o ON o.STAY_AA_ID = st.AA_ID INNER JOIN lab.lab.V_P_LAB_TEST_RESULTtr ON tr.ORDER_AA_ID = o.AA_ID INNER JOIN lab.lab.V_P_LAB_SPECIMENsp ON sp.ORDER_AA_ID = o.AA_ID AND tr.SPECIMEN_TYPE = sp.SPECIMEN_TYPE AND tr.ORDERING_WORKSTATION_ID = sp.WORKSTATION_ID INNER JOIN lab.lab.V_S_LAB_TESTstest ON tr.TEST_ID = stest.ID AND tr.ORDERING_WORKSTATION_ID = stest.WORKSTATION_ID WHERE o.COLLECT_DATE BETWEEN %StartDate% AND %EndDate% AND tr.TEST_ID LIKE %Test_ID% AND st.CLINIC_ID LIKE %Ward% AND o.PRIORITY LIKE %Priority% AND (CASE WHEN (o.COLLECT_TIME) BETWEEN 1501 AND 2300 THEN 1 WHEN (o.COLLECT_TIME) BETWEEN 2301 AND 2359 THEN 2 WHEN (o.COLLECT_TIME) BETWEEN 0000 AND 0700 THEN 2 WHEN (o.COLLECT_TIME) BETWEEN 0701 AND 1500 THEN 3 END) LIKE %Shift% AND tr.RESULT NOT LIKE '.%' AND o.TESTS_CANCEL = 'N' AND tr.STATE <> 'Canceled' AND sp.COLLECTION_TIME <> -1 AND sp.RECEIVE_TIME <> -1 AND tr.VERIFIED_TIME <> -1 AND sp.IS_COLLECTED = 'Y' AND sp.IS_CANCELLED = 'N' ORDER BY (CASE WHEN %SortBy% = 'TID' THEN (tr.TEST_ID) WHEN %SortBy% = 'WARD' THEN (st.CLINIC_ID) WHEN %SortBy% = 'DEPT' THEN (stest.DEPARTMENT_ID) WHEN %SortBy% = 'WID' THEN (tr.ORDERING_WORKSTATION_ID) END) New Syntax :Param Old Syntax '%Param%' A colon is now used in front of the parameter ** NOTE: In Versions 1.1.11 and lower Database Prefixes are displayed and required. In Version 1.1.12 and above they are hidden.

  32. TAT Report Results

  33. Questions???

  34. SoftBank Linking – Removal of Links

  35. SoftBank Linking – Removal of Links

  36. SoftBank Linking – Correct Linkage

  37. SoftBank Linking – Removal of Links

  38. SoftBank Manual Links

  39. Transfusion Report Linkage

  40. SELECT DISTINCT bb.bbank.V_P_BB_Patient_Transfusion.STARTDT AS TXDT, TO_CHAR(bb.bbank.V_P_BB_Patient_Transfusion.STARTDT, 'MM/DD/YY HH24:MI') AS TX_DTTM, bb.bbank.V_P_BB_Patient.LAST_NAME, bb.bbank.V_P_BB_Patient.FIRST_NAME, bb.bbank.V_P_BB_Patient.MRN, bb.bbank.V_P_BB_BB_Order.ORDERNO AS ORDNO, bb.bbank.V_P_BB_Selected_Unit.WARD, bb.bbank.V_P_BB_Patient_Stay.DRG, bb.bbank.V_P_BB_Product_Order.PHYSICIAN AS PHYS, bb.bbank.V_P_BB_Product_Order.REASON AS REAS, bb.bbank.V_P_BB_Product_Order.SURGICAL_PROCEDURE AS SURG, bb.bbank.V_S_BB_Y_Blood_Product.DISPLAYCODE AS DIS_CODE, bb.bbank.V_P_BB_Selected_Unit.SELECTED_PRODUCT_CODE AS UPROD, bb.bbank.V_P_BB_Selected_Unit.SELECTED_UNITNO AS UNITNO, bb.bbank.V_P_BB_Selected_Unit.SELECTED_PRODUCT_EXT AS EXT, bb.bbank.V_P_BB_Unit.ABO AS uABO, DECODE(bb.bbank.V_P_BB_Unit.RH, 'P', 'POS', 'N', 'NEG') AS uRH FROM bb.bbank.V_P_BB_Patient INNER JOIN bb.bbank.V_P_BB_Patient_Stay ON bb.bbank.V_P_BB_Patient.AA_ID = bb.bbank.V_P_BB_Patient_Stay.PAT_RECORD INNER JOIN bb.bbank.V_P_BB_BB_Order ON bb.bbank.V_P_BB_Patient_Stay.AA_ID = bb.bbank.V_P_BB_BB_Order.PTS_ORDER INNER JOIN bb.bbank.V_P_BB_Product_Order ON bb.bbank.V_P_BB_BB_Order.AA_ID = bb.bbank.V_P_BB_Product_Order.ORD_PROD INNER JOIN bb.bbank.V_P_BB_Selected_Unit ON bb.bbank.V_P_BB_Product_Order.AA_ID = bb.bbank.V_P_BB_Selected_Unit.PROD_SELUN INNER JOIN bb.bbank.V_P_BB_Patient_Transfusion ON bb.bbank.V_P_BB_Patient_Transfusion.AA_ID = bb.bbank.V_P_BB_Selected_Unit.TRAN_UNIT AND bb.bbank.V_P_BB_Patient.AA_ID = bb.bbank.V_P_BB_Patient_Transfusion.TRANSFUSION INNER JOIN bb.bbank.V_P_BB_Unit ON bb.bbank.V_P_BB_Selected_Unit.SELECTED_UNITNO = bb.bbank.V_P_BB_Unit.UNITNO AND bb.bbank.V_P_BB_Selected_Unit.SELECTED_PRODUCT_CODE = bb.bbank.V_P_BB_Unit.UNIT_PRODUCT AND bb.bbank.V_P_BB_Selected_Unit.SELECTED_PRODUCT_EXT = bb.bbank.V_P_BB_Unit.UNIT_EXTENSION LEFT JOIN bb.bbank.V_S_BB_Y_Blood_Product ON bb.bbank.V_P_BB_Unit.UNIT_PRODUCT = bb.bbank.V_S_BB_Y_Blood_Product.CODE WHERE bb.bbank.V_P_BB_Patient_Transfusion.STARTDT BETWEEN TO_DATE(:D1, 'YYYY-MM-DD HH24:MI') AND TO_DATE(:D2, 'YYYY-MM-DD HH24:MI') + .99999 ORDER BY bb.bbank.V_P_BB_Patient.LAST_NAME, bb.bbank.V_P_BB_Patient.FIRST_NAME, bb.bbank.V_P_BB_Patient.MRN, bb.bbank.V_P_BB_Patient_Transfusion.STARTDT Transfusion Report – sql ** NOTE: In Versions 1.1.11 and lower The correct syntax for criteria is using % signs instead of : The correct syntax needs to change from :D1 to %D1% and from :D2 to %D2% New Syntax :Param Old Syntax '%Param%' A colon is now used in front of the parameter

  41. DERIVED Tables • A “derived table” is essentially a statement-local temporary table created by means of a subquery in the FROM clause of a SQL SELECT statement. It exists only in memory and behaves like a standard view or table.

  42. When to use Derived tables: • Derived tables are useful when you need to generate aggregates in a table • AGGREGATES: • COUNT, SUM, AVG, MAX, MIN Example: SELECT q_ordcount.ORDERED_DATE, Count(q_ordcount.ID) AS ORD_COUNTS FROM (SELECT o.ID, o.ORDERED_DATE, NVL(%StartDate%, to_char(trunc(add_months(sysdate, -3), 'MM'), 'YYYYMMDD')) AS SDATE, NVL(%EndDate%, to_char(trunc(last_day(add_months(sysdate, -1))), 'YYYYMMDD')) AS EDATE FROM lab.lab.V_P_LAB_ORDER o WHERE o.ORDERED_DATE BETWEEN NVL(%StartDate%, to_char(trunc(add_months(sysdate, -3), 'MM'), 'YYYYMMDD')) AND NVL(%EndDate%, to_char(trunc(last_day(add_months(sysdate, -1))), 'YYYYMMDD')) ) q_ordcount GROUP BY q_ordcount.ORDERED_DATE ORDER BY q_ordcount.ORDERED_DATE

  43. Result from Query: • This shows counts of orders per day • M is month in number

  44. RHIG Candidates Report (Mom ~ Baby Report)Use of Derived Table

  45. RHIG Candidates Report (Mom ~ Baby Report)Use of Derived Table

  46. SoftBank Functions – Report without Function

  47. SoftBank Function – Report with Function

  48. Please remember to take a few moments to fill out the session survey. You can find the QR code in your Conference Brochure as well as hyperlinks to the survey online at www.snuginconline.org with the SNUG 2019 handouts.

More Related