I have run out of options in terms of how to get the output to display properly.

Here's the situation...I'm creating a report that shows the Phone Call Summary by Subject. I'm using Across to show the output of a field as columns (the field being subjects, i.e. Booked Meeting, Followed up, Thank you).

What I've done is that I've used IF statements to create the proper format for the fields and then called each one.
My program is also based on who's logged in when they're pulling the report. Depending on the user's permissions and the region they're from they get a certain output.
There are about 25 different subjects that the user could choose from.

Here's the problem...the user might not have all the subjects so the program doesn't recognize certain fields (subjects) because the user doesn't have that particular subject chosen. So the report gives an error saying cannot recognize the field.

My question is, is there a way to show ALL fields even if the user doesn't have certain subjects and just assign a 0 as the output?

Thanks.

Here's some of the code:

TABLE FILE MSCRM_STRINGMAP
PRINT
ATTRIBUTENAME
ATTRIBUTEVALUE
COMPUTE SUBJECT/A255 = IF VALUE EQ '-Pick a Subject' THEN 'MISSINGSUBJECT'
ELSE IF VALUE EQ 'ACB' THEN 'ACB'
ELSE IF VALUE EQ 'Account Inquiry' THEN 'ACCOUNTINQUIRY'
ELSE IF VALUE EQ 'Dealer Rep kit Request' THEN 'DEALERREPKITREQUEST'
ELSE IF VALUE EQ 'DIP (Outgoing)' THEN 'DIPOUTGOING'
ELSE IF VALUE EQ 'DSC Full Report' THEN 'DSCFULLREPORT'
ELSE IF VALUE EQ 'DSC Inquiry' THEN 'DSCINQUIRY'
ELSE IF VALUE EQ 'DSC Refund' THEN 'DSCREFUND'
ELSE IF VALUE EQ 'Full Free Unit Report' THEN 'FULLFREEUNITREPORT'
ELSE IF VALUE EQ 'Full Trial Balance Report' THEN 'FULLTRIALBALANCEREPORT'
ELSE IF VALUE EQ 'Income Trusts' THEN 'INCOMETRUSTS'
ELSE IF VALUE EQ 'Marketing Fulfillment' THEN 'MARKETINGFULFILLMENT'
ELSE IF VALUE EQ 'Other' THEN 'OTHER'
ELSE IF VALUE EQ 'PPNs (Link Notes)' THEN 'PPNSLINKNOTES'
ELSE IF VALUE EQ 'Product Inquiry' THEN 'PRODUCTINQUIRY'
ELSE IF VALUE EQ 'Statement Request' THEN 'STATEMENTREQUEST'
ELSE IF VALUE EQ 'Tax Inquiry' THEN 'TAXINQUIRY'
ELSE IF VALUE EQ 'Flow Through' THEN 'FLOWTHROUGH'
ELSE IF VALUE EQ 'Market Conditions' THEN 'MARKETCONDITIONS'
ELSE IF VALUE EQ 'Book Meeting' THEN 'BOOKMEETING'
ELSE IF VALUE EQ 'Follow-up' THEN 'FOLLOWUP'
ELSE IF VALUE EQ 'Fund Campaign' THEN 'FUNDCAMPAIGN'
ELSE IF VALUE EQ 'Introduction' THEN 'INTRODUCTION'
ELSE IF VALUE EQ 'Information Change' THEN 'INFORMATIONCHANGE'
ELSE IF VALUE EQ 'Marketing' THEN 'MARKETING'
ELSE IF VALUE EQ 'Problem Resolution' THEN 'PROBLEMRESOLUTION'
ELSE IF VALUE EQ 'RSM Project' THEN 'RSMPROJECT'
ELSE IF VALUE EQ 'Rep Complaint' THEN 'REPCOMPLAINT'
ELSE IF VALUE EQ 'Rep Request/Change Meeting' THEN 'REPREQUESTCHANGEMEETING'
ELSE IF VALUE EQ 'Rep Inquiry' THEN 'REPINQUIRY'
ELSE IF VALUE EQ 'Thank You' THEN 'THANKYOU'
ELSE IF VALUE EQ 'Rep Request Promos' THEN 'REPREQUESTPROMOS'
ELSE IF VALUE EQ 'PPNs (Link Notes)' THEN 'PPNSLINKNOTES'
ELSE IF VALUE EQ 'Income Trusts' THEN 'INCOMETRUSTS'
ELSE IF VALUE EQ 'Prospectus' THEN 'PROSPECTUS'
ELSE IF VALUE EQ 'DSC Refund' THEN 'DSCREFUND'
ELSE IF VALUE EQ 'Due Diligence' THEN 'DUEDILIGENCE'
ELSE IF VALUE EQ 'Flow Through' THEN 'FLOWTHROUGH'
ELSE IF VALUE EQ 'Market Conditions' THEN 'MARKETCONDITIONS'
ELSE VALUE;
WHERE OBJECTTYPECODE = 4210 AND ATTRIBUTENAME EQ 'cf_subjectclientservices' OR ATTRIBUTENAME EQ 'cf_subjectsales'
BY ATTRIBUTEVALUE NOPRINT
ON TABLE HOLD AS SUBJECTS
END

JOIN ATTRIBUTEVALUE IN SUBJECTS TO ALL CF_SUBJECTCLIENTSERVICES IN MSCRM_PHONECALL AS J1
JOIN ATTRIBUTEVALUE IN SUBJECTS TO ALL CF_SUBJECTSALES IN MSCRM_PHONECALL AS J1

TABLE FILE SUBJECTS
SUM
CNT.SUBJECT AS 'Subjects'
MAX.CREATEDBYNAME
&WHEREDATE1
BY CREATEDBY
BY SUBJECT
ON TABLE HOLD AS SUBJECTS3
END

TABLE FILE MSEXT_SYSTEMUSERVIEW
PRINT
SYSTEMUSERID
BY SYSTEMUSERID NOPRINT
ON TABLE HOLD AS ISRID
END

-******************************************************************************
-* Extract User Profile
-******************************************************************************
-INCLUDE FEIPROF1
-IF &FOCERRNUM NE 0 GOTO ACTIVITY_ERR;

-******************************************************************************
-* Generate Report
-******************************************************************************
SET NODATA = 0
JOIN CREATEDBY IN SUBJECTS3 TO SYSTEMUSERID IN ISRID AS J2
JOIN CREATEDBY IN SUBJECTS3 TO ALL SYSTEMUSERID IN MSEXT_CRMTEAMCOVERAGEVIEW AS J3

TABLE FILE SUBJECTS3
PRINT
REGION
&WHERERGN
ON TABLE HOLD AS CHECK
END

-IF &FOCERRNUM NE 0 GOTO ACTIVITY_ERR;
-IF &LINES = 0 THEN GOTO NOREPORT ELSE GOTO PRINTOUT;
-NOREPORT

TABLE FILE MSEXT_CONTACTVIEW
PRINT
FULLNAME NOPRINT
WHERE RECORDLIMIT EQ 1

-GOTO HSTYLE
-PRINTOUT

SET NODATA = 0
TABLE FILE SUBJECTS3
SUM
MAX.SUBJECTS &RSMPRNT.EVAL AS ''
ACROSS SUBJECT AS ''
BY REGION NOPRINT
BY CREATEDBYNAME &RSMPRNT1.EVAL
WHERE SYSTEMUSERID NE ' ' AND SUBJECTS NE 0;
ON TABLE HOLD AS SUBJECTS4
END

-IF &USERTYPE EQ 'CSDS' THEN GOTO SUBCLIENTSERVICES
-ELSE IF &USERTYPE IN ('RSM', 'ISR', 'SC') THEN GOTO SUBSALES
-ELSE GOTO SUBALL;

-SUBCLIENTSERVICES
TABLE FILE SUBJECTS4
PRINT
CREATEDBYNAME AS 'Created By'
ACB As 'ACB'
ACCOUNTINQUIRY
DEALERREPKITREQUEST
DIPOUTGOING
DSCFULLREPORT
DSCINQUIRY
DSCREFUND
FULLFREEUNITREPORT
FULLTRIALBALANCEREPORT
INCOMETRUSTS
MARKETINGFULFILLMENT
MISSINGSUBJECT AS 'Missing Subject'
OTHER
PPNSLINKNOTES
PRODUCTINQUIRY
STATEMENTREQUEST
TAXINQUIRY
FLOWTHROUGH
MARKETCONDITIONS
BY REGION NOPRINT
ON TABLE ROW-TOTAL/D15 AS 'Total'
ON REGION SUBHEAD
"&WHERERGN

This message has been edited. Last edited by: <GGOFAnalyst>,

Just off the top of my head, you might want to put all of the subjects in a separate file. After you've generated all of your counts, hold them as you are already doing but make it a Focus file with an index on subject. Then SET ALL=ON and join the subject table to the count table. This should give you all the subjects regardless of whether there are counts or not.

Now I haven't examined your code in detail so you might have to do other stuff regarding other sort keys in order to get this to work. But this is one technique.

A caution might be that if you do an ACROSS, you might get an error on title size. There is a limit which I don't know without looking it up. In that case, you would need to make each subject value its own column. I know techniques to do that as well but I wanted to give you a quick answer to get you started.

In the last TABLE FILE, you could possibly code

PRINT *

instead of mentioning the field names.

Thanks for the quick response, GinnyJake.
However, I've tried that but what happens is the fields instead of showing up as columns, show up as rows.

Here's one way.

-SET &ECHO=ALL;

SET HOLDFORMAT=ALPHA
SET HOLDLIST=PRINTONLY
SET ASNAMES=ON
-RUN

-*-- CREATE A HOLD FILE WITH ACROSS
TABLE FILE CAR
SUM
SALES
ACROSS BODYTYPE
BY COUNTRY
ON TABLE HOLD AS H001
END
-RUN

-*-- USE CHECK FILE ... HOLD TO CREATE A FILE CONTAINING ATTRIBUTES OF THE H001 HOLD FILE
-*-- INCLUDING THE FIELD NAMES
CHECK FILE H001 HOLD
-RUN

-*-- FILEDEF TO ENABLE THE -INCLUDE
FILEDEF HOLD1 DISK HOLD1.FEX
-RUN

-*-- CREATE A HOLD FILE CONTAINING JUST A LIST OF FIELD NAMES THAT ARE IN THE H001 HOLD FILE
TABLE FILE HOLD
PRINT
FIELDNAME
-*-- EXCLUDE FIELDS THAT WILL BE BY FIELD IN THE FINAL REPORT
WHERE FIELDNAME NE 'COUNTRY'
ON TABLE HOLD AS HOLD1
END
-RUN

-*-- CREATE THE FINAL REPORT, INCLUDING THE LIST OF FIELD NAMES
TABLE FILE H001
PRINT
-MRNOEDIT -INCLUDE HOLD1
BY COUNTRY
END
-RUN

Remove -MRNOEDIT if this is not executed in MRE.

This message has been edited. Last edited by: Francis Mariani,

Let me play with this on my own machine and get back to you with some code that might work.

Alright thanks.
I'm playing around with it right now as well.

OK, here is something:

TABLE FILE CAR
PRINT COUNTRY NOPRINT
COMPUTE MDLCNT/A20=IF COUNTRY EQ 'ENGLAND' THEN 'ENGCNT'
ELSE IF COUNTRY EQ 'JAPAN' THEN 'JPNCNT'
ELSE IF COUNTRY EQ 'FRANCE' THEN 'FRACNT'
ELSE IF COUNTRY EQ 'ITALY' THEN 'ITLCNT'
ELSE 'GERCNT';
BY COUNTRY
ON TABLE HOLD AS SUBJECT
END

-* CREATE SOURCE FILE THAT IS MISSING FRANCE
TABLE FILE CAR
PRINT MODEL
BY COUNTRY
-*BY CAR
WHERE COUNTRY NOT EQ 'FRANCE'
ON TABLE HOLD AS MODELS FORMAT FOCUS INDEX COUNTRY
END


-* CREATE THE COUNT FILE WITH THE SUBJECT IN IT FOR ALL DATA PRESENT
JOIN COUNTRY IN SUBJECT TO ALL COUNTRY IN MODELS AS J1
TABLE FILE SUBJECT
SUM CNT.MODEL AS MODELCOUNT
BY MDLCNT
BY COUNTRY
ON TABLE HOLD AS MDLCNT FORMAT FOCUS INDEX MDLCNT
END


-* JOIN THE FILE WITH ALL THE SUBJECTS IN IT SO THAT YOU'LL GET A HEADING FOR FRANCE.
SET ALL=ON
JOIN MDLCNT IN SUBJECT TO MDLCNT IN MDLCNT AS J1
TABLE FILE SUBJECT
SUM MODELCOUNT
ACROSS MDLCNT
END

You can't do the join from the file with ALL of the subjects in it until just before you are ready to present the report. Do all of your data massaging ahead of time.

If this works properly and you don't get an error on title characters, something like what I did might work.

Thanks for your help Francis the report works.

However, how would i change the name of my fields?
Do I have to create another hold file and then print all the fields again and change their names that way?

Something like this:

-SET &ECHO=ALL;

SET HOLDFORMAT=ALPHA
SET HOLDLIST=PRINTONLY
SET ASNAMES=ON
-RUN

-*-- CREATE A HOLD FILE WITH ACROSS
TABLE FILE CAR
SUM
SALES
ACROSS BODYTYPE
BY COUNTRY
ON TABLE HOLD AS H001
END
-RUN

-*-- USE CHECK FILE ... HOLD TO CREATE A FILE CONTAINING ATTRIBUTES OF THE H001 HOLD FILE
-*-- INCLUDING THE FIELD NAMES
CHECK FILE H001 HOLD
-RUN

-*-- FILEDEF TO ENABLE THE -INCLUDE
FILEDEF HOLD1 DISK HOLD1.FEX
-RUN

-*-- CREATE A HOLD FILE CONTAINING JUST A LIST OF COLUMN NAMES AND COLUMN TITLES THAT ARE IN THE H001 HOLD FILE
TABLE FILE HOLD
PRINT
FIELDNAME
COMPUTE ASNAME/A100 =
IF FIELDNAME EQ 'SALCONVERTIBLE' THEN 'AS ''Sales,Convertible''' ELSE
IF FIELDNAME EQ 'SALCOUPE' THEN 'AS ''Sales,Coupe''' ELSE
IF FIELDNAME EQ 'SALHARDTOP' THEN 'AS ''Sales,Hard Top''' ELSE
IF FIELDNAME EQ 'SALHARDTOP' THEN 'AS ''Sales,Hardtop''' ELSE
IF FIELDNAME EQ 'SALROADSTER' THEN 'AS ''Sales,Roadster''' ELSE
'AS ' | FIELDNAME;

WHERE FIELDNAME NE 'COUNTRY'
ON TABLE HOLD AS HOLD1
END
-RUN

-*-- CREATE THE FINAL REPORT, INCLUDING THE LIST OF FIELD NAMES
TABLE FILE H001
PRINT
-INCLUDE HOLD1
BY COUNTRY AS 'Country'
END
-RUN

In your program, the code would be this, Which is a reversal of an earlier COMPUTE. You could add commas (e.g. 'Flow,Through') to wrap the report column titles:

COMPUTE ASNAME/A100 =
     IF FIELDNAME EQ 'MISSINGSUBJECT'                THEN 'AS ''-Pick a Subject'''
ELSE IF FIELDNAME EQ 'ACB'                           THEN 'AS ''ACB'''
ELSE IF FIELDNAME EQ 'ACCOUNTINQUIRY'                THEN 'AS ''Account Inquiry'''
ELSE IF FIELDNAME EQ 'DEALERREPKITREQUEST'           THEN 'AS ''Dealer Rep kit Request'''
ELSE IF FIELDNAME EQ 'DIPOUTGOING'                   THEN 'AS ''DIP (Outgoing)'''
ELSE IF FIELDNAME EQ 'DSCFULLREPORT'                 THEN 'AS ''DSC Full Report'''
ELSE IF FIELDNAME EQ 'DSCINQUIRY'                    THEN 'AS ''DSC Inquiry'''
ELSE IF FIELDNAME EQ 'DSCREFUND'                     THEN 'AS ''DSC Refund'''
ELSE IF FIELDNAME EQ 'FULLFREEUNITREPORT'            THEN 'AS ''Full Free Unit Report'''
ELSE IF FIELDNAME EQ 'FULLTRIALBALANCEREPORT'        THEN 'AS ''Full Trial Balance Report'''
ELSE IF FIELDNAME EQ 'INCOMETRUSTS'                  THEN 'AS ''Income Trusts'''
ELSE IF FIELDNAME EQ 'MARKETINGFULFILLMENT'          THEN 'AS ''Marketing Fulfillment'''
ELSE IF FIELDNAME EQ 'OTHER'                         THEN 'AS ''Other'''
ELSE IF FIELDNAME EQ 'PPNSLINKNOTES'                 THEN 'AS ''PPNs (Link Notes)'''
ELSE IF FIELDNAME EQ 'PRODUCTINQUIRY'                THEN 'AS ''Product Inquiry'''
ELSE IF FIELDNAME EQ 'STATEMENTREQUEST'              THEN 'AS ''Statement Request'''
ELSE IF FIELDNAME EQ 'TAXINQUIRY'                    THEN 'AS ''Tax Inquiry'''
ELSE IF FIELDNAME EQ 'FLOWTHROUGH'                   THEN 'AS ''Flow Through'''
ELSE IF FIELDNAME EQ 'MARKETCONDITIONS'              THEN 'AS ''Market Conditions'''
ELSE IF FIELDNAME EQ 'BOOKMEETING'                   THEN 'AS ''Book Meeting'''
ELSE IF FIELDNAME EQ 'FOLLOWUP'                      THEN 'AS ''Follow-up'''
ELSE IF FIELDNAME EQ 'FUNDCAMPAIGN'                  THEN 'AS ''Fund Campaign'''
ELSE IF FIELDNAME EQ 'INTRODUCTION'                  THEN 'AS ''Introduction'''
ELSE IF FIELDNAME EQ 'INFORMATIONCHANGE'             THEN 'AS ''Information Change'''
ELSE IF FIELDNAME EQ 'MARKETING'                     THEN 'AS ''Marketing'''
ELSE IF FIELDNAME EQ 'PROBLEMRESOLUTION'             THEN 'AS ''Problem Resolution'''
ELSE IF FIELDNAME EQ 'RSMPROJECT'                    THEN 'AS ''RSM Project'''
ELSE IF FIELDNAME EQ 'REPCOMPLAINT'                  THEN 'AS ''Rep Complaint'''
ELSE IF FIELDNAME EQ 'REPREQUESTCHANGEMEETING'       THEN 'AS ''Rep Request/Change Meeting'''
ELSE IF FIELDNAME EQ 'REPINQUIRY'                    THEN 'AS ''Rep Inquiry'''
ELSE IF FIELDNAME EQ 'THANKYOU'                      THEN 'AS ''Thank You'''
ELSE IF FIELDNAME EQ 'REPREQUESTPROMOS'              THEN 'AS ''Rep Request Promos'''
ELSE IF FIELDNAME EQ 'PPNSLINKNOTES'                 THEN 'AS ''PPNs (Link Notes)'''
ELSE IF FIELDNAME EQ 'INCOMETRUSTS'                  THEN 'AS ''Income Trusts'''
ELSE IF FIELDNAME EQ 'PROSPECTUS'                    THEN 'AS ''Prospectus'''
ELSE IF FIELDNAME EQ 'DSCREFUND'                     THEN 'AS ''DSC Refund'''
ELSE IF FIELDNAME EQ 'DUEDILIGENCE'                  THEN 'AS ''Due Diligence'''
ELSE IF FIELDNAME EQ 'FLOWTHROUGH'                   THEN 'AS ''Flow Through'''
ELSE IF FIELDNAME EQ 'MARKETCONDITIONS'              THEN 'AS ''Market Conditions'''
ELSE 'AS ' | FIELDNAME;

Check your code - there are some duplicates, e.g FLOWTHROUGH.

And, welcome to the club.

Thanks for you help, it worked.
Yea I'm gonna go over my code once the report is done.
Thanks again.

One more question Francis...I don't really want region to appear on my report and when I say NOPRINT in my report, it doesn't seem to work.
And I remember you telling me that I should exclude BY in my HOLD file.
How would I be able to get rid of the region field then?

Did you add the exclusion criteria to the step that creates the list of Report columns?

TABLE FILE HOLD
PRINT FIELDNAME
...
WHERE FIELDNAME NE 'REGION'
...

This will exclude REGION from the hold file that's created by the step. REGION will still be available for BY's and HEADING's because it exists in the file you're reporting from, it just won't be a report column in the -INCLUDE.

Yea I realized that AFTER I asked this question when I was cleaning up my report.
The where was missing so that's why it was showing.
Now it all works.
Thanks for your help