[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

Pie Charts and Grand Totals

I'm sure if I didn't have this huge headache, and the immense desire to leave for the weekend, I could search myself for the answer.
 
But posting a fresh question is much easier.
 
First off, I'm using Crystal Repots XI
 
I have a report where I need to show a pie chart wherein the sections show values representing sums from counting records based on the values of certain fields.
 
Let me explain...
 
I have a field called "Progress".  I have several formulas that return 1 to 0 based on the contents of the field.  For instance, I have a formula object called "Sales In Transit" which has the code like this:
Dim nRetVal As Number
If {MYTABLE.Progress} = "In Transit" Then
    nRetVal = 1
Else
    nRetVal = 0
End If
formula = nRetVal

Open in new window

This is repeated for a few others such as "Ordered", "In Picking", "Delivered" etc.
 
In the Report Footer, I have Sums of each of these, and the values are correct.  Now I want a pie chart (also in the Report Footer) that shows a segment for each of the "Progress" values.
 
In the Chart Expert, I tried putting the field objects in the "Show Objects" section of the Data tab (and selected For All Records), but that doesn't work.
 
How do I do this?
 
TIA
0
Clif
Asked:
Clif
  • 3
  • 2
  • 2
1 Solution
 
mlmccCommented:
Use the field MyTable.Progress as the summary for the chart and use COUNT as the summary

mlmcc
0
 
ClifAuthor Commented:
Ok, I get that.  There's a small problem, though.  In the original formula (shown in my OP), when the field contains "Delivered" (as well as one or two other options), it also contains a date, something like this: "Delivered - 08/20/2012".  The formula ignores the date.  So, the formula Progress_Delivered looks more like this:
Dim nRetVal As Number
If Left({MYTABLE.Progress}, 10) = "Delivered" Then
    nRetVal = 1
Else
    nRetVal = 0
End If
formula = nRetVal

Open in new window


When I setup the chart as you suggest, it sees each "Delivered" as a separate value when the dates are different.

How do you suggest I get around this so that every field that begins with "Delivered" is treated as the same no matter what text comes after the word "Delivered"?
0
 
mlmccCommented:
You could use a formula like

If Left({MYTABLE.Progress}, 10) = "Delivered" then
    "Delivered
Else
   {MYTABLE.Progress}

ANother way would be
If {MYTABLE.Progress} STARTSWITH "Delivered" then
    "Delivered
Else
   {MYTABLE.Progress}

If {MYTABLE.Progress} LIKE  "Delivered*" then
    "Delivered
Else
   {MYTABLE.Progress}

mlmcc
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
James0628Commented:
mlmcc left out the closing quote at the end of "Delivered".

 I don't use charts, but if I'm following, and you have other cases where something could be after the status, like "Ordered - 09/01/2012", then you'd need to add those to the formula.  For example:

if {MYTABLE.Progress} like "Delivered*" then
    "Delivered"
else
  if {MYTABLE.Progress} like "Ordered*" then
    "Ordered"
  else
<tests for any other values that could be followed by something else>
    else
      {MYTABLE.Progress}



 FWIW, if you could use some general rule for the end of the progress value, then you could have the formula just remove anything that was after that (as opposed to checking for specific values).  But if you've got values that include spaces, like "In Picking", coming up with a general rule to determine the cutoff point could be difficult.

 James
0
 
ClifAuthor Commented:
That was exactly what I needed.  (It also resulted in a "duh!" moment for me).

Thanks
0
 
ClifAuthor Commented:
James0628,

I know you expanded on mlmcc's response, but what he posted was enough.  I'm sure you understand.
0
 
James0628Commented:
No problem.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now