Is Crystal formula possible? 1

[wld1957]

CRXI – Technical user.
Not sure if this can be done by a Crystal Formula or if it has to be done by sql statement.
I have three (3) tables – cpmain \ sys_recp \ sys_trac (tables are linked)
I need a total by month and based on the same date range for two of the tables:
sys_recp \ sys_trac
Summary report by Month each year – 09-01-xx to 08-31-xx

I currently can do this only for one of the tables or the other on the main report. I have not been able to get them on the same group within the report, that’s because the group has to be based on one or the other {sys_recp.recptdate} or {sys_trac.tracktime}. If I group by {sys_recp.totalpaid} then the fees comes up correctly. If I group by {sys_trac.trackamt} then the deed fees come up right. I tried the below formulas to sum up the fields but it does not work. It draws all of the dates.

Currently I have the total for {sys_recp.totalpaid} on the main report. I have a subreport that passes the figure for {sys_trac.trackamt} at the bottom in the report footer. I then add the two figures to reach a final total (report footer b). I was trying to change the report to be more like the example below.

Formula’s:
Does not work (brings up all dates)
{sys_recp.recptdate} = {?Date Range};
If {sys_recp.transtype} = "SFEE" then {sys_recp.totalpaid}

Does not work (brings up all dates)
{sys_trac.tracktime} = {?Date Range - Deed Fees};
If {sys_trac.actioncode} = "DF" then {sys_trac.trackamt}


What I was trying to accomplish: Example.
//{sys_trac.trackamt} = Deed Fees
//{sys_recp.totalpaid} = Fees
Group by Month Deed Fees Fees Total
September $2,856.00 $33,490.12 $36,246.12
October $5,661.00 $34,601.09 $40,262.09
November etc…
Grand total
Any ideas on how to do this would be greatly appreciated as I’m out of ideas. I also tried a crosstab but that did not work either. Thanks in advance.

Bill

 

[lbass]

I think you would be better off using a command as your datasource, where you would use your main table and one other on each side of a union all statement. This way you could get the dates and the amounts merged into one field. It would look like this:

select 'Recpt' as type, sys_recp.recptdate as date, sys_recp.totalpaid as amt
from sys_recp
where sys_recp.transtype = 'SFEE'
union all
select 'Trac' as type, sys_trac.tracktime,sys_trac.trackamt
from sys_trac
where sys_trac.actioncode = 'DF'

You could then insert a crosstab that uses {command.date} as the row (group options: on change of month), {command.type} as the column, and {command.amt} as the summary field.

-LB

 

[MCuthill]

Bill,

Could you provide some sample data as to what is in each table / how they are linked?

In your formula's above, the first line of each is not part of the if statement and perhaps why not working.

Try:
{@NewFormula1}

IF {sys_recp.recptdate} in {?Date Range} THEN
(
   IF {sys_recp.transtype} = "SFEE" THEN {sys_recp.totalpaid} ELSE 0
)ELSE 0

this could also be simplified to:

IF ({sys_recp.recptdate} in {?Date Range} AND {sys_recp.transtype} = "SFEE" THEN {sys_recp.totalpaid} ELSE 0

I am not sure this will resolve your overall issue though.

If you can populate a formula field with the applicable date, and another for whether "Deed Fees" or "Fees" -- you could likely use the crosstab to do all the math. These formulas would be used for the Row (Date) and Column (Fee Category) headings of the crosstab.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

Bill:

Please ignore my post, LBass has it covered.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[wld1957]

Thanks so much. It took me a few minutes to get this but once I did it worked perfectly. Thank you very much.

Bill