manual cross tab group by months for newbee

[khekking]

SQL dbase
Crystal XI

Greetings,

Long-time lurker first time poster. I need to create a report that shows sales history for the last 12 months grouped by each month. The report will be run at the close of each month; thus, I do not want to re-create the report each month by coding in static dates. I hope there is a method that will allow the report to identify the last full month and then group the 11 preceeding months.

I hope this info is adequate.

Cheers,

Ken

 

[synapsevampire]

Depends on the format of the report.

To limit the rows returned to the report to the last full 12 months, use Report->Selection Formulas->Record and place:

(
{table.date} <= maximum(lastfullmonth)
and
{table.date} > dateadd("m",-12,maximum(lastfullmonth))
)

Then you can group by the date, and in the options select For Each Month.

Not knowing if the data is a columnar summary or details with all rows, etc., limits how we might respond, but this demonstrate the basics.

-k

 

[khekking]

I would like the data to appear in a colmnar summary grouped by month as follows:

Month1 Month2 Month3 Month4 ... Month12

with Month12 being the LastFullMonth and Month1 being one year old. I can write the code in a static form, e.g. (Month12 date in 7/1/06 to 7/31/06, Month11 date in 6/1/06 to 6/30/06) but the report needs to be run at the close of each month and there are multiple data sets for each month which would require a lot of maintenance. Thus, I want to write a formula for each cell of the manual cross tab that looks at the current date and adjusts accordingly. I anticipate that I will need to write a unique formula for each cell. Many thanks for any help.

Cheers,


Ken

 

[khekking]

I forgot to state that this will be a running report. That is, the report will always select the 12 most recent months. Thanks again.


Cheers,


Ken

 

[synapsevampire]

You my be able to use what I've suggested and then just insert a crosstab and make the date the column, and whatever field you wish to summarize as the summary field.

Your post shows that all you want to display are dates, which probably isn't correct, if you take the time the first time to describe your requirements , you'll sabve everyone time.

If you can't use a crosstab, then might create 12 formulas for the details, such as:

if {table.date} in dateadd("m"-11,minimum(lastfullmonth))
to dateadd("m"-11,maximum(lastfullmonth)) then
{table.value}

Adjust the 11 up for each month.

Then right click the details for each month formula and select insert summary for whatever level of summary you need.

Then you can suppress the details section.

-k

 

[khekking]

Formula works great. Thank you, thank you. Please accept my apologies for not being as descriptive as I now understand I should have been.

Cheers,


Ken