| Author |
Message |
Pierrick
Newbie
Joined: 13 Nov 2007
Location: France
Online Status: Offline
Posts: 4
|
   Topic: Summarize stringvar
Posted: 13 Nov 2007 at 6:26pm |
|
Hi,
I have been struggling with this for a while. I have looked everywhere (google and forums) and I think I might approach my problem the wrong way so here is what I want to do, using Crystal Reports 10:
I have a string (varchar in DB) field which populates my detail section in CR. I need to be able to draw a chart based on the last value of that field.
So, here is an example:
Date (1st Group): 10/11/2007
Interaction (2nd Group): 1234567890
Details:
Connected
Transfer to menu x
Disconnected
Interaction (2nd Group): 1234567890
Details:
Connected
Transfer to menu x
Here, "Disconnected" and "Transfer to menu x" are what I call the last actions.
In this case, I need to draw a pie graph that will show 50% of "Disconnected" and 50% of "Transfer to menu x".
Could you let me know how you would do it? I tried cross-tab, regular graphs but everytime, they summarize values and not occurences of stringvars.
Thanks in advance!
Pierrick
|
 IP Logged |
|
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|
Posted: 14 Nov 2007 at 10:17am |
|
You may need to get tricky with this.
First, how do you determine what value is "last?" Do you have some sort of date stamp field associated with it? If so, part of the key is using Max (or Min) on that field.
Now, the most straightforward solution is to use a subreport, based on an aggregate SQL statement. Write your SQL statement to grab the last value of each group. Then, your chart construction should be obvious. However, building subreports is not always the easiest solution, and can present problems for certain reports.
You can build a aggregate function to put in your group header, that should do a big part of the job. The formula should looks something like:
If {MyReport.ActionDate} = Maximum ({MyReport.ActionDate}, {MyReport.Interaction})
Then
{MyReport.ActionType}
Else
""
That should return the last action taken for each group. Then, just create a chart based on that. This method is likely to be a bit inefficient. I'd also check it frequently, to make sure it is working correctly. It is very easy to get a weird bug into this kind of formula.
|
|
IP Logged |
|
Pierrick
Newbie
Joined: 13 Nov 2007
Location: France
Online Status: Offline
Posts: 4
|
Posted: 14 Nov 2007 at 12:08pm |
|
Sorry. I forgot to mention that the "last" action is based on date/time.
Thanks for the tip on the function. I will give that a try this evening and see if I can make a graph based on that formula.
|
|
IP Logged |
|
Pierrick
Newbie
Joined: 13 Nov 2007
Location: France
Online Status: Offline
Posts: 4
|
Posted: 14 Nov 2007 at 7:18pm |
It looks like it worked but partly. The formula works great but I am also trying to make a graph out of the values that are coming out of this formula and it also draws the "" (empty string) values along with the valid values. Thanks for the help though. If you have any ideas about how to remove the empty string count from the graph, I would be happy to oblige 
|
|
IP Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|
Posted: 15 Nov 2007 at 5:59am |
|
There are a couple options for that.
If the list of possible actions is finite and known, then you can use the "specified order" ordering of the graph. You pick the order in which items are added to the graph. The convenient thing is that you can just not put the empty string on the list, then tell the ordering to discard anything not listed. However, I suspect that this will not work for your purposes.
The other easy option is to create another formula, based on the first:
If @FirstFormula = "" Then 0 Else 1
Create your graph based on a sum of this formula, rather than a traditional count. That way, the value for the empty string is always 0.
|
|
IP Logged |
|
Pierrick
Newbie
Joined: 13 Nov 2007
Location: France
Online Status: Offline
Posts: 4
|
Posted: 15 Nov 2007 at 8:49am |
|
It worked. Thanks a lot!! Really appreciated the help!
|
|
IP Logged |
|
|
|
