hi ,

i need to get two summarized values in the Data field of a pivot table(they should get displayed as columns).But i am getting one data one below the other.

Data
111 555

but i am getting it as:

DATA
111
555

kindly suggest....

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

swapna,

You have told us exactly nothing.

Just showing the data output is of no value.

To be able to help we need to see an example of your code (preferably against the Focus Car database)
That way we will be able to see where your problem is.

Swapna

If you want us to help you in the best way, please update your signature with version information. The possibilities are different per version.

hi,

this is my code.....

TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
SALES
BY MODEL
BY CAR
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
TABLE FILE HOLD1
PRINT
DEALER_COST
RETAIL_COST
SALES
BY MODEL
BY CAR
ON TABLE PCHOLD FORMAT EXL2K PIVOT
END

In my output,the cols namely "sum of DEALER_COST","sum of RETAIL_COST" and "Sum of Sales" are displayed in Data Field of the pivot table as rows...
But i need to display it column wise....

Hi swapna,

I'm fairly new at WebFOCUS, but will changing

BY MODEL
BY CAR

in your TABLE FILE HOLD1

to :
ACROSS MODEL
ACROSS CAR

give you what you want ?

Hope this helps ?

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

hi Charlz,

This wont work as i need to display data,sort by the model and car.

Can some one else help me with it????

OK, I guess I'm not sure what you want.

The 'ACROSS' modification sorts by CAR, then by MODEL, horizontally - is that not what you want ?

If you want to sort by MODEL, then by CAR,

reverse these two steps to :

ACROSS CAR
ACROSS MODEL

... because the major sort field is the last field.


Otherwise, I can't visualize what you are asking, but we'll try again...

Can you give us a sample of how you want it sorted ?

Swapna

First you say you want a pivot table. Pivot is IMHO the same as ACROSS.
Now you say you want it row by row.....

Can you create an example report based on CAR and post the output as a screen shot, so we better understand this.
I'm afraid we will be a bit confused now.

Something like

          model
           aaaaa    bbbbb   cccc   ddddd    
country   
ENGLAND     12344   44444           66666
ITALY       44333   44567   65433   
FRANCE          0   54221   64332     766
....

if you post your example between code tags we can read it as it should be.....

code tags are found in the upper right corner (the red ones)

hi,

this is the format in which i want my output to be in pivot table,.i.e the cols namely Sum_Cost and Sum_Sales are shown col wise in DATA FIELD...

                DATA                    

 Model   Car    Sum_Cost    Sum_Sales   

 aaa     a      111         2222        

 bbb     b      222         3333        

but currently i am getting it row wise as shown below:

                DATA                    

 Model   Car    Sum_Cost    111         

 aaa     a      Sum_Sales   2222        

 bbb     b      Sum_Cost    222         

                Sum_Sales   3333        

And the code for this is as given below:

TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
SALES
BY MODEL
BY CAR
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
TABLE FILE HOLD1
PRINT
DEALER_COST
RETAIL_COST
SALES
BY MODEL
BY CAR
ON TABLE PCHOLD FORMAT EXL2K PIVOT
END

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

This is a bit wrong.....

ON TABLE PCHOLD FORMAT EXL2K PIVOT

Change

this line to

TABLE FILE HOLD1
SUM
DEALER_COST
RETAIL_COST
SALES
BY MODEL
BY CAR
ON TABLE PCHOLD FORMAT EXL2K 
END

So instead of PRINT use SUM
and instead of FORMAT EXL2K PIVOT just
FORMAT EXL2K

I hope this helps....

this does not help...

i want the o/p in pivot tables only.... and for tat we have to include
ON TABLE PCHOLD FORMAT EXL2K PIVOT

i need those cols namely "sum_cost and sum_sales to be specified in the "DATA field" of pivot tables....


can u share ur mail id so tat i can send the excel format which wil clearly specify wat i need.....

Swapna,

I'd like to join in on this and see your sample of your desired output as well. You can email anyone on the forum by clicking on the persons name / click on 'view [named] profile' / profile / Send Private Message to [name]. Or, you can upload it to a sie and add the URL in your post.

Have you considered using (something like) the following code and creating your own Pivot Table within Excell from the resultant data?

TABLE FILE CAR
PRINT MODEL CAR DEALER_COST RETAIL_COST SALES
ON TABLE PCHOLD FORMAT EXL2K
END


And, yes, please update your profile and add your code in your initial posts. I know you're new to this forum. So, take this as kind and helpful advise.

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

Hmmm...

Seems to me that simply removing "PIVOT" from the FORMAT statement, "FORMAT EXL2K" instead of "FORMAT EXL2K PIVOT," will accomplish what Swapna wants ?

Is there a special reason for using a pivot table ?

Do you need the dropdown selection lists or some other "pivot table" feature ?

If not, a simple "FORMAT EXL2K" gives the output described in the October 03, 2008 06:40 AM post.

Or is there more to this ?

Doug, Charlz

I'm not sure if repeating of my answer is very helpful....

Sorry Frank,

I didn't mean to simply repeat what you said.

I'm kind of new at FOCUS and WF, and it looked to me that changing PRINT to SUM didn't need to be changed, since the SUM was already done in the first TABLE FILE ?

Like I said, I'm new, and I thought the only change needed was to eliminate "PIVOT" to give the results that Swapna wanted ?

I like to look at questions and responses as sort of a tutorial, and tested it as best I could.
I may be wrong though, and if so, I apologize for stepping on your reply.

Thanks for listening,
Regards

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

FrankDutch: Somewhat of a ditto to Charlz. I didn't mean to merely repeat your reply but to elaborate upon it. Swapna seems to be a bit new to this whole thing and I though that hearing (close to) the same thing would add emphasis and support the thoughts of others on the subject as well as to elaborate upon it by additional comments such as: "creating your own Pivot Table within Excel from the resultant data". I thought that the elaboration would have been a benefit to Swapna in that Swapna needs to do something beyond what WebFOCUS gives.

Swapna: Did any / all of these replies to your issue help in reaching your final resolution? As you can see, there is a wealth of information here and sometimes we may emphasis what other have already said in one form or another.

Regards, Doug

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

Swapna,

Bearing in mind that you maintain that you want Excel Pivot output, once you have your data in the Excel plug-in can you change the "over" appearance to a side-by-side as you wish to?

I am not sure that you can, which means that your request should be sent to Bill Gates as it is Excel that has the limitation.

If, however, you are able to move it then you would need to raise an NFR with IB support (if you hold a valid licence) to get the behaviour.

Remember than WF only outputs to a plug-in's capabilities and can achieve no more than that plug-in can achieve normally (PDF, Excel etc.).

Simple rule - If you can achieve what you want using the end tool and WebFOCUS currently does not, then raise an NFR with IB Support. If you can't do what you want in the end tool then contact the end tool supplier and ask them to change their app to satisfy your requirements.

If you do not have to have Pivot output then use Frank's suggestion above.

T

Thank you, Tony,

It was not clear in the original post (which was Swapna's very first post, if I remember correctly), and I wondered whether a PIVOT table was actually needed ?
There may have been other functionality that was needed that wasn't mentioned.

If not, what was asked could be accomplished simply with Excel (which can do what is asked.)

I'm not sure, but the question intrigued me.

Whether or not the pivot table is necessary in this particular issue, I am interested in the resolution of this issue, since I may come across this same problem.

I'm hoping Swapna, who was new when this issue was posted, will let us know if we are helping solve the problem ?

Just wondering if anyone ever came up with a solution for doing this with the output as a PIVOT table. I have the same exact issue. This is going to be an on demand report and the user wants it in a PIVOT table. Any ideas?

Thanks,

Krysti

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

FYI - I read in Tech Support that WebFocus automatically generates OVER when using multiple verb objects.

Please let me know if anyone has a workaround for this.

Thanks,
Krysti

What version of Excel are you using? This (OVER) appears to be the default mode in Excel 2007. Can you test this report on a machine with Office 2000?

Excel 2002

You can force Excel to place measures (DEALER_COST, etc.) in columns by making them BY fields. The problem is that EXL2K PIVOT always requires at least one PRINT object, which becomes the PIVOT measure and the final column. So the only solution I could find is to PRINT one of the measures, such as SALES; PRINT an unimportant measure, such as SEATS; or PRINT one of the dimensions, such as COUNTRY (which probably isn't what you'd want).

Print SEATS so COSTs and SALES become columns:

TABLE FILE CAR
 SUM DEALER_COST
     RETAIL_COST
     SALES/D8
     SEATS
  BY COUNTRY
  BY CAR
  BY MODEL
  ON TABLE HOLD AS HOLD1
END
-*
TABLE FILE HOLD1
 PRINT SEATS
 BY COUNTRY
 BY CAR
 BY MODEL
 BY DEALER_COST
 BY RETAIL_COST
 BY SALES
 ON TABLE SET BYDISPLAY ON
 ON TABLE PCHOLD FORMAT EXL2K PIVOT
END

Print COUNTRY instead of SEATS:

TABLE FILE HOLD1
 PRINT COUNTRY
 BY CAR
 BY MODEL
 BY DEALER_COST
 BY RETAIL_COST
 BY SALES
 ON TABLE SET BYDISPLAY ON
 ON TABLE PCHOLD FORMAT EXL2K PIVOT
END

This message has been edited. Last edited by: Dan Satchell,

...or PRINT one of the columns already in the report.

TABLE FILE CAR
 SUM DEALER_COST
     RETAIL_COST
     SALES/D8
  BY COUNTRY
  BY CAR
  BY MODEL
  ON TABLE HOLD AS HOLD1
END
-*
TABLE FILE HOLD1
 PRINT SALES AS 'SALES '
 BY COUNTRY
 BY CAR
 BY MODEL
 BY DEALER_COST
 BY RETAIL_COST
 BY SALES
 ON TABLE SET BYDISPLAY ON
 ON TABLE PCHOLD FORMAT EXL2K PIVOT
END

or

TABLE FILE HOLD1
 PRINT COUNTRY AS 'COUNTRY '
 BY COUNTRY
 BY CAR
 BY MODEL
 BY DEALER_COST
 BY RETAIL_COST
 BY SALES
 ON TABLE SET BYDISPLAY ON
 ON TABLE PCHOLD FORMAT EXL2K PIVOT
END