Field coming as percentage is getting summed in cross tab

NickHoward
NickHoward used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Where is it getting summed?

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

mlmcc

Author

Commented:
I have detail detail records behind. Example I showed above was in cross grouped by Products.
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
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
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 McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You can do this with formulas and a manual cross tab

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

mlmcc
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

Author

Commented:
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.

Author

Commented:
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.
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
You can use calculated cells but I believe the total rows still show totals and not a new calculation.

mlmcc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial