SCOPE FOR in a REPORT

[LennartC]

Hi All!
I am working in FP 2.0.
I am trying to make a report based on a databasefile with the fields
Ordernr, Articlenr, Qty, Price etc.
The file is indexed on Articlenr, Ordernr.
The report is Grouped on Articlenr.
Details are Ordernr, Qty, Price etc.

Now, the records could be as follows:
Ordernr Articlenr Qty
10548 12345 10
10555 20000 10
10560 20000 10
10575 20000 10
10125 34567 10
10560 34567 10
10233 99999 10

In my report I like to have all articlenr within ordernr 10560 and 10675 covered
and in the detail band all orders for that article. How to do it, please?

Example:
Group Articlenr 20000
Detail Ordernr 10555 Qty 10
10560 10
10575 10
Sum 30
New page
Group Articlenr 34567
Detail Ordernr 10125 Qty 10
10560 10
Sum 20


Esc

 

[JRB-Bldr]

Based on your question itself, the issue has nothing to do with SCOPE for the Report.

Instead you appear to be looking to launch a new Report Page once the Group changes.

Within the Report Form
Data Grouping
Add/Change
When Group Changes, Begin...
put a check in New Page

If you want to limit the report to only include specific ordernr (such as 10560 and 10675), then you might want to either use SQL to collect a record sub-set and send that sub-set to the REPORT FORM (prefered method), or you could set a FILTER on the original table.

SELECT *;
  From MyData;
  WHERE INLIST(ordernr,10560,10675);
  NOCONSOLE;
  INTO CURSOR ThisRpt

SELECT ThisRpt
REPORT FORM MyReport NOCONSOLE TO PRINTER
USE

[B]* --------- OR -----------[/B]

SELECT MyData
SET FILTER TO INLIST(ordernr,10560,10675)
GO TOP
REPORT FORM MyReport NOCONSOLE TO PRINTER

Good Luck,

JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[tesar]

Only small comments:
Instead INLIST (not in 2.0) use BETWEEN;
FILTER is safer for 2.0 and very fast (>1GHz) comp.
Tesar

 

[LennartC]

Thanks JRB-Bldr and Tesar.

I have tried the SELECT command with INLIST and BETWEEN.
The SELECT command gives a DBF-file - ThisRpt - that is sorted on Ordernr and not on Articlenr. Therefore MyReport will not give the result I want.

Also, the records in ThisRpt covers only the orders in the span 10560, 10675 and none others.

As you can see in my first Posting I want one page for each Articlenr within the span 10560, 10675 but in the detailband all orders for that Articlenr.

Thanks again


Esc

 

[JRB-Bldr]

Your original posting was not clear.

When you said
"have all articlenr within ordernr 10560 and 10675"
I did not know if you wanted
* "have all Articlenr within ONLY ordernr 10560 and 10675"
or if you wanted
* "have all Articlenr within the span between ordernr 10560 and 10675".

However, again, you could have modified the SQL Query to get specifically what you wanted.

As is explained in your FP Help file, the BETWEEN command should have given you ALL records which spans 'between' the first parameter and the last parameter.

The original SQL Query I suggested was somewhat generic with the hope that you would see the general sugggested approach, study the associated FP Help file for learning more and then modify the query as required for your specific needs.

If you want the resultant cursor sorted by Articlenr and then Ordernr you might need to do something like the following...

SELECT *;
  From MyData;
  WHERE BETWEEN(ordernr,10560,10675);
  NOCONSOLE;
  ORDER BY Articlenr, Ordernr;
  INTO CURSOR ThisRpt

That will give you ALL records containing an Ordernr from 10560 through 10675 (the ENTIRE SPAN) and it will Order it first by Articlenr and then Ordernr.

Good Luck,

JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[LennartC]

Thanks again, I´ll be back.

Esc

 

[LennartC]

Okey. Sorry. English is not my native language so I think I fooled myself.
Oh yes I have read the help files and the manuals from A to Z.
However, my problem is the following:
Order nr 10555 for 10 pcs of article nr 20000 comes in and I print our a "Worksheet" for my workshop manager for him to know the quatity of article 20000 which have to be produced.
Like this:
Example:
Group Articlenr 20000
Detail Ordernr 10555 Qty 10
Sum 10

Then some days later a new order - 10560 - comes in asking for the same article nr 20000.

Later on a third order - 10575 - for article nr 20000 plus additional another 50 orders for other articles comes in.

Now, I have to print a new "Worksheet" for my workshop manager. And he wants a "Worksheet" for article nr 20000 - and all other articles ordered - with the new orders as well as the previous one.
That is something like this:

Group Articlenr 20000
Detail Ordernr 10555 Qty 10
10560 10
10575 10
Sum 30
So I want to print "Worksheets" for all Articlenr covered in the Ordernr from 10560 to 10675 AND including in the detailband the previous Ordernr 10555.
Thats the issue.





Esc

 

[JRB-Bldr]

Based on what you have just described, so what would not work for you about what I suggested?

Let's assume that on Day #1 you have in MyData.dbf

Ordernr Articlenr Qty
10548 12345 5 <-- Not BETWEEN(Ordernr,10560,10675)
10555 20000 10 <-- Not BETWEEN(Ordernr,10560,10675)
10560 20000 10
10575 20000 2
10125 34567 10 <-- Not BETWEEN(Ordernr,10560,10675)
10560 34567 10
10233 99999 3 <-- Not BETWEEN(Ordernr,10560,10675)

Running the above SQL Query would result in a Cursor ThisRpt

10560 20000 10
10575 20000 2
10560 34567 10

* ============================
And on Day #2 additional records have been added...

Ordernr Articlenr Qty
10548 12345 5 <-- Not BETWEEN(Ordernr,10560,10675)
10555 20000 10 <-- Not BETWEEN(Ordernr,10560,10675)
10560 20000 10
10575 20000 2
10125 34567 10 <-- Not BETWEEN(Ordernr,10560,10675)
10560 34567 10
10233 99999 3 <-- Not BETWEEN(Ordernr,10560,10675)
10565 11100 7
10666 11275 11
10566 34567 15
10663 11100 15
10551 12345 15 <-- Not BETWEEN(Ordernr,10560,10675)

Running the above SQL Query would result in a Cursor ThisRpt

10565 11100 7
10663 11100 15
10666 11275 11
10560 20000 10
10575 20000 2
10560 34567 10
10566 34567 15

The resultant RptDBF cursor records are now restricted to the Ordernr span that you desired.
And the records are ordered with the Articlenr numbers together and in sequential order, then under each of those Group's you have the Ordernr appearing in sequential order.

Then within the Report Form itself create a variable which will SUM the QTY variable per Group (RESET to Zero when Group Articlenr changes) and have it display within the bottom Group Band.

If that is not meeting your needs, then I am still not clear on what your problem is.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[LennartC]

Well, what´s missing is the order 10555 that also has the Article 20000
10555 20000 10 <-- Not BETWEEN(Ordernr,10560,10675)

and the Report should look like this:

Articlenr 20000
Ordernr 10555 Qty 10
Ordernr 10560 Qty 10
Ordernr 10575 Qty 10

So I come back to my original question:

In my report I like to have all articlenr within ordernr 10560 and 10675 covered (that is BETWEEN 10560 and 10675)
AND in the detail band all orders for that article. How to do it, please?




Esc

 

[JRB-Bldr]

As you can see
10555 is less than 10560 and therefore is NOT BETWEEN(Ordernr,10560,10675)

One thing to note is that IF Ordernr were a Character field instead of a Numeric field, then the selection criteria expression would need to change to:
BETWEEN(VAL(Ordernr),10560,10675)

However, it sounds as though what you want is not as you have described it. "all Articlenr within Ordernr 10560 and 10675"

Perhaps you need to re-think the report's selection criteria that you are wanting and describe it better.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[LennartC]

Thanks for your effort.
You put me back to square one.

Esc

 

[JRB-Bldr]

Topmost post -- "have all Articlenr within Ordernr 10560 and 10675"
or is it...
Later post -- "I want one page for each Articlenr within the span 10560, 10675 but in the detail band all orders for that Articlenr."

In the first posting you wanted ALL Articlenr but Limited Ordernr's
In the second posting you wanted ALL Ordernr but Limited Articlenr's

Perhaps all you need to do is to modify the sample SQL Query above to change which variable you put into the BETWEEN() command, if that is what you truly want to do.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[NasibKalsi]

LennartC:

May be you can do this in the program as follows:

First create a report form

use MainTable
create form Myreport


Index on GroupItem+Detailed to T_ind

go top
set filter to val(Datailed) >= NumericStart .and. val(Detailed) <= NumericEnd

report form MyReport


quit


That is it.
Please proper Field Names as required.


Try it

Nasib Kalsi