Sodexo_IDX_Balance_Reconciliation_SSRS
What this report does
Sodexo IDX Balance Reconciliation compares three sources of meal and flex balance truth:
- Colleague expected now - what active meal-plan assignments say the person should have
- IWU sent successfully - COMMITTED rows in
[sodexo].[IDX_MealPlanTransactExportRow](last successful Transact export) - Current IDX balance - overnight snapshot in
[csv].[IDX_Balances]from Transact Jasper (/Output/IDX_Balances.csv)
The report lists discrepancies only. Matching accounts are omitted from the detail grids.
Use it when Sodexo or Student Development reports that IDX balances do not match Colleague, or when validating manual IDX adjustments before the next automated export.
How to open the report
SSRS Student Development / Sodexo IDX Balance Reconciliation
There is no IT Diagnostics linked copy for this report today; use the Student Development folder path.
Parameters
| Parameter | Default | Description |
|---|---|---|
| Customer ID (optional) | blank | Filter to one 7-digit Colleague person id. Leave blank for institution-wide discrepancy scan. |
| Section | All | All, Meal plans, or Flex dollars - limits which account types appear |
| Exclude Summer Athletic from IDX meal rows | true | Hides Summer Athletic meal rows from IDX side of meal compare (recommended for routine review) |
How to run it (step by step)
Institution-wide review (morning ops)
- Confirm dataexports task imported last night's
IDX_Balances.csv(typically ~1:30 AM ET). - Open the report with Customer ID blank.
- Review the meta line (run time, export gaps, sync suspects, pending exports).
- Scan Meal Discrepancy Status counts (MATCH rows are excluded from detail).
- Work the Sync Suspects and Detail grids - highest priority for manual IDX fixes.
Single-student investigation
- Get Colleague person id from Sodexo ticket or Sodexo Demographics Diagnostics.
- Enter Customer ID and run.
- Read column definitions below; note Recommended IDX Fix before changing Transact admin.
How to read the report
The report includes an on-page legend. Key columns:
| Column | Meaning |
|---|---|
| Colleague Expected Now | What the student should have in Colleague right now (active assignments + integration map) |
| IWU Sent Successfully | What IWU already exported to IDX and marked COMMITTED |
| Current IDX Balance | Meals remaining or flex dollars from latest Transact snapshot |
| Export Gap (Expected - Sent) | Colleague expected minus what IWU sent. Non-zero means the export pipeline is behind or ahead of Colleague |
| Variance (IDX - Sent) | Current IDX balance minus what IWU sent. Negative often means meals or flex were used after export (normal consumption) |
| Recommended IDX Fix | Suggested manual step in IDX admin - not the last CSV action IWU sent |
Meta line (top of report)
| Counter | Meaning |
|---|---|
| Export gaps | Colleague expects a total IWU has not yet committed to Transact |
| Colleague and IDX agree but IWU sent total differs | External systems align; IWU committed amount is the outlier |
| Sync suspects | Rows flagged for manual review (not explained by normal meal usage) |
| Pending exports | Staging rows not yet COMMITTED after SFTP - next meal-plan job may clear |
Compare status values
| Status | Meaning |
|---|---|
| MATCH | IDX balance equals IWU committed total (excluded from detail) |
| MISMATCH | IDX and IWU committed differ - check Variance and Recommended IDX Fix |
| IDX_ONLY | Balance in Transact snapshot but no matching IWU committed row |
| IWU_ONLY | IWU committed export but no matching IDX balance row |
Recommended workflow
1. Export gap non-zero?
-> Fix Colleague assignment or run / wait for meal-plan export job.
-> Re-run demographics report if person not in cardholder pool.
2. Export gap zero but Variance non-zero?
-> Often normal usage since last export.
-> Confirm with Sodexo whether adjustment is still required.
3. Sync suspect row?
-> Use Recommended IDX Fix column.
-> Document manual Transact change; do not change Colleague assignment
to match IDX without business approval.
4. Pending exports > 0?
-> Check automation log for DM-StudentDevelopment-SodexoMealPlanTransactFTP
before manual IDX edits.
Sync Suspects is the primary work queue until full automated reconciliation exists. Treat it as likely needs manual IDX review, not automatic Colleague correction.
Data dependencies
| Source | Refresh |
|---|---|
csv.IDX_Balances | Nightly SFTP download from Transact to dataexports, imported into DM-StudentDevelopment |
[sodexo].[IDX_MealPlanTransactExportRow] COMMITTED | Updated when meal-plan SFTP job succeeds and calls Commit |
| Colleague assignments | RP-COLLEAGUE snapshot (daily) |
Running this report before the nightly IDX import completes will show stale Current IDX Balance values.
When to use another report or doc
| Situation | Next step |
|---|---|
| Person not in export at all | Sodexo Demographics Diagnostics (SSRS) |
| CSV / SFTP / job failure | Sodexo / Transact IDX integration |
| Need to sync committed ledger (DBA) | [sodexo].[SP_IDX_Balances_SyncCommittedLedger] - operator / DBA only, not exposed in SSRS |
Technical reference
| Item | Value |
|---|---|
| Database | DM-StudentDevelopment |
| Stored procedure | [sodexo].[SP_IDX_Balances_Reconciliation] |
| RDL (source) | Reporting/Reports/Student Development/Sodexo_IDX_Balance_Reconciliation.rdl |
Example (SSMS, support only):
-- Institution sync suspects
EXEC [sodexo].[SP_IDX_Balances_Reconciliation] @ResultSet = N'SYNC_SUSPECTS';
-- One person detail
EXEC [sodexo].[SP_IDX_Balances_Reconciliation]
@ResultSet = N'DETAIL',
@CustomerId = N'2897330';