Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] I want the field to show up even if empty

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] I want the field to show up even if empty
 Login/Join
 
Silver Member
posted
I'm working in COSTPOINT, so let me preface by saying ^%$&^% &^%^# @#%$

The data is broken down in two catalogs:
Time Sheet - where the charged hours are reported
MO Router - The actual Manufacturing Order that contains all the routing steps that an employee can charge time.

What I have done, is use the MO_ID, and MO_OPER_SEQ_NO as my JOIN. Common in both catalogs.

What I want to show is a simple report as follows:

Sum
ACTUAL CHARGED HOURS

By
Manufacturing Order ID

Across
Workcenter {a group of operations from the router denoting which area of the shop charged the hours}


My problem is that ACROSS will only show the Workcenter if hours have been charged. I want them to show all Workcenters even if there were no hours charged.

I've tried:
If Then statement that says if charged hours are missing then show "0"

My next option was to put the Workcenter in the By and the MO_ID in the Across, dump it to Excel and manipulate the data there.....

Thanks in advance for all the great information you folks are about to lay on me..... but be gentle, I'm not a real programmer, I'm whatever falls below "Aspiring Novice"

This message has been edited. Last edited by: Pops,


Release: WebFOCUS 8
Windows
HTML, PDF, EXCEL
 
Posts: 40 | Registered: October 24, 2013Report This Post
Platinum Member
posted Hide Post
This might work for you. Here's an example using the CAR file.

TABLE FILE CAR
SUM RETAIL_COST
ACROSS CAR
BY COUNTRY
END
-*
TABLE FILE CAR
SUM RETAIL_COST
ACROSS CAR
BY COUNTRY
WHERE COUNTRY IN ('ENGLAND','FRANCE','ITALY','JAPAN')
END
-*
TABLE FILE CAR
SUM RETAIL_COST
ACROSS CAR COLUMNS AUDI AND DATSUN AND JAGUAR AND PEUGEOT
BY COUNTRY
WHERE COUNTRY IN ('ENGLAND','FRANCE','ITALY','JAPAN')
END


The third report's ACROSS statement displays 4 specific CAR columns regardless if any values or not.

This might work for you if you know all the workcenter values you want.


WebFocus 8.201M, Windows, App Studio
 
Posts: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008Report This Post
Expert
posted Hide Post
Or, are you talking about MISSING data / values?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by Doug:
Or, are you talking about MISSING data / values?


Example:

I want the workcenter to show up even if no one has charged any hours in the time frame prompted.

Currently if no one has charged hours to Joint Welding, it will not show up on the report as a column.

The JOIN is set up as a LEFT-OUTER join, so that is not the issue.

The Workcenter data is pulling from the MO_ROUTER and not the Timesheet, so that wasn't the issue.


Release: WebFOCUS 8
Windows
HTML, PDF, EXCEL
 
Posts: 40 | Registered: October 24, 2013Report This Post
Expert
posted Hide Post
quote:
I want the workcenter to show up even if no one has charged any hours in the time frame prompted.

How about removing the filtering of "charged any hours in the time" in the report being displayed? Or, is there something in the bigger picture of which I am not aware?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Silver Member
posted Hide Post
The report will show the Plant Manager where the hours have been charged for the month balanced against the Plan for the Month.

For Example:
...........BEND SHOP.............WELD SHOP
Job.....Plan......Actual.....Plan......Actual
B00023...42........24.........142........00

However, in my report, you would not see WELD SHOP because no hours have been charged.


Release: WebFOCUS 8
Windows
HTML, PDF, EXCEL
 
Posts: 40 | Registered: October 24, 2013Report This Post
Virtuoso
posted Hide Post
Are there a discrete number of Plants or is this an unlimited/constantly changing list? If it's a limited list that rarely changes, you can calculate the value for each plant in a separate column and print the columns. Something like this:

 
DEFINE FILE.....
BEND_SHOP/I9=IF PLANT EQ 'BEND SHOP' THEN HOURS ELSE 0;
WELD_SHOP/I9=IF PLANT EQ 'WELD SHOP' THEN HOURS ELSE 0;
.
.
.
TABLE FILE....
SUM BEND_SHOP WELD_SHOP......
BY JOB
END  


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Virtuoso
posted Hide Post
You "issue", which is not one, is due, I think, to the fact that from the selected period, it doesn't extract any data for WELD SHOP because no data exist.
And in this case it's normal not to have it displayed on the report.

So you need to force each "categories" to be displayed no matter what and this is what you require.

Another option is to create a dummy file with what you want to have in, to then add the real value in it.
It's a manual way to perform what jfr99 or Babak suggested
TABLE FILE GGSALES
SUM COMPUTE DOL /I8 = 0;
    COMPUTE BDG /I8 = 0;
BY REGION
BY COMPUTE PRODUCT /A16 = 'Capuccino';
ON TABLE HOLD AS TMP1
END
-RUN

TABLE FILE GGSALES
SUM COMPUTE DOL /I8 = 0;
    COMPUTE BDG /I8 = 0;
BY REGION
BY COMPUTE PRODUCT /A16 = 'Espresso';
ON TABLE HOLD AS TMP2
END
-RUN

TABLE FILE GGSALES
SUM COMPUTE DOL /I8 = 0;
    COMPUTE BDG /I8 = 0;
BY REGION
BY COMPUTE PRODUCT /A16 = 'Latte';
ON TABLE HOLD AS TMP3
END
-RUN

TABLE FILE GGSALES
SUM COMPUTE DOL /I8 = 0;
    COMPUTE BDG /I8 = 0;
BY REGION
BY COMPUTE PRODUCT /A16 = 'Regular';
ON TABLE HOLD AS TMP4
END
-RUN

TABLE FILE TMP1
SUM DOL
    BDG
BY REGION
BY PRODUCT
ON TABLE HOLD AS BUCKETFIL
MORE
FILE TMP2
MORE
FILE TMP3
MORE
FILE TMP4
END
-RUN

TABLE FILE GGSALES
SUM DOLLARS    AS 'DOL'
    BUDDOLLARS AS 'BDG'
BY REGION
BY PRODUCT
WHERE CATEGORY EQ 'Coffee';
ON TABLE HOLD AS EXTDATA
END
-RUN

TABLE FILE BUCKETFIL
SUM DOL
    BDG
BY REGION
BY PRODUCT
ON TABLE HOLD AS RPTDATA
MORE
FILE EXTDATA
END
-RUN

TABLE FILE RPTDATA
SUM DOL
    BDG
BY REGION
ACROSS PRODUCT
END
-RUN

Note : when you want to add sample code or sample data in your post, use the "code" tag (last one on the ribbon)
</>

It will kept the positioning (spaces) and you won't have to do it as you did with so many dots.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
OK, here is the report as it currently looks:

As I mentioned in the original post, "Thanks in advance for all the great information
you folks are about to lay on me..... but be gentle, I'm not a real programmer,
I'm whatever falls below "Aspiring Novice""

I thought by adding this, it may inspire someone to reply,
"You idjut, all you have to do is..."

My next option was "to put the Workcenter in the By and the MO_ID in the Across, dump
it to Excel and manipulate the data there....."
but that is the same place that I am now, so why even bother.

Thanks. What you have posted so far has helped some, but I just am not smart enough
to translate it to my report... I keep getting error messages or the same results as I was getting before.

-*COMPONENT=Join_J001
JOIN MO_HDR.MO_HDR.MO_ID IN MO_HDR
TO UNIQUE MO_ROUTING.MO_ROUTING.MO_ID IN MO_ROUTING TAG J001 AS J001
END
-*COMPONENT=Join_J002
JOIN LEFT_OUTER J001.MO_ROUTING.MO_ID AND J001.MO_ROUTING.WC_ID AND J001.MO_ROUTING.MO_OPER_SEQ_NO IN MO_HDR
TO UNIQUE TS_LN_MO.TS_LN_MO.MO_ID AND TS_LN_MO.TS_LN_MO.WC_ID AND TS_LN_MO.TS_LN_MO.MO_OPER_SEQ_NO IN TS_LN_MO TAG J002 AS J002
END
-*COMPONENT=Define_MO_HDR
DEFINE FILE MO_HDR
 Define_1/A16=IF (J001.MO_ROUTING.WC_ID EQ '04-HARP' AND   J001.MO_ROUTING.MO_OPER_SEQ_NO EQ 460) THEN '03.92-SOCKET' ELSE  IF (J001.MO_ROUTING.WC_ID EQ '04-HARP' AND   J001.MO_ROUTING.MO_OPER_SEQ_NO EQ 450) THEN '03.91-HDR WELD' ELSE  IF (J001.MO_ROUTING.WC_ID EQ '04-HARP' AND   J001.MO_ROUTING.MO_OPER_SEQ_NO IN (330, 340, 350)) THEN '03.9-SF' ELSE IF J001.MO_ROUTING.WC_ID EQ '04-HARP' THEN '04-HARP' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0460' THEN '01.1 JOINT' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0450' THEN '02-WELD SHOP' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0110' THEN '01.1 JOINT' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0530' THEN '19.0 OVR MACH' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0540' THEN '19.1 REPAIR' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0120' THEN '01.1 JOINT' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0105' THEN '01.1 JOINT' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0140' THEN '01.3 FIT_TACK' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0210' THEN '02.1 TUBE CLN' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0230' THEN '02.2 FIN WELD' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0250' THEN '02.3 PNL WLD' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0700' THEN 'Z-ENG' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0710' THEN 'Z-ENG' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '2640' THEN 'Z-ENG' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0710' THEN 'Z-ENG' ELSE J001.MO_ROUTING.WC_ID;
 ACTUAL_HOURS/D12.2C=IF J002.TS_LN_MO.CHG_HRS IS MISSING THEN 0 ELSE J002.TS_LN_MO.CHG_HRS ;
END
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE MO_HDR
SUM ACTUAL_HOURS
BY J001.MO_ROUTING.MO_ID
ACROSS Define_1
WHERE J002.TS_LN_MO.TS_DT GE DT('&TS_DT_BEG.TS_DT: BEG DATE.') AND J002.TS_LN_MO.TS_DT LE DT('&TS_DT_END.TS_DT: END DATE.');
WHERE MO_HDR.MO_HDR.MO_ID LIKE 'B2%' OR 'B5%' OR 'B0%' OR 'B9%';
ON TABLE PCHOLD FORMAT EXL07 FORMULA
ON TABLE ROW-TOTAL
ON TABLE RECOMPUTE 
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/combine_templates/ENSilver_Light2.sty,$
TYPE=REPORT, FONT='HELVETICA', SIZE=11, COLOR=RGB(0 0 0), STYLE=NORMAL, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, HFREEZE=OFF, BACKCOLOR=(RGB(255 255 255) RGB(255 255 255)), $
TYPE=DATA, FONT='HELVETICA', SIZE=11, COLOR=RGB(0 0 0), STYLE=NORMAL, BACKCOLOR=(RGB(255 255 255) RGB(255 255 255)), $
TYPE=TITLE, FONT='HELVETICA', SIZE=11, COLOR=RGB(0 0 0), STYLE=NORMAL, BACKCOLOR=RGB(255 255 255), $
ENDSTYLE
END

-RUN

This message has been edited. Last edited by: Pops,


Release: WebFOCUS 8
Windows
HTML, PDF, EXCEL
 
Posts: 40 | Registered: October 24, 2013Report This Post
Virtuoso
posted Hide Post
Hi Pops,

As I mentioned above, the two important thing are :
1- "from the selected period, it doesn't extract any data for WELD SHOP because no data exist"
2- "you need to force each "categories" to be displayed no matter what"

Unfortunately, we're not here to do the code for you.
So you have to understand the three sample that we gave you and apply one of them to your situation.

What you have so far it's the data extract (the step where in my sample I HOLD it as EXTDATA).
From there you need to use one of the method to create the "empty" buckets to result in what you want.

Regards,


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by MartinY:
Hi Pops,

As I mentioned above, the two important thing are :
1- "from the selected period, it doesn't extract any data for WELD SHOP because no data exist" {The data [the workcenter] exists in the MO_Router even if there is not a timesheet}

2- "you need to force each "categories" to be displayed no matter what" {That was my question from the start....}

Unfortunately, we're not here to do the code for you. {I've never asked you to do the code for me, I just asked for help. Thought that was what this forum was for? Maybe I was wrong there, too.}

So you have to understand the three sample that we gave you and apply one of them to your situation. {If I could apply them, I surely would... Sorry to have bothered you!}

What you have so far it's the data extract (the step where in my sample I HOLD it as EXTDATA).
From there you need to use one of the method to create the "empty" buckets to result in what you want.

Regards,


Release: WebFOCUS 8
Windows
HTML, PDF, EXCEL
 
Posts: 40 | Registered: October 24, 2013Report This Post
Expert
posted Hide Post
Hey Pops,
Try this and see if your work station is part of the output:

JOIN LEFT_OUTER MO_HDR.MO_HDR.MO_ID IN MO_HDR
TO UNIQUE MO_ROUTING.MO_ROUTING.MO_ID IN MO_ROUTING TAG J001 AS J001
END
TABLE FILE MO_HDR
PRINT *
ON TABLE PCHOLD FORMAT EXL07 or EXL2K or XLSX <---- WHATEVER EXCEL OUTPUT WORKS FOR YOU HERE
WHERE MO_ID LIKE 'B2%' OR 'B5%' OR 'B0%' OR 'B9%';
END
-EXIT


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
quote:

The data [the workcenter] exists in the MO_Router even if there is not a timesheet

This I understood, but you may have created your JOIN in the wrong way.
I don't know your table and their relationship. I don't know which one is the parent of who.
In FOCUS you should not think of JOIN the same way as using them in SQL. Maybe your problem is only there.

quote:

That was my question from the start....}

And I think that we've answered.

quote:

I just asked for help

This is exactly what we did, or at least tried to. :-)

quote:

Sorry to have bothered you

You didn't. I was only saying that IMHO we've already gave you what you need to complete your request. I you had bothered me, I wouldn't have taken time to answer your post and tried to help you.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by Tom Flynn:
Hey Pops,
Try this and see if your work station is part of the output:

JOIN LEFT_OUTER MO_HDR.MO_HDR.MO_ID IN MO_HDR
TO UNIQUE MO_ROUTING.MO_ROUTING.MO_ID IN MO_ROUTING TAG J001 AS J001
END
TABLE FILE MO_HDR
PRINT *
ON TABLE PCHOLD FORMAT EXL07 or EXL2K or XLSX <---- WHATEVER EXCEL OUTPUT WORKS FOR YOU HERE
WHERE MO_ID LIKE 'B2%' OR 'B5%' OR 'B0%' OR 'B9%';
END
-EXIT


Nothing changes....

I cannot get data without time being charged.


Release: WebFOCUS 8
Windows
HTML, PDF, EXCEL
 
Posts: 40 | Registered: October 24, 2013Report This Post
Expert
posted Hide Post
Pops,
Then you need to go to the table that has a Master List of Work Stations and use that as your Host file with LEFT_OUTER...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by jfr99:
This might work for you. Here's an example using the CAR file.

TABLE FILE CAR
SUM RETAIL_COST
ACROSS CAR
BY COUNTRY
END
-*
TABLE FILE CAR
SUM RETAIL_COST
ACROSS CAR
BY COUNTRY
WHERE COUNTRY IN ('ENGLAND','FRANCE','ITALY','JAPAN')
END
-*
TABLE FILE CAR
SUM RETAIL_COST
ACROSS CAR COLUMNS AUDI AND DATSUN AND JAGUAR AND PEUGEOT
BY COUNTRY
WHERE COUNTRY IN ('ENGLAND','FRANCE','ITALY','JAPAN')
END


The third report's ACROSS statement displays 4 specific CAR columns regardless if any values or not.

This might work for you if you know all the workcenter values you want.



SUCCESS!!! The only thing that was missing was:
ACROSS CAR COLUMNS AUDI AND DATSUN AND JAGUAR AND PEUGEOT

ACROSS CAR COLUMNS 'AUDI' AND 'DATSUN' AND 'JAGUAR' AND 'PEUGEOT'

Once the "DUH" kicked in and I realized to add the ' then everything works fine.

Thanks to all for the suggestions and help!

 	01-BEND SHOP	01.1 JOINT	01.3 FIT_TACK	02-WELD SHOP	02.1 TUBE CLN	02.2 FIN WELD
MO ID						
B00043-01	.	40.00	.	.	.	.
B00043-02	.	.	.	.	.	.
B00043-03	8.00	.	.	.	.	.
B00043-08	.	.	.	.	20.00	.
B00045-01	84.50	.	.	10.00	.	.
B00047-01	40.00	.	.	.	.	.
B00047-02	.	.	.	.	8.00	.
B51076-01	8.00	.	.	.	.	.
B51083-01	12.00	.	.	.	.	.
B90124-01	.	.	.	.	.	.
TOTAL	152.50	40.00	.00	10.00	28.00	.00
 


Release: WebFOCUS 8
Windows
HTML, PDF, EXCEL
 
Posts: 40 | Registered: October 24, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] I want the field to show up even if empty

Copyright © 1996-2020 Information Builders