| Author |
Message |
MartinHalford
Newbie
Joined: 13 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 19
|
  
Topic: Formula for DB retrieval
Posted: 17 Aug 2009 at 5:42am |
I have a query that returns a single value for me, but this is no good to me as an SQL expression because it contains a parameter field value.
So I need to retrieve a numeric value from column TABLE.NUM in table TABLE where another column value TABLE.STRING equals some value (can be hard-coded) and TABLE.BATCH is the value of the BATCH parameter selected for the report.
Any clues?
The actual formula I am using is:
IF (({VW_BULK_FILL_LOG.SIGNATURE_REASON}="Print Barcode Info" )
and ({VW_BULK_FILL_LOG.BATCH_ID}={?BATCH}))
then {VW_BULK_FILL_LOG.BOTTLE_QTY}
But this always returns zero.
If I run the equivalent SQL statement (from TOAD on Oracle 11i) it returns the expected result, i.e:
Select BOTTLE_QTY
from VW_BULK_FILL_LOG
where SIGNATURE_REASON='Print Barcode Info'
and BATCH_ID='10467580010'
This returns 0.512. Edited by MartinHalford - 17 Aug 2009 at 7:23am
|
|
Thanks!
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Aug 2009 at 7:27am |
Sorry, a little confused on your issue here.
Are you trying to write a select statement in Crystals Select Expert using a parameter?...Like this...?
{VW_BULK_FILL_LOG.SIGNATURE_REASON}='Print Barcode Info'
and
{VW_BULK_FILL_LOG.BATCH_ID}={?BATCH}
|
| IP Logged |
|
MartinHalford
Newbie
Joined: 13 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 19
|
Posted: 17 Aug 2009 at 8:52am |
Yes - exactly that. I need to get {VW_BULK_FILL_LOG.BOTTLE_QTY}, and I can't use an SQL expression because using the ?BATCH parameter can't be included in the SQL statement, so I have to use a formula, but I can't get it to return anything but zero. If I browse the data in that formula field, I see zero plus the value that I need.
Thanks for any help you can give.
P.S I'm not using the Select Expert - I'm using the formula editor - because I need the value as a record that I can use in some other calculations. Edited by MartinHalford - 17 Aug 2009 at 8:55am
|
|
Thanks!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Aug 2009 at 9:11am |
Your formula ...
IF (({VW_BULK_FILL_LOG.SIGNATURE_REASON}="Print Barcode Info" )
and ({VW_BULK_FILL_LOG.BATCH_ID}={?BATCH}))
then {VW_BULK_FILL_LOG.BOTTLE_QTY}
is basically correct but it evaluates on a row by row basis so if you placed it in a header or a footer it will return the value from the first or last record in that set. I am guessing this is what you are doing.
If you placed it on the details you should see it Insert 0.00 whenever a row is not Print Barcode Info and {?BATCH} otherwise it will showe the bottle quantity for that row (hence your data browse showing non zero values).
You can use it in a formula in a header or footer perhaps but it depends on your data grouping, what value you want and why.
Rathe than all of that, what are you trying to make your report show as there may be an easier way.
|
| IP Logged |
|
MartinHalford
Newbie
Joined: 13 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 19
|
Posted: 17 Aug 2009 at 9:50am |
|
Well, I have a data set where the BOTTLE_QTY is cumulative, ALL EXCEPT the first row. So lets say we have 100 bottles of 2 L. Then the data would be something like 2, 2, 4, 6, 8, etc. So to be able to present the total, I would need to add the value for the first row (this is the only row where the SIGNATURE_REASON}="Print Barcode Info") to the maximum value found in that column (easy using the maximum function). It sounds as though, if I reverse my formula to filter by ?BATCH first, then by the signature reason, then it might work ...
|
|
Thanks!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Aug 2009 at 10:00am |
maximum({VW_BULK_FILL_LOG.BOTTLE_QTY}) + Minimum({VW_BULK_FILL_LOG.BOTTLE_QTY})
That assumes your first row is =< your second row.
If not let me know and there are other ways to do it. Edited by DBlank - 17 Aug 2009 at 10:02am
|
| IP Logged |
|
MartinHalford
Newbie
Joined: 13 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 19
|
Posted: 17 Aug 2009 at 10:30am |
|
Unfortunately the second bottle may be smaller than the first one, and the last bottle filled is often the smallest. The maths must accomodate (the sum of the first bottle plus the sum of all the rest).
|
|
Thanks!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Aug 2009 at 11:05am |
OK. If I understand your set up you can use 2 Running Totals (RTS)...or variables but that is not my forte.
Fisrt RT called "FirstValue" (or whatever you want)
Field to summarize={VW_BULK_FILL_LOG.BOTTLE_QTY}
Type of Summary=Maximum
Evaluate Use a Formula
{VW_BULK_FILL_LOG.BATCH_ID}={?BATCH}
Reset as Never
Next RT called "MaxValue" done the axact same way but change the formula to
{VW_BULK_FILL_LOG.BATCH_ID}<>{?BATCH}
Create a formula field for your total as
{#FirstValue} + {#MaxValue}
Must be placed on Report Footer to show value. Edited by DBlank - 17 Aug 2009 at 11:06am
|
| IP Logged |
|
MartinHalford
Newbie
Joined: 13 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 19
|
Posted: 17 Aug 2009 at 11:18am |
Thank you - I can see where you're going with that - I think I'll be good from here. Thanks again, your help is very much appreciated.
|
|
Thanks!
|
| IP Logged |
|
|
|
