As of 7 am on 9/3/2002, you should have access to the sa degree progress instance (also referred to as spaa) which houses the

[e-mail] Permission was granted via email from the graduate school to email Tracie these results since she is missing access to on/more of the underlying records; adm_appl_data

When using record residency_off, the record design may cause multiple rows of data to be returned for those students who switch between IS and OS. More specifically, if you do not code special logic, you will get duplicates in your query results and may not be aware. This due to poor record design by PS which includes a field they call effective term, but there is no eff dated logic present. To get the proper residency_off record, do this:

1. 
   add the effective_term as a 4 char expression.
   
2. 
   Use this expression via an inlist operator. In the sub query, select the residency_off record again. Select just the effective_term field, perform a max aggregate on it.
   
3. 
   Add criteria to match the emplids, careers and institution fields
   
4. 
   DO NOT add fields to the primary query which use the effective term field OR the residency field. Add expressions instead.
   

When working with the record JSR_PLN_CHG_TBL need to add not in list logic as a result of a user finding an issue. False drops are being returned when a student does something like adding a 2nd major. In specific cases, such as when the student already had a minor as plan sequence=20, the 2nd major is added as this plan sequence, and their minor is moved from plan sequence=20 to plan sequence=30. The app engine SQL which populates this record thinks this is a plan drop and shows it as the minor being dropped when in reality the minor is still active, just under a different plan sequence number. Specific SA emplid 108763193 and plan 0000103050 is a case in point between the dates 6/30/2013 and 6/30/2014. As of 7/3/2014, all queries using this record had the not in list logic added.

Common query writing tips:

1. 
   When building a connected query, results are NOT reliable IF any of the queries included in the connected query includes a UNION selection. Such design seems to confuse the connected query when it is producing the XML file, and the results of the XML become unreliable.
   
2. 
   When joining from class_tbl to class_instr, auto join will include the criteria EMPLID from class_tbl. Do not use this field as part of the join. It represents the emplid of the “course administrator” used for the blue course admin system, NOT the emplid of an instructor. Leaving this as part of the join will cause zero records to be returned.
   
3. 
   When joining from acad_plan to acad_plan_tbl, do not include the acad_career field as it is not keyed by the Registrars office and will cause zero rows to be returned.
   
4. 
   Class_tbl acad_org vs. study_field logic. The field of study is the actual college the subject falls under.  Acad_org is security driven.  So it is possible that the acad_org for ENG is ENG because they only want the people in the English Department to schedule courses for English.  They don’t want them to be able to schedule all classes for the College of Arts and Letters.  That is why we had to set up the field of study to show which college each subject “lives” in. study_field has been populated by the registrars office on two records: acad_plan_tbl and subject_tbl. This relationship allows you to perform data analysis for a “college” given any course or academic plan.
   
5. 
   DBA’s say using a subquery with an inlist (or not inlist) operator on a single field performs better than using the exists subquery.
   

Formatting header data via the XSL language in an RTF template document. Spent hours trying to find a way to change the format of a date field in the header section of an RTF document being used by BIP, so including here for future reference.

Using a running total in a BI Publisher report. See reference in jbu002.rtf from hprd.

Formatting data in an RTF file used for BI Publisher. Context: jadbip14 had two areas from a connected query which needed specific formatting, and inserting a normal conditional region was causing new lines to appear in the results between the fields no matter what I did (and I tried a LOT of options). Ended up using a suggestion from Rick Gardner. For each field within the RTF that you do NOT want to appear if it is blank or zero, use this XSL code embedded with a begin condition XSL variable. In other words, insert a conditional region and get the tag for the end of the condition, then edit the begin condition and set the code to either one of these values:

1. 
   If the field is numeric: 0?>
   
2. 
   If the field is alpha: " "?>
   

This note meant for users who schedule queries and BI Publisher reports with the results emailed to users. There are business process reasons when you may NOT want users to receive the results of a query or BI Publisher report IF there is no data selected for the given run. There are several processes in both the SA and HRMS systems which use recurring process definitions which recur daily, weekly, etc. when we do NOT want the users to receive email from the respective system if there are no results. Unfortunately, IF you use a query or BI Publisher report without bursting, the results are sent and there is nothing that you can do to prevent the empty results from being sent. A work around is available which will NOT send the results, details follow.

1. 
   Edit the data source (query or connected query) and however you need to, have the results include an oprid to be used for bursting. The oprid can be real (as in the intended recipients) OR if that logic does not apply to your report, you can force a single oprid be returned via an expression or a hard coded join to a record such as psoprdefn. Anyway, the data source MUST render an oprid as a field.
   
2. 
   Edit the BI Publisher report, go to the bursting tab and use the drop down box to select your oprid you created above to use for bursting. All of the other security can also be added as a normal bursted report. Save the report.
   
3. 
   In your process definition, as usual with any burted report, change the type to Email and whatever format you desire. As usual with bursted reports, edit the distribution hyperlink and enter appropriate verbiage for the email subject and body. You can now use either the email address list box to include additional email addresses to receive the bursted results (such as department email addresses) OR rows in the distribute to section.
   
4. 
   Allow the BI Publisher report to run (including recurrence) as normal. On days when results ARE produced, your recipients will receive the attached results as expected. On days when results are NOT produced, they will not receive results. If all of this is too confusing, call Pete DeSmit for clarification.
   

Page of Last updated by Pete DeSmit on 7/17/2018