hi friends,

Thanks a lot for your help..

I have a cross - tab report in which the PRIMARY BPOs are listed across grouped by lifecycle stages.

The cross tab report shows the count of applications in a primary BPO under different Lifecycle Stages .

I have provided two listbox filters for selecting Primary BPO and Lifecycle Stage.

I am not able to achieve one thing .

When the user say selects the lifecycle stage "Evaluate" and no selection in the BPO Listbox. The user wants to see all the BPOs listed with the count as 0 for those BPO which do not have any applications tied to them.


Similarly , If a user selects BPO to be "MMP" and no selection in the Lifecycle. He wants to see all the lifecycles displayed with a count of 0 for those not having an application.

This report is driven by a view which is there in the metadata.

What i want is a constant list of all the BPO values and a constant list of all the lifecycle stage values.

I tried using SET ALL = PASS and tried using a hold file to create a list of values of primary BPO and Lifecycle Stages.
But i was not successful..

Please advice\

Thank you very much.

Arvind,

See if this article gives you some ideas on how to achieve your goal.

T

hi tony ,

The article is really helpful..but i am having a hard time executing it..

(FOC339) DIALOGUE MANAGER -READ FAILED: CHECK FILEDEF OR ALLOCATION FOR: -READ

The READ command is not able to read the file which i saved using ON TABLE SAVE command..

Am i missing something??
Please advice

Arvind,

Are you sure you have a -RUN after the END statement of the table request creating the SVAE file? Remember Dialog Manager is run before TABLE requests, so you have to force the file to be created by issuing -RUN.

TABLE FILE CAR
BY COUNTRY
ON TABLE SAVE
END
-RUN
-READ SAVE &Country.A10

T

T

Hi Arvind,
I am not sure how your view is setup. I am thinking if you change the view SQL as outter joins to include the BPOs wihout LifeCycles and LifeCyclse without BPOs that might work. I think you can use UNION to accomplish this. This might automatically provide you the result you are lokking for.

hi lakshmi ,

This is how my view is exactly, there are two unions . In the first union , we are adding all the lifecycle stages no tied to an application and in the second union all primary BPOs not tied to an application.

It is not working. What client wants to c is

Even after applying a filter condition say

Evaluate in Lifecycle Stage and all (FOC_NONE )in PRIMARY_BPO .
She wants to see even those Primary BPOs where applications are not there in Evaluate stage , and the count should be zero for that.

Thank you for your suggestion Mr Rao

Hi Arvind,

May be it is not summing because you have space or null if the match is missing? Then how about creating a ComputeField or define like if the data is space then read it as 0. Then use the defined field as SUM for the crosstab.

hi lakshmi,

In my cross-tab report, i am displaying count and percent-count of applications in Different Primary BPOs and Lifecycle Stages. So , wherever there is no application attached the count is displayed as zero and not blank space, but the problem arises when the user filters the data , only those Primary BPOs or Lifecycle Stages are displayed having count > 0 ...

Rest of them are ignored.

What the requirement is All the Primary BPOs should be displayed when one lifecycle stage is displayed.

The count should be zero for other Primary BPOs

Tony's link is really good, but hte problem is i am having a very difficult time implementing it in my program...

Arvind,

See if this helps you -

SET HOLDLIST = PRINTONLY
SET NODATA   = 0
TABLE FILE CAR
BY COUNTRY
ON TABLE SAVE AS CTRYSAVE
END
-RUN
-SET &Cols = 'COLUMNS '; 
-READ CTRYSAVE NOCLOSE &Country.A10.
-REPEAT Read_It WHILE (&IORETURN EQ 0); 
-SET &Cols = &Cols | '''&Country.EVAL'''; 
-READ CTRYSAVE NOCLOSE &Country.A10.
-SET &Cols = IF &IORETURN EQ 0 THEN &Cols | ' AND ' ELSE &Cols; 
-Read_It 
-RUN 

TABLE FILE CAR
SUM RCOST
ACROSS COUNTRY AS ''
&Cols
WHERE COUNTRY CONTAINS 'G'
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  GRID=OFF, SIZE=9, $
ENDSTYLE
END

T

hi tony,

Thank you very much for that code..It was of great help..

But i have a question , When i try to add one more table file statement and in that i have Lifecycle_Stage column. I want to READ this save file and do ROWS OVER.

It is throwing an error saying
Duplicate DASH REPEAT LABEL FOC1851

I know i might be making a silly mistake ..PLease advice

hi tony ,

Thank s a lot for your help..a little search in this great forum did the trick..

Hmmm but the alignment of the column headers is distorted after i run the cross-tab report , but it is serving the functionality properly.

Is there any way i can display the column titles here as before?

Thank you

hi friends ,

Is there any way of retaining the column titles when i use the columns in the file which is created using ON TABLE SAVE.

SET HOLDATTR = ON did not work for me..

Please advice ..

SAVE doesn't create a master. If you want column titles, you'll have to do an ON TABLE HOLD.

Thanks a lot for your reply ginny,

Hmmmm can i access the file created using ON TABLE HOLD the same way when it was created using ON TABLE SAVE..

thanks

hi ginny,

Even after setting it to ON TABLE HOLD and using the clause

APP HOLDMETA..The column titles are not getting displayed ..
Am i missing something here??

Arvind,

I don't know what you are trying to do. If you create a HOLD file, you get a master and an ftm file. In the same procedure, you can then issue a TABLE FILE HOLD etc. If you create the hold file in a different focexec then you will have to APP HOLD both the master and the data and then in a new fex do a filedef to point to the file.

If you want to continue to use the SAVE file for something, you will have to create your own master and then filedef it to the save.ftm file.

Or you can do a -READ.

Arvind,

Firstly, look up the differences between SAVE and HOLD - remember those things called manuals?

You can get a similar file output from HOLD as SAVE by using FORMAT ALPHA.

When you HOLD a file the resulting mas file will retain any TITLE attribute that existed in the original mas file(s) of the data sources your table request hit. If you have only specified AS 'title text' in your table request then this will not be passed to the hold files mas as a TITLE attribute but will be used as the FIELDNAME attribute.

Finally, you are getting a little off topic here so it might be a suggestion to start up a thread with a subject depicting your new question. It might help others searching for a particular subject.

T

I I captain ...i will start a new thread.

Thanks for that tip of manuals