Solved

Dynamic Crystal Report - Getting percentage from SUM issues

Posted on 2004-10-18
15
488 Views
Last Modified: 2012-05-05
Hello

My data resemble this (but is not this only for illustration) : extract from Stored procedure

Code  Desc     Price     Type
A       Apple     0.50     Fruit
C       Carrot    0.70     Vege
O       Orange   0.80     Fruit
.........................................
.........................................
.........................................

I need to have a CR that look like this

No         Code      Fruit         Vege    Total         %Fruit    %Vege     %Total          
1           Apple     0.50          0.00     0.50          31.25     0.00         16.67
2           Carrot    0.00          0.70     0.70          0.00       50.00       23.33
3          Orange   0.80          0.00     0.80          50.00      0.00        26.67
4          Tomato   0.30         0.70      1.00         18.75      50.00       33.33
Total                  1.60          1.40      3.00         100        100          100
%Total               53.33        46.67     100          -            -              -


ANY idea how to? It is possible that there might be additional columns Fruit/Vege/Mixture/Merge etc
am really new to CR. Plesae give me idea how to group or manage it. I am able to come out partial report but is hardcoding the Fruit/Vege grouping. Which is not flexible as the TYPE name might not always be Fruit or Vege.

Advices or suggestions are greatly appreciated.
Thanks !!

0
Comment
Question by:neele
  • 7
  • 6
15 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 12346550
If the columns can't be pre-determined then the only way you're going to get close to what you want is with a crosstab report.  Create a new report and instead of 'Standard' choose 'Crosstab'.

To get started drag the {code} and {desc} fields into the crosstab rows section.  Drag the {type} field into the crosstab columns.  Drag the {price} into the crosstab summary area - make sure the summary type is set to SUM (this should be the default).

Once you've got this working if you have further specific questions let me know - this will get you most of the way there and the crosstab will automatically expand horizontally to accomodate whatever the contents of the {type} field might be.

HTH

frodoman
0
 
LVL 2

Author Comment

by:neele
ID: 12348361
Hi frodoman

thanks for your reply. In fact I tried using cross tab before. Just that I am unable to get the percentage columns out. I am able to toggle till the Total Column using cross tab.

BTW : Now there is changes to my report :(  there would be additional GROUPING/HEADER
                         AUSTRALIA         NEW Zealand
No         Code      Fruit         Vege  Fruit     Vege    Total         %Fruit    %Vege     %Total          
1           Apple     0.50          0.00    0.50    0.00    0.50          31.25     0.00         16.67
2           Carrot    0.00          0.70    0.00    0.70     0.70          0.00       50.00       23.33
3          Orange   0.80          0.00     0.80    0.50      0.80          50.00      0.00        26.67
4          Tomato   0.30         0.70     0.30     0.70     1.00         18.75      50.00       33.33
Total                  1.60          1.40    1.60    1.90      3.00         100        100          100
%Total               53.33        46.67  53.33   46.67    100          -            -              -

FOR NOW i yet to even EXTRACT out my DATA due to the changessss :C must come tedious
1) I think i can ONLY make use of CROSS-TAB right if i want it to be dynamic?
2) How can I make use of CR to help me with the calculation of percentage even after using cross-tab
3) Can i have condition for my grouping header as in AUSTRALIA/NEW ZEALAND etc..
IF (header is "ABC") then i do not want to display the grouping header. Is it possible?

Thanks ! Sorry..unable to produce the data sample now..
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12350417
In the summary box (crosstab expert) you'll want to add the {price} field 3 times.  

Click on one of the 3 and then click the "Change Summary Button".  Leave the summary type as SUM and check the checkbox next to "Show as Percentage".  The dropdown should already show "Total: SUM of {price}" but if it doesn't change it so it does.  Leave the 'From' radio button as "Row.

Now click on one of the other 3 and follow the exact same process except select the "Column" radio button.

This will give you the percentages - the report won't look exactly as you showed above but it's probably as close as you can get given the dynamic data requirements.  You can click on some of the summary fields and suppress them also so they aren't visible.

I was very quickly able to get test data into this format:
                  Fruit       Veg        Total
Apple            10          10          20
                    50%       50%
                                               31%

Carrot           25           0           25
                    100%      0%      
                                               38%

Orange          5             15        20
                    25%         75%    
                                               31%

I believe this is basically what you're going for.

frodoman
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12350445
Following up, I think this is about as close as you can get with dynamic data.  If you know in advance that there will be specific columns then we can get a report showing exactly what you want but w/out knowing the specific columns you are much more limited.

Also, assuming you can live with this formatting then adding your country is simply a matter of putting the country field in the Columns section (make sure it's the top field on the list).  May have to play a little more w/ summaries but the same concepts are at work.

frodoman
0
 
LVL 2

Author Comment

by:neele
ID: 12353716
Hi frodoman

Thanks for your fast reply. But i do NEED the layout to be similar/same as in COLUMN display. Any get around??
I tried the percentage, it seem fine (as of the value calculated)
But some is "redundant" how to suppress them? ..

I will check out my report changes in data but FORMAT should be more or less the same except for another HEADER/Grouping. Yes the summarise valuess might have issues if i were to add in more grouping :( >>
0
 
LVL 2

Author Comment

by:neele
ID: 12356487
Here the data..

Code  Desc     Price     Type     Special_Grp    Region
A       Apple     0.50     Fruit      Tropical         Australia
C       Carrot    0.70     Vege     Tropical         New Zealand
O       Orange   0.80     Fruit      Exotic           America
B      Banana  0.40     Fruit        aaa              Malaysia
G      Grapes  0.40     Fruit        bbbb            Malaysia

THE REPORT SHOULD LOOK LIKE
==== Is showing expandable columnsss

-------------------------------------------------------------------------------------------------------
No  |     Special_Grp |  AUSTRALIA  NEW Zealand   ====TOTAL
                              | ------------------------------------------------------------------------------
                              | Fruit    Vege  Fruit   Vege  === Fruit    Vege    %Fruit %Vege  %Total          
-------------------------------------------------------------------------------------------------------
1           Apple            0.50     0.00   0.50    0.00    === 0.50   0.50          31.25   0.00     16.67
2           Carrot           0.00     0.70   0.00    0.70    === 0.70   0.70          0.00     50.00    23.33
3          Orange          0.80     0.00   0.80    0.50    === 0.80   0.80          50.00   0.00      26.67
4          Tomato         0.30      0.70   0.30     0.70   === 1.00   0.80      18.75    50.00      33.33
Total                         1.60      1.40  1.60    1.90     === 3.00  0.80       100      100         100
%Total                     53.33     46.67  53.33   46.67 ===  100  100        -            -            -

Wahhh that how the report should resemble..>> I am using Cross-Tab now which enable me to get until before percantage columns. as the percantage column would become ROW if i using the way being show on the previous replies..

Thanks..Suggestions please...Or way to do it?? or..


0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 42

Expert Comment

by:frodoman
ID: 12358228
>>> I tried the percentage, it seem fine (as of the value calculated)  But some is "redundant" how to suppress them? ..

To suppress ones you don't need just rt-click on it then Format Field -> Common -> Suppress.


You aren't going to be able to get a crosstab exactly like you want - in particular, the percentages that you have at the right will be aligned under the other values instead of at the right.  You can make them different fonts / colors to help set them apart but you can't change where they appear (of course you could reverse what you have in rows & columns but that's just going to 'flip' everything so it'll still be opposite).

As I've said, your hands are tied because you don't know what columns you will have.  If you knew then you could create formulas and exactly duplicate what you want to see.  However if you did that for the two columns shown here then the results would be inaccurate as soon as a third entry was introduced.

frodoman
0
 
LVL 2

Author Comment

by:neele
ID: 12378197
Opps you mean no way around? In fact i found ways to set the Percentage to be@ the right side. By Customise the STYLE put summarized field to be Horizontal.

But still lack this and that. Is there any way to add a COLUMN counter at the very bottom?
As in a counter to counter each fruit and vege column ??

I cannot know the columns. Since it is based on queries extraction as in the data might have more or less , it depends..>> I can only know surely there is Fruit/Vege column the region header are not Fixed.

Seem not much idea on customising Crosstab :( am rather new to it and playing ard with it. Seem without using cross tab it is much more flexible..>>
 **There is no way to without using crosstab the CR able to group and expand horizontally right????
0
 
LVL 2

Author Comment

by:neele
ID: 12378784
I need to know is it possible to have multiple Summarzied total as in percentage etc ?
something like

-------------------------------------------------------------------------------------------------------
No  |     Special_Grp |  AUSTRALIA  NEW Zealand   ====TOTAL
                              | ------------------------------------------------------------------------------
                              | Fruit    Vege  Fruit   Vege  === Fruit    Vege    %Fruit %Vege  %Total          
-------------------------------------------------------------------------------------------------------
1           Apple            0.50     0.00   0.50    0.00    === 0.50   0.50          31.25   0.00     16.67
2           Carrot           0.00     0.70   0.00    0.70    === 0.70   0.70          0.00     50.00    23.33
3          Orange          0.80     0.00   0.80    0.50    === 0.80   0.80          50.00   0.00      26.67
4          Tomato         0.30      0.70   0.30     0.70   === 1.00   0.80      18.75    50.00      33.33
Total                         1.60      1.40  1.60    1.90     === 3.00  0.80       100      100         100
%Total   fruit/total    53.33     46.67  53.33   46.67 ===  100  100        -            -            -
%Total 2 type/total   53.33     46.67  53.33   46.67 ===  100  100        -            -            -

Is that possible for Total , %Total, %total2..?? I am unable to make the rowsss that why :(
0
 
LVL 42

Accepted Solution

by:
frodoman earned 150 total points
ID: 12380187
You were right on target when you say that crosstab is NOT very flexible at all - it's a great tool for some very specific uses but you don't have enough control over it.  You can, as you said, make some changes with custom style but these are mostly superficial like how the lines are displayed, moving things from right side to left side, etc.

>>> **There is no way to without using crosstab the CR able to group and expand horizontally right????

This is correct within the Crystal environment.  Of course you could write an application that will read the database and determine all of the required columns first and then you could have the application create a completely customized report on the fly.  This approach however is not a simple undertaking by any means and it requires one of the advanced editions of Crystal and possibly use of royalty-required runtime files.  Unless you're able and willing to tackle it from that perspective then it cannot be done.

>>> Is that possible for Total , %Total, %total2..??

Not that I'm aware of.  You can have totals based on groupings so if you formatted your crosstab like this:

Fruit
   Apple
   Orange
      ** %Fruit Total
Vege
   Carrot
   Peas
      ** % Vege Total
** % Total

But otherwise I don't know of a way to do the totals like that.

frodoman

0
 
LVL 2

Author Comment

by:neele
ID: 12408621
So there is no way out it seems.....unsure whether there is anyway to play around with the data? in order to get the report layout i wanted?

****
Is there ways to get CrossTab column total?? As in the columns are dynamically populated. But i need to use the column price total DIVIDE by the TOTAL column Total value.

That is to get the ROW as below >> (pls refer back to previous posting)
%Total   fruit/total    53.33     46.67  53.33   46.67 ===  100  100        -            -            -

Now i am trying to get the value..(ignoring the display issue first)
please anyone !!..


0
 
LVL 42

Expert Comment

by:frodoman
ID: 12410439
Sorry neele, you can keep asking but the answer isn't going to change - you simply can't do what you want using Crystal-only.

frodoman
0
 
LVL 2

Author Comment

by:neele
ID: 12460254
Hmmm ok so i would like to close this question since no answer to it. Might repost other question. Am i able to close and give you some points for replying?
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now