Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Boolean Array is required here Post Reply Post New Topic
Page  of 2 Next >>
Author Message
abernut
Newbie
Newbie
Avatar

Joined: 25 Aug 2010
Location: United States
Online Status: Offline
Posts: 25
Quote abernut Replybullet 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 IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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 IP Logged
abernut
Newbie
Newbie
Avatar

Joined: 25 Aug 2010
Location: United States
Online Status: Offline
Posts: 25
Quote abernut Replybullet 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 IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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 IP Logged
abernut
Newbie
Newbie
Avatar

Joined: 25 Aug 2010
Location: United States
Online Status: Offline
Posts: 25
Quote abernut Replybullet 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 IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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 IP Logged
abernut
Newbie
Newbie
Avatar

Joined: 25 Aug 2010
Location: United States
Online Status: Offline
Posts: 25
Quote abernut Replybullet 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 IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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 IP Logged
abernut
Newbie
Newbie
Avatar

Joined: 25 Aug 2010
Location: United States
Online Status: Offline
Posts: 25
Quote abernut Replybullet Posted: 28 Jul 2011 at 5:54am
For this report...
Only the ones in my list
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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 IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.