Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formulas for different ranges

Status
Not open for further replies.

aj3221

Technical User
May 14, 2008
79
US
I don't know if this can be done.

I am looking at transactional data for a specific date range by customer.

I wanted to create something like this:

Cust# Name 01/01/12-01/31/12 02/01/12-02/29/12
0123 ABC Co $15,523.12 $20,123.78

Right now I'm running a January report, then a February report (and so on) in crystal, exporting to excel. Then I combine the excels into crystal matching on the Cust#.

Just looking for an easier way to get the sales $ for each month in one report.

I've done subreports too. It just takes forever to spin through the data.

I appreciate any help. Thanks in advance!
 
You should be able to create a formula for each month to accumulate the total money. You can use a conditional to determine if it falls in a particular month or not, then add it accordingly.

You can also create a formula for each month to use as the column header (01/01/2012-01/31/2012) and could base this off of a parameter or could calculate the first day of the current month, then you could calculate each subsequent month in the remaining formulas using the DateAdd formula (along with some conditional date to test for the new year).

If you take this route, you'll need to group on a field, then place these formulas in the group footer...I suggest using a group footer section for the column headings and a second group footer section to display the values.

Set the report to landscape and you should be in good shape.

If you need some more specifics, don't hesitate to ask.

Hope this helps,
beacon
 
If you have a date field, you can insert a crosstab in the report header or footer that uses Cust# and Company Name as rows and {table.date} as the column->click on group options->on change of month. Then go to the customize style tab and select the row fields and check "suppress subtotals". Add the sales amount as the summary field.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top