I am trying to create a crystal report with the following characteristics:

Column 1: Original Budget (RevId = “0001”) – Formula: if {PJREVCAT.RevId} = "0001" then {PJREVCAT.Amount} else 0

Column 2: Cumulative Budget Adjustments (Column 3 – Column 1)

Column 3: Revised Budget – Field: Total_budget_amount

 

I would like it to look as follows:

Task          Original Budget          Cum. Budget Adj.                    Revised Budget

00000        10000                        5000                                        15000

11111        500                            0                                             500

22222        0                               1000                                        1000

 

If I don’t use the selection expert I have a problem for the rows (tasks) with values in more than 1 RevId.

The table looks as follows:

Task          Original Budget          Cum. Budget Adj.                    Revised Budget

00000        0                               15000                                      15000

11111        500                            0                                             500

22222        0                               1000                                        1000

 

Then the amount shown for task “00000” is 0 because it does not know which PJREVCAT.Amount it should take.

 

If I use the selection expert and tell it to only use data for PJREVCAT.RevId = “0001” it displays the correct amounts for all the rows BUT it does not show the rows that don’t have a value in RevId “0001” but in a later RevId.

The table looks as follows:

Task          Original Budget          Cum. Budget Adj.                    Revised Budget

00000        10000                        5000                                        15000

11111        500                            0                                             500

 

Any suggestions are appreciated.

 

Thanks for your help.