Formula not showing in Crosstab Expert

[mirogak]

Hi there,
Using: CR 11.5 with Oracle 10g db

I have a cross tab with multiple rows and multiple summarized fields. I need to add a formula as a summarized field. I create the formula but when I go to the Crosstab expert I do not see it the 'Available Fields' tab, even though I see it in the Field Explorer. And neither can I drag and drop it from the Field Explorer onto the crosstab.

Any idea what may be happening here? The CR manual tells me you can easily add formulas to rows, columns or the summarized fields.

Appreciate your help.
Mirogak

 

[lbass]

Please show the content of the formula that isn't showing up.

-LB

 

[mirogak]

Sure.

The report itself is sales report showing previous monthly sales and comparing them to monthly budgets. It also shows YTD sales (adding up the monthly sales including current YTD) and compares them to monthly budgets (adding up the monthly budgets). It groups the sales by product categories. All that is happening in the details sections which is being suppressed because I want to use the crosstab in the report footer.

This is what the cross tab looks like

Product_Type | Jan | Feb | Mar | Apr | so on
Monthly_Sales
YTD_Sales
Monthly_Budget
YTD_Budget

YTD_Sales and YTD_Budget are running totals (formulas) that I used up in the Details sections and now added to the crosstab summary. They work fine.

Problem is that now I want to declare a new formula that simply subtracts the YTD_Budget from YTD_Sales to get the variance.

Here is the formula
{#YTD Net Sales}-{#YTD Budget}

When I drag this formula up in the Details/Group Footer sections it works fine. I just now need to pull it to the Crosstab as well but I can not see it in the Available Fields section.

Hope this helps.

Thx,
Mirogak

 

[lbass]

Please explain why you used running totals or explain how each running total is set up. If you can instead use formulas (conditional or summary), you will be able to create a formula you can use in the crosstab.

-LB

 

[mirogak]

I thought the running total made sense for this requirement, since I have monthly net sales and they need to be added up for a YTD net sale and reset when the product type changes.

Here is how the Running Total is set up for the YTD Net Sale:

Summarize : Query1.Net Sales
Evaluate : for each record
Reset : on product type

Are you saying, that it would be better to group sales by product type and then by month. And use those summaries in the crosstab?

Thanks,
Mirogak

 

[lbass]

I think you are right--you need the running total--I wasn't paying atttention to the YTD aspect of this. Instead of trying to add a formula to do the subtraction, you could first add a holder formula as the fifth summary:

whilereadingrecords;
0

Then in preview mode, select the YTD Sales->right click->Format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar sales := currentfieldvalue;
false

Repeat for the budget summary, entering a formula like this:

whileprintingrecords;
numbervar budg := currentfieldvalue;
false

Then select the 0 summary->right click->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar sales;
numbervar budg;
totext(sales-budg,2)//2 for two decimals

If your fields are actually currencies, then replace numbervar with currencyvar in all places.

-LB

 

[mirogak]

Not sure what you have suggested up there but I followed it.

Here is what happened:
- the formulation does work
- the formula now appears in Crosstab's Available Fields

When I drag that new formula into the detail or group header sections of the report it works great BUT when I pull it in the crosstab, it shows me a 0.

Any idea, why that is so?

Thx LBASS

 

[lbass]

The only formula I expected you to add to the crosstab was the one that returns 0--all other formulas are intended to be used in the formula area of formatting features. Please reread my post and follow each step.

-LB