Creating a new Crystal Report and using two Parameters to filter data, then.....

[ghevia99]

Crystal Report Version :  2010

I already have the SQL Select statement to get the data, now, in order to filter the data using two Dates as a Date range, I need to create the Starting Date and End Date, I started doing that.  Then, I tried to use those two parameters in the COMMAND file I created, but is not working, it means all the data is passing. thru.

Those two parameters,  I need to use as the End Date : the Current Date, I created as p_CurrentDate.

And for the Beginning Date, I need to use a calculated value as :  The first Parameter minus 30 days,  it means  :  f_CurrentDate - 30 Days.

These  should be taken by default, to be run by SAP BI and creating an excel file.

Then.

First Paremeter :  p_CurrentDate :  Taking the CurrentDate from the System

Second Parameter :  p_CurrentDate - 30 Days.Date,

But, the user, also, could run the Crystal report to get a different period of time, it means the CurrentDate would be different, because it becomes the end date, then, the Begin Date sould be equeally 30 days before.

So, the first parameter ( p_CurrentDate ), should be flexible to get the really Current Date or to use a different date to caluclate the second parameter, maybe using a formula.

So, for some reason, when I run the report, is not taking the two parameters to filter the data and show only the records in that Date range.

May I missing something in the parameters Settings?

I attached a file with the setup of that Parameter, the second one is very similar.

 

[SDE-K · Accepted solution]

Create a new formula and write something like that :

local datevar firstday;
local datevar lastday;
firstday:={?p_date1}; lastday:={?p_date2};

// Auto mode -> Show : last month
if firstday<date(1900,1,1) then 
  cstr(date(DateAdd ('m', -1, cdate(year({?p_date2}),month({?p_date2}),1))),'MMMM yy')

// Else -> Show 2 dates
else
  cstr(firstday)+' to '+cstr(lastday);

Then, put you formula on header.

[ghevia99 · Accepted solution]

Yes, it works. Thank you, very nuch for your help. I am ready.

[ghevia99 · Accepted solution]

Hi SDE-K, I was very happy with the solution you gave me, in fact was ready to prepare the change and pass it to Production, when the Requester contacted me to add a new requirement to the same report, now they want to have the same option to filter the plant #, adding a new parameter, to have the option by default "All" plants or a specific plant from a list created in the parameter, then I figured out that I least I needed to add some lines : local stringvar plantno;, then assign the received value in the parameter "p_plantno", the issue I have is how to set the If then else to cover two options "All" plants or just one selected by the user, let me know if that's possible. Thanks.

This what I did for the formula checking the dates.

I believe is possible to have the same formula and using it to include a third parameter, the plantno to use "All" or one specific plant from a list of possible plants : '02-07-12-14-18-19-22-26-27-67', and if you remember you gave me also the option to have a header with the Month & Year, or a Period of time, then, because I am adding a third parameter, I believe I need another formula to print then the Plant : All Plants  or the Specific plant.

Do you think that is feasible?

This is the Formula I have for the heading, that also you recommended to me, part of the text is in french.

Thanks in advance.

[SDE-K]

Hello,

Did you filter the query with your parameter?

You should have something like this :

Best.

[ghevia99]

Ok. So, I created the two Parameters, then I should create the formula to check the entered value, is that correct ?

[ghevia99]

And the field I should compare to is the Date field saved in the DB.

[ghevia99]

Ok, Now I was able to filter the data, using the two parameters, what I want now is to check if the Parameter entered as CurrentDate was empty to set it up using the Current date from the system and calculate the Bginning Date substracting 30 days, How can I do that ?

[ghevia99]

I believe I would need to use a formula, which it would be evaluated after the parameter is entered, and because it will be executed from SAP BI, under a scheduled task, it will be using the Current Date most of the times ( every month ).

[SDE-K]

I think you only need one parameter for the “reference date”. Since the other date is 30 days before, you need to take the reference date - 30.
I don't know if SAP BI can send an empty date.

To control the value of the date parameter, you can work with a formula in "field explorer"

or directly in the record selection filter.

If SAP feeds the "empty" date with a distant date, you can do:

if {?p_currentdate}<date(1900,1,1) then
  {command.dat}>=currentdate-30 and {command.dat}<=currentdate
else 
  {command.dat}>={?p_currentdate}-30 and {command.dat}<={?p_currentdate}

If you create a formula field, you will must call it to filter records :

[ghevia99]

Interetsting...

[ghevia99]

Then, having two parameters as Date Range, and a formula to check if the parameter were empty, how can I make the parameter to accept entering empty values, because having the parameter, is not allowing me to click Ok, and I got the error,

[ghevia99]

Sorry, I hit enter before I finished my comment, then, the error is telling me that I must enter the date.

[ghevia99]

And I confirmed, with the people who work as the admin for SAP BI, it is possible to set a parameter value from SAP BI, but the thing is how to allow leave an empty value for the parameters been created in the report?, Still they would need to run the report for an especial period of time ( Date Range )

[ghevia99]

Also, If I use that Formula, is going to filter the data ?, Should I add something in the COMMAND file to include that new formula?

[SDE-K]

You can’t have a empty date with CR.
If you want execute your reports without date, you must declare your parameters in string, and control and format the input with formula. 
It’s better and easier to use date parameter.

[ghevia99]

Ok, make sense I can't use an empty parameter as date, then. Can I have this : Having two parameters to be set by the user, Beginning date and the Ending date, that's the typical way, that works, but the Request I have is that should be set automatically to run every first of the month, then taking the data for the last month, from the day first until the last day of the month, could be Jan 1st, 2024 to Jan 30, 2024, or could be Feb 1st to Feb 28, 2025. I believe we could build the FIrst one, using the Current Date -1, since it will be executed the first day of every month, then second one, using the same date but changing the day to "1", I could have the correct time period. that way, if the user sets the two parameters, it works, then your idea setting a date like that (1,1,1900), will be used the way taking the Current date and calculating the both dates, then the last step L how to use both values to filter the data, that would be included in the Command file? or would be modifying the two parameters been declared ? Is that possible to do ?

[SDE-K]

OK, I'm not sur to understand your second option.

Option 1 : You want the records for the last month. -> Today = May 1st, 2025 to May 31, 2025.

Option 2 : You want the records for the user parameters.

If it's that, I will use 3 parameters : start date, end date and mode to use (=option #1 or #2).

For exemple :

Auto mode is the last month.

+ 2 dates parameters

Create formula field :

With

local datevar firstday;

local datevar lastday;

// Initiate with parameter

firstday:={?p_date1}; lastday:={?p_date2};

// If the automode, first date is the first days of current month – 1 month. Last days is the first days of current month – 1 day.


if {?p_automode} then (

  firstday:=date(DateAdd ('m', -1, cdate(year(currentdate),month(currentdate),1)));

  lastday :=date(DateAdd ('d', -1, cdate(year(currentdate),month(currentdate),1)))

);

// Then, I filter records :

{Command.dat}>=firstday and {Command.dat}<=lastday;

You put the formula in records select.

If you don’t want the p_automode parameter, you must check the dates parameters. Imagine date #1 is not use = initiate with old date. Date #2 is your reference date (= first date of month)

local datevar firstday;
local datevar lastday;
firstday:={?p_date1}; lastday:={?p_date2};

if firstday<date(1900,1,1) then (
  firstday:=date(DateAdd ('m', -1, cdate(year({?p_date2}),month({?p_date2}),1)));
  lastday :=date(DateAdd ('d', -1, cdate(year({?p_date2}),month({?p_date2}),1)))
);

{Command.dat}>=firstday and {Command.dat}<=lastday;

[ghevia99]

Interesting both cases, then, any of these cases, using the formula, I would need to add that formula using the tab Report/Selection Formulas/Record to make the work and filter the data. but what About the COMAND file, Do I need to specify the Where Conditions to filter the data, I believe is not needed, I just need the SELECT ( fields ) from Table. Is that correct ?

[ghevia99]

Then, If I select your second option, not using the automode parameter, I should set that parameter by default as "1/1/1900", right ? or could be set by SAP BI as well.

[SDE-K]

You can use the where in your request if you have a fix condition.

[ghevia99]

Ok. SDE-K, It works, the last thing I need is : Since the Time period could be using the automatic set for the date, and In order to have in the Main Header of the report the time period used to get it, can I get the Month and Year ?, let's say the Automatic mode running "today" Jun 1st,2025, then, the data shown in the report corresponds to May 2025, can I have that in one variable to be used on the Header ?, and that information would not be taken from the parameters, since with that code the parameter are not changed. Another option, could be instead of using "May 2025" , using two dates, since the user can also use the first parameter for the initial date, then I will have two dates, the Beginning and the End date, or maybe create two new temporary variables and use for the headers?