"NA" for no entry

[bielet]

Using Crystal Reports XI, MS SQL Server 2000

I have two tables with one-to-many relationship: Issue and Processing. For each Issue there can be 1 or 2 entries in the Processing table, i.e "Resolved" and "Workaround". There is always an entry for "Resolved" (Goal_Type = 'Resolved', Goal_Time = e.g. 24, Measured_Time = e.g. 16).

For High priority Issues there is also always an entry for "Workaround" (Goal_Type = 'Workaround', Goal_Time = e.g. 5, Measured_Time = e.g. 4)

For Medium and Low priority Issues there can on cannot be an entry for "Workaround". What i would like to get is the following output:

Issue ID Create Date Workaround Resolved
-------- ----------- ---------- --------
High:
00000001 1.1.2006 100 % 100 %
00000002 1.1.2006 100 % 95 %
00000003 5.1.2006 90 % 90 %
00000004 5.1.2006 100 % 100 %

Medium:
00000005 3.1.2006 100 % 100 %
00000006 4.1.2006 NA 85 %
00000007 4.1.2006 NA 100 %
00000008 4.1.2006 85 % 80 %

Low:
00000009 4.1.2006 NA 100 %
00000010 4.1.2006 NA 80 %
00000011 4.1.2006 NA 90 %
00000012 4.1.2006 NA 95 %

Where "NA" would be the output when there is no entry for "Workaround", otherwise the percentage.

So let's say i fetch the data with the following command and group the results by priority:

SELECT Issue.Issue_ID, Issue.Create_Date, Issue.Priority, Processing.Goal_Type, Processing.Goal_Time, Processing.Measured_Time
FROM Issue, Processing
WHERE Issue.Issue_ID = Processing.Source_ID

If i then create formulas for calculating the percentages like:

formula 1:
if (Goal_Type = "Resolved")
then
if (Measured_Time <= Goal_Time)
then
100
else
(Goal_Time % Measured_Time)
else
"NA"

formula 2:
if (Goal_Type = "Workaround")
then
if (Measured_Time <= Goal_Time)
then
100
else
(Goal_Time % Measured_Time)
else
"NA"

and insert the formulas to the report, i get this kind of output:

Issue ID Create Date Workaround Resolved
-------- ----------- ---------- --------
High:
00000001 1.1.2006 100 % NA
00000001 1.1.2006 NA 100 %
00000002 1.1.2006 100 % NA
00000002 1.1.2006 NA 95 %
...

i.e. I get duplicate rows.

If i create two commands to fetch the rows like:

command_1:
SELECT Issue.Issue_ID, Issue.Create_Date, Issue.Priority, Processing.Goal_Type, Processing.Goal_Time, Processing.Measured_Time
FROM Issue, Processing
WHERE Issue.Issue_ID = Processing.Source_ID AND
Processing.Goal_Type = 'Resolved'

command_2:
SELECT Issue.Issue_ID, Issue.Create_Date, Issue.Priority, Processing.Goal_Type, Processing.Goal_Time, Processing.Measured_Time
FROM Issue, Processing
WHERE Issue.Issue_ID = Processing.Source_ID AND
Processing.Goal_Type = 'Workaround'

and link them by Issue_ID, I get only the rows where there are entries for both "Workaround" and "Resolved" for the Issue ID and no results when there is an entry only for "Resolved".

So the QUESTION: Can i somehow insert the percentage value for "Workaround" if there is an entry in the database and if not, the string "NA" (or some default value)?

There is an option in the formula workshop for "Default Values For Nulls", but there arent't even NULLs in the database, simply no entry at all.

One option would be to modify the functionality of the underlying system to insert at least NULL values for "Workaround" when ever a value for "Resolved" is created and then use the "Default for Nulls" option. Any other ideas?

Thanks for your time!

-Ville

 

[synapsevampire]

If you group by the issue, then you can create seperate formulas, as in:

group header formula:

whileprintingrecords;
numbervar workaround:=0;
numbervar resolved:=0;

Details formula:
whileprintingrecords;
numbervar workaround;
numbervar resolved;
if (Goal_Type = "Resolved")
then
if (Measured_Time <= Goal_Time)
then
resolved:= 100
else
resolved:= (Goal_Time % Measured_Time)
else
if (Goal_Type = "Workaround")
then
if (Measured_Time <= Goal_Time)
then
workaround:= 100
else
workaround:= (Goal_Time % Measured_Time)

Group footer formulas (this is there you display the results, suppress the group header and details):

Workaround:
whileprintingrecords;
numbervar workaround;
If workaround <> 0 then
totext(workaround,2)
else
"N/A"

Resolved
whileprintingrecords;
numbervar resolved;
If resolved <> 0 then
totext(resolved,2)
else
"N/A"

Should get you close.

-k

 

[bielet]

Hey yea, it's working! Thanks a million synapsevampire!

(Something i wouldn't have figured out my self, at least not very quickly..)

-Ville

 

[bielet]

For the original requirement the report is working fine. Now I have an additional requirement for this report: I would like to present the results also graphically i.e. show the average for each Goal_Type per quarter. So I would group by Create_Date and Issue_ID.

However, because the report is using whileprintingrecords formulas, I cannot summarize on the group footer values. Is there any way to accomplish this? Can I for example pass the values from group footer formulas to a sub report and display the graph there? How should I do this? Or is there some other way?

Best regards,
Ville