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] regarding pivot tables

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] regarding pivot tables
 Login/Join
 
<swapna>
posted
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,
 
Report This Post
<JG>
posted
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.
 
Report This Post
Virtuoso
posted Hide Post
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.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<swapna>
posted
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....
 
Report This Post
Gold member
posted Hide Post
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,


WF 7.6.4 & 5.3
Charles Lee
 
Posts: 93 | Registered: June 17, 2008Report This Post
<swapna>
posted
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????
 
Report This Post
Gold member
posted Hide Post
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 ?


WF 7.6.4 & 5.3
Charles Lee
 
Posts: 93 | Registered: June 17, 2008Report This Post
Virtuoso
posted Hide Post
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)




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<swapna>
posted
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>,
 
Report This Post
Virtuoso
posted Hide Post
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....




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<swapna>
posted
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.....
 
Report This Post
Expert
posted Hide Post
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,




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Gold member
posted Hide Post
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 ?


WF 7.6.4 & 5.3
Charles Lee
 
Posts: 93 | Registered: June 17, 2008Report This Post
Virtuoso
posted Hide Post
Doug, Charlz

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




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Gold member
posted Hide Post
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,


WF 7.6.4 & 5.3
Charles Lee
 
Posts: 93 | Registered: June 17, 2008Report This Post
Expert
posted Hide Post
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,
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
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



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, 2004Report This Post
Gold member
posted Hide Post
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 ?


WF 7.6.4 & 5.3
Charles Lee
 
Posts: 93 | Registered: June 17, 2008Report This Post
Silver Member
posted Hide Post
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,


WF 767
 
Posts: 34 | Location: Chicago, IL | Registered: September 02, 2004Report This Post
Silver Member
posted Hide Post
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


WF 767
 
Posts: 34 | Location: Chicago, IL | Registered: September 02, 2004Report This Post
Member
posted Hide Post
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?
 
Posts: 13 | Registered: March 28, 2007Report This Post
Silver Member
posted Hide Post
Excel 2002


WF 767
 
Posts: 34 | Location: Chicago, IL | Registered: September 02, 2004Report This Post
Virtuoso
posted Hide Post
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,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
...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


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report 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] regarding pivot tables

Copyright © 1996-2020 Information Builders