Transcription
CAMS Enterprise – Custom ReportsEnterpriseCustom ReportsThree Rivers Systems, Inc.Published: 18 September 2015AbstractThis document is designed to provide information necessary to use customized reports in CAMS Enterprise by modifyingexisting reports. It will discuss the following: Setup Considerations Customize existing reports Create custom reports Create custom reports for use with BYORs Create advanced custom reports How to handle sub reports How to use .ttx files for data Add customized reports to report selection dropdowns lists SSI files for dropdownsThis document will not discuss creating a Crystal report, except when necessary to add detail to the above items or toprevent errors. The use of Crystal Reports is beyond the scope of this document. It is assumed that you know how touse Crystal Reports.Page 1 of 50
CAMS Enterprise – Custom ReportsDisclaimerThree Rivers Systems, Inc. makes no representation orwarranties with respect to the contents or use of this guide.Further, Three Rivers Systems, Inc. reserves the right to revisethis guide and make changes to its contents at any time withoutobligation to notify any person or entity of such revisions orchanges.In no event will Three Rivers Systems, Inc. be liable to buyer orany other party for any damages, including any lost profits, lostsavings, or other special incidental or consequential damagesarising out of the use of or inability to use such product, even ifThree Rivers Systems, Inc. has been advised of the possibilityof such damages, or for any claim by any other party. 2015 Three Rivers Systems, Inc. All rights reserved. Allcompany and product names included in this site may betrademarks or registered trademarks of their respectivecompanies. The information contained in these pages issubject to change at any time without prior notice.Page 2 of 50
CAMS Enterprise – Custom ReportsTable of ContentsSetup Considerations . 4Overview . 4Customization Rule. 4Elements . 5Adding a Report . 7Modifying a Pre-Existing CAMS Enterprise Report . 7Create a Custom Report (Intermediate) . 8Security . 8Create a Custom Report for BYORs (Intermediate) . 15Create a Custom Report (Advanced) . 21Displaying The Custom Report . 24User Defined Routines . 26Advanced Variation with Dropdowns . 28Final Code . 32Adding A Customized Report . 36Modify an SSI File . 37Report Items . 39Subreports . 39TTX Files . 39Verify Database . 41Type 1000 Reports . 42SSI Files . 44Admissions . 44Registration . 44Billing . 45Financial Aid . 46Housing . 47Health. 47Parking . 47Placement . 47Development . 48Faculty . 48Meeting . 48CAMS Manager . 48Tools . 49Index. 50Page 3 of 50
CAMS Enterprise – Custom ReportsSetup ConsiderationsOverviewUsing customized reports is easy in CAMS Enterprise. Some items to considerare: Knowing the name of the report that you want to customize. Knowing where (in which report dropdown list) the customized report willbe accessed. Knowing what SSI (Server Side Include) file must be modified.You can get the name of a report that you want to modify by opening the reportand looking in the status bar of the report to see the name. You may also usethe SSI Files section at the end of this document to find out what SSI file you canlook in for the name of the report.The SSI Files section at the end of this document will assist you in determiningwhich SSI file is used for a specific report dropdown.Once you have customized your report you will need to modify the appropriateSSI file and then you will be able to access the report immediately from CAMSEnterprise.Customization RuleWhen customizing a report provided by Three Rivers Systems, we recommendthat you first save the report in a custom report subdirectory within the Reportsdirectory. We advise giving the customized report a different name, for example,starting each custom report with the initials of your institution. This will avoidsituations where your custom report could be overwritten by Three RiversSystems during the patch process.Note: If your custom report uses subreports, those subreports may also need to be copiedto the Custom folder for the report to work correctly.On occasion, Three Rivers Systems may make changes to CAMS Enterprise thatmay adversely affect your customized report. To troubleshoot the issue, pleasecheck the What’s New documentation and CAMS Manager emails for the latestinformation. If further assistance is needed, please contact your CAMSmanager.Although a fairly straightforward process, issues can arise, so it is suggested thatyou make a backup copy of any SSI files you need to modify before you begin.Page 4 of 50
CAMS Enterprise – Custom ReportsElementsThere are four (4) elements to a CAMS Enterprise report: The report criteria choice screen(s) The SSI file that contains the names and options for each of the reportsavailable in the dropdown list. The ASP page that will call the query and then the report. The Crystal ReportThe report criteria choice screen(s)Each of the tabs/screens on the report screens allows you to choose the criteriafor your report. Most of these fields are exported to record sets which are thenincluded in your Crystal Reports.Figure 1: Sample Report Criteria pageSSI FilesSSI (Server Side Include) files are how CAMS Enterprise populates thedropdown lists from which you select reports.SSI files are usually located in the “SSI” subfolder (for your college database) ofyour CAMS Enterprise folder. (SSI2 is for the training database.) They aresimple text files which contain HTML code and can be edited with any text editorin Windows.Items appear in the dropdown list in the same order as they appear in the file.Figure 2: Sample SSI filePage 5 of 50
CAMS Enterprise – Custom ReportsThe HTML code in the SSI file contains all the information that CAMS Enterpriseneeds to run a report; an internal system code, the location of the Crystal reportfile, the asp file that pulls the data and passes it to the report, and the display textyou will see for the report in the drop down on CAMS Enterprise.Note: There are two folders under the CAMS Enterprise installation, SSI and SSI2. TheSSI folder has all the SSI files associated with your institution’s database and SSI2 hasthe files associated with the training database. When CAMS Enterprise patches require aCAMS Manager to modify an SSI file for new or modified reports Three Rivers Systemswill modify the corresponding SSI file in the SSI2 folder allowing the CAMS Manager toverify that they’ve made the changes correctly.ASP PagesThe ASP pages that do the query and call the Crystal Reports are all stored inthe root folder of the CAMS Enterprise installation. Where possible, use theCAMS Enterprise ASP pages that are already created.Figure 3: Root FolderCrystal ReportAfter selecting the criteria, selecting the report from the dropdown, and clickingthe Print button, all the above items work together to create the actual report.It is very important that all four of these elements be correctly set up andassociated with each other.Figure 4: Sample Crystal ReportPage 6 of 50
CAMS Enterprise – Custom ReportsAdding a ReportCAMS Enterprise reports are usually created using data sets that are populatedfrom the CAMS Enterprise middleware. Modifying certain reports from CAMSEnterprise allows the developer to only use the fields that are available for theoriginal report. If additional fields are required for a report but are not in a dataset (TTX) then a custom report must be developed.Three options to adding additional reports to CAMS Enterprise: Modifying a Pre-Existing CAMS Enterprise Report Create a Custom Report (Basic) Create a Custom Report (Advanced)Modifying a Pre-Existing CAMS Enterprise ReportWhen modifying a pre-existing report care must be taken to not overwrite theoriginal report file.Step-By-Step: Modify a Report with TTX1. Open the report in Crystal Reports. Immediately use Save As to rename thefile and save it to the Custom directory under the CAMSEnterprise Reportsdirectory.2. Reports that contain a TTX data source under the Field Explorer cannot haveadditional fields added to the report. This is because the data provided to theTTX is static. Changes to the internal code would have to be made to allowadditional fields.TTX Data SourceFigure 5: TTX Data Source3. Modify the report as desired. You can only add fields to the report that areavailable in the Database Fields section.4. Save the file.5. See Adding a Customize Report on page 36 to make the report available tousers.Note: Additional fields may not be available to search from in a BYOR screen.Page 7 of 50
CAMS Enterprise – Custom ReportsCreate a Custom Report (Intermediate)The following information will provide the necessary steps to add a custom reportthat uses a view(s) for the data source.SecurityDesigning the following reports types requires that you have a valid SQL username and login (integrated security should never be used as the report will fail torun at runtime out of CAMS Enterprise). Three Rivers Systems cannot supply thisfor you, you need to contact your CAMS Manager, Network Administrator, and/orDatabase Administrator for this information.Step-By-Step: Modify a Report with ViewsSome reports in CAMS use SQL views for their data source. Unlike TTX files theview provides the data to the report. This means that technically you couldmodify a view with the additional fields needed and they would be available in thereport.Warning: Never delete fields from the view. You can only add fields.Before you can customize the view you need to determine which view it is.1. Determine that the report you want to customize is using a view.Figure 6: View Data SourcePage 8 of 50
CAMS Enterprise – Custom Reports2. You can now open that view in SQL and add your additional fields.Figure 7: Add Fields to a View3. Save the view.4. Open the report in Crystal Reports. Immediately use Save As to rename thefile and save it to the Custom directory under the CAMSEnterprise Reportsdirectory. Click Database Verify Database. Once it is updated it will nowdisplay your additional fields for you to add to the report.5. Customize the report as desired.6. See Adding a Customize Report on page 36 to make the report available tousers.Note: Additional fields may not be available to search from in a BYOR screen.Page 9 of 50
CAMS Enterprise – Custom ReportsStep-By-Step: Modify a Report with ViewsBefore creating the custom report you will need to determine what the report isused for, what data should provided, the names of all the fields and their datatypes, and the layout of the report.The custom report used for this documentation displays a list of all advisors andtheir advisees.1. Open Crystal Reports and access the Database Expert. Use Create NewConnection and select OLE DB (ADO) and then Microsoft OLE DBProvider for SQL Server.Figure 8: Set New Connection2. Click Next.Figure 9: Connection Information3. A valid User ID and Password must be used that has access to the SQLserver tables or views for the CAMS Enterprise database. Do not useIntegrated Security.4. Click Finish.Page 10 of 50
CAMS Enterprise – Custom ReportsFigure 10: Access to Tables and ViewsYou can now select the tables and/or views that you would like to use in thereport. If possible, create views to provide all the data and use that as theonly data source. Crystal Reports does not handle multiple data sources verywell.5. Ensure that if more than one table or view is used that they are linkedcorrectly.Figure 11: Linked Tables6. Create the report. Notice that there are no TTX files in use. You are workingdirectly from the tables and views.Figure 12: Create the ReportPage 11 of 50
CAMS Enterprise – Custom Reports7. Save the report in CAMS Enterprise Reports Custom folder. For thisexample the report is called TRSAdviseeList.rpt.8. Create the ASP pageA special ASP page must be used to run this and any other custom reportscreated above.Below is the ASP code necessary to run the custom report. No modification ofthis code is required. Save the ASP in the CAMS Enterprise folder. For thisexample it is called TRSCustomReports.asp.Warning: It must be saved to this location. %@ LANGUAGE "VBSCRIPT" % %Dim bGetCriteriaDim eGetCriteriaDim reportNameDim strUserIDDim strDBNameDim strdbSrvDim strSrvNameDim rsSubDocumentsDim iDim strParse()Dim strDBUserDim strDBPasswordDim RptCntDim objDBTableDim objCPPropertiesreportname e.rpt"% !-- #include file "reports\AlwaysRequiredStepsv8.asp" -- %on error goto 0strUserID trim(Request.QueryString("uid"))strDbName trim(Request.QueryString("db"))Page 12 of 50
CAMS Enterprise – Custom ReportsstrDbSrv trim(Request.QueryString("dbSrv"))strSrvName trim(Request.QueryString("Srv"))Set busObj t objDBTable session("oRpt").Database.Tables(1)Set objCPProperties m("Provider") "SQLOLEDB"Call busObj.SetCRConnProp(objCPProperties, strDbSrv, strDbName)RptCnt session("oRpt").Database.Tables.CountFor i 1 to RptCntSet objDBTable ion objDBTable.NameNext% If this line is uncommented it willlimit the report datato students whohave a last name ofTurk.‘You can use formulas and variables directly in the Crystal Report or in thisASP page as shown below. Uncomment both lines.‘session("oRpt").FormulaSyntax 0'session("oRpt").RecordSelectionFormula "{student.lastname} ""Turk"""‘If a subreport is used then for each subreport un-comment the lines below‘including the data source lines. It should be noted that if a subreport isused‘it will severely add to the amount of time it takes to display the report.‘Therefore, ‘subreports are not recommended.'Set CRSubreports session("oRpt").OpenSubreport("gg ) "SQLOLEDB"'Call busObj.SetCRConnProp(objCPProperties, strDbSrv, strDbName)'objDBTable.Location objDBTable.Name'Set objDBTable tion objDBTable.NamePage 13 of 50
CAMS Enterprise – Custom Reports !-- #include file "reports\MoreRequiredSteps.asp" -- !-- #include file "reports\SmartViewerActivexv8.asp" -- 9. Save the ASP page.10. Add the report to a dropdownOnce the report is finished it can be added to any available report dropdownsthat are listed below in the SSI Files section of this document.For this example the report will be added to the Admissions Student BYOR report screen.Figure 13: Adding New Custom ReportThe last line in the SSI file is used to display the report. Use the followingformat all on one line no spaces except for what the user will see in thedropdown: Optionvalue "20 reports\custom\TRSAdviseeList.rpt TRSCustomReports.asp" Advisor Student List Report /option You can use Option value ”20 for all entries using theTRSCustomReports.asp. This is the location of the custom report, reports\custom\TRSAdviseeList.rpt. This is the reference to the ASP page that is used to display the customreport, TRSCustomReports.asp" . This is what the user will see displayed in the dropdown, AdvisorStudent List Report. Spaces are allowed here. This closes the Option, /option .Figure 14: Custom Report Available in DropdownPage 14 of 50
CAMS Enterprise – Custom ReportsCreate a Custom Report for BYORs (Intermediate)Three Rivers Systems calls these types of reports Direct Connect Reports. Theyuse reports shells, known as Direct Connect Shells, which utilize a permanenttemporary table that will contain the data set that matches selected reportcriteria. These “permanent” tables are placed in the database to handletemporary transactions.When the BYOR report is printed the selected criteria first populates theassociated Direct Connect Shell temporary table with the resulting prospects,students, or faculty. The tables or views that you have added to the customreport are linked to this temporary table and display the correct report data.For the purpose of demonstration, the following steps will provide instructions oncreating a new report in the Student BYOR. However, this same process can beapplied to all areas where the Direct Connect Shells have been placed. See thetable included in this document for list of such reporting areas.The purpose of this report is to display a Student and all of their defined contacts.Step-By-Step: Open the Starter Report1. In order to have data available for print preview during the design of yourcustom report, you will need to add the starter report to therptADMBYORReports.ssi file then run the report in CAMS. If you do notrequire data for print preview during the customization, skip to step 4. To addthe starter report to the SSI file, add the following line: optionvalue "1000 reports\StuAdmStarter.rpt ADMStudentBYORType1000.asp" Tmp Table Starter /option 2. From the CAMS Enterprise Home page, click Admissions Students Report BYOR. Click the Reports tab and select the Tmp Table Starterreport you added.Figure 15: TMP Table Starter ReportPage 15 of 50
CAMS Enterprise – Custom Reports3. Click Print. The report opens. Look at the status bar at the bottom of thereport for the actual report name, which is StuAdmStarter.rpt.4. Open Crystal Reports and open the StuAdmStarter.rpt report.Figure 16: Student Starter ReportThe temporary table for this report is called tmpRptBYORAdmStudent andnotice there are only three fields available, RptUser, StudentUID, andAddressID. RptUser – used to define specific results for the current user running thereport. Allows other users to run custom reports at the same time. StudentUID – used to identify the student. AddressID – used to identify the specific address requested.4. Immediately save this report in the custom directory with a custom name.This report will be called TRS StudentContact.rpt.5. To add additional fields you need to add and link to other tables or views,including custom tables or views.Step-By-Step: Link Other Tables or Views1. On the report access the Database Expert. Use Create New Connectionand select OLE DB (ADO) and then Microsoft OLE DB Provider for SQLServer.Figure 17: Set New ConnectionPage 16 of 50
CAMS Enterprise – Custom Reports2. Click Next.Figure 18: Connection Information3. Select the CAMS Enterprise SQL server and enter a valid SQL User ID andPassword Do not use Integrated Security. Select the CAMS Enterprisedatabase.4. Click Finish.Figure 19: Access to Tables and ViewsYou can now select the tables and/or views that you would like to use in thereport. If possible, create views to provide all the data and use that as the onlydata source.Page 17 of 50
CAMS Enterprise – Custom ReportsStep-By-Step: Adding Data Sources1. Some knowledge of the database and its objects is required to pick theproper CAMS supplied tables/views, or you can simply create one of yourown. In this example the following TRS supplied view will be used and theappropriate Direct Connect Shell:a. CAMS StudentContactList Viewb. CAMS Student ViewNote: If the views/tables that you want to use are not available to you, you need to contactyour CAMS Manager, Network Administrator, and/or Database Administrator to see if theSQL User name you used has a minimum of Select rights to those objects (at RuntimeCAMS uses it’s predefined SQL user that has full rights to all objects in our database sothere is no worries about that).Figure 20: Student Contact View2. Click the greater than arrow to add the views to the Selected Tables: column.Figure 21: Views AddedPage 18 of 50
CAMS Enterprise – Custom ReportsStep-By-Step: Link Data Sources1. Once the views have been added to the report, click on the Links tab at thetop of the Database Expert window. The default join type is an Inner Join. Bydefault, Crystal Reports links by all matching field names/types with an innerjoin.Figure 22: Linked Data Sources2. Click OK and the report will be displayed in the preview tab.3. Create the report. Notice that there are no TTX files in use. You are workingdirectly from the tables and views. You can delete the existing fields from thereport and then add the fields you want.Figure 23: Sample Report DesignThe views that were added will display every student in the database and theircontacts. By adding the temporary table you will be able to use the BYOR to limitthe list of students to only those that meet the selected criteria.Page 19 of 50
CAMS Enterprise – Custom ReportsStep-By-Step: Add Report to CAMS Report List1. The next step is to add this report to the Student BYOR report formatdropdown. Open the rptADMBYORReports.ssi in a text editor. This file islocated in the SSI folder for the College database and in the SSI2 folder forthe training database.Figure 24: rptADMBYORReports.ssi file2. Find the line that contains the Tmp Table Starter text. You must copy this lineexactly as written and paste it at the bottom of the list. (Where ever this lineis located determines where it displays in the drop down list.)You will only change the report name and location and the drop downdescription that your users will see. Everything else in the line must remainthe same. option value "1000 reports\StuAdmStarter.rpt ADMStudentBYORType1000.asp" Tmp Table Starter /option Change the StuAdmStarter.rpt text to custom\TRS StudentContact.rpt and change the Tmp Table Starter to TRSStudent Contacts.The line should now read: option value "1000 reports\custom\TRS StudentContact.rpt ADMStudentBYORType1000.asp" TRS Student Contacts /option Note: By adding the college initials to the beginning of the report name and the drop downdisplay Three Rivers Systems will recognize that it is a custom report and your users willknow that that is the report they should use.3. Save this file.You can now go to the Student BYOR and your report will be listed. Selectyour BYOR criteria and then print your custom report.Page 20 of 50
CAMS Enterprise – Custom ReportsCreate a Custom Report (Advanced)The following information will provide the necessary steps to add a customreport, using a custom stored procedure, custom TTX file and the User DefinedRoutine section of CAMS Enterprise.Only basic steps will be listed in creating a store procedure, ttx file, and customreport. Additional information about these items can be found using your SQLand Crystal Reports documentation.Data for the Custom ReportBefore creating the custom report you will need to determine what the report isused for, what data should provided, the names of all the fields and their datatypes, and the layout of the report.The custom report used for this documentation displays a list of all ethnicities andtotal count of students who are registered for a specific term.Figure 25: Ethnicity Custom ReportPage 21 of 50
CAMS Enterprise – Custom ReportsCreate the TTX fileTo create a custom report that uses a data set you need to create a TTX file thatcontains all the needed fields, their data type, length and sample data.Figure 26: Report TTX fileAdd the TTX file as the data source to your blank custom report.Figure 27: Adding TTX As Data SourceCreate The ReportOnce the data source is attached to the report you can design the report.Figure 28: Designing The ReportPage 22 of 50
CAMS Enterprise – Custom ReportsBy clicking on the preview tab you can view the sample data from the TTX file.Figure 29: Previewed ReportCreate Stored ProcedureA stored procedure is used to build the data set directly from the database thatwill be displayed when the report is run.Figure 30: Stored ProcedurePage 23 of 50
CAMS Enterprise – Custom ReportsDisplaying The Custom ReportTo display the custom report using the Crystal Report viewer and the storedprocedure the calling ASP page must be created. The following code is asample of the ASP page used in designing the Ethnicity report used for thisexample. To simplify the coding some items are hard-coded, such as theconnection information. Creating web pages with variables instead of hardcoding is beyond the scope of this document.Ethnic Report.asp %@language VBScript % %Response.buffer trueDim strUserIDDim strSemesterDim strTypeDim strDBNameDim strSvrNameDim strDBSrvDim strError, CriteriaRSDim objCmd, objParam, objParam1, objRS, obj, busAdmitObjDim strOutDim reportnameReport Namereportname "reports\TRSEthnicReport.rpt"strUserID trim(Request.QueryString("uid"))Dim strConnectionSet obj erver NamestrConnection obj.DBConnString("Servername", "CAMS Enterprise")DatabaseSet objDBConn ommandTimeout 600objDBConn.open(strConnection)% !-- #INCLUDE FILE "reports\AlwaysRequiredStepsv8.asp" -- %Must include this lineSet objRS Server.CreateObject("ADODB.Recordset")objRS.Open "TRS Ethnic Report 'SP-04', 'Official'", objDBConnPage 24 of 50
CAMS Enterprise – Custom ReportsSet session("oData") objRSRun stored procedureif session("oData").BOF or session("oData").EOF thenStored nd ifsession("oRpt").DiscardSavedData' link the data to the re
CAMS Enterprise - Custom Reports Page 1 of 50 Enterprise Custom Reports Three Rivers Systems, Inc. Published: 18 September 2015 Abstract This document is designed to provide information necessary to use customized reports in CAMS Enterprise by modifying existing reports. It will discuss the following: Setup Considerations
