TABLE ALIASES 1

[reidtw]

Help!

Working in CR8 to a SQL database thru' ODBC.

I need to create a report of exchange rates where I have divided a foreign rate by the gbp rate applicable on the same day, e.g. (USD) 1.00000 / 0.52308 (GBP) = 1.91175.

All of the rates are in the same table so I have tried to split it up by using an alias - using a sub-report is not an option!

I have linked two instances of an exchange rates table to a currency table thus: -

Currencies.Id -> ExRate.CurrId
-> ExRate_1.CurrId

and grouped by

Grp1 - ExRate.CurrId
Grp2 - ExRate_1.CurrId
Grp3 - EXRate.Date
Grp4 - EXRate_1.Date

I have then fully suppressed all of the sections bar Grp#3 & Grp#4 footers which I have conditionally suppressed as: -

Grp#3 Header - ExRate.CurrId = "GBP"
Grp#4 Header - ExRate_1.CurrId <> "GBP"

I now think this is where I have got lost because having inserted the CurrId fields in the appropriate group footers, CR is returning millions of records - definitely not right!

The concept of using aliases is obviously alien to me so any ideas on how to resolve this would be appreciated.

Thanks
T

 

[Madawc]

If the groups you want to suppress are details you don't want, then it would be better to suppress them in the report selection, ExRate.CurrId <> "GBP" and ExRate_1.CurrId = "GBP" . This should be a lot faster, since the Server will exclude the unwanted records rather than sending them to your machine for processing and suppression.

I suspect you also need to link the dates on record and alias.


Madawc Williams (East Anglia, UK)

 

[reidtw]

I've dropped the currencies table and linked the primary and alias rates tables together using the currids and dates: -

ExRate.CurrId -> ExRate_1.CurrId
ExRate.Date -> ExRate_1.Date

The formulas are now in the record selection but no records are returned.

Is the formula combination wrong or should the two rates table still be linked to the currencies table?

T

 

[Madawc]

Probably they should be linked. Try commenting out the selections and see what you get.

Madawc Williams (East Anglia, UK)

 

[reidtw]

Hi Madawc,

I left the selections and linked the rates tables to the currencies and also linked the rates tables to each other on date.

Appears to be working great now.

Many thnaks for your input.

Regards
T