Difference between 2 columns 1

[maddyyikes]

HI,
I would like a report to be generated in the following format:

Problem Problem Problems Problems Change
Description in July in June


ID/Pwd Logging prob. 56 45 11

Novell
Security Pwd. Change 23 32 9

I need to extract the no. of problems for the months of June and July from the database and place them in the appropriate columns as shown above. I also need to calculate the change (difference between no. of problems reported in June and July) and place it in the change field. Furthermore, I need to determine the problems reported over any two successive months and calculate their difference. Please suggest if a generic template can be generated for the same and how it needs to be done. Thanks in advance.

 

[synapsevampire]

A generic approach is to Group by the problem field, and then use Running Totals (Insert->Field Object->Right click Running Totals and select New) to count or sum the problems for each month.

Then in the Evaluate->Use a formula use something like:
month({table.date}) = 7 for July, 6 for June.

If you want the report to always use the current month and the previous month so that you don't have to change it, use something like:

month({table.date}) = month(currentdate)

and for the previous month, use:

month({table.date}) = month(dateadd("m",-1,currentdate))

You might also use a parameter.

If you want specific answers, pleasse post some basic technical information, such as:

Crystal version
Dtaabase/connectivity used
Example data
Expected output

-k

 

[maddyyikes]

Thanx a lot..That was really of gr8 help to me! I am currently using CR 8.5 We are using a proprietary database and driver used for the same is ODBC...However how do I display the difference of values in 2 columns, (in another column), in the same report itself? The two columns whose difference I want to determine are formula based columns, for instance:

@Month: Displays the no. of probs. encountered in the current month.

@PreviousMonth: Displays the no. of probs. encountered in the preceeding month.

I want to have another column called "Change" which accounts for the difference between the values present in @Month and @PreviousMonth fields. For example:

For example:

@Month @PreviousMonth Change

1300 1250 50
2000 2250 250
1500 1000 500

How is it thus possible to display this difference in the "Change" column?
Note that in the third case the value in @Month is greater than that in @PreviousMonth. For such cases I want the difference (500) to be displayed with a special background color in order to indicate an increase in the no. of problems over subsequent months. Please let me know as to how this can be done....Thanx in advance!

 

[synapsevampire]

You show @month, which means that you used a formula, not a running total, running totals are prefixed with #.

Create a formula (Insert->Field Object->Right click formulas and select new) which contains something like:

{#Month}-{#Previousmonth}

To change colors, right click the field and select Format Field->Border->X 2 next to background and use a formula such as:

if {#Month}>{#Previousmonth} then
CRYELLOW

You're covering a lot of topics here, you might want to try different threads, and these are pretty generic Crystal questions, not specific to the Crystal Analysis product, so you might get better results in a Crystal Reports forum (more traffic).

-k