I am new to Crystal Reporting, I am using Crystal Reports XI,

          I got a task to prepare a report on Change Notes,to show the history of Change Note status for every month.

          Generally, one Change Note will take 1 to 2 months to close after passing through different status & every month we will have around 10 to 15 Change Notes(average).

for example : in the month March we have 6 Change Notes,out of which 3 Change Notes are closed & 2 in progress & 1 is cancelled, Now i have to prepare a Bar Chart  to show the Change Notes status Since March till the present date with only three status(Open, Close,In progress) & "In progress" status Change Notes of March should be carry forwarded to "In Progress status Change Notes  of April  & so on untill the Change Note is "Closed".

 

I tried my level best in explaining my problem, hope you understand my explanation.

 

Could any one give me a solution on how to prepare a Bar chart based on above explained information.

 

Thank you

newcommer

CN-0001                    19/03/2008  13:30:29   

      .                                            .

      .                                            .

The above information is from the table called "changenote "

 

submitted (Opened)

analysed}  

approved}    (in progress) 

developed} 

integrated} 

deployed(closed)

 

the above information is from the table "statedef"

 

i am linking two tables to get the required information

 

I still don't have all of the information I need to be able to recommend a solution.  Where are you linking StateDef to in your ChangeNote table?  Do you have any sort of history table for ChangeNote so that you can determine when the note moved from one state to another?  Or are there specific date fields for each state in the ChangeNote table?

 

-Dell

 

Assuming you're looking for the status at the end of the month, here's what I would do (this is going to be complicated and may not be complete, so you're going to have to play with it and tweak it.):

 

1.  Create a formula called InProgressDt to get the most recent "In Progress" date.  It will look something like this:

If isNull({changenote.dateintegrated}) then
  if isNull({changenote.datedeveloped}) then
    {changenote.dateanalysed}
  else
    {changenote.datedeveloped}
else
  {changenote.dateanalysed}

 

2.  Create a formula called OpenMonth to get the month of the open date:

month({changenote.datesubmit})

For CloseMonth do the following:

if isNull({changenote.datedeployed}) then 0
else month({changenote.datedeployed})

Use this same technique for InProgressMonth, using the month of the {@InProgressDt} formula. 

 

Note:  With just the month, you'll only be able to run this for the current year.  If you need to run it for multiple years, you'll want to do something like this:

ToText(year({changenote.datesubmit}, 0) +
right('0' + ToText(month({changenote.datesubmit}, 0), 2) This code gives you the 4-digit year appended with a two digit month that has a leading zero if it's a single-digit month number.  You need to do it this way in order to get it to sort correctly.

 

4.  Create a command that gets your list of months.  I don't know what type of database you're using but in Oracle it would look something like this:

For just the month:

Select to_Number(to_Char(datesubmit, 'MM')) as GroupMonth
from ChangeNote

For the year and month:

Select to_Char(datesubmit, 'yyyyMM')) as GroupMonth
from ChangeNote

Use the syntax that is correct for your database.  DO NOT link this command to any of your tables!  Crystal will complain about it, but if I understand your needs correctly, you want to evaluate the records multiple times - once for each month between the datesubmit and the datedeployed.

 

5.  Create a group on the GroupMonth field.

 

6.  Create a formula called IsOpened:

if {@OpenMonth} <= {command1.GroupMonth} and {@InProcessMonth} = 0 then 1 else 0

Create another formula called IsInProcess:

if {@ClosedMonth} = 0 
  and {@InProcessMonth} <= {command1.GroupMonth} 
then 1 else 0

And one called IsClosed:

if {@ClosedMonth} = {command1.GroupMonth} then 1 else 0

 

7.  Sum each of these formulas at the GroupMonth level to get the numbers that you need for your bar chart.

 

-Dell