parameter field date range

[rmcguire80]

Is it possible if I have a parameter field for a 3 month date range: ex. 2/1/2012 thru 4/31/2012 to have 3 formula fields then which would display the actual names of the months in that parameter

So, Formula 1 would show February, Formula 2 March, Formula 3 April

 

[briangriffin]

If it will always be three months then this will work; if not you would have to use a loop.

Formula 1:

whileprintingrecords;
numbervar v_min;
numbervar v_max;

v_min := month(minimum({?Date Range}));
v_max := month(maximum({?Date Range}));

numbervar array v_months := [0,0,0];

v_months[1] := v_min;
v_months[2] := v_min + 1;
v_months[3] := v_max;

Then your three formulas would look like this:

whileprintingrecords;
numbervar array v_months;
monthname(v_months[1])

For the other two formulas change the [1] to [2] and [3] respectively.

 

[briangriffin]

Just realized that this won't work if the range crosses years. Stay tuned..

 

[briangriffin]

This should work better...

whileprintingrecords;

numbervar v_range := datediff("m",minimum({?Date Range}),maximum({?Date Range})) + 1;
numbervar v_counter := 1;

numbervar array v_months := [0];
v_months[1] := month(minimum({?Date Range}));

while v_counter < v_range

do
(redim preserve v_months[ubound(v_months) + 1];
v_months[ubound(v_months)] := month(dateadd("m",v_counter,minimum({?Date Range})));
v_counter := v_counter + 1;
);

The display formulas would be the same, except you should test for the size of the array just in case the user enters a range of less than x months:

whileprintingrecords;
numbervar array v_months;
if ubound(v_months) >= 3 then
monthname(v_months[3])
else ""

 

[rmcguire80]

the three months will always be next to each other....for instance February, March, and April

 

[rmcguire80]

when I put in my parameter field Pm-Date Range in:

whileprintingrecords;
numbervar v_min;
numbervar v_max;

v_min := month(minimum({?Pm-Date Range}));
v_max := month(maximum({?Pm-Date Range}));

numbervar array v_months := [0,0,0];

v_months[1] := v_min;
v_months[2] := v_min + 1;
v_months[3] := v_max;


its saying a date is required here...my parameter field is a date range

 

[briangriffin]

I'm guessing that your parameter is actually defined as datetime and not date. It would be better to change the parameter datatype, but you could modify the formula:


whileprintingrecords;
numbervar v_min;
numbervar v_max;

v_min := month(date(minimum({?Pm-Date Range})));
v_max := month(date(maximum({?Pm-Date Range})));

numbervar array v_months := [0,0,0];
v_months[1] := v_min;
v_months[2] := v_min + 1;
v_months[3] := v_max;


However, this formula will not work if the starting month is November or December - better to use the second formula I posted (you'll still have the problem with the parameter datatype).

 

[rmcguire80]

When I run this now I get

A subscript must be between 1 and the size of the array

Also it's not seeing my parameter field

 

[lbass]

numbervar first := month(minimum({?Date Range}));
numbervar last := month(maximum({?Date Range}));
numbervar tot := last-first+1;
numbervar i;
stringvar array x;
for i := 1 to tot do(
redim preserve x[tot];
x := monthname(first+i-1);
);
join(x,", "); //to show all in one row or

...create multiple formulas like the above and replace the last line with individual subscripts in separate formulas:
if tot >= 4 then
x[4]

-LB

 

[rmcguire80]

replace which line? For instance my months will be February March and April

 

[lbass]

The formula as shown ending with the join function would return: February, March, April. If you wanted to show the second month by itself, you would replace the line containing the join function with:

if tot >=2 then
x[2]

-LB

 

[rmcguire80]

I'm needing though for my individual months to be displayed by themselves

So my parameter would be rand of 2/1/2012 to 4/31/12

Then have three formulas to display the months as labels individually

Sorry if this is a little confusing...thank you for the help

 

[lbass]

Which is what I explained how to do already. Please reread and try.

-LB

 

[rmcguire80]

Ok, when I run my report for March 1st - May 31st which should display March, April, and May

I have 3 formulast firstmonth, secondmonth, and thirdmonth

firstmonth = the above with

if tot >=1 then
x[1]


do the same for secondmonth and thirdmonth replacing the 1 with 2 and 3, but when I run the report it shows instead of March, April, and May.....May,June, and July

 

[lbass]

Please copy the three formulas exactly as you created them into the thread.

-LB

 

[rmcguire80]

firstmonth

numbervar first:= month(minimum({?Pm-Date Range}));
numbervar last := month(maximum({?Pm-Date Range}));
numbervar tot := last-first+1;
numbervar i;
stringvar array x;
for i := 1 to tot do(
redim preserve x[tot];
x := monthname(first+i+1);
);
if tot >= 1 then
x[1]



secondmonth

numbervar first:= month(minimum({?Pm-Date Range}));
numbervar last := month(maximum({?Pm-Date Range}));
numbervar tot := last-first+1;
numbervar i;
stringvar array x;
for i := 1 to tot do(
redim preserve x[tot];
x := monthname(first+i+1);
);
if tot >= 2 then
x[2]


thirdmonth


numbervar first:= month(minimum({?Pm-Date Range}));
numbervar last := month(maximum({?Pm-Date Range}));
numbervar tot := last-first+1;
numbervar i;
stringvar array x;
for i := 1 to tot do(
redim preserve x[tot];
x := monthname(first+i+1);
);
if tot >= 3 then
x[3]

 

[lbass]

Your formulas are incorrect. Please note the signs in the following line:

x := monthname(first+i-1);

-LB

 

[rmcguire80]

ok, thanks for your help...that seems to be working now...

My other problem is Im trying to create a chart off of this data now for months(x axis) vs. loads(y axis) problem is my fields in my database are all separate

i.e. the loads fields are LOADS_PREVMONTH, LOADS_MTH2, LOADS_MTH3. In the current case May, April, March...


The dates are just labels which is where my parameter field comes in, but can you do a chart with a parameter field?

Or would this not be possible to do a chart this way with all the loads fields being separate

thanks

 

[lbass]

You can just add the fields as separate summaries in the chart, with no "on change of" field. Instead use "for all records". You would have to add the formula labels separate from the chart, however, positioning them below the corresponding bars.

-LB

 

[rmcguire80]

is there a way to put the charts in my report not in the header or footer? I'll have multiple charts on the report as well.