More Calculated Member questions

[Former Member]

So, this calculated member thing seems like a great idea, but it's really confusing.  I'm trying to add a calculated member column to produce the "Average Volume" column in this report, instead of the way I did it here where I made it a horizontally displayed summarized field.  It doesn't work this way because when you export it to Excel (which it will need to be), it puts an empty column where I've suppressed the Average Volume in the data columns.

(You'll have to open the screen shots to see the whole thing)

It is calculated using the Total Volume for each sales rep from the Grand Total column, divided by another formula that counts the number of business days in the current month so far: Sum({ContractBasisVW.Equipment Original Cost},{ContractBasisVW.Sales Rep Name})/{@CountDays} .

I've tried right clicking on the last date column (Submitted Date) which is currently 12/11/12, and inserting a Calculated Member column, then editing the calculation for it to be my Average Volume calculation above.  However, it gives me the same value which is the correct total for the first Sales Rep for every line:

And, my insertion formula will also need to be changed, but I don't know what to make the formula so it always appears BEFORE the Total Volume (Grand Total) column, no matter how many columns I have in my report, because on a day to day basis those columns are going to increase for each day I have submitted applications.  Right now, it keeps putting it after the column that has 12/11/12 in it, and tomorrow, it will be in the wrong spot when I have a 12/12/12 column because it will put it in between them.  I'm just not sure how to write the insertion formula.  I really wracked my brain trying to figure out how to accomplish this, but I'm just not getting it.  I would appreciate any help for both issues!!

[abhilash_kumar · Accepted solution]

Hi Katy,

The position of the Calculated Member or let's say the column where the Calculated Member appears, depends on the "Insertion Formula".

In your case, since you add the Calculated Member after 12/11/12, it will Alyways appear after this column. When new columns are added, the Calculated Member will still appear after 12/11/12.

To do away with this, here's what you need to do:

1) I hope you've set the Column Grand Totals to show on the right. By default CR shows the Column Totals on the left. To set it to the right, right-click the Total Column while in Preview Mode > Select Column Grand Totals > uncheck 'Totals on left'

2) Once you have the Grand Totals to show on the right, right-click the Grand Total column > Select Calculated Member > Insert Child.

This will insert a Blank Column to the left of the Grand Total. When new columns are added or removed, this column will still appear at the end after all the date columns.

So, that solves one of the issues!

3) To show the average, right-click one of the cells in the newly added column > Calculated Member > Edit Calculation Formula and use this code:

local numbervar i;  
local numbervar avg;  
local numbervar cnt; 
for i := 0 to CurrentColumnIndex-1 do  
(  
    avg := avg + (GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex);  
    cnt := cnt + 1;  
);  
avg/cnt;

4) You might also want to name this column as Average. So, right-click the blank header cell > Calculated Member > Edit Header Formula and type this:

"Average"

Let me know how this goes!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Unfortunately, didn't get very far.  Yes, I do have my Grand Totals on the right ('Totals on Left' is unchecked).  When I right-click on Grand Totals and Insert Child, nothing gets inserted.  I get a message that SAYS it is inserting a column, but then there's nothing there???

So that's why I did it the other way--by right clicking on the last Date column and using "Insert Column", which put the column where it will be in between my date columns where I don't want it.  But that's the only way I could get a column to even show up.  Can I do that, and then edit the insertion formula so it puts it to the left of my Grand Total column?  Would the insertion formula be the one Jyothermayee S typed out above?

[Former Member · Accepted solution]

Abhilash, I also tried your formula, but it's telling me it's missing a closing parenthesis which it doesn't look like it is to me, but that's what I'm getting.  Also, I'm not sure that formula will work because isn't it using the count of the columns for the divisor?  That's not going to be 100% accurate, we may have days where no applications come on so there won't be a column for it, however that day still needs to be counted in the average.  Which is why I created the formula that counts the number of business days in the month so far, which is:

Local DateTimeVar d1 := DateSerial(Year(CurrentDate), Month(CurrentDate), 1);

Local DateTimeVar d2 := CurrentDate;

DateDiff ("d", d1, d2) -

     DateDiff ("ww", d1, d2, crSaturday) -

     DateDiff ("ww", d1, d2, crSunday)

[abhilash_kumar · Accepted solution]

Hi Katy,

Alright, try this:

1) With the Columns Totals still set to show on the right, right-click the last date and Insert a Calculated Member

2) Edit the Insertion Formula and change it to:

GetColumnGroupIndexOf(CurrentColumnIndex) = 0

3) Sometimes, this causes the Calculated Member to not show up. To get it back, right-click the White-space at the top-left corner of the Crosstab object and select Advanced Calculations > Calculated Member

4) This is another place that let's you manage Calculated Members. Take a look at the screenshot.

Where it says "Please Enter a description"; that is the one column that you have added. If there are more, you'll see more of those below.

If you look at the right, by default the "Insert Evaluation" is "After". Change that to "Before" and click OK.

That'll bring the Calculated Member column back.

Let me know how this goes!

-Abhilash

[abhilash_kumar · Accepted solution]

Katy,

The formula is indeed missing the closing parenthesis. Look closely at the 6th line of the code. It should be:

avg := avg + (GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex));   

And yes, you're right, this code counts the number of columns and takes it as the divisor.

You can use the DateDiff part as the divisor instead. That should work.

-Abhilash

[Former Member · Accepted solution]

Still can't get the column to show up, I followed your instructions to a T.  Here it is through the Calculated Member expert, it was there as "Please enter a description" like you said (I deleted all the other ones from all my attempts!), so I changed that to "Average Volume".  I also edited the header formula to be "Average Volume"  And I changed the insertion formula to the GetColumnGroupIndexOf(CurrentColumnIndex) = 0, but the column is still not there.

The value formula currently is set to 0, just so I can see if I can get the column to show up, then I was going to toy around with the calculation.  But I can't even get the column to show.

[abhilash_kumar · Accepted solution]

Okay, time to share the report. Would you be able to attach the report with saved data to this thread please?

Change its extension from .rpt to .txt before you attach.

-Abhilash

[Former Member · Accepted solution]

Sure, here you go.  And thank you VERY MUCH in advance for your help!!

Activity - App Volume.txt.zip

[abhilash_kumar · Accepted solution]

Strange! I see the Calculated Member when I open your report on my machine and I'm using Crystal Reports 2008 v.12.0.x.x; even more strange!

Anyway, I went ahead and modified the Calculation Formula and I've attached the report again. Please have a look and let me know if you see the Calculated Member and the correct Averages.

-Abhilash

Activity - App Volume_updated.txt.zip

[Former Member · Accepted solution]

LOL, it probably works great, but I still can't see if it's working or not because it still doesn't show the column.    Do I need to open a case with SAP at this point?

[abhilash_kumar · Accepted solution]

Hi Katy,

Before you get in touch with SAP, could you try this on another computer that has Crystal Reports 2008 or higher?

Also, what version of Crystal Reports are you on? Try upgrading to a latest patch.

-Abhilash

[Former Member · Accepted solution]

OK, so I have confirmed that on an older Service Pack (2) of CR 2008 the Average Volume column you created for me DOES show.  I had to refresh it to show up.  Then if I go to my version which was SP4, it shows up until I refresh.  So I upgraded to SP5, and it still doesn't show.  So do we have a SP issue here?  I double checked that my Report settings and Options settings were the same on both versions.

[abhilash_kumar · Accepted solution]

Hi Katy,

What happens if you create a new Calculated Member in SP5?

Use the same formulas I posted above and let me know your findings.

-Abhilash

[jyothirmayee_s · Accepted solution]

Hi,

Do you have any Groups in report?.

if you have a group:

local numbervar d := maximu(Date),<GroupName>);

local stringvar s := totext(d,0,"");

currentcolumnindex = GetcolumnPathIndexOf(s) + 1

Abhilash suggestions should work but you can also give a try on the above formula too..:)

Thanks,

Jothi

[Former Member]

Wow. This has been killing me as an issue and it is clear that this is affecting Crystal 14 also. I get exactly the same issue appearing.

[JWiseman]

hi John,

this is an old discussion by another forum member. please create a new discussion as per the forum rules.

however, if you are trying to find a way to have two totals / summaries at the end of a cross-tab please see this discussion.  note that the Relative Positions formatting option is the key to making this work.

-jamie

[Former Member]

No - the issue is that there is still a bug in CR dating back this far which has not been fixed!

[Former Member]

Same behavior.  It appears if I use the "Insert Column" method that inserts it relative to my date column (which I don't want), but disappears if I change the insertion formula to GetColumnGroupIndexOf(CurrentColumnIndex) = 0.   And the "Insert Child" method of adding it as a child of my Grand Total column doesn't work either, it disappears.

[jyothirmayee_s]

Hi,

Did you tried using below functions for Calculations?.

1. GridValueAt (CurrentRowIndex, CurrentColumnIndex ,0 )

2. Gridvalueat()

Thanks,

Jothi