Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Add Parameter Crystal 10 Post Reply Post New Topic
Page  of 2 Next >>
Author Message
TanyaCVV
Newbie
Newbie
Avatar

Joined: 06 Sep 2016
Online Status: Offline
Posts: 17
Quote TanyaCVV Replybullet Topic: Add Parameter Crystal 10
    Posted: 20 Sep 2016 at 5:47am
I have added two parameters for Start Date and End Date, I want to implement it into the following:

{SPECIAL_PRICING.BVSPECPRICESOURCEID} = "V" and
{SPECIAL_PRICING.BVSPECPRICEWHSE} in {?BV99IN01FromWarehouse} to {?BV99IN02ToWarehouse} and
{SPECIAL_PRICING.BVSPECPRICECODE} in {?BV99VN01FromVendor} to {?BV99VN02ToVendor} and
(if {?BV99IN25InStockUOM}
    then {SPECIAL_PRICING.BVSPECPRICEUOM} = {INVENTORY.BVSTKUOM}
else {SPECIAL_PRICING.BVSPECPRICEUOM} >= "") and
(if not({?BV99IN23IncludeHeldItems})
    then {INVENTORY.HOLD} = 0
else
    {INVENTORY.HOLD} < 2)

To do so I did the following:
{SPECIAL_PRICING.BVSPECPRICESOURCEID} = "V" and
{SPECIAL_PRICING.BVSPECPRICEWHSE} in {?BV99IN01FromWarehouse} to {?BV99IN02ToWarehouse} and
{SPECIAL_PRICING.BVSPECPRICECODE} in {?BV99VN01FromVendor} to {?BV99VN02ToVendor} and
{SPECIAL_PRICING.BVSPECPRICECODE} in {?BV99DT09StartDate} to {?BV99DT10EndDate} and
(if {?BV99IN25InStockUOM}
    then {SPECIAL_PRICING.BVSPECPRICEUOM} = {INVENTORY.BVSTKUOM}
else {SPECIAL_PRICING.BVSPECPRICEUOM} >= "") and
(if not({?BV99IN23IncludeHeldItems})
    then {INVENTORY.HOLD} = 0
else
    {INVENTORY.HOLD} < 2)

The result : It will not post my snapshots but it says a string is required here
Both parameters were set up as in other reports, New Added with value type of Date and then created with the default values set.
I am unsure of how to get these parameters working, please help if you can. I should also note this is version 10 but a built-in version using Sage Business Vision.


Edited by TanyaCVV - 20 Sep 2016 at 5:49am
Tanya Vander Vecht
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 20 Sep 2016 at 8:14am
One thing I noticed. {SPECIAL_PRICING.BVSPECPRICECODE} is the same field as the the line above.  The 'in' might work, but 'between' or doing two statements >= {?BV99DT09StartDate} and a <= {?BV99DT10EndDate} (with the appropriate field to compare to) is normally how I handle a date range.
IP IP Logged
TanyaCVV
Newbie
Newbie
Avatar

Joined: 06 Sep 2016
Online Status: Offline
Posts: 17
Quote TanyaCVV Replybullet Posted: 22 Sep 2016 at 12:45am
Thanks, I still have not figured it out I am new to parameters. All I want to do is be able to make the report dependent on a specified date criteria instead of pulling everything year to date. The database categorizes everything into last year, this year, next year and then specifies periods within so it is not an easy process to create a monthly view.
Tanya Vander Vecht
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 22 Sep 2016 at 4:32am
I still think there is an error with your SQL statement.  You are using {SPECIAL_PRICING.BVSPECPRICECODE} as a date field, and I do not think it is.  Once that is fixed then it will work or there should be a simple solution.
IP IP Logged
TanyaCVV
Newbie
Newbie
Avatar

Joined: 06 Sep 2016
Online Status: Offline
Posts: 17
Quote TanyaCVV Replybullet Posted: 22 Sep 2016 at 5:55am
So where I am getting confused maybe is where I am even trying to add this in. When I right click on the field in my report for YTD Totals which is where I have been trying to add my parameters and click edit formula I get the following

{INVENTORY.TY_PER_SLS01} + {INVENTORY.TY_PER_SLS02} + {INVENTORY.TY_PER_SLS03} +
{INVENTORY.TY_PER_SLS04} + {INVENTORY.TY_PER_SLS05} + {INVENTORY.TY_PER_SLS06} +
{INVENTORY.TY_PER_SLS07} + {INVENTORY.TY_PER_SLS08} + {INVENTORY.TY_PER_SLS09} +
{INVENTORY.TY_PER_SLS10} + {INVENTORY.TY_PER_SLS11} + {INVENTORY.TY_PER_SLS12} +
{INVENTORY.TY_PER_SLS13}

This tells me that it is pulling from every period for this year. Should I be building the parameters in here to tell it that I want it to only pull the amounts per sales period by the parameter?
If that is the case how would I do that?

I am at a bit of a loss but assuming I need to specify for each period that is my {?BV99DT10EndDate} and {?BV99DT09StartDate} within a certain range then use {INVENTORY.TY_PER_SLS01} ?
Tanya Vander Vecht
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 22 Sep 2016 at 8:05am
So it appears that there is not truly a date field that you can filter on.  I am not sure how you can filter on those fields except through some sort of case statement (MS-SQL format).  Something like this.

case when {?Period} =1 then INVENTORY.TY_PER_SLS01 else
 case when {?Period} = 2 then INVENTORY.TY_PER_SLS02 else
   case when {?Period} =3 then INVENTORY.TY_PER_SLS03 ...etc.

Each  case statement has to have a 'end'.  I you wanted multiple periods, that would be more complicated (but probably doable).
IP IP Logged
TanyaCVV
Newbie
Newbie
Avatar

Joined: 06 Sep 2016
Online Status: Offline
Posts: 17
Quote TanyaCVV Replybullet Posted: 27 Sep 2016 at 1:02am
Hello,

No there is no date field, I was attempting to build in date parameters but the database is not set up to easily handle that.

Another idea perhaps might be easier and maybe you can help me with it would be to create a new column for Avg Monthly Sales.

Would I be able to type in all of the TY periods or even include the LY (last year periods) and build a formula that says for all of these periods I want to know the average monthly sales of the item in this row.

Would I be able to do that generically without having to specify each part / as well? Sorry, I am new to crystal aside from more basic formulas and general design and look of reports.
Tanya Vander Vecht
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 27 Sep 2016 at 4:00am
I do not really understand your data structure.  It appears that you have a different field for each period (almost sounds like a data warehouse set up). So any parameter will have to take that into account. I suppose you could set up a formula that looks at a parameter field and will include any values that are necessary (A parameter with a range might be easier than descrete values).
IP IP Logged
TanyaCVV
Newbie
Newbie
Avatar

Joined: 06 Sep 2016
Online Status: Offline
Posts: 17
Quote TanyaCVV Replybullet Posted: 27 Sep 2016 at 8:09am
Is there some more information I can provide that may help? Because I do not understand either that is why I posted this up :) 
Tanya Vander Vecht
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 27 Sep 2016 at 11:24am
Maybe if you can explain more what you are trying to achieve (remembering that you do not have a date field to filter on).
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.016 seconds.