Sodexo_Demographics_Diagnostics_SSRS
What this report does
Sodexo Demographics Diagnostics (SSRS title: Sodexo Demographics and Meal Plan Diagnostic) traces one Colleague person id through every layer of the Sodexo export pipeline:
- RP-COLLEAGUE (person, employment status, meal-plan assignments)
- DW-IWUDATA (student / employee pools, people validity)
- Access It (
IWU_MAGSTRIPE- physical card / magstripe) [sodexo].[IDX_CardholderDemographics](cardholder CSV pool)- Meal-plan export view and committed / pending Transact export rows
- Latest IDX balance snapshot (
csv.IDX_Balances)
Use it when someone asks: Why isn't this person on Sodexo? or Why didn't their meal plan export?
How to open the report
| Location | Path |
|---|---|
| Canonical | SSRS Student Development / Sodexo Demographics Diagnostics |
| IT alias (linked) | SSRS IT / Diagnostics / Sodexo Demographics Diagnostics |
Both run the same report definition.
Parameters
| Parameter | Required | Description |
|---|---|---|
| Customer ID (Colleague person id) | Yes | Colleague PERSON.ID, with or without leading zeros. Stored procedure normalizes to 7 digits. |
There are no other report parameters. Grace-day and CAS-program settings use production defaults from [sodexo].[IDX_CardholderDemographics].
How to run it (step by step)
- Confirm daily warehouse loads have completed (especially RP-COLLEAGUE, RP-ACCESSIT, IWUDATA people/student/employee jobs) if the case is missing from last night's export.
- Open the report in SSRS Report Manager or the Report Server web portal.
- Enter the Colleague person id (same value used as Transact Customer ID when padded to 7 digits).
- Run the report.
- Read top summary first, then Checks, then detail tabs as needed.
Reading the report
Summary header
The first panel shows:
- Demographics: INCLUDED / NOT INCLUDED - whether the person appears in
[sodexo].[IDX_CardholderDemographics](cardholder CSV pool). - Export view - row count in the current meal-plan export view, or
none. - IDX snapshot - whether the person appears in last night's
csv.IDX_Balancesimport from Transact. - Colleague meal plans - count of active
MEAL_PLAN_ASSIGNMENTrows. - Next meal-plan expiration - earliest end date among active assignments.
Green status generally means demographics and meal export both look healthy. Red on demographics means the person is not in the cardholder export pool.
Full ID Card Value
When an Access It card exists, the report shows the Full ID Card Value (for Sodexo IDX) - the magstripe / credential encoding Transact expects. Share this with Sodexo ops when the issue is card encoding, not Colleague data.
Checks tab
Ordered pass/fail checklist with reason codes. Failed checks explain the most likely exclusion.
| Area | What it validates |
|---|---|
| DEMOGRAPHICS | Colleague person, DW-IWUDATA people, employee/student pool rules, Access It card |
| MEAL_PLAN | Assignments in Colleague, active dates, integration map, export view membership |
Detail sections
| Section | Use when |
|---|---|
| PERSTAT | Employment status history in Colleague affects employee eligibility |
| PROGRAMS | Student program active / grace rules exclude the person |
| MEAL_PLAN_HISTORY | Assignment exists but export flags show blocked (map, dates, start gate) |
| MEAL_PLAN_EXPORT | What the export view would send on the next meal-plan run |
| EXPORT_HISTORY | Prior COMMITTED / PENDING rows IWU sent to Transact |
| IDX_BALANCES | What Transact reported overnight for this Customer ID |
Work top down: fix the first failed check in Colleague, Access It, or warehouse data before expecting the next export to succeed.
Common exclusion reasons (Checks)
| Reason code | Typical fix |
|---|---|
NO_COLLEAGUE_PERSON | Wrong Customer ID, or person not in Colleague |
NO_DW_PEOPLE | IWUDATA people load behind; wait for nightly job |
NO_ACCESSIT_MAGSTRIPE | No active Access It card for 7-digit id - ID Office / card issue |
NO_STUDENT_OR_EMPLOYEE_POOL | Not in active or grace student/employee pool in DW-IWUDATA |
COLLEAGUE_EXCLUDED_STATUS | PERSTAT status excluded (NON, RET, etc.) |
NO_COLLEAGUE_ASSIGNMENTS | No meal plan in Colleague - Student Development / billing |
ASSIGNMENTS_NOT_IN_EXPORT | Plan code missing from integration map, or start-date gate - see MEAL_PLAN_HISTORY |
NOT_IN_CARDHOLDER_POOL | Fix demographics first; meal export requires cardholder row |
When to use another report or doc
| Situation | Next step |
|---|---|
| Person exports correctly but IDX balance wrong | Sodexo IDX Balance Reconciliation (SSRS) |
| SFTP / CSV / job failure | Sodexo / Transact IDX integration |
| Conference / Sodexo meal cards (not Colleague person) | Conference export uses [sodexo].[IDX_CardholderDemographics_Conference] - this report is for campus Colleague ids |
Technical reference
| Item | Value |
|---|---|
| Database | DM-StudentDevelopment |
| Stored procedure | [sodexo].[SP_IDX_CardholderDemographics_Diagnostics] |
| RDL (source) | Reporting/Reports/Student Development/Sodexo_IDX_CardholderDemographics_Diagnostics.rdl |
Example (SSMS, support only):
EXEC [sodexo].[SP_IDX_CardholderDemographics_Diagnostics]
@CustomerId = '2917382',
@ResultSet = N'ALL';