Forum Discussion

Shadoxity's avatar
Shadoxity
Contributing User
5 years ago
Solved

EXO - Report on Analysis Codes

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's avatar
    Will_H
    5 years ago

    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.

2 Replies

Sort By
Replies have been turned off for this discussion
  • Will_H's avatar
    Will_H
    MYOB Moderator
    5 years ago

    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.