Technical Questions: Boolean Array is required here

Print Page | Close Window

Boolean Array is required here

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13903
Printed Date: 18 Apr 2024 at 11:03pm

Topic: Boolean Array is required here

Posted By: abernut
Subject: Boolean Array is required here
Date 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

Replies:

Posted By: DBlank
Date 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?

Posted By: abernut
Date 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.

Posted By: DBlank
Date 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?

Posted By: abernut
Date 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

Posted By: DBlank
Date 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?

Posted By: abernut
Date 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.

Posted By: DBlank
Date 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')

Posted By: abernut
Date Posted: 28 Jul 2011 at 5:54am

For this report...
Only the ones in my list

Posted By: DBlank
Date 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?

Posted By: abernut
Date Posted: 28 Jul 2011 at 7:48am

It seems like you have run time paramters though? Is that correct?
I don't thing so. When I think of parameters I think of something the user must enter before the report runs. The only thing I have is the formulas listed above.

Posted By: DBlank
Date Posted: 28 Jul 2011 at 8:18am

So I am goint to guess that in general you are always working with the current and past year of data.

I think I would create a command to limit the sales data to the last 2 years and then ledt join that to the customer table which you can filter on.

so your command will be something like this

SELECT cus_no, tot_sls_amt, inv_dt, orig_ord_type

FROM oehdrhst_sql

WHERE (inv_dt between DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) AND DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))))
AND oehdrhst_sql.orig_ord_type<>'C'

that should pull all your records between jan 1 of last year and dec 31 of this year

now add the customer table to this command on the customerid as a left outer join to select all rows from the customer table

then in your select statement in crystal filter the customer list to what you want using the customer table not the command (sales)

Posted By: abernut
Date Posted: 29 Jul 2011 at 1:05am

Thank you for your help on this.
I will try that code out today and post back the results

Mike

Print Page | Close Window