Inspection Report --- FINALLY!

Incoming book😊

I have been wanting to really try this one for a few years now but never got the opportunity. I recently was able to start working on it and I can now say that I was successful. I have created an Inspection Report that dynamically returns the data based on the Specification and the user entered description (not the Number001). The method I use only needs Epicor functionality to accomplish it. No Stored Procedures, Table Valued Functions, or code. I have seen multiple posts looking for something like this and wanted to share.

  1. Setup
    Ok, not sure if other setups will work but I want to explain mine so if someone has a different one they may be able to adjust. First is to create the Inspection Attributes and create an attribute for EVERY Table Field Name. Make the Attribute ID the same name as the Table Field Name. See below
    image

Next, create a generic Inspection Plan and Revision. As an example, I created a FAIR Inspection Plan and just made the Revision 1.
image

image

Next is to configure the Inspection Plan in the Configurator. I have one Configurator Designer with every attribute in it and it is very important to use the Inspection Attribute settings from the Specification.

image

While this does not make the Inspection Input screen all pretty, it does provide the ability to dynamically present the information required.
image

Now we have the data and input set up, it’s time to move to the report.

  1. BPMs
    The first thing we need to do is create a UD Column for every InspResultsXXX table. I called the column RelatedToSysRowID and set the field as a string.
    image

Next thing we need to do is create a Method Directive and some Data Directives. The Method directive is on Erp.ConfigurationRuntime.PreStartConfiguration and is Post Processing. We are capturing the GUID of the InspResults row and saving it to a BPM Data Field. Then we need to create an In-Transaction Data Directive for each InspResultsXXX table to put the SysRowID we captured in the UD column we created.
image

This allows us to ensure that if there are multiple plans on an operation that we can match the inspection data correctly.
3. BAQs
To get the data out, we need to create 8 BAQs. The first 6 are just straight tables with a Job Num parameter for InspResults, Char, CheckBox, Date, Num, and ShortChar.
The seventh is the SpecAttr table that joins to InspResults with the JobNum parameter.

select 
	[InspResults].[Company] as [InspResults_Company],
	[SpecAttr].[SpecID] as [SpecAttr_SpecID],
	[SpecAttr].[SpecRevNum] as [SpecAttr_SpecRevNum],
	[SpecAttr].[AttributeID] as [SpecAttr_AttributeID],
	[SpecAttr].[Description] as [SpecAttr_Description]
from Erp.InspResults as InspResults
left outer join Erp.SpecAttr as SpecAttr on 
	InspResults.Company = SpecAttr.Company
	and InspResults.SpecID = SpecAttr.SpecID
	and InspResults.SpecRevNum = SpecAttr.SpecRevNum
where (InspResults.Company = @CurrentCompany  and InspResults.JobNum = @JobNum)
group by [InspResults].[Company],
	[SpecAttr].[SpecID],
	[SpecAttr].[SpecRevNum],
	[SpecAttr].[AttributeID],
	[SpecAttr].[Description]

The last is SpecAttr Grouped by Company, SpecID, and SpecRevision with a sub query to stuff all of the inspection attributes into 1 field.

with [SubQuery3] as 
(select 
	[InspResults].[Company] as [InspResults_Company],
	[InspResults].[SpecID] as [InspResults_SpecID],
	[InspResults].[SpecRevNum] as [InspResults_SpecRevNum],
	[InspResults].[JobNum] as [InspResults_JobNum]
from Erp.InspResults as InspResults
where (InspResults.Company = @CurrentCompany  and InspResults.JobNum = @JobNum)
group by [InspResults].[Company],
	[InspResults].[SpecID],
	[InspResults].[SpecRevNum],
	[InspResults].[JobNum])

select 
	[SubQuery3].[InspResults_Company] as [InspResults_Company],
	[SubQuery3].[InspResults_JobNum] as [InspResults_JobNum],
	[SubQuery3].[InspResults_SpecID] as [InspResults_SpecID],
	[SubQuery3].[InspResults_SpecRevNum] as [InspResults_SpecRevNum],
	(REVERSE ( STUFF ( REVERSE ( REPLACE ( REPLACE ( ((select 
	(SpecAttr.AttributeID + ',') as [Calculated_Cols]
from Erp.SpecAttr as SpecAttr
where (SpecAttr.Company = SubQuery3.InspResults_Company  and SpecAttr.SpecRevNum = SubQuery3.InspResults_SpecRevNum  and SpecAttr.SpecID = SubQuery3.InspResults_SpecID FOR XML PATH('')))) , '</Calculated_Cols>', '' ) , '<Calculated_Cols>', '' ) ) , 1 , 1 , '' ) )) as [Calculated_Insp_Columns]
from  SubQuery3  as SubQuery3
  1. RDD
    This is not a BAQ Report, so you have to create the RDD manually. Add in all of the BAQs and map the parameters to one criteria.

  2. Report
    Finally, create the Report Style and the SSRS report. As my inspections are for Jobs, I created the Matrix with 4 Row Groups and 2 Column Groups.
    image

I then have 3 columns to display the results. The first column is the Inspection Attribute ID manually typed into each cell. The second column is an expression that looks up the value in the cell to the left to the InspSpec table to return the user entered Description.

=Lookup(ReportItems!Textbox9056.Value, Fields!SpecAttr_AttributeID.Value, Fields!SpecAttr_Description.Value, "InspSpec")

The last column is the inspection data. If there is more than 1 sample, the columns will grow to the right to accommodate them. The last piece is to put an expression in each row for visibility.

=Lookup(ReportItems!Textbox9056.Value, Fields!SpecAttr_AttributeID.Value, Fields!SpecAttr_Description.Value, "InspSpec")

It is a ton of work to set up, but once it is done, you will have an Inspection Report that you can produce and send to a customer.
image

image

I am sure I missed something or poorly explained a step so I will probably edit this post at some point in the future, but wanted to get it out to everyone.

16 Likes

Nicely done sir! I’ve been contemplating a solution to the variable dataset across inspection plans and I like what you’ve done. And thank you for the write-up and sharing on the site.

Here is what the SSRS report looks like. It is run based off of the Job number.

3 Likes

Also, here is the RDL, no one else should have to go through reproducing this.

If you need to edit the inspection attributes, I found it is much easier to do with regex to the XML instead of editing it through Report Builder.

2 Likes

I was thinking of making ours a BAQ Report and feeding it to .rdl since we cant do direct data source connections. But it gives me enough information to build something.

Thank You!

Mine is a BAQ Report. Want me to post the RDD or BAQs?

2 Likes

Yes that would be awesome!

1 Like

RDDInspectionData.xml (467.5 KB)
InspColumns.baq (41.5 KB)
InspHead.baq (25.0 KB)
InspRes.baq (18.2 KB)
InspResChar.baq (16.9 KB)
InspResCheckBox.baq (36.6 KB)
InspResDate.baq (27.2 KB)
InspResNum.baq (27.1 KB)
InspResShortChar.baq (27.3 KB)
InspSpec.baq (19.1 KB)

7 Likes

@hasokeric I’m sure someone with your skill set will probably find ways to improve what I’ve done. Don’t forget to share when you do!!

1 Like

Nice job, I have developed something very similar. I created a BAQ Report and use a BPM to generate a PDF of the report when the labour activity is completed. I save the PDF automatically to a folder and when the product is shipped I attach the PDF to an automated despatch email to the customer and also attach the PDF to the pack for reference.

As @jkane said it takes some setting up, but works well once up and running.

This is amazing, it helped me to understand the way inspections store the values

1 Like

Is this similar to the presentation given at Insights? If so, this really gave us a lot to think about when it comes to the QA module in Epicor.

Thanks for sharing your work!

Yes, this is the same as the presentation I gave. Glad it is helping so many users.

1 Like