I'm referencing this thread and quote to try and aggregate and sort a new report we're trying to build in 8.1.04:

http://forums.informationbuild...=602103461#602103461

quote:

Originally posted by GamP:
Something like this perhaps:

DEFINE FILE CAR
MARGINDOL/D12.2=RETAIL_COST - DEALER_COST;
END
TABLE FILE CAR
SUM
DEALER_COST NOPRINT
RETAIL_COST NOPRINT
COMPUTE MARGIN/D12.2% = MARGINDOL / DEALER_COST; NOPRINT
BY CAR
SUM
DEALER_COST 
RETAIL_COST
COMPUTE MARGIN/D12.2% = MARGINDOL / DEALER_COST;
BY CAR
ACROSS MODEL
COMPUTE RTDC/D12 = C1; AS 'TotDcost'
COMPUTE RTRC/D12 = C2; AS 'TotRcost'
COMPUTE RTMG/D12.2% = C4; AS 'TotMargin'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
END

Hope this helps ...

However, we need to sort the report by the highest 'TotDCost' that was calculated in the ACROSS instead of by "CAR".

Anyone suggest how we can sort by the highest value in the across computed RTDC column (TotDCost)?

Thanks.

This message has been edited. Last edited by: Deej,

I've never seen this type of compute done, so if you can help me understand how C1 C2 and C4 even work, because those are not fields in the CAR synonym. After I understand that, I might be able to help you.

GavinL,

Thanks for the reply but we haven't completely wrapped our brains around how the C1, C2 and C4 work just yet, but we copied the code referenced into our environment and it works.

C1 totals the 1st SUM row (DEALER_COST) as RTDC (Row Total Dealer Cost). C2 totals the 2nd SUM row (RETAIL_COST) as RTRC (Row Total Retail Cost). C4 totals the COMPUTE 'Margin' as RTMG (Row Total Margin).

We know their has to be a way to sort the ACROSS computed summary column, we just haven't been able to figure out how to do it.

@ Gavin,

The C1, C2 etc . are column notation where C1 infers column 1 etc. This link relates use within the RECAP syntax but the idea is the same.

@ Deej,

If you think of the ability to sort by a computed value by using BY TOTAL fieldname, the you should be able to work out why this works.

DEFINE FILE CAR
MARGINDOL/D12.2=RETAIL_COST - DEALER_COST;
END
TABLE FILE CAR
SUM
DEALER_COST NOPRINT
RETAIL_COST NOPRINT
COMPUTE MARGIN/D12.2% = MARGINDOL / DEALER_COST; NOPRINT
COMPUTE SORT_VAL/D6 = DEALER_COST; NOPRINT
BY HIGHEST TOTAL SORT_VAL NOPRINT
BY CAR
SUM
DEALER_COST 
RETAIL_COST
COMPUTE MARGIN/D12.2% = MARGINDOL / DEALER_COST;
BY HIGHEST TOTAL SORT_VAL NOPRINT
BY CAR
ACROSS MODEL
COMPUTE RTDC/D12 = C1; AS 'TotDcost'
COMPUTE RTRC/D12 = C2; AS 'TotRcost'
COMPUTE RTMG/D12.2% = C4; AS 'TotMargin'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
END

T

I was thinking that at first, but column 1 isn't Dealer Cost, it's CAR, column 2 is Dealer cost, and column 4, well that is Margin. So I'm still a little confused.

Beautiful Tony! Thank you!!

We understand what (any why) you did by adding the SORT_VAL column and we understand the C1, C2 and C4 (for the most part)...

I think what is throwing us off is why the 'TotMargin' column is numbered C4 instead of C3.

Are rows numbered by SUM fields first (DEALER_COST-C1, RETAIL_COST-C2), then DEFINE fields (MARGINDOL-C3), then COMPUTE fields (MARGIN-C4, SORT_VAL-C5)? If so, then we have it!

The easiest way (for me ) to understand it is ...

C1 = first DEALER_COST.
C2 = first RETAIL_COST.
C3 = first MARGINDOL because it is used within the compute and the internal matrix may require it.
C4 = first MARGIN compute.

The DEALER_COST within the MARGIN compute is already available in the matrix and therefore would not be a Cn.

Because the MARGINDOL is a define, the component fields are not required in the matrix so would not be Cn.

Column notation can be awkward to get your head around until it all clicks into place.

I am sure that others would explain it differently


T

This message has been edited. Last edited by: Tony A,

Also check out this link which includes the environmental setting CNOTATION.

Check the final section of the page!

T

quote:

Originally posted by Tony A:
Column notation can be awkward to get your head around until it all clicks into place.

Yeah, you can say that again!!

We'll apply to our report and hopefully it will sink in.

Thanks again for the help!

Deej,
The C fields are called "column notation" [see "Assigning Column Reference Numbers" in the Creating Reports manual].
Columns are numbered in the order they appear in the TABLE request.
Sort fields are not counted.
Fields appearing in a COMPUTE and not refereced previously are also counted.
In your example:
C1 is the first occurence of DEALER_COST
C2 is the first occurence of RETAIL_COST
C3 is the value of MARGINDOL in the first COMPUTE of MARGIN
C4 is the first occurence of SORT_VAL
Your output is thus sorted descending by the computed SORT_VAL which appears under the title 'TotMargin'.
As you rightly say: "Beautiful"!

I've looked at the CNOTATION help and now I'm even more confused.. Hate to hijack this thread, but this doesn't make sense..

@Danny
BY columns come before SUM columns within a report.

The columns appear as follows:
CAR DEALER_COST RETAIL_COST MARGIN

So how are you saying DEALER_COST is 1?

If I'm using formatting, then I use N1 to represent the CAR column, so shouldn't C1 also be for CAR? Why are the BY columns being skipped? Is the CNOTATION not follow the NNOTATION numbering system?

quote:

Originally posted by Danny-SRL:
Deej,
The C fields are called "column notation" [see "Assigning Column Reference Numbers" in the Creating Reports manual].
Columns are numbered in the order they appear in the TABLE request.
Sort fields are not counted.
Fields appearing in a COMPUTE and not refereced previously are also counted.
In your example:
C1 is the first occurence of DEALER_COST
C2 is the first occurence of RETAIL_COST
C3 is the value of MARGINDOL in the first COMPUTE of MARGIN
C4 is the first occurence of SORT_VAL
Your output is thus sorted descending by the computed SORT_VAL which appears under the title 'TotMargin'.
As you rightly say: "Beautiful"!

Danny,

I think we're on the same page... however... the way the columns play out is:

C1 is the first occurrence of DEALER_COST
C2 is the first occurrence of RETAIL_COST
C3 is the DEFINE value of MARGINDOL
C4 is the first COMPUTE of MARGIN
C5 is the first COMPUTE of SORT_VAL

I still don't understand exactly why it works out like it does but I understand the concept which is good enough at this point to move forward with.

@Gavin, in the last link Tony posted, it says "BY fields are not assigned column numbers" which means CAR doesn't play into this scenario and DEALER_COST and RETAIL_COST are #1 & #2 (those two make complete sense). MARGINDOL is #3 and MARGIN #4 (those two are a little confusing) then SORT_VAL is #5 (makes sense).

Thanks for the replies everyone!

Yea, I should be use to this by now.. C Notation doesn't follow the same rules as N Notation for columns. Not sure why they did it that way, but at least I know now.

"BY fields do not get a column reference, so the first column reference is for TRANSTOT with its original format, then the reformatted version. Next is QUANTITY with its original format and then the reformatted version. Last is the calculated value, PRODUCT."

Gavin,

quote:

C Notation doesn't follow the same rules as N Notation for columns. Not sure why they did it that way

As far as I know, the C notation was created in order to distinguish between multiple uses of the same field. In the provided example

COMPUTE RTDC/D12 = C1;

you want to display the first occurence of DEALER_COST. As for not including the BY fields in the notation, this is because each BY field appears only once and they will not participate in a COMPUTE.
Of course you could use a DEFINE to change the name of DEALER_COST for its second occurence.