few questions about Expert Select, datetime and landscaping a report

[voirin]

Hi im new to Crystal reports v8. I have a few questions as I'm relativley new to this application. Sorry if my lingo is incorrect:

1. I have set up a report that uses 'Expert Select' on a particular column - to filter away any rows containing non-positive numbers in that particular column. The result is further grouped. So essentially I am doing a:

select * from matters
where matters.a > 0
group by matters.b

If i include all numbers, i.e. positive, negative and zero, then my query is:

select * from matters
group by matters.b

What i need to incorporate into the report is a percentage calculation for each grouping, namely the percentage of positive numbers to all numbers: count(matters.a > 0 in the group) / count (all matters.a in the group). How can i calculate a count of all positive, negative and zero numbers if i have applied an Expert Select to select only positive numbers?

2. How do you write an SQL "where date between '01/JAN/2005' and '31/JAN/2005'" in the Crystal Formula Editor when the date is a date-time field i.e. is it:

date in '01/JAN/2005 00:00:00am' and '31/JAN/2005 00:00:00am'?

date in '01/JAN/2005 00:00:01am' and '31/JAN/2005 11:59:59pm'?

I want all records with a date from the very first second of 01/JAN/2005 to the very last second of 31/JAN/2005

3. How do i produce a landscape report page setup? It's set on portrait.

Any help most appreciated!

Cheers, voirin

 

[Turkbear]

Hi,
Do not exclude those in the selection formula, instead set up some testing formulas and place them in the details section (suppress them if you do not want to see them):

@negnumber
If a < 0 the 1 else 0

@posnumber
If a > 0 then 1 else 0

@znumber
if a = 0 theh 1 else 0


Summarise these at the Group level and you can then perform calculations of the sums to get the %s etc..

Hope it helps...

 

[lbass]

For your second issue, if the field is of datetime datatype, you can use:

{table.date} in date(2005,01,01,00,00,00) to date(2005,01,31,23,59,59)

For landscape orientation, go to file->printer setup->orientation->landscape

-LB

 

[voirin]

Thanks so much for your help! I really appreciate it! I'll try it out when i get to work next week

cheers, voirin