asked on

Field coming as percentage is getting summed in cross tab

I have field from database where percentage is already calculated.

Product Sold Total %Age sold
A 122 300 40.66
B 152 600 25.33

When I put above fields in cross tab, %Age sold field is getting summed.

Is there way that I can show data as it is from database instead of sums? Or create a %age formula in CR 11 and use in cross tab so that %age is evaluated at group level?

I know you would say use manual cross tab but we want to avoid this since cross tab is easily to create and I will have many reports where these would be used.

Thanks for your help.

Mike McCracken

Where is it getting summed?

Do you mean in the total line?
Or do you have detail records for % that are being summed?

mlmcc

NickHoward

ASKER

I have detail detail records behind. Example I showed above was in cross grouped by Products.

James0628

If "%Age sold" is just a field that you're summarizing in the cross-tab, try changing the type of summary for that field to Minimum or Maximum or Average. FWIW, min or max seems more efficient.

James

NickHoward

ASKER

James.

I tried but it gave me zeros either I use min or max.

In fact, above mentioned records in my cross tab are aggregated in crosstab.

The detail records are (example):

Item      Product      Sold      Total      %Age Sold
123       A      100      200      50,00 %
345       A      22      100      22,00 %
A 122 300 40.66 (being shown in cross tab)
678       B      150      400      37,50 %
981       B      2      200      1,00 %
B 152 600 25.33 (being shown in cross tab)

As you can see %age already calculating on detail level above.

So I need to reset this on detail level while calculate only at Product Group.

Any help.

Thanks.

Mike McCracken

You can do this with formulas and a manual cross tab

http://www.kenhamady.com/form12.shtml

mlmcc

James0628

Ah. When you said "percentage is already calculated", I thought you meant that the % was calculated before the data got to CR and it was stored in a field in your data.

In your last post, you said that 40.66 and 25.33 are being shown in the cross-tab. Those are the correct %'s for those Sold and Total figures. If that's not what you want to see, what do you want?

What do you have in the "%Age Sold" column/field in the cross-tab?

James

NickHoward

ASKER

Sorry I should repharse it:

Item Product Sold Total %Age Sold
123 A 100 200 50,00 %
345 A 22 100 22,00 %
A 122 300 40.66 (should show in cross tab)678 B 150 400 37,50 %
981 B 2 200 1,00 %
B 152 600 25.33 (should show in cross tab)

Only the Bold one are in my cross tab as I am using Product (not items) so items being grouped by Products.

Hope it is clear now.

Thanks.

NickHoward

ASKER

Someone said that now it is possible in Crystal 2011 to use Calculated Members in cross tabs to calculate any thing. However I could not manage to do this.

Any idea or someother way to do this in cross tab?

Thanks.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial