Quarterly group by 3 different Dates in 1 parameter

[manlia]

The client need a report to show the calculation for each quarter based on the user defined time periods ("From" and "To" date parameters). The "From" and "To" date parameters are ranged among 3 different date fields. If the parameter is put from 01/01/2003 to 06/30/2003, the report should show all the calculations broken down by each Quarter. And, the Q1 and Q2 should be shown for each group.

Here is defined in the record selection formula:
{table.datefield1} in {FromDate?} to {ToDate} and
{table.datefield2} in {FromDate?} to {ToDate} and
{table.datefield3} in {FromDate?} to {ToDate} and

I tried to do the group by {FromDate} parameter. But, it seems that Crystal report doesn't allow it. When I click on the "Insert group" option, I don't see any parameters on the list. Then, I tried to put the parameter {FromDate} inside a formula and grouped off that formula, but I can't see it either on the list. Is this a limitation?

The format is kind like a cross tab:

Q1 2003 Q2 2003
================== ============
Locations | Totals Totals
------------------ ------------
Loc 1 | 15 16
Loc 2 | 13 19
Loc 3 | 40 35
Loc 4 | 12 20

If the parameter is put from 01/01/2003 to 06/30/2003, the report should show all the calculations broken down by each Quarter. And, the Q1 and Q2 should be shown for each group.

Is this something that can be done?

Any help is greatly appreciated.
manlia

 

[synapsevampire]

A parameter isn't data, hence you can't group on it.

Insert a date field (not sure why you're using 3 date fields, this will obviously confuse things, it's either a poorly designed database, or concept) as the group field, and select group will be printed for each quarter.

-k

 

[manlia]

The requirement is based on those 3 dates. I never done this before either. It seems an awkward report. To me it doesn't make sense either. But, any other workaround will be great.
Thanks

 

[synapsevampire]

If you need the data based on three different dates, then are you to consider it three different data sources? Presumably...

First put super glue all over your dba's desk, once they adhere, light it on fire and toss it out a very high window.

Here's a CR 8.5 way, since you don't bother to post Crystal version nor database, nor connectivity, or anything technical, I'll just supply theory.

Union the data together in a View

or

Here's a Crystal 8.5 way to do it:

Select the table and columns needed, but just one date

Select Database->Show SQL

In the existing SQL add to the select line before the FROM , 'Date1' to identify the first date.

Copy the SQL

Type the word Union after all of the exisiting SQL and paste in the SQL after it.

Add to this SQL on it's select line before the FROM , 'Date2' to identify the first date.

Type the word Union after all of the SQL and paste in the SQL again after it.

Add to this SQL on it's select line before the FROM , 'Date3' to identify the first date.

Now you have 3 distinct sets of data.

Perhaps that's what you want, hard to say, and I would correct it on the database, as would most who had it as an option, hence your request is something I wouldn't consider doing.

-k

 

[manlia]

Your assumption above is correct. Sorry for not explaining the details. I'm using CR 8.5 with Oracle OLE DB database. And, the 3 dates are 3 different fields from 3 different tables in the database.
Thanks for the workaround, but I probably won't do it since it's touching the SQL query which I'm not comfortable with. And, like you said, that you won't consider doing it either. I will just stay with the From and To parameters that I have defined in the record selection (above) and show only records that fall within the parameter inputs.
I'm getting some data when I input from 01/01/04 to 06/30/04. As far as the format, is it possible to do it like that above cross tab?
Thanks again.

 

[lyanch]

Whether you can create the output that you want depends on whethere you can answer the question I asked before (you have this thread running several places).

If you have dates in different quarters do you know which quarter you want to display it in since all three dates fall in one row of data.

As an example:

row table.datefield1 table.datefield2 table.datefield3 table.location table.amount

1 04/30/2004 03/20/2004 05/25/2004 105 250
2 04/30/2004 04/20/2004 04/25/2004 102 110
3 03/31/2004 03/20/2004 04/01/2004 101 25

The question is which quarter does each *row* fall in. Row 1 and 3 have dates in more than one quarter so which quarter does it belong to? If you can answer that question logically (you can write it in a yes no formula) then the answer is probably yes, you can create the layout you want.

That doesn't mean that the data is correct. If you could explain the tables and relationships between them this might be easier.

Lisa

 

[synapsevampire]

Good point, Lisa.

That was why I suggested the Union, thereby making each date a single entity.

I would guess that they don't really know what the data is, and so can't explain what they need.

Hadn't realized the thread was streaming elsewhere, I've offered up as much as I probably can anyway given the limited understanding, so I'll slide on out the back door...

-k