Group field based on Month

[Crystalguru]

I am trying to get counts for Internet sources by Month.

The report should look like this:
Jan 2003
xxx.com 3 request 4orders
yyy.com 5 request 3 orders
Dec 2002 1 request 0 orders

Here's my situation, the request dates all have valid dates, not null. But the order dates can be null. Plus, a person can request information (requestdate) but not order (orderdate) till the next month, therefore giving (Jan 2003 xxx.com 3R, 4O).

Is there any way to group on a date field NOT from the database? But keep it dynamic?
thanks

 

[SmpTraining]

What part are you having problems with? The output you show looks fairly straight-forward.

Can you also explain the tables involved, are all these fields in one table or is there an order and a request table for example?

In answer to your last question, to group on fields not in your database you first create a formula field, then group on that. Steve Phillips, Crystal Consultant

 

[Crystalguru]

I want to group on months. But not months from the database. So I would like to create a formula that is @Month= Currentdate - 12... To display all 12 months...I'm not sure how to do that without using a database field...

thanks

 

[chelseatech]

The problem is that your data needs to have one record to appear in two places at once. Is it possible that ONE record has a order date and a request date for different months?

There is an SQL solution using a UNION join.
Select 'Request' as RecType, DSN, ReqDate as TrDate
From Table
UNION ALL
Select 'Order' as RecType, DSN, OrderDate as TrDate
From Table

In CR8.5 and earlier, create the above as a View/Query or Stored Proc and use that as the source for your report. In CR9 you can use that as a SQL command in your report.

Then you can group by TRDate and DSN, and count up the different Rectypes. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[SmpTraining]

You can of course group on the date field in the database and use the select expert to only select data from the last 12 months using a selection formula such as :

{my_date_field} >= CurrentDate - 365

or

{my_date_field} >= date(year(currentDate-1), month(currentdate), day(currentdate))

If you may not have data for all the months, I think your only solution is to create a database table and populate it with a date for every month (1-Jan-2003, 1-Feb-2003, 1-Dec-2002 etc).

You could then use this as the main report (using selection criteria as mentioned above) and then include a subreport which gathers data from your actual database for each month in turn.

Variations on this might include expanding the new table to include a date for every day and linking this directly to your own database (this saves on the subreports). Steve Phillips, Crystal Consultant

 

[MrBillSC]

One option you might look into is reporting all the data in the report footer. Create 2 formulas for each of the 12 months (1 for request and 1 for order). For example:
formula RequestMonth1
If {RecordMonth} = 1 then 1 else 0

formula RequestMonth2
If {RecordMonth} = 2 then 1 else 0

formula OrderMonth1
If {RecordMonth} = 1 the 1 else 0

etc.

Divide the report footer into 12 sections. In each section sum the 2 formulas for that month. If there are none, the result will be zero, but the section for that month will still print.

Best of luck.

 

[synapsevampire]

My generic statement along these lines is to create a Period table in the database.

This solves many reporting problems, and is standard fare in a Data Warehouse.

Then you can easily select the dates from the Period table, and then join your tables based on the dates.

There are other solutions, such as using SQL, but this is the simplest solution, and provides long term advantages.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Crystalguru]

Thank you all for MANY different approaches to my problem. This is exactly why I continue to use tek-tips! Not sure which approach I am going to take, may just test ALL!

thanks again