Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Formula Error Post Reply Post New Topic
Author Message
Mike_B1
Newbie
Newbie
Avatar

Joined: 31 Oct 2012
Location: United States
Online Status: Offline
Posts: 2
Quote Mike_B1 Replybullet Topic: Formula Error
    Posted: 31 Oct 2012 at 4:46am
Hello
I am rather new at Crystal and need to know if my approach to a particular formula is correct.  I have created a report that has parameters: Beginning Period, Beginning Year, End Period, End Year, and Salesman ID.  The report basically shows sales and profit betweem the periods specified.  I want to add annual sales and profit using the invoice date based on the beginning year.  I have so far created a formula for annual cost but recieve a Boolean is required here error in the bold portion of my code.
 
if {?Beginning Period} = [1,2,3] then
{p21_view_invoice_hdr.invoice_date} = DateTime ({?Beginning Year}-1,1,1) to CurrentDate and
(if {p21_view_oe_line.po_cost} > 0 then
{p21_view_oe_line.po_cost} else
if {p21_view_oe_line.commission_cost} > 0 then
{p21_view_oe_line.commission_cost} else
{p21_view_oe_line.sales_cost}) else
{p21_view_invoice_hdr.invoice_date} = DateTime ({?Beginning Year},1,1) to CurrentDate and
(if {p21_view_oe_line.po_cost} > 0 then
{p21_view_oe_line.po_cost} else
if {p21_view_oe_line.commission_cost} > 0 then
{p21_view_oe_line.commission_cost} else
{p21_view_oe_line.sales_cost})
 
Like I said, I am new at this and learning on the fly with no support, so any input will be greatly appreciated.
 
Thank You in advance
 
IP IP Logged
comatt1
Senior Member
Senior Member
Avatar

Joined: 19 May 2011
Online Status: Offline
Posts: 337
Quote comatt1 Replybullet Posted: 31 Oct 2012 at 5:38am
The error is just telling you things in the select have to evaluate to true/false... the syntax is off at the beginning, but in the middle, that stuff won't evaluate to true/false.


if {?Beginning Period} in [1,2,3] then
{p21_view_invoice_hdr.invoice_date} >= DateTime ({?Beginning Year}-1,1,1) and {p21_view_invoice_hdr.invoice_date} <=CurrentDate
else
{p21_view_invoice_hdr.invoice_date} >= DateTime ({?Beginning Year},1,1) and {p21_view_invoice_hdr.invoice_date} <=CurrentDate

----

This above is all I would add to the select, the rest I would add to formulas.


I don't think you should have this in the select, add to a formula within the report

(if {p21_view_oe_line.po_cost} > 0 then
{p21_view_oe_line.po_cost} else
if {p21_view_oe_line.commission_cost} > 0 then
{p21_view_oe_line.commission_cost} else
{p21_view_oe_line.sales_cost})



-----------------
----------------
f {?Beginning Period} = [1,2,3] then
{p21_view_invoice_hdr.invoice_date} = DateTime ({?Beginning Year}-1,1,1) to CurrentDate and
(if {p21_view_oe_line.po_cost} > 0 then
{p21_view_oe_line.po_cost} else
if {p21_view_oe_line.commission_cost} > 0 then
{p21_view_oe_line.commission_cost} else
{p21_view_oe_line.sales_cost}) else
{p21_view_invoice_hdr.invoice_date} = DateTime ({?Beginning Year},1,1) to CurrentDate and
(if {p21_view_oe_line.po_cost} > 0 then
{p21_view_oe_line.po_cost} else
if {p21_view_oe_line.commission_cost} > 0 then
{p21_view_oe_line.commission_cost} else
{p21_view_oe_line.sales_cost})


Edited by comatt1 - 31 Oct 2012 at 5:48am
IP IP Logged
Mike_B1
Newbie
Newbie
Avatar

Joined: 31 Oct 2012
Location: United States
Online Status: Offline
Posts: 2
Quote Mike_B1 Replybullet Posted: 31 Oct 2012 at 7:45am
Thank you for your response.  I guess my next question would be if I were to put the code you specified into the select, would it contradict the date range I have already put in?  The end user specifies the start and end period and year for a given range.  Here is my select.
 
{p21_view_ship_to_salesrep.primary_salesrep} = "Y" and
not ({p21_view_oe_line.detail_type} in [1, 2]) and
{p21_view_oe_line.assembly} <> "" and
{p21_view_invoice_hdr.bill2_name} startswith "" and
not ({p21_view_invoice_line.product_group_id} in ["FILEQU", "FILSUP"]) and
{p21_view_oe_line.cancel_flag} = "N" and
{p21_view_oe_line.complete} = "Y" and
{p21_view_oe_line.delete_flag} = "N" and
{p21_view_invoice_hdr.salesrep_id} = {?Salesman ID} and
(if {?Beginning Year}= {?End Year}
then
{p21_view_invoice_hdr.year_for_period}= {?Beginning Year} and {p21_view_invoice_hdr.period} in {?Beginning Period} to {?End Period}
else
({p21_view_invoice_hdr.year_for_period} in {?Beginning Year} to ({?End Year}-1) and {p21_view_invoice_hdr.period} in {?Beginning Period} to 12) or
({p21_view_invoice_hdr.year_for_period} = {?End Year} and {p21_view_invoice_hdr.period} in 1 to {?End Period}))
 

Thank you
IP IP Logged
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.016 seconds.