Skip to Content
author's profile photo Former Member
Former Member

Grid with Summary Row that expands with detail rows

Hello all,

I am trying to figure out how to create a grid that functions similar to the Bill of Material Report (Reports -> Production -> Bill of Material Report). When collapsed, the rows display summary information (not just the group by column). When expanded, the rows are made up the detail rows that make up the summary row.

I can't figure out how to have two sql queries (one for the parents and one for the children) and how to populate the columns of the collapsed row with the summary information. One other point of intrigue, is that the collapsed/expand icons is on the grid itself, not in the columns. This would be perfect for what I need to accomplish - I just can't figure out how even after spending several days trying everything I could think of or read about in this forum. Thanks in advance. Mike

Edited by: Michael Borman on Jan 24, 2008 8:07 PM

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Jan 24, 2008 at 07:51 PM


    Did you review the sample that comes with the Business One SDK for using the Grid object? This also shows you how to expand and collapse grid information. You can locate the sample at ...

    ..\Program Files\SAP\SAP Business One SDK\Samples\COM UI\VB.NET\19.Grid


    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 28, 2008 at 02:51 PM

    If the question is "Can I issue one SQL statement to provide the summary rows and the detail rows in one result set" then the answer is yes. You need to use the UNION operator. It will let you combine summary and detail results as separate rows that can be intermingled. The important things are that the rows from both tables contain the same number of columns and the columns between the two tables need to be of the same data type (you can cast if needed).

    This works:

    select '0', docentry, docnum, cardcode, cardname, doctotalsy from ordr


    select '1', docentry, 0,itemcode, dscription, linetotal from rdr1

    order by 2,1

    the zero and the one allow you to make sure the header row always appears before (or after if you want) the detail rows depending on how you sort them. Notice that on the header rows, two columns have card code & card name while the detail rows, in the same columns have item code & description. The "order by " clause can only be applied at the end of the last select (it applies to the combined result set).



    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.