Looking for ideas for a Reason Code Where Used Report

I have been requested to create a BAQ (soon to be a report), that finds where a certain reason code is being used. There are about 30 tables that have reason code fields. Has anyone done anything like this? What would be the best way to accomplish this. Thanks!

In what context is the certain reason code being used? Inspection/DMR? Sales/CRM? Yes, ReasonCodes is reused across the system, but it’s just a generic system. An individual code is going to have a specific context.

In fact, that’s why you have to select a “Reason Type” when creating a code, as that restricts where that code will show up. I don’t need Inventory Adjustment reasons showing up in service calls.

Figure that out and I suspect the number of tables in play will shrink dramatically.

Agree with @jtownsend here. Each reason code has a type. From the data dictionary:

"S" - Scrap, 
"R" - Rework, 
"M" - Inventory Adjustments, 
"A" - RMA, 
"D" - DMR, 
"Q" - Quality Assurance 
"C" - Service Calls 
"W" - Win CRM 
"L" - Loss CRM, 
"T" - Task CRM,
"H" - Part On Hold, 
"E" - ECO Task, 
"H" - HelpDesk, 
"C" - Service Call, 
"U" - Course Result, 
"F" - Field Service Contract

I see, so i guess i should have a report for each reason type then?

Grouped by Type then Reason Code I would think.

Consider that the PartTran table has the reason code
you would be able to group by the trantype and reason code to review the use of the codes.
Consider using the pivot table approach or advanced grouping to show the use of the codes and dollars, qtys, and number of transactions would be possible.