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,
October 01, 2008, 09:08 AM
<JG>
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.
October 02, 2008, 06:13 AM
FrankDutch
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
October 03, 2008, 12:51 AM
<swapna>
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....
October 03, 2008, 01:27 AM
Charlz
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
October 03, 2008, 02:07 AM
<swapna>
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????
October 03, 2008, 02:51 AM
Charlz
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
October 03, 2008, 04:55 AM
FrankDutch
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
October 03, 2008, 06:40 AM
<swapna>
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 ENDThis message has been edited. Last edited by: <swapna>,
October 03, 2008, 07:15 AM
FrankDutch
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
October 03, 2008, 08:42 AM
<swapna>
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.....
October 03, 2008, 11:54 AM
Doug
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
October 03, 2008, 01:50 PM
Charlz
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
October 05, 2008, 02:57 PM
FrankDutch
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
October 05, 2008, 06:20 PM
Charlz
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, RegardsThis message has been edited. Last edited by: Charlz,
WF 7.6.4 & 5.3 Charles Lee
October 05, 2008, 10:17 PM
Doug
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, DougThis message has been edited. Last edited by: Doug,
October 06, 2008, 02:17 AM
Tony A
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
October 06, 2008, 03:34 AM
Charlz
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
January 19, 2010, 02:23 PM
Krysti
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,
KrystiThis message has been edited. Last edited by: Krysti,
WF 767
January 19, 2010, 03:04 PM
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
WF 767
January 20, 2010, 10:05 AM
AG
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?
January 20, 2010, 12:56 PM
Krysti
Excel 2002
WF 767
March 12, 2010, 10:32 AM
Dan Satchell
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
March 12, 2010, 12:15 PM
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