Dates / amounts formulas

[aspphp]

Hello, need help please. Thank you in advance.

In my Access table, I have Companyname, Inv_dt,
Amount. I want to create a report (may be cross tab report), showing line code as columns, and heading (jan, feb, mar, soon as clumns, amount for each in rows. This is similar to 0-30, 31-60, 61-90, 90-over format. Can some body help me please.

Clear Example.

......... Inv_Dt Inv_Dt Inv_Dt
Description Jan. Feb. Mar.

Companyname Amount Amount Amount
Companyname Amount Amount Amount
Companyname Amount Amount Amount
Companyname Amount Amount Amount
Companyname Amount Amount Amount
Companyname Amount Amount Amount
Companyname Amount Amount Amount
Companyname Amount Amount Amount
Companyname Amount Amount Amount
Companyname Amount Amount Amount

 

[briangriffin]

It sounds like you need a simple crosstab report. From the Insert menu, select Crosstab. Drag the crosstab object to the report header or footer.

Right click on the crosstab and select Crosstab Expert. Put the CompanyName field in the Rows section and Amount in the Summarized Fields section. Put Inv_Date in the Columns section, then click on Group Options. Change the dropdown to say the column will be printed "for each month".

Search CR help for more info on creating and formatting crosstabs.

 

[aspphp]

Hello briangriffin (Programmer),

Thank you. You are something.

Little extension to report,
I am seeing 'column totals' in first column,
and 'row totals' on top, is there any way I
can move 'column totals' to bottom, and 'row
totals' to at the end of report on all the to
right side (at the end).

Thank you again.

 

[GJParker]

Reopen the crosstab exper -> go to the customise style tab -> uncheck column totals on top and uncheck row totals on left.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[aspphp]

briangriffin, GJParker,
Thank you to both.

If I can extend one last request, please help.

In above report, I want to add one column 'budget $' after 'companyname', then at the end as last column,
I want to do formula 'budget $ - totals of rows'. (budget minus totals of all invoices), showing as what balance left.

Help / advise, if this can be done.

Thank you.

 

[lbass]

What version of CR are you using?

-LB

 

[aspphp]

Sorry for late reply.

Crystal 11.5 version.

 

[lbass]

You can do this is you are willing to have the budget, actual, and difference next to each other. Add the budget as your first summary field (use maximum if the field already contains the desired value). For the difference field, create a holder formula:

whilereadingrecords;
0

Add this as your third sumamry. In the customize style tab, choose horizontal summaries and show labels.

In preview mode, select the column label, inner cell, and column total for both the {@0} summary and the budget summary->right click->format field->suppress. In design mode, minimize the width of these two columns so they are barely visible. Then select the row total (and the column total) for the budget->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar budg := currentfieldvalue;
false

Then select row total and column total for the actual summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar actual := tonumber(currentfieldvalue);
false

Then select {@0} (row and column total) ->right click->format field->display string->x+2 and enter:

whileprintingrecords;
numbervar budg;
numbervar actual;
totext(budg-actual,2) //2 for two decimals

Edit the summary labels as desired.

-LB