displaying dynamic quarters

[goodprg]

I have a report in which user inputs the start quarter,start year,end quarter and end year.

I want in the report to dynamically display all the quarters that fall between the selected parameters.

For ex if user selected 2 qtr of 2002 to 3rd qtr of 2004 then the report should display 2,3,4 of 2002 and 1,2,3,4 qtr of 2003 and 1,2,3rd qtr of 2004.

Also if user only selects the start and ending qtr for 1 year then report should display only for that year.

Does somebody have any ideas?

TIA.

 

[synapsevampire]

Assuming tthat you have dates in the database and not quarters (you should post specifics such as this), create 2 formulas for use in the reocrd selection formula:

start date formula:
numbervar StartMonth:=
select {?start quarter}
case 1 : 1
case 2 : 4
case 3 : 7
case 4 to 12 : 10;
cdate({?start year},Startmonth,1)

end date formula:
numbervar EndMonth:=
select {?end quarter}
case 1 : 3
case 2 : 6
case 3 : 9
case 4 to 12 : 10;
dateserial({?end year},Endmonth+1,1)-1

Then in the Report->Edit Selection Formula->Record use:

(
{Orders.Order Date} >= {@startdate}
and
{Orders.Order Date} <= {@end date}
)

This will pass the SQL to the database as well.

-k

 

[goodprg]

I do have quarters in the database. The user are selecting the start and end quarter and year. I want to dynamically create the quarters between the parameters and display it in report (whatever can fit on a page landscape mode).

I am using CR10.

Thanks,

 

[synapsevampire]

So you don't want to retrieve data, you simply want to display the quarters between the periods?

Please post technical information so I don't have to guess again:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[goodprg]

CR10, Oracle 10g(native connection).

Example data from the database-

Qtr year value
1 2002 Traffic
2 2002 Mis
3 2002 Speed
1 2002 Traffic

2 2003 Mis
3 2003 Speed

Expected Output -
1stQtr of 2002 2ndQtr'2002 3rdQtr'2002 1'2003
Trfc 2 1 0 0
Mis 0 1 1 1
SPd 1 0 1 1

The numbers displayed are count of values. I want to display as many as quarters as poosible on a paper (landscape mode)

Sorry for not posting the whole thing at first place.

Thanks,

 

[synapsevampire]

Create the following formula:

{table.year}+({table.month}*.1)

This will group the data accordingly, and then you can just format another field to use for display purposes.

To limit the data being returned by the database, use the Report->Selection formulas->Record and place something like:

(
{table.quarter} >= {?startquarter}
and
{table.year} >= {?startyear}
)
and
(
{table.quarter} <= {?endquarter}
and
{table.year} <= {?endyear}
)

Now create a cross-tab (Insert cross-tab) and place the date formula in for the column, and the Value in for the row, and a count of the value for the summary row.

-k

 

[synapsevampire]

This should read:

Create the following formula:

{table.year}+({table.month}*.01)

 

[lbass]

I think you add {table.year} as your first column and {table.quarter} as your second column in the crosstab expert. You can then highlight {table.quarter}->group options->customize group name->use a formula to customize name->x+2 and enter:

"Qtr " + totext({table.quarter},0,"")
//remove totext(,0,"") if already a string

You can then suppress column subtotals in the customize style tab, if you wish.

-LB

 

[goodprg]

For Ex - I want the report between 2nd Qtr of 2003 to 1st qtr of 2004.

So report should display 2,3,4 qtr of 2003 and 1st qtr of 2004.

if I say ({table.quarter} >= 2 and {table.year} >= 2003) and
({table.quarter} <= 1 and {table.year} <= 2004)

Then nothing is reutrned as I have contradiction in my record selection formula. I hope you can see what I want to say with my example.

How do I fix this now?