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.
- 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
Next, create a generic Inspection Plan and Revision. As an example, I created a FAIR Inspection Plan and just made the Revision 1.
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.
While this does not make the Inspection Input screen all pretty, it does provide the ability to dynamically present the information required.
Now we have the data and input set up, itās time to move to the report.
- 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.
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.
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
-
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. -
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.
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.
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.
