Cross-Tab formula field not listed, HELP! URGENT!

[jen1701]

I am using CR 8.5. I am trying to set up a cross-tab report. I set up a formula field called shortage. I want to calculate (on-hand-qty) - sum(balance due). After I created the formula field, it is not listed for me to select as row or column. Why? I am new to CR. Is there limitations of using formula field in this way in CR croos-tab? Please give me some idea! I really need it badly.
Thanks,

Jen

 

[midearth]

The reason your formula isn't showing up as being available for the cross tab is due to the fact that it hasn't been evaluated at that point. About the only work-around I know of would be to create a manual crosstab instead of inserting a cross tab which should do what you want.

You can go to

http://www.kenhamady.com

for tips/tricks and details on creating a manual crosstab. Look in the "formulas" section of his website. They're (manual cross-tabs) not hard to create, but they take some time.

 

[jen1701]

Hi midearth,

Thank you so much for your reply. I did crate a manual crosstab, but the only problem is that I can not display the other column Job ID horizontally. Do you have any idea how to make manual cross-tab column displaying horizontally? Thank you again for your reply. I need some help on this very very much.

Jen

 

[lbass]

We don't know what you mean by "the other column JobID", but I'm guessing that you might be approaching this incorrectly. Let's say that JobID is your column going across the report, and that the rows are based on part numbers and the summary field is {table.qty}. First you would want to group on {table.partnumber}, and then create formulas like the following:

{@col1}:
if JobID = 1000 then {table.qty}

{@col2}:
if JobID = 1001 then {table.qty} //etc.

Then you would insert sums on these formulas to get the group level totals, and then suppress the details.

For more help, you need to supply more information about your report, including sample results and a display of how you would like the report to look.

-LB

 

[jen1701]

Thank you so much LB for the reply.
First of all, i was trying to create a cross-tab report like this:

Component Id (row) JobId, TotalBalDue, OnHndQty, Shortage
PartId
DueDate


JobId,PartId,DueDate(they are on one column), TotalBalDue, OnHndQty, Shortage are my columns. Shortage is OnHandQty - TotalBalDue. But the shortage field is not listed for me to insert into cross-tab. It would be nice if i can.

Then I created a manual cross-tab. It is grouped by componentId and JobId, i get sum of balance due. I also have a formula field shortage which is Onhand - sum of balance due. The results are fine in manual cross-tab, but the JobId, PartId, and Due date are vertically displayed. I want them to be displayed horizontally like spreedsheet format. I really need advise on this. Do I have to manually code the columns. I don't even know how many jobId i will get for the columns. Please give some ideas on this. I have been stuck with this for a while and have no idea how to solve it. Thank you ever so much if you can shine some lights on my problem. Thank you again.

jen

 

[lbass]

I honestly cannot see how this is a crosstab. Perhaps it would become obvious if you showed some sample data, instead of just the column labels. Nor do I see how the Job ID, Part Id, and Due Date came to be vertically displayed--do you have them in separate detail sections?

Please explain your fields and how they relate to each other, describe what report sections you are placing the fields in, and provide some sample data.

-LB

 

[synapsevampire]

Sounds like you're on to it, you need to manually code the columns, or use Running Totals with an evaluate->Use a Formula.

LB is correct though, you give no indication as to what goes anywhere, jsut a picture of column headings.

All the best this New Years, LB, all!

-k

 

[jen1701]

I am sorry that I did not explain it clearly. My current manual cross-tab likes look like this:

1234(PartId) BalDue On-handQty Shortage
P21(JobId)
12/23/2003 40

3456
P23
12/24/2003 100
PDY678 60 -80
(CompID)

The report is group by compId and JobId. compId and onHandQty, totalBalDue, Shortage displayed in Group Footer1 which by compId. PartId, JobId, DueDate and BalDue against each job are displayed in Group Footer2 by JobID. I supress the GroupHeader1,2 and Details. The results are correct but I would like to displayed Jobid, PartId and duedate in spreedsheet format.

OnHand TotBalDue 1234 5678 7899 2345 short
P21 P23 P34 P78
12/23/ 12/25 12/26 12/28

PDY678 0 60 10 20 30 10 -60


Is it possible to do the above format in manual cross-tab? or shall I think other way to do it. So I can not using format of on-handQty minus sum(balanceDue) in Cross-tab? it is not a complex formula. I wonder whether it is the limitation of cross-tab or I am just not experienced enough. Thank you for your help.

Jen