Cross Tab Sort based on Calculated Member 1

[cspuckett]

I've searched all over for this and do not have an answer.

I have your standard financial report with Actual and Budget numbers. Then, I have a calculated "difference" column showing the dollar variance.

The issue is that, for this report, I want this report to sort based on this variance - from "best" variance to "worst".

Report as it looks today:

ACCT Actual Budget Variance
4000 $200 $180 $20
4010 $300 $330 ($30)
4020 $500 $400 $100

Desired output:

ACCT Actual Budget Variance
4020 $500 $400 $100
4000 $200 $180 $20
4010 $300 $330 ($30)

Any thoughts?

 

[lbass]

What version of CR are you using? Is this an inserted crosstab? If so, how is it set up? If it is not a crosstab, what are we seeing in your example above--are these summaries? What report section are we seeing. Please show us the formula for variance.

-LB

 

[cspuckett]

Shoot, sorry, I meant to put the version details in my post.

CR 2008, and yes this is an inserted crosstab (so you are seeing summaries).

My real report is actually a little different from the "example" (the end result is still the same).

It's actually a report that pulls 13 months of data from a GL transaction table. Current month plus prior 12 months. There is a calculated member column that takes the prior twelve months and determines the average. That formula is:

(
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -13, {@MyCalcDate}))), CurrentSummaryIndex) +
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -12, {@MyCalcDate}))), CurrentSummaryIndex) +
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -11, {@MyCalcDate}))), CurrentSummaryIndex) +
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -10, {@MyCalcDate}))), CurrentSummaryIndex) +
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -9, {@MyCalcDate}))), CurrentSummaryIndex) +
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -8, {@MyCalcDate}))), CurrentSummaryIndex) +
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -7, {@MyCalcDate}))), CurrentSummaryIndex) +
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -6, {@MyCalcDate}))), CurrentSummaryIndex) +
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -5, {@MyCalcDate}))), CurrentSummaryIndex) +
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -4, {@MyCalcDate}))), CurrentSummaryIndex) +
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -3, {@MyCalcDate}))), CurrentSummaryIndex) +
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -2, {@MyCalcDate}))), CurrentSummaryIndex)
)
/
12


If curious, "@MyCalcDate" is a formula:

Global dateVar mycalcdate;
If {?AcctPeriod}=12 then
mycalcdate := Date ({?FiscalYear}+1, 1 ,1 )
else
mycalcdate := Date ({?FiscalYear},{?AcctPeriod}+1 ,1 );

Right or wrong, I base part of my filter on posting dates less than "mycalcdate" (which is the first day of the next month).

The variance column calculation, because I could not figure out how to do a difference calculation off of a calculated member, is simply:

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(CDate (DateAdd ("m", -1, {@MyCalcDate}))), CurrentSummaryIndex)

and then minus that same "average" formula above.

So, end result in that variance column is Current Month minus 12-prior month Average.

My rows are a concatenated string of GL account numbers and the account descriptions. Right now, this appears to be the only way to sort this cross tab.

My "regular" cross tab columns are the 13 periods, using the Cross Tab Expert to group by another formula called "@MyCurrentDate"...which, is basically the first day of the current month. It's grouped in ascending order and a column is printed "for each month" and the value printed is "the first date in the period".

So, end result, I still want to sort by "best" variance to "worst" variance.

 

[lbass]

I'm assuming you have checked (while the crosstab is selected) report->group sort and have found that the calculated member is not available for sorting. At least I'm guessing it isn't.

Other than that simple suggestion, I can't help you, as I am unfamiliar with CR2008. Maybe someone else will step in.

-LB

 

[cspuckett]

You are correct. The only seem to have two sort options. The primary sort is by my row member, which is the GL account number. I can also sort by the sum of the amounts (ascend or descend), so, for example, I can have the account with the most dollars first and the account with the least 13-month dollars will be last. But I can't sort by that variance I mentioned.

 

[lbass]

Thread767-1489425 might be helpful. If you can create a SQL expression that does the calculation, you could use it as your third summary in the crosstab (maximum), and then sort the crosstab on this.

-LB

 

[cspuckett]

LB - thanks for your postings. Through this and a couple others you had responded to concerning sorting, I got it to work.

Because I was looking to sort on the current month amount minus the prior 12-month average, I built the following formula:

if {DBGLGLT.POSTING_DATE} >= {@MyCurrentDate} and {DBGLGLT.POSTING_DATE} < {@MyCalcDate} then
{DBGLGLT.BASE_AMOUNT}
else if {DBGLGLT.POSTING_DATE} >= DateAdd ("m", -13, {@MyCalcDate}) and {DBGLGLT.POSTING_DATE}< {@MyCurrentDate} then
-({DBGLGLT.BASE_AMOUNT}/12)
else
0

In English, the formula returns the full line item amount if the period is with the current month. If the amount is in one of the prior 12 months, it takes "negative" 1/12th of the line amount.

I then put this formula within the cross-tab detail. The monthly figures are worthless (I suppress these). The value I want is in the "Total" column. I actually suppress the total column becuase I have my own calculated column for the variance number as well (I just couldn't sort on this column).

Using the Report Group sort, I am sorting my Accounts (i.e., the rows) based on the sum of my variance formula.

Works like a charm.

 

[lbass]

Glad you got it working, but I don't see much of my influence in your solution--more your own thinking, which is great!

-LB