• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Set Sequence and Custom Level Breaking?

DBohner-(db)

Legendary Poster
Is it Friday YET???


I have a very simple UBE and I want to aggregate Quantities per level-breaks on Year, District or Region. The oddity is that some regions are in multiple districts and some districts are in multiple regions (don't ask why - just accept this as gossip).

In the data, I have defined three level-breaks (One for Year, one for District and one for Region).

Ideally - I should be able to use the Set Sequence to change the sorting and level-breaking, based on a Processing Option.
Let's say if the Processing Option is:
- YDR - Year, District then Region
- YRD - Year, Region then District

I set the Default Sequence on the section for YDR. When I run the report for YDR - the results are exactly as expected (Year, District and Region) - and level-break as expected.


Now, if I change the internal sequencing to YRD - the data is sorted correctly, but I am getting the Region level-break after each District (every time District changes, I get the Level-Break-Footer for the Region, too). The Region hasn't changed - but I'm getting a Level Break, anyway?

I have other reports where the Set User Sequence Function works flawlessly - why they heck doesn't it work on this 'simple' report?

I've seen a function that says 'forget about prior sequencing / level-breaking' - but I can't seem to find the magic, today...

(db)
 

jdelisths

Reputable Poster
I think the level breaks are "hard wired" to the default data sequencing. If you change the data sequencing on demand, it simply does not know how to adapt. You can create two conditional sections with YDR and YRD default sequencing and use a driver section to call them based on the processing option.
 

peterbruce

Legendary Poster
Dan,

Does the "physical" order of the level break sections affect the processing? Is one a LBH and the other a LBF?
 

Kim Schmidt

Active Member
You could have a flag that indicates which sort is in effect. Then add logic in your Region level break footer to test whether the flag indicates YRD sorting, and if so, test if the BC Region = PC Region. If the region is unchanged, hide the level break section. You may also need to do manual accumulation of the quantities. I have used a similar technique when a UBE might need to have subtotals on either MCU or HMCU depending on the version - and also use group instead of columnar sections when this flexibility is needed.
 

DBohner-(db)

Legendary Poster
Peter,

If I change the 'Physical Order' / sequence - the LBF for the section that has not changed (has not been triggered) is being triggered.

I have a different report with six LBFs - and as the Set Sequence changes in the Initialization section, the LBFs are triggered in the proper sequence (as they change).

Not sure why this one is being so difficult =(

I may need to open a ticket - get a little clarity.

(db)
 

DBohner-(db)

Legendary Poster
Just for giggles - If I alter the sequence of the Version at run-time, the UBE will display the Level-Breaks in the sequences expected.

Where's the #EASYBUTTON?
 

BOster

Legendary Poster
Hmmm, maybe try creating three LBF sections that are set to break on the three different values. Then hide or suppress output on the two that are not being used based on the Proc Option selection.
 

jdelisths

Reputable Poster
I have a different report with six LBFs - and as the Set Sequence changes in the Initialization section, the LBFs are triggered in the proper sequence (as they change).

So, the LBs are not hard-wired. Good to know.
 

DBohner-(db)

Legendary Poster
Hari,

They are supposed to be hard-wired to the change between PC and BC. I'm opening a ticket with Oracle for a better understanding "What am I missing???"...

If you alter the sequence using the ER in the Initialization Section "Set User Sequence" - the sequence is changing. However, the LBFs are kicking off 'strangely'.... I am not sure if it is tools related or just a blatant misunderstanding from my.

If you change the sequence at the Version (during submission) - the LBFs kickoff as they are supposed to (per the new sequence)
If you change the sequence internally (Initialization section, 'Set User Sequence') - the sequence of the report is correct, but the LBFs don't kickoff when PC/BC changes occur.

(db)

(db)
 

peterbruce

Legendary Poster
Dan,

This may be a simple question but I've fallen victim to it before: Did you set the append sequence flag and/or run the clear user sequence system functions before the set user sequence?
 

johndanter

Legendary Poster
If you are changing that selection/sequence manually, why not do this via a new PO and control the sections yourself?
Just use ER to control if something breaks or not and call a custom section that contains variables you've been totaling anyway.

I always thought if you had a few level breaks, and the engine breaks on the highest one, then the others are ignored (until they break)

Someone once told me that ALL level break sections are processed even when one of the other sections breaks in the engine.
I'm not sure if that's true or not or maybe they meant a lower level break triggers the higher level breaks to execute as well....Or if a high one breaks, then all the lower ones also break.

As that had caused me problems before.

I did write this down and I'm trying to find the document I saved.

But for now I found this here https://docs.oracle.com/cd/E17984_0...htm#g8d6ab57f7cedeaac_ef90c_10a77c8e3f7__65f8

If you set up multiple level break headers for a report section, the batch system processes the headers from the highest level in the report to the lowest level in the report. For example, if you define two level break headers, one on business unit and one on company, the company level break header processes first, and then the business unit level break header processes.

Which we kind of all new right? But I think my friend was saying when company breaks, the engine also then goes on to call the MCU break
 

DBohner-(db)

Legendary Poster
Peter, I did confirm the 'Append Sequence=No' and the 'Clear User Sequence' - prior to my Set User Sequence (as always - we all thank you for the reminder).

John, I'm queuing my logic for level-breaking based on:
"The system identifies a change in the data of a specific field by comparing the current record to the previous record. A level break occurs when the system identifies the change between the records for the fields that you define as level break fields."

A level-break section should occur only if the BC/PC values are different AND/OR the BC/PC Values are different in a column within the prior syntax of sort sequence. At least - that's my expectation.

I did open a ticket with Big-O, and we'll see how they pounce on it (and I'll make sure to provide some feedback).

(db)
 

DBohner-(db)

Legendary Poster
I believe I've convinced Oracle to create a #Bug.

In the meantime, they suggest doing the same as I've been doing - create separate version for each way the report can be sequenced:
- Set the Version Sequence to match the Internal ER Sequencing and Processing Option

Finally, after fifteen year - maybe it will be fixed =D

(db)
 
Top