Dynamic Sequencing?

[jdesmm]

Is there any way to program a report to sequence dynamically? I've been trying to work with the Set User Selection system function but without success. In fact, it generates a very strange error "Unable to locate table in specified data source for section: Main."

The problem I've got is that management would like many different "views" of a custom report, which already has a particular sequence and level breaks. They want to be able to dynamically change the sort, depending on a PO setting. I know this can be done at the version level and that may be the way that we have to go, but it seemed like the system function should do what I'm looking for and negate the need to maintain multiple versions. Anyone had success with it?

*----------------------*
* Shelley M. Maas *
* Development Manager *
* Apio, Inc. *
*----------------------*

 

[flyingmg]

Shelley,

Have you tried another system function called Set User Sequencing?

This function is typically used at Initial Section of a UBE. Therefore, you can add IF event rule in the initial section to determine which sequencing you would like to use based on a PO value.

Clear User Sequencing and Set Sequencing Append Flag are another two related system functions.

MG
Win2000/SQL 2000, B7332 SP13.3



G
[email protected]

 

[jdesmm]

Yes, in fact that is the system function that's generating the error - I just mis-typed in my original post. Can you shed any light on how you've gotten it to work? I do have it in my initialize section.

*----------------------*
* Shelley M. Maas *
* Development Manager *
* Apio, Inc. *
*----------------------*

 

[Zoltan_Gyimesi]

Hi Shelley,

First of all, welcome to JDEList Forums!

1.) You can use SetUserSelection only in the Initialize Section event.
2.) "Item1" should be always a BC (Business View Column).
3.) You have to set "And/Or" parameter to "None" in the first occurence of "SetUserSelection" and have to set to AND or to OR in all subsequent occurences of "SetUserSelection".

By the way, check the Trips & Traps board on the Forum about how to access OneWorld online help, where you can read brief but very useful informations about System Functions and Events.

Please, keep us informed about your progression! Thanks!

Regards,

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[flyingmg]

Shelley,

Here are basica steps to set custom sequence:

1. call "Set Sequence Append Flag" and set it to "YES" if you want to append your custom sequence to existing data sequencing;
2. call "Set Use Sequence". Pass BC to "ITEM" parameter and pass "Ascending" or "Descending" to "SORT ORDER" parameter.

Here is a tip that your custom sequence may generate error or be ignored if it tries to override Level Break data sequencing.

Hopefully it helps. Or you can tell a little more about what you try to do and what UBE you are modifying.

MG
Win2000/SQL2000 B7332/SP13.3

G
[email protected]

 

[jdesmm]

Hi guys, I've still got a problem with this.

Following your advice and that of a consultant, I removed my default sequencing and then programmed the sequencing entirely in code. The code no longer generates an error, but it is basically ignoring the level break headers and footers that I had created. There is nowhere that I can find in the Set User Sequence function that allows you to specify whether it is a level break or not, so I'm guessing the program sets the sequence and then decides there are no level breaks.

Below is a sample of my code. It basically checks processing options, and based on their values, adds to the sequence in certain ways. I could certainly use your help / suggestions.

// Determine sequencing based on PO sort option and on levels selected.
Clear User Sequence
Set Sequence Append Flag( <Yes>)
//
// Test for Ship-To sort
If PO cLOBSort_EV01 is not equal to "Y" And PO cShipToSort_EV01 is equal to "Y"
If PO cShowShipTo_EV01 is equal to "Y"
Set User Sequence(BC Name - Alpha (F0101), <Ascending>)
End If
If PO cShowLOB_EV01 is equal to "Y"
Set User Sequence(BC Category Code - Business Unit 30 (F0006), <Ascending>)
End If
If PO cShowSLSP_EV01 is equal to "Y"
Set User Sequence(BC Category Code - Address Book 25 (F0101), <Ascending>)
Set User Sequence(BC Ordered By (F4201), <Ascending>)
End If
Else
// Otherwise, LOB is default sort
If PO cShowLOB_EV01 is equal to "Y"
Set User Sequence(BC Category Code - Business Unit 30 (F0006), <Ascending>)
End If
If PO cShowSLSP_EV01 is equal to "Y"
Set User Sequence(BC Category Code - Address Book 25 (F0101), <Ascending>)
Set User Sequence(BC Ordered By (F4201), <Ascending>)
End If
If PO cShowShipTo_EV01 is equal to "Y"
Set User Sequence(BC Name - Alpha (F0101), <Ascending>)
End If
End If
//
// Regardless of sort chosen, the following levels are the same - test for
// whether or not to add them to the sort based on Show Level PO's
If PO cShowCmty_EV01 is equal to "Y"
Set User Sequence(BC Sales Catalog Section (F4211), <Ascending>)
End If
If PO cShowPack_EV01 is equal to "Y"
Set User Sequence(BC Sub Section (F4211), <Ascending>)
End If
If PO cShowSize_EV01 is equal to "Y"
Set User Sequence(BC Sales Category Code 3 (F4211), <Ascending>)
End If
If PO cShowItem_EV01 is equal to "Y"
Set User Sequence(BC Description (F4101), <Ascending>)
Set User Sequence(BC Description - Line 2 (F4101), <Ascending>)
Set User Sequence(BC Item Number - Short (F4211), <Ascending>)
End If


*----------------------*
* Shelley M. Maas *
* Development Manager *
* Apio, Inc. *
*----------------------*

 

[Sef]

Hi Shelley

As you have discovered, dynamic sequencing and level breaks don't really mix together very well. The suggested solutions to use system functions work quite well if you just wish to change the sequence, but not that well if level breaks and accumulations are involved. If you do want to go down that path, you will also need to code the level breaks yourself, by comparing BC (business view column) to PC (previous BC). When they are different you can call a custom section to display variables that you also programatically accumulated in the Do-section of the body of your report.
Depending on how many level breaks you have and how much you want to display in the level break sections, this can become quite a large task.

Personally I don't believe RDA is suitable for this type of tasks. RDA is an extremely powerful tool, but for 'slice and dice' type reports you are much better of using a BI type tool, such as Cognos Powerplay.

Good luck,

Sef van den Nieuwelaar
Australia
B732 on NT, XE on NT, B732/A73 on AS400, B733 on NT

* Coming to a European City near you soon *

 

[flyingmg]

Shelley,

You can follow the exact logics in your Initial Section (set user sequencing) and put them in a DO Section. For each condition which you would like to level break, you can "check BC and PC values". If they are different, call "Do Custom Section" system function. You custom section is where to store level break information.

As SEF's post, Page Break Header and Footer will not work together with your custom data sequencing.

Good Luck.

MG

G
[email protected]

 

[jastips]

Just I would like to add some points to MG’s mail.

Due to level break sections are directly depend on sequence, If you change sequencing you will get strange results. So you can incorporate level break logic in your code like MG said or

Try to fix level break dependent sequence in the report and try to append other field’s sequence. If your requirement is changing the level break sequence then you have to follow MG’s procedure.


Jastips
JDE CNC Consultant
DTI
USA

 

[MCCInet]

Hi Shelley,

Also be aware of the bug in the ERW that will not allow any level breaks when you put the SUB ahead of the OBJ when sorting. It will sort properly, but will not level break. This is a known bug that apparently will not be addressed.

The FASTR tool will perform properly in this manner, but the Financial Reports in ERW will not.

You must also live with the slower report generating times that according to JDE are 5 times slower than FASTRs. Our experience is that is a generous estimate.

I am running a OW Financial Report now that I started more than 5 hours ago. It is not a row-spec and has only 4 coloumns and is selection only expense object codes.

I just created a FASTR to create the same 4 column financial report and it ran in under 1 minute. The OW report I started this morning is still running. No fancy ER's - just the heading and company retrievals.

We are currently looking at either Cognos or Crystal to perform timely reporting. Any recommendations from anyone that has used both of these products?

Thanks in advance.




I created a 4 column FASTR today