I am running CR 8.5 from a (large) SQL Server db. My aim is to use
crystal enterprise so end users in our sales team can access reports on
demand for a given date range and client so I am trying to keep contact
with the database to a minimum. IE currently processign a report takes
about 10 minutes and involves manually running stored procs in sql
server to produce smaller, client specific temporary tables that CR can
easily cope with. The proposed new system won't rely on knowledge of
SQL server and will take about 10 secs to run a report.
My problem is - as part of a sector analysis for a client (say Oil &
Gas sector for BP) i need to normalise the values returned from a sum
of various fields in the summary as we can't provide the client with
'real' figures on their compatitors. So what i need to do is divide
the summary field by the maximum value for any entry in that summary.
ie
Stock Value
Bp 5
Royal Dutch 10
BG Group 2
Total 6
so for BP i need to 5/10 to give me 0.5.
In this case, the above are all grouped, and then divided by the number
of sectors the stock is associaated with (to avoid double counting).
these are then to be associated to the market cap of the stock, which
is a simple calc and irrelevant to this problem.
so in CR i have the formula applied to the each grouped stock:
Count ({log.LOGID},{l og.stock})/{sector.sector_ count}
this works fine, but i dont seem to be able to then divide this by the
maximum value for all stocks in that sector without returning an error
(summary / running total field could not be created)
ie (Count ({log.LOGID},{l og.stock})/{sector.sector_ count}) /
maximum(Count ({log.LOGID},{l og.stock})/{sector.sector_ count})
I can (and have in the existing process) do this in the details section
of CR via grouping the data in SQL server and linking in another
table/SP fo that data, but that involves having to run a seperate
stored proc on SQL Server for each report/sector which obviously
removes any benefit of automation, so i need to be able to run this
from a grouped summary, not the details section.
It all seems far too obvious so hopefully i am just making a small
error or not finding a simple function within CR!!
I know CR can't cope with charting 2 levels of formula within a
formula, but this only uses one so cant see how that could be the
problem.
apologies if this is long winded, thanks in advance for any help.
Matt
crystal enterprise so end users in our sales team can access reports on
demand for a given date range and client so I am trying to keep contact
with the database to a minimum. IE currently processign a report takes
about 10 minutes and involves manually running stored procs in sql
server to produce smaller, client specific temporary tables that CR can
easily cope with. The proposed new system won't rely on knowledge of
SQL server and will take about 10 secs to run a report.
My problem is - as part of a sector analysis for a client (say Oil &
Gas sector for BP) i need to normalise the values returned from a sum
of various fields in the summary as we can't provide the client with
'real' figures on their compatitors. So what i need to do is divide
the summary field by the maximum value for any entry in that summary.
ie
Stock Value
Bp 5
Royal Dutch 10
BG Group 2
Total 6
so for BP i need to 5/10 to give me 0.5.
In this case, the above are all grouped, and then divided by the number
of sectors the stock is associaated with (to avoid double counting).
these are then to be associated to the market cap of the stock, which
is a simple calc and irrelevant to this problem.
so in CR i have the formula applied to the each grouped stock:
Count ({log.LOGID},{l og.stock})/{sector.sector_ count}
this works fine, but i dont seem to be able to then divide this by the
maximum value for all stocks in that sector without returning an error
(summary / running total field could not be created)
ie (Count ({log.LOGID},{l og.stock})/{sector.sector_ count}) /
maximum(Count ({log.LOGID},{l og.stock})/{sector.sector_ count})
I can (and have in the existing process) do this in the details section
of CR via grouping the data in SQL server and linking in another
table/SP fo that data, but that involves having to run a seperate
stored proc on SQL Server for each report/sector which obviously
removes any benefit of automation, so i need to be able to run this
from a grouped summary, not the details section.
It all seems far too obvious so hopefully i am just making a small
error or not finding a simple function within CR!!
I know CR can't cope with charting 2 levels of formula within a
formula, but this only uses one so cant see how that could be the
problem.
apologies if this is long winded, thanks in advance for any help.
Matt