1 / 21

One Report, Two Data Sources

One Report, Two Data Sources. Reporting from Unidata and SQL Server in a single report. PRESENTER: Jared Carter | jcarter@tesc.edu | October 11, 2010. Quick Facts about Thomas Edison State College Primarily an online distance education institution Approximately 18,735 students

christmas
Download Presentation

One Report, Two Data Sources

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. One Report, Two Data Sources Reporting from Unidataand SQL Server in a single report PRESENTER: Jared Carter| jcarter@tesc.edu | October 11, 2010

  2. Quick Facts about Thomas Edison State College • Primarily an online distance education institution • Approximately 18,735 students • Approximately 45% of our students in the military • Datatel (Unidata) • Blackboard (SQL Server)

  3. Challenge Certain military contracts required TESC to notify the military when a student fell behind in a course. Manually reporting from Mentors was: • Labor intensive • Inconsistent and confusing • Resulted in potential contract violation A decision was made to use the date the student last accessed a course to indicate “tardiness” in the course. This information will be reported regularly to the military.

  4. Solution Create a report that pulls “live” data from both Datatel and Blackboard Steps • Identify and prepare data sources, tables, and fields needed for reporting. • Define the selection criteria (Filter) • In Informer, map data source, files, and remote links. • Create Informer Report.

  5. Identify your data sources, tables, and fields SQL Server: Tables, Views, Fields Unidata: Entities/Files, Attributes, computed columns/i-descriptors Searching through 3rd party software, databases, and documentation is time consuming, and at times, very frustrating. Solicit help.

  6. Blackboard Data Sources • USERS • pk1 • user_id • COURSE_MAIN • pk1 • users_pk1 • course_id • batch_uid • COURSE_USERS • crsmain_pk1 • last_access_date Datatel Data Sources • PERSON • @ID • SSN, LAST.NAME, FIRST.NAME • STUDENT.COURSE.SECTION • SCS.STUDENT • X.SCS.SYNONYM • SCS.REG.METH • COURSE.SECTIONS • SEC.TERM • SEC.SUBJECT, SEC.COURSE.NO, SEC.START.DATE, SEC.END.DATE, SEC.NAME • COURSE.SEC.FACULTY • CSF.FACULTY

  7. This will not work because you need a single linked key and your associations must be a hierarchy.

  8. In SQL, create a new view that has a unique key field called “ID” • In Unidata, create a unique computed column in the entity (file) that will be your key to the “ID” field in your SQL view.

  9. Blackboard Data Sources • BEHIND_PACE_vw • BB_SCS_KEY • user_id • course_id • batch_uid • last_access_date Datatel Data Sources • PERSON • @ID • SSN, LAST.NAME, FIRST.NAME • STUDENT.COURSE.SECTION • SCS.STUDENT • X.SCS.BBKEY • X.SCS.SYNONYM • SCS.REG.METH • COURSE.SECTIONS • SEC.TERM • SEC.SUBJECT, SEC.COURSE.NO, SEC.START.DATE, SEC.END.DATE, SEC.NAME • COURSE.SEC.FACULTY • CSF.FACULTY

  10. Define the selection criteria (Filter) • When using two data sources, only one data source can have a filter. (However, if you are using a view, you can add criteria to the view.) • For this example: • SCS.REG.METH = “EARMY” • SEC.TERM = <<PROMPT>>

  11. Map Datasource Tables

  12. Add Properties to your Tables

  13. Add Links to your Tables

  14. Add details for Remote Link

  15. Create Informer Report

  16. Add selection criteria

  17. Add Fields

  18. Sample Report Add Fields

  19. What’s Next? • More reports! • More data sources!

  20. Thank you! Any questions?

  21. Supporting documentation

More Related