Setting marks in formulas

[bidra]

Using CR 10 and
Cross tab defined in report header

Hello,

I have an urgent problem as I need some kind setting marks for wether or not to count record occurences in a formula.

The data rows consist of firm number, property LP and costs come. They come sorted by firm number an property LP.

I have formula1 that is to count every occurrence of firm number with property LP.
But only once if the property LP occurs more than once out of (LP-1621,LP-1623).
Formula2 adds all costs not matter what property LP looks like.

My problem with formula1 is how to mark that records with LP-1621 or LP-1623 have already been counted for the same firm number and that the firm number has changed.

Has anybody out there an idea how to solve this problems with formula1?


Many thanx in advance

bidra

 

[dtempleton]

How do you want to "mark" the records?

Dave

 

[bidra]

I thought of using a global variable setting to 1 if a firm number with LP-1621 or LP-1623 ooccurs so that I know that this combination has already been counted.
It is set to 0 if the firm number changes.

So what I need is a value that gives the information what has happend in that respect.

Bidra

 

[lbass]

Try using the running total editor. Select {table.firmno}, distinctcount, evaluate based on a formula:

{table.propertyLP} in ["LP-1621","LP-1623"]

Reset on change of firm no for a subtotal at the firm level. Make a second running total with "reset never" for a grand total.

-LB

 

.. tried but doesn't work as the result of the running total is always 0.
I think the reason is that the cross tab is defined in the report header but running totals can only be used in the detail section of the report.

Any chance for my crod tab?

bidra

 

[lbass]

I think they are 0 because the running total is calculated in a later pass. It was not clear that you were using these formulas in a crosstab. You are probably better off creating a manual crosstab, and if you need it in the report header, create it within a subreport.

Let's assume that your column field is sales year for the following example, and that your group is on firm number. In a manual crosstab, you would then create detail level formulas like:

//{@2003} to be placed in the detail section:
if {table.propertyLP} in ["LP-1621","LP-1623"] and
year({table.salesdate}) = 2003 then 1 else 0

Then you would create a second formula:
//{@2003sum} to be placed in the group header or footer for firm number:
if sum({@2003},{table.firmno}) > 0 then 1

To get grand totals across firms, you would create these formulas:

//{@accum} to be placed in the group header or footer for firm number:
whileprintingrecords;
numbervar sum2003 := sum2003 + {@2003sum};

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar sum2003;

You would have to create separate formulas like these for each column in the crosstab.

-LB

 

[bidra]

... thank you ibass for your advice.
I have chosen the "automatic" cross tab because the number of columns and rows is not fix and can vary from year to year and so that a manual crosstab had to be adapted to every change in row/column number.
But maybe I've got no other choice ...

Thanx
Bidra