Group Footer 1

[hgg21]

Hi,

I have this:

Group#1 Header (Suppressed)
Group#2 Header
Details (Suppressed)
Group#2 Footer (Suppressed)
Group#1 Footer

There's a page break in details with this formula:
{field} <> Next({field})

When I insert Group#1, the Group#1 Footer for the first set of data will always be on a new page or be on the same page as the second set of data. However, the Group#1 Footer for the second set of data is on the same page as the data. It is not a case as in overflow for the 1st set of data.

How do I make the Group#1 Footer right after the data?

It is urgent, please help!!
THanks!!

 

[hgg21]

The Group#1 footer cant appear on the first page.
Why is that so even though there are a lot of spaces left??

 

[hgg21]

The Group1 footer cant appear just after the 1st set of info. Why is this so? It keeps jumping to a new page. Subsequent footers are fine.

Will be really glad if someone can help me with this.
Thanks!!! Been racking my brain for this. Urgh.
Jerry

 

[kenhamady]

In this expression:

{field} <> Next({field})

Is the field one of your group fields?
Which one?

If so, I would simply check 'new page after' on that group footer. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Ngolem]

Well it really depends on what field this refers to

{field} <> Next({field})

Frankly if it refers to a Group 2 field you could be flipping several pages before you see a Group 1 footer.

Why don't you give us a small sample of the datatables for both groups and a sample of what you would like the report to really look right

Then we would not be guessing. Jim Broadbent

 

[hgg21]

Hi,

thanks to you both for replying.

In {field} <> Next({field}), the field refers to Group 1 fields.

Group 1 Header (Suppressed)
Group 2 Header
Details (Suppressed)
Group 2 Footer (Suppressed)
Group 1 Footer

Group 1 field {FundGroup} has this data HEDG and MMLG
Group 2 fields is the ClientName

Any ClientName can belongs to HEDG or MMLG. So therefore in details it's like this:

ClientName FundGroup
AAAAA HEDG
BBBBB HEDG
BBBBB MMLG
CCCCC HEDG and so on.....

So I grouped the FundGroup field and put this formula {field} <> Next({field}) in.

and becomes like this:

ClientName FundGroup
AAAAA HEDG
BBBBB HEDG
CCCCC HEDG
CCCCC HEDG
pagebreak
ClientName FundGroup
BBBBB MMLG and so on.....

However, I do not want duplicates as in CCCCC HEDG so I group ClientName as Group 2 and suppressed the details, and display only the Group 2 headers and summary in Group1 footer.

I get this now:
for HEDG:
Group 1 Header (suppressed)
Group 2 Header (displayed)
Details (suppressed)
Group 2 footer (suppressed)
pagebreak (How do I remove this pagebreak??)
Group 1 footer (summary)
pagebreak
for MMLG:
Group 1 Header (suppressed)
Group 2 Header (displayed)
Details (suppressed)
Group 2 footer (suppressed)
Group 1 footer (summary)

Oh I have placed this sentence Not OnLastRecord in the Group 1 footer format section.

How do I do away with that pagebreak?

Thanks for your help once again!!!
Regards
Jerry

 

[kenhamady]

Take out your page break formulas and put NotOnLastRecord in the NewPageAfter property for Group Footer 1. This is simpler and should give you exactly what you want. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[hgg21]

hi Kenhamady,

you are great! Thanks for the help.

One more question though.

I have 4 tables. Client, Product, ParentProduct and Trade.
They are all linked up. I only need to display fields ClientName(String) and ProdCredit(Numbers) from Client and Trade respectively, but with specific conditions on Product and ParentProduct.

The report should look like this:

ClientName ProdCredit1 ProdCredit2 ProdCredit3
AAAAAA 12345 56778 54534
BBBBBB 23186 87263213 8923712

ProdCredit1 is a formula like this:
If {ParentProduct.ParentProductName} = &quot;Commission&quot; Then
{Trade.ProdCredit}
else
0

ProdCredit2 is a formula like this:
If {Product.Product} = &quot;APT&quot; Then
{Trade.ProdCredit}
else
0

ProdCredit3 is a formula like this:
If {Product.Product} = &quot;Convertibles&quot; Then
{Trade.ProdCredit}
else
0

I want to show the ProdCredits for the ClientName when ParentProduct and Product are certain values in the same report. Is what I have done correct??? I m not very sure.

Thanks for any advice.
Jerry

 

[kenhamady]

I think that if you put these 3 formulas on the detail band, subtotal them by customer, and hide the details, you will get what you want. Try it and see.

Can an APT/convertible also be a commission? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[hgg21]

Hi Ken,

>I think that if you put these 3 formulas on the detail >band, subtotal them by customer, and hide the details, you >will get what you want. Try it and see.

I m doing what u said.

Sorry what do u mean by

>Can an APT/convertible also be a commission?

Jerry

 

[kenhamady]

Is it possible that on a record with a {ParentProduct.ParentProductName} of &quot;Commission&quot; that the same record will also have a {Product.Product} = &quot;APT&quot;. In that case you might get the credit in both columns. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[hgg21]

Yes i m afraid that is possible.

I would want to show the credits for both.

Jerry

 

[kenhamady]

Then you should be fine. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[hgg21]

Thanks a lot Ken!

I will try it out and see whether it works.

I m still unable to know what's the outcome as the time taken to access the database is very long......(can be up to a few hours....)

I m using Sybase Database and CR version 8.

Get back to u when I tried it out.

Thanks
Jerry

 

[kenhamady]

That amount of time seems excessive.
How many records in the database?
What is your record selection formula?
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[hgg21]

Hi, actually I m a student on attachment trying to create this report for my company. I m not very sure how many records there are in the database. But I guess there are a lot.

My record selection formula is:

SELECT
Trade.ProdCredit, Trade.TradeDate, Trade.DelFlag,
Account.SystemId, Account.AccountType, Product.Product, ParentProduct.ParentProductName, Bucket.BucketName, ELPClientList.FundGroup
FROM
DbProd.dbo.Trade Trade,
DbProd.dbo.Account Account,
DbProd.dbo.SubProduct SubProduct,
DbProd.dbo.Symbol Symbol,
DbProd.dbo.Product Product,
DbProd.dbo.ParentProduct ParentProduct,
DbProd.dbo.Bucket Bucket,
DbProd.dbo.ELPClientList ELPClientList
WHERE
Trade.AccountNo = Account.AccountNo AND
Trade.DealTypeId = SubProduct.SubProductId AND
Account.IPSymbol = Symbol.IPSymbol AND
SubProduct.ProductId = Product.ProductId AND
Product.ParentProductId = ParentProduct.ParentProductId AND
Symbol.Bucket = Bucket.Bucket AND
Bucket.Bucket = ELPClientList.Bucket AND
(Account.SystemId <> 'GTE' AND Account.SystemId <> 'CROS' AND Account.SystemId <> 'AP' AND Account.SystemId <> 'ADJU') AND Account.AccountType = '1' AND Trade.DelFlag <> 'Y' AND Trade.TradeDate >= ? AND Trade.TradeDate < ?
ORDER BY
ELPClientList.FundGroup ASC,
Bucket.BucketName ASC

Initially I m still able to read the records(but very slow) when I didnt specifiy the TradeDate. When I try to specify the TradeDate, it just kept on accessing the database and cant read the records anymore. Is there any problem with my SQL query?

Please advise....thanks!!!
Jerry

 

[HaraldBuesching]

If fou are sure that it is the database, leave this forum,
ask your database expert or go to an sybase forum.
Database performance is really not easy .....
Harald.

 

[kenhamady]

hgg, that is your SQL Statement, not your selection formula. Your selection formula is under Report - Edit Selection Formula. CR converts it to a SQL WHERE clause.

Did you write this SQL Statement yourself, or did CR write it for you? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[hgg21]

Hi Ken,

CR wrote the SQL statement. I merely do all the record selection using select expert

This is what i have in the Report - Edit selection formula:

not ({Account.SystemId} in [&quot;ADJU&quot;, &quot;AP&quot;, &quot;CROS&quot;, &quot;GTE&quot;]) and
{Account.AccountType} = &quot;1&quot; and
{Trade.DelFlag} <> &quot;Y&quot; and
{Trade.TradeDate} in DateTime (2001, 01, 01,00, 00, 00) to DateTime (2002, 02, 27,00, 00, 00)

Jerry

 

[kenhamady]

No obvious problems. The date might not be making it to the database, but you don't have many options there for a date selection.

You may be simply pushing your database's limits. You could try running the SQL statement in the database itself, and seeing how it performs there (with dates filled in). If it is just as slow, then the report is doing all it can.

In some databases an ORDER BY forces the use of an inefficient index which kills performance. You might try a test of this same report without any sorting or grouping and see if that has an effect on performance. If so, create one-field formula fields and sort/group on the formulas instead of the fields. That forces the Sort/Group to be local and doesn't add an ORDER BY to the SQL. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com