Grid with Summary Row that expands with detail rows

[Former Member]

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

[Former Member]

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

union

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).

HTH

hth

[Eneveux]

Michael,

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

Eddy

[Former Member]

Yes, but the sample grid just displays the group by value on the collapsed row. When expanded it displays the detail. The grid format I am talking about (e.g. Bill of Material Report) has the summary detail for each column in the collapsed row and expands to show the detail rows that made up that summary

[Nussi]

Hi Michael,

i think what you are looking for isn't possible in sql.

it is possible to make a select with two union to get the following result.

K1000 100,00

K1000 20,00

K1000-Sum 120,00

K2000 10,00

K2000 15,00

K2000-Sum 25,00

TotalSum 145,00

but than you cannot collapse the rows like you want.