help with dates

[abdess]

i want to display in a crosstable informations depending on a date .
this date is included between a "startDate" and an "endDate".
for a startDate = 27/09/2006 to an endDate = 30/09/2006 i must have a result similar to :

27/09/2006 : results of 27/09/2006
28/09/2006 : results of 28/09/2006
29/09/2006 : results of 29/09/2006
30/09/2006 : results of 30/09/2006

thanks

 

[DataDivaJill]

First it isn't = signs you want.
You want to use2 parm fields one for asking your start date and another to ask your end date.
then
datefield in database >= ?startdate and datefield in database <= ?enddate
this would be in your record selection filter

then I gather you want to sort by the datefield.

 

[abdess]

its not so simple ,
i don't have all the dates in my database so i cant have the result that i want .
i want to display a date interval between two dates in a cross table and the summarized field will be calculated for these dates . if results dosen't exist , zero will be displayed.

i allredy done this but by using an sql command :

SELECT TRUNC( {?dateDebut}) + (ROWNUM-1) as jour
FROM DUAL
CONNECT BY TRUNC( {?dateDebut}) + (ROWNUM-1) <= TRUNC( {?dateFin})

i wonder if i can do something like this command by simply using formulas

thanks

 

[lbass]

You either have to create a periods table (see the FAQ on this) and then left join your data table to this periods table, or you need to use either conditional formulas or running totals that use evaluation criteria that limit records to the particular date.

-LB

 

[abdess]

First i would thank lbass and DataDivaJill for they fast answer .
Unfortunatly it isn't what i want, the purpos of my post in that forum is to find a "not sql" solution for displaying a date interval , i like to know if it possible to do this simply by using formulas .

thanks

 

[lbass]

The only way I know of were the two suggestions I provided earlier. The best solution is to create a periods table. Did you read the FAQ on this?

Otherwise you will have to create a manual crosstab where you use formulas like:

//{@startdate+2}:
if {table.date} = {?startdate}+2 then {table.amt}//or 1 for counting

Then you would insert a summary on each formula at the group or report level. If you have row inflation, you would need to use a running total, where you used and evaluation formula of:

{table.date} = {?startdate} + 2

-LB

 

[synapsevampire]

I gather by NOT SQL you mean not a database oriented solution, such as adding a table or using a Command object. I can understand not eing able to add a Period table (although that's what the big kids do in data warehousing), but not using a Command Object seems odd.

What's eluding your way of thinking is that Crystal will NOT fabricate data, not a single row of it, as with your clever SQL statement, where additional rows ARE created.

So the solution might be to use a Command Object (your own SQL statement as the data source for the report) that returns all of your columns and then add in a UNION that fabricates data based on the SELECT statement you supplied to return all dates against a MINUS (or NOT IN) of the dates in the SQL statement for the rows that return your recordset.

You're using Oracle as the database, however you did not post your Crystal software version, something that should be included with any post in any forum, so it may not work, as you need CR 9 or above to do this.

The Add Command will be listed under the Oracle connection.

Should work the same way, I'm not sure why you're opposed to using a custom SQL statement to do this, that's a huge advantage to using CR 9 or above with complex requirements.

btw, LB meant that you would use the Period table as the main table and LO join to your data,not the reverse.

-k

 

[abdess]

hi,
i use "crystal reports XI", my huge problem is when i want to preview my report in a jsp page under " bea weblogic worshop ", if the report contain a commande with parameters , a java.lang.NullPointerException is thrown .
the reports works perfectly in the crystal reports viewer but when i call it with my jsp it's dosen't display.
it's for this reason that i dont want to use a command and specify {?startDate} and {?endDate} as parameters for this command .
i wonder if my problem has a solution , i 'm loosing hope .

thanks

 

[synapsevampire]

A period table allows for this elegantly, otherwise you might use a manual cross-tab and fabricate data within the report by using a loop, such as:

whileprintingrecords;
Stringvar output;
numbervar counter;
if not(onfirstrecord)
and
previous({table.date})+1 <> {table.date} then
for counter: = 1 to (({table.date}-1) - (previous({table.date})+1)) do(
output:=output & totext({table.date}) & ": 0 " & chr(13)
);
Output

Right click the details and select insert section below and place the formula in the lower details section.

Then right click the new section and select format section->X2 next to suppres and place:

onfirstrecord
or
previous({table.date})+1 = {table.date}

-k

 

[synapsevampire]

Also format the field to can grow...

and change the last statement to:

left(output,len(output)-1)

-k