EXO - Report on Analysis Codes

[Shadoxity]

Hey Community,

I am trying to work out how to use analysis codes on reports.

We have the standard DLRSalesbyStaff, which we can do an active filter of analysis codes to get items for that analysis code, however I am just trying to get a summary of sales per analysis code.

But I am struggling to find the right table to use in the report.

Analysis table has the codes, but this does not seem to reference any of the sales orders that has the codes in it!

Any assistance appreciated.

[Will_H · Accepted solution]

Hi @Shadoxity ,

You'll want to talk to your Business Partner for indepth reporting help, but in terms of the data structure:

The ANALYSIS table holds the individual Code names/Seqnos

The ANALYSIS_TYPES table holds a key to the different types.

The ANALYSIS_MATRIX table holds the actual links between things.

Example of the ANALYSIS, ANALYSIS_TYPES and ANALYSIS_MATRIX tables

So you can join ANALYSIS and SALESORD_LINES using something like:
Select * from SALESORD_LINES

INNER JOIN ANALYSIS_MATRIX ON ANALYSIS_MATRIX.TRAN_SEQNO=SALESORD_LINES.SEQNO AND ANALYSIS_MATRIX.TRAN_TYPE='L'

INNER JOIN ANALYSIS ON ANALYSIS.SEQNO=ANALYSIS_MATRIX.CODE_SEQNO

Note, the above is a very rough approximation of the syntax, and is the extent of help I can provide.  Your Business Partner will be available to assist if you need further help with analysis codes or report writing.

[Shadoxity]

Thank you, that got us to the goal!