Using a variable in a report

Michelle D.

Active Member
Does anyone have any clever workarounds so that you can actually "use" a
variable in a report (for things like sorting, totaling, data selection,
etc.)? I'm most interested in the best way to handle the following example:

I have a variable called PickMonth which is fiscal year and fiscal year
period concatenated. I need to total by PickMonth. In the following,
PickMonth 210 equals October, 2002 and so on.

Item No. PickMonth Dollars
12345 210 2,500
67890 210 5,000
Total 7,500
44433 211 3,000
Total 3,000
99654 212 6,000
93535 212 3,400
Total 9,400

The "Total" lines are what I need. Not sure how much difference it makes,
but I am sequencing on DCTO, PDDJ, LITM and page breaking on DCTO.

Thank you in advance for any ideas!

Michelle Dulay
ERP Coordinator
Pump & Engine Control Systems
Goodrich Corporation
[email protected]

LIVE ON:
OneWorld Xe, SP 18.1, Update 2
ESU JD10371, JD10833, JD11176, JD12367
Windows 2000 Deployment / Enterprise
SQL 2000
 
Hi Michelle.

You can Sort, Total, and do Data Selection on "Pickmonth".
Right-click on the detail section and you can select "Sequence", as well as
"Select".
While in the Sequence window, there is a tab where you can specify
Page/Level breaks.
Next simply add a section, Level-Break Footer, and select the key field
"Pickmonth".
The wizard will then allow you to add the aggregates.
You can also add aggregate later via the pulldown toolbar menu.
Hope this helps ;>)
Joseph




mdulay
<Michelle.Dulay@goo
drich.com>
To: joseph.q.sadler
Sent by:
owner-jdeowdev@jdel cc:
ist.com Subject: Using a variable in a report


19-Mar-2002 10:08
Please respond to
[email protected]
m





Does anyone have any clever workarounds so that you can actually "use" a
variable in a report (for things like sorting, totaling, data selection,
etc.)? I'm most interested in the best way to handle the following
example:

I have a variable called PickMonth which is fiscal year and fiscal year
period concatenated. I need to total by PickMonth. In the following,
PickMonth 210 equals October, 2002 and so on.

Item No. PickMonth Dollars
12345 210 2,500
67890 210 5,000
Total 7,500
44433 211 3,000
Total 3,000
99654 212 6,000
93535 212 3,400
Total 9,400

The "Total" lines are what I need. Not sure how much difference it makes,
but I am sequencing on DCTO, PDDJ, LITM and page breaking on DCTO.

Thank you in advance for any ideas!

Michelle Dulay
ERP Coordinator
Pump & Engine Control Systems
Goodrich Corporation
[email protected]

LIVE ON:
OneWorld Xe, SP 18.1, Update 2
ESU JD10371, JD10833, JD11176, JD12367
Windows 2000 Deployment / Enterprise
SQL 2000




--------------------------
 
Something like this should work:

Create a driver section with a business view over your table,
and a global total variable. Make all of the fields invisible. In the do section of your dollar field, add the value to the total.
Set up a section to level break on changes to your period value.
This section prints your period and the total for the period.
Then reset the total global variable to zero in the ER of the break
section.

Good luck!
-seg
 
Joseph,

You can only do those things for data items that are in your business view.

Michelle
 
Hi Joseph,

Are you sure this works?(I highly doubt this as "Pickmonth" is, as Michelle
said, a variable, not a BSVW field).

Generally, to accomplish what Michelle wants, you would need a driver
section and one or some conditional sections. Have one idea for you though:
you said that you need only the totals. I am assuming that your variable
"Pickmonth" is constructed based on a julian date. Can you do a driver
section(invisible) based on your initial BSVW and sort this section by the
julian date and have a GV holding the total. When the Julian date changes
from one period to the next(210 to 211)(you could easily check for this
change), call a custom section that displays your total as well as the
"Pickmonth" variable. Then reset the total. In case you need the details as
well, this will not work.

Please let us know if it works.

Adrian Oanta
B7331 (ex-Xe)
 
Michelle,

From what I gather, you are using F4211 and the PickMonth variable you create based on PDDJ. If this is the case, unfortunately you can not sequence and total on the derived variable PickMonth since the fiscal year/period fields do exist in that file (there is a FY field but it relates to the DGJ date). What I have done is:

1. create a workfile containing the fields you need for output
2. your driver section will do table i/o write for every record you would otherwise print. Then do a SuppressSectionWrite.
3. create a conditional section using your workfile bsvw as input
4. in your driver section 'End Section' event do a 'Do Custom Section' for the new conditional section.

In your conditional section you will then have all the fields necessary to allow the batch engine to do the sort/totalling/etc.

When creating workfiles for things like this, you should have some means of identifying records your job places in the file, so you can delete just those you put in. For example, use a next number field at the top of the primary key of your workfile that you will determine prior to inserting records. Then every record that this job inserts will have this unique identifier. When your conditional section is done, be sure to do a table i/o delete for all records with this unique value. This will allow other jobs/users to run the report simultaneously.

One other point if you decide to do this approach. The unique field must be a report variable (RV) in order for it to be used in data selection (which you'll need for your conditional section to select only your job's records). So create a non-visible global variable in some section.

Jeremy

Jeremy
JDE Consultant
Minnesota, USA
 
Forgive me everyone,
and thank you Adrian for pointing this out:
It seemed so obvious until you pointed out that it is a variable, and NOT a
BSVW field.
The only way I could see doing this is to output a work-file as previously
stated in an earlier reply.

Thanks again, Adrian.
I thought the world was going mad !?

Joseph.





aoanta
<Adrian.Oanta@Crystaldeci
sions.com>
To: joseph.q.sadler
Sent by:
[email protected] cc:
m Subject: RE: Using a variable in a report


19-Mar-2002 16:43
Please respond to
[email protected]





Hi Joseph,

Are you sure this works?(I highly doubt this as "Pickmonth" is, as Michelle
said, a variable, not a BSVW field).

Generally, to accomplish what Michelle wants, you would need a driver
section and one or some conditional sections. Have one idea for you though:
you said that you need only the totals. I am assuming that your variable
"Pickmonth" is constructed based on a julian date. Can you do a driver
section(invisible) based on your initial BSVW and sort this section by the
julian date and have a GV holding the total. When the Julian date changes
from one period to the next(210 to 211)(you could easily check for this
change), call a custom section that displays your total as well as the
"Pickmonth" variable. Then reset the total. In case you need the details as
well, this will not work.

Please let us know if it works.

Adrian Oanta
B7331 (ex-Xe)



--------------------------
 
Re: RE: Using a variable in a report

Hi Joseph,

Can you make custom table with a year, month and PickMonth? Put periods from the very first entry that you have to 2020 for example. It is only 12 records per year. Join that table in your business view and you can sort and make level brakes on that PickMonth.

Hope this helps,

Bojan.
 
Listing of ER for Report: Open Sales Orders by SOT, Sched. Pick Date, =
Item Number (R55JPSOS)
 
Listing of ER for Report: Open Sales Orders by SOT, Sched. Pick Date, =
Item Number (R55JPSOS)
 
Thank you all for your responses. I keep trying to attach the ER for my
report but there seems to be a problem sending attachments. I guess I'll
try again later if anyone is interested.

FYI - I specifically did not want to write anything out to a workfile
because I know the user is going to continue to have me make changes to this
report (probably adding / removing columns, different totaling, sorting,
etc.). You'll also notice that there is quite a bit of formatting around my
fiscal year and period. This obviously is necessary to make the code work
correctly - I just wanted it as readable for the users as possible.

Michelle Dulay
 
Hi Jeremy

I´m just wondering what do you mean by "Create a new Workfile" ?

What´s a Workfile ?

Regards
Jonas

<P ID="edit"><FONT SIZE=-1>Edited by DamienXe on 3/22/02 01:24 AM.</FONT></P>
 
Re: RE: Using a variable in a report

Michelle,

if you are attempting to send your attachment as part of an email to the list - it won't work. You must be on the jdelist web site using the forums there to post an attachment.

Regards,

Larry Jones
[email protected]
OneWorld XE, SP 15.1
HPUX 11, Oracle SE 8.1.6
Mfg, Distribution, Financials
 
In the past I have created a SQL view of the information that I am wanting to see and then created a JDE table and BSVW definition of that SQL view to use in the report. You could calculate the FY and Period as part of the SQL view.

B7333 SP11_3, SQL 2000
 
Michelle,

Why did you need to create the SQL view in addition to the JDE table and
BSVW?

Michelle Dulay
 
Re: RE: Using a variable in a report

Michelle,

I would create a SQL view so that you can concatenate the FY and Period as one column, then I would create the Table and Business View definitions of that SQL view as though it was a table.

To create a table definition of a SQL view,
1) Create the SQL view in the Enterprise Manager
2) Create a matching table definition using the Table Designer
3) Click on generate, BUT when the form W9866E (Object Librarian - [Generate Table]) is displayed click on CANCEL. This will cause JDE to create the table specs without actually creating the table.

Although having said this I also like Bojan suggestion about a custom table that you could link your table to in a business view.



Michelle Perrin
WEL Networks Ltd
Hamilton, New Zealand

[email protected]

B7333 SP11_3, SQL 2000
 

Similar threads

Using a variable in a report
Replies
2
Views
2K
aoanta
Using a Wildcard in Report Writer
Replies
1
Views
2K
NM1234
Using a Wildcard in Report Writer
Replies
6
Views
2K
flyingmg
Back
Top