| Author |
Message |
abernut
Newbie
Joined: 25 Aug 2010
Location: United States
Online Status: Offline
Posts: 25
|
   Topic: Boolean Array is required here
Posted: 28 Jul 2011 at 4:08am |
|
I was using this formula to calculate LastYearMTD
If {oehdrhst_sql.inv_dt} in LastYearMTD then {oehdrhst_sql.tot_sls_amt} else 0
It worked but the sales people want to see the customer even if they had never placed an order for that time frame. And with the above formula, it did not include them. Which I assume is because of Null values.
So I tried using
If not isnull({oehdrhst_sql.inv_dt}) in LastYearMTD then {oehdrhst_sql.tot_sls_amt} else 0
It highlights LastYearMTD and tells me A Boolean Array is Required Here.
Thank you,
Mike
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Jul 2011 at 4:32am |
your formula is returning a boolean
not isnull({oehdrhst_sql.inv_dt})
and then trying to see if that booean is in LYMTD which is not a booean value so it won't let the comaprison happen.
Regardless I do not think that wil help you as this is just a calculation not a select or suppression formula.
Is your select statement excluding the customers or are you suppressing customers some how?
Edited by DBlank - 28 Jul 2011 at 4:32am
|
|
IP Logged |
|
abernut
Newbie
Joined: 25 Aug 2010
Location: United States
Online Status: Offline
Posts: 25
|
Posted: 28 Jul 2011 at 4:50am |
|
Not sure if this will help but here is my sql statement I am using.
Sales is only interested in a handful of accounts so I just manually entered them.
SELECT "oehdrhst_sql"."cus_no", "oehdrhst_sql"."tot_sls_amt",
"oehdrhst_sql"."inv_dt", "oehdrhst_sql"."orig_ord_type"
FROM "001"."dbo"."oehdrhst_sql" "oehdrhst_sql"
WHERE ("oehdrhst_sql"."inv_dt">={ts '2010-01-01 00:00:00'}
AND "oehdrhst_sql"."inv_dt"<{ts '2011-12-31 00:00:01'})
AND ("oehdrhst_sql"."cus_no"='103974'
OR "oehdrhst_sql"."cus_no"='103976'
OR "oehdrhst_sql"."cus_no"='103977'
OR "oehdrhst_sql"."cus_no"='103978'
OR "oehdrhst_sql"."cus_no"='103979'
OR "oehdrhst_sql"."cus_no"='103980'
OR "oehdrhst_sql"."cus_no"='103981'
OR "oehdrhst_sql"."cus_no"='103982'
OR "oehdrhst_sql"."cus_no"='103984'
OR "oehdrhst_sql"."cus_no"='103985'
OR "oehdrhst_sql"."cus_no"='106386'
OR "oehdrhst_sql"."cus_no"='106403'
OR "oehdrhst_sql"."cus_no"='106496'
OR "oehdrhst_sql"."cus_no"='1280')
AND "oehdrhst_sql"."orig_ord_type"<>'C'
If you need anything else please let me know
Thank you.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Jul 2011 at 4:59am |
so this will only show you customers that had an order bewteen 1-1-2010 and 12-31-2011 and are in your list.
Your formula is trying to get at the amount for LYMTD.
Is the new request to show all customers regardless of any sales at all or is it still supposed to only show customers with sales in in the above date range and in your list?
Edited by DBlank - 28 Jul 2011 at 5:00am
|
|
IP Logged |
|
abernut
Newbie
Joined: 25 Aug 2010
Location: United States
Online Status: Offline
Posts: 25
|
Posted: 28 Jul 2011 at 5:27am |
|
Sorry,
("oehdrhst_sql"."inv_dt">={ts '2010-01-01 00:00:00'}
AND "oehdrhst_sql"."inv_dt"<{ts '2011-12-31 00:00:01'})
can be removed.
The new request needs to show all customers regardless of any sales for the formulas I have created.
LastYMTD "If {oehdrhst_sql.inv_dt} in LastYearMTD then {oehdrhst_sql.tot_sls_amt} else 0
LastYYTD "If {oehdrhst_sql.inv_dt}in LastYearYTD Then {oehdrhst_sql.tot_sls_amt}Else 0"
MTD "If {oehdrhst_sql.inv_dt} in MonthToDate then {oehdrhst_sql.tot_sls_amt} else 0"
YTD "If {oehdrhst_sql.inv_dt}in YearToDate then {oehdrhst_sql.tot_sls_amt} else 0"
Everything works correctly, meaning the report shows the listed customers LastYMTD, LastYYTD, MTD, and YTD sales information. The only problem is that it does not dislpay customers that did not have any sales for that time period. And our Sales Department would like to see All Customers even if it is $0
Thank you,
Mike
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Jul 2011 at 5:34am |
1. do you have aseperate table of customers or are they only stored in the oehdrhst_sql table?
2. can you write your own views or stored procs are do you need a crystal only solution ? or is the 'oehdrhst_sql' already one of these?
|
|
IP Logged |
|
abernut
Newbie
Joined: 25 Aug 2010
Location: United States
Online Status: Offline
Posts: 25
|
Posted: 28 Jul 2011 at 5:46am |
1. do you have aseperate table of customers or are they only stored in the oehdrhst_sql table? There is a table named cicmpy that contains all of the customers. It has a column "cmp_code" which relates to "oehdrhst_sql"."cus_no"
2. can you write your own views or stored procs are do you need a
crystal only solution ? Unfortunately this is all new to me so I'm not sure how to write my own procs. or is the 'oehdrhst_sql' already one of these? The 'oehdrhst_sql' is just the Order History Table with in the database.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Jul 2011 at 5:52am |
for clarity sake, do the users want to see all customers from the customer table or all of the customers from your list
"oehdrhst_sql"."cus_no" in ('103974' ,'103976' ,'103977' ,'103978' ,'103979' ,'103980' ,'103981' ,'103982' ,'103984' ,'103985' ,'106386', '106403' ,'106496' ,'1280')
|
|
IP Logged |
|
abernut
Newbie
Joined: 25 Aug 2010
Location: United States
Online Status: Offline
Posts: 25
|
Posted: 28 Jul 2011 at 5:54am |
|
For this report...
Only the ones in my list
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Jul 2011 at 5:58am |
there are a number of ways to do this. the more efficient ways are using a sql view or stored procedure.
if you don't use either of those you can either pull all the records and use suppression criteria to show specific data
or you can write a command to limit the data.
It seems like you have run time paramters though? Is that correct?
Edited by DBlank - 28 Jul 2011 at 5:59am
|
|
IP Logged |
|
|
|
