Total Value Want To Display In Level Break Header

abhay123

Active Member
I created one report using processing option value from date & to date as per the requirement of the user.
Case 1: In the detail section user wants data which having transaction date (TRDJ) greater than From Date and less than To Date.
Case 2: In LBH user wants the total value of all quantities which having a transaction date is less than From Date.

I Tried to fulfill the requirement but not able to do 2nd case.
Any help would be appreciated.
 

Attachments

  • Example.pdf
    94.3 KB · Views: 27

JMR

VIP Member
You need to understand how the report engine works. Level Break Headers perform PRIOR to detail processing for that level's data. There is no visibility to the totals of the detail section at the time the LBH outputs because it hasn't read those detail records yet.

Will the user accept a Level Break Footer? This is really how the engine is supposed to be used.

If the user absolutely requires total information in the LBH, then you are going to have to manually determine those totals. There are a couple of ways to go about this, but you need to be aware of decreased efficiency... you most likely will be reading each detail record twice... one pass to gather your totals and output your LBH. Then another pass to output your detail records. You know your data... is this terribly inefficient? If so, you might want to sell the user on the LBF solution.

Depending on what totals you require, you could use the JDB_ API in a c-bsfn to perform your totals in a single SQL statement. This obviously requires some c-expertise and knowledge on how to use those JDB API's, but could be much more efficient than a 2-pass solution if the LBH is required.
 

JMR

VIP Member
Sorry... I misread your original post pertaining to #2 being LessThan FromDate. Forget the bit about the LBF I mention above as it does not work for your scenario.

This exacerbates the efficiency issue if that file continues to grow larger and larger. I would really consider trying to use the JDB_ API to perform a SQL such as:

select sum(F55XXXXX.QTY)
from F55XXXXX
where F55XXXXX.TRDJ < FromDate

That will probably be the slickest, quickest solution. However, if you can't figure out how to do that using the API, then you will have to manually read all the records in that table with a date < FromDate and tally the qty's yourself. You can use a custom section to read and tally all records prior to FromDate.
 

jileto

Well Known Member
#2 is one of those scenarios where BIP would save the day.

Code the UBE as if all the totals are at the footer. The UBE will generate a XML with all the output data. You can then use BIP to produce the output using the XML data from the UBE.
 

abhay123

Active Member
Thank You JMR & Jileto for your quick response.
JDB_API is a good solution for this, but I have never used before even not having basic knowledge of this so can you please send me some document regarding this.
Till the time I will try with BI…
 

BOster

Legendary Poster
I would look further into jileto's solution (use BIP). If that is not an option look into the following C API:

Code:
ubeSection_GetDataSelection      ;Get data selection off a UBE section, you might be able to use this to get the same data selection used on the UBE section
JDB_OpenViewExtended             ;Open the same view as the UBE section uses.
JDB_SetAggregate                     ;Define what you want to aggregated, basically your SQL SUM, AVG, etc.
JDB_SetGroupBy                       ;set which fields you want to group by (optional, only needed if you plan to have a recordset in addition to your aggregates)
JDB_SetSelectionX                     ;can set the selection you obtained with ubeSection_GetDataSelection and can be called again to append any additional WHERE clauses you might need based on the level break (customer #, MCU, etc.)
JDB_SelectKeyed                       ;exectue the query
JDB_FetchAggregate                   ;fetch the results of the query
JDB_CloseView                          ;close the table handle
ubeSection_FreeDataSelection      ;free resources allocated with ubeSection_GetDataSelection
 
Top