Across query

Platinum Member

posted

Hello,

When doing an Across type query in Focus, how do you for example set data in a count to an “x” instead of a 1.

Thanks,

WebFocus 7.7.02 WinXP

Posts: 236 | Registered: May 12, 2006

Francis Mariani

Expert

posted

?

Could you please provide more detail?

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Platinum Member

posted

I’m creating a skill report and need report on who has certain skills from each team. What I did was create an across type query report through Focus and did a count on the reps to mark who had what skill. When you do a count, you receive a number ‘1’ for the data. I would like to replace this ‘1’ with an ‘X’ instead. Is there a way to set data = ‘x’. I’m new with FOCUS.

Thanks,
SKILLS
Rep 10 120 3 15
Joe X X X
Bill X x

WebFocus 7.7.02 WinXP

Posts: 236 | Registered: May 12, 2006

Ignored post by JOE posted

Tony A

Expert

posted

Joe,

You can do this, you just have to interpret the value that will be displayed upon your report and modify it. Try the following code which uses the CAR file -

TABLE FILE CAR
SUM CNT.MODEL NOPRINT
    COMPUTE MARK_IT/A1 MISSING ON = IF CNT.MODEL GE 1 THEN 'X' ELSE MISSING;
BY CAR
ACROSS COUNTRY
END

In this code I am listing all Car values across Country. I include a CNT.MODEL in the report to count the number of MODELs but I add the NOPRINT to stop it being displayed. I need to include this in the report because I want to reference it later.
I then add a COMPUTE to interpret the value of the count at report creation time and set it to an 'X' if it has a value greater than or equal to 1, otherwise I set it to a NULL value.

Hope this helps.

T

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Platinum Member

posted

Thanks I'll give this a try and let you know how it works.

WebFocus 7.7.02 WinXP

Posts: 236 | Registered: May 12, 2006

Ignored post by JOE posted

Platinum Member

posted

tONY,

i KEEP GETTING THIS ERROR MESSAGE:

0 ERROR AT OR NEAR LINE 5 IN PROCEDURE MEMFEX FOCEXEC *
(FOC171) TYPE OF MISSING CALCULATION IS NOT SPECIFIED ON LEFT SIDE: ;
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

mY CODE IS AS FOLLOWS:

TABLE FILE TBLID_SKILLS
SUM
'CNT.TBLID_SKILLS.PP_NUMBER' NOPRINT AS 'COUNT,TBLID_SKILLS.PP_NUMBER'

COMPUTE SKILLA/A1 = IF CNT.TBLID_SKILLS.PP_NUMBER GE 1 THEN 'X' ELSE MISSING;
BY
'TBLID_SKILLS.FULLNAME'
ACROSS
'TBLID_SKILLS.SKILL'
HEADING
"WHERE ( TBLID_SKILLS.TEAM_NAME EQ 'Account Mgmt 2' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Letter',
LEFTMARGIN=0.250000,
RIGHTMARGIN=0.250000,
TOPMARGIN=0.250000,
BOTTOMMARGIN=0.250000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END

tHANKS FOR YOUR HELP

WebFocus 7.7.02 WinXP

Posts: 236 | Registered: May 12, 2006

Ignored post by JOE posted

Francis Mariani

Expert

posted

Well, the text for error FOC171 states:

quote:
(FOC171) TYPE OF MISSING CALCULATION IS NOT SPECIFIED ON LEFT SIDE:
A computed field created through the DEFINE command or through the
COMPUTE subcommand of the MODIFY command is assigned the value
MISSING, but its definition does not specify NEEDS SOME DATA or
NEEDS ALL DATA.

But why not just change the word MISSING to a blank (eg. ' ')?

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

codermonkey

Platinum Member

posted

If you use missing as part of the assignment you need to define -- on in this case COMPUTE --the field with MISSING ON as Tony has done:

COMPUTE MARK_IT/A1 MISSING ON = IF CNT.MODEL GE 1 THEN 'X' ELSE MISSING;

You can do it without MISSING ON if you change to blank, as Francis suggested.

COMPUTE MARK_IT/A1 = IF CNT.MODEL GE 1 THEN 'X' ELSE ' ';

I just wanted to post an example of what Francis said to make it clear.

Posts: 118 | Location: DC | Registered: May 13, 2005

Ignored post by codermonkey posted

Platinum Member

posted

It ended up working. Thanks alot for all of your help.

WebFocus 7.7.02 WinXP

Posts: 236 | Registered: May 12, 2006

Ignored post by JOE posted