totals by month 1

[mwmark]

I have a Crystal 10 report (sql db) that uses a running total to calculate the total sales by sku(range determined by user).
What I need is to break that down by month on each sku.
I was going to create a running total for each month, but was hoping for a better way

Sku: Jan Feb Mar Apr ...... Total
12345g 50 2 0 200 252
456999 0 0 80 5 85

 

[lbass]

Why are you using running totals? If you grouped on sku and inserted a sum, would the results be correct?

If so, you could just insert a crosstab using sku as the row and date as the column. While the date is highlighted->group options->print on change of month. Use the sum of sales as the summary field. Place the crosstab in the report footer.

-LB

 

[mwmark]

You are right, I changed to the sum by group and totals are correct.

I tried the cross tab and I get close but....
it is giving me a sub total by sku (which I dont need), the date is not the last date of the month(Even when set for monthly-last date in period)and if I add the sku description field, it creates another row and total.

Are there any good tutorials on Cross-Tab reports?
Apparently, I dont quite understand the process.

 

[lbass]

To remove the subtotal by sku, while in the crosstab expert->customize style->sku and check "suppress subtotal". If the description field is unique to the sku, it shouldn't add another separate row. Again, you can suppress the subtotal.

If the description still doesn't display correctly, create a formula:

{table.sku}+": "+{table.description}

Use this formula as your row field.

To show the last date of the month, again, while in the crosstab->date column->group options->customize group name->use a formula->x+2 and enter:

totext(dateserial(year({table.date}),month({table.date})+1,1)-1,"MM/dd/yyyy")

-LB

 

[mwmark]

The date formula works great as did the row formula.
Unfortunately, the totals by sku are incorrect.
I am using the summary that I placed in the group footer (which is the correct totals. The cross tab is placed in the report footer

 

[lbass]

What do you mean by

I am using the summary that I placed in the group footer

?

What are the groups in your main report? What did you enter for row, column, and summary field (and what kind of summary) in the crosstab expert?

-LB