We have a request to show MPS forecast data in a horizontal fashion. Since the dates are just multiples of 7, we can use:

function get.fore.date()
{
for i = 1 to 14

fore.date(i) = timps300.pdat + count
count = count + 7
endfor
}

The problem comes when we want to use j to get the forecast for those same weekly buckets. Can anyone tell me how the coding should be done?

Thanks for any help you can offer,

staugner:o

I do not understand the question - can you provide more information. I assume getting the dates works - what exactly is it you are trying to do.

Thanks

Hi Mark,

We can get the dates with no problem using an array. When we try to populate the date buckets however, the forecast information is printed vertically instead of horizontally under the dates. A simple array doesn't seem to work and we are left with multiple if/case statements instead. How can we create an array to populate as follows:

9/13 9/20 9/27
155 204 373

Thanks for your reply,

Staugner

Is this for a form or for a report

What I did for a report was basically to create 2 arrays. Each array was initialized to spaces before entering the loops. The reason the arrays were characters was because in some cases they would run a variety of time frames - they could pick months or weeks and the number of buckets. In this case I would first get the date range and then sum the hours - like this(not exactly, but something like it)

startdate = initial date (from form)
enddate = startdate + weeks or months depending on what user selected.
for i = 1 to numcount (how many weeks or months)
select sum(tisfc010.prtm):totalhrs
from tisfc010.prtm
where tisfc001.cwoc = some cost center
and tisfc010.prdt >= startdate and tisfc010 <= enddate
selectdo
endselect
rptdate(i) = string value of date
rpthrs(i) = str$(totalhrs)
rprt_send()

The actual code was more complicated than that, but that is the basic of what I did. Then on the report I would have workcenter and the dates in the header(each workcenter had its own page). Then hours (actual, production, capactity - a bunch of different calculations) went down the page.

It should work for either a report or a form. On a form you just have to get the calculations in the correct even. I would not do something like this with a form with scrollbars - it could be done, but would require a lot of coding.

Sorry Mark, this doesn't work. We have no problem populating the dates across the header. The problem comes in when we want to populate the forecast fields for those dates. Our dates are each incremented by 7. The results we get from our queries still give us forecast(1) for all dates on line 1 and forecast(2) for all dates on line 2, etc.

We are on Baan IVc4.

Thanks for trying.

staugner:

Is this a form or a report?

This is a report, Mark.

Okay - can you post the code that gets the data for each of the dates? I know this can be done because my header is one array with dates and the details is another array with the data.

Here it is Mark,

function read.main.table()

{

select timps300.*
from timps300
where timps300._index1 between {"IM1", 5, :plni.f} and
{"IM1", 5, :plni.t}
and timps300.pdat >= date()
order by timps300.plni
selectdo
count = 0
line = 0
get.fore.date()

endselect

}
function get.fore.date()
{
for j = 1 to 14
fore.date(j) = timps300.pdat + count
count = count + 7
endfor
rprt_send()
}

Okay I was expecting the code to look something like this:

function read.main.table()

{
|get first date and infor to be broken into buckets.
select timps300.*
from timps300
where timps300._index1 between {"IM1", 5, :plni.f} and
{"IM1", 5, :plni.t}
and timps300.pdat >= date()
order by timps300.plni
selectdo
count = 0
line = 0
get.fore.date()
endselect

}
function get.fore.date()
{
long total.demand
domain tcdate startdate, enddate
| Now go get the data for the buckets. First time through use the first mps date.
for j = 1 to 14
fore.date(j) = timps300.pdat + count
startdate = timps300.pdat + count
enddate = startdate + 7
select sum(a.demf):total.demand
from timps300 a
where a._index1 = {:timps300.plnc,....}
and a.pdat >= startdate and a.pdat< enddate
selectdo
endselect
demand(j) = total.demand
count = count 7
endfor
rprt_send()
}

In the for loop on the select you would use whatever index you wanted to narrow down the records you want in each bucket.

Dear Mark,

Thank you, thank you, thank you! This code does just what we needed. I think what we missed was adding up the total forecast for each 7-day period.

This was very much appreciated.

Staugner