same result with table change

[gagani]

I am trying to explain the earlier and the current situations

Earlier
table1:
orderid A B C Active Customer
100 1 1 2 Y abc
101 1 0 1 Y def

The report is like this:
Orderid A combined({table1.B}+{table1.C})
100 1 3
101 1 1

In the current situation, the columns(A,B,C) of table1
will be removed and the table1 will become

orderid Active Customer
100 Y abc
101 Y def
And I should get the same report result by joining
table2 with table1

table2:
orderid product quantity
100 A 1
100 B 1
100 C 2
101 A 1
101 C 1
table1 must be joined with table2 as the report has some other columns
belonging to table1

 

[lbass]

Link the tables on order ID, and then create a formula {@prod} like this:

if {table2.product} in ["B","C"] then
"BCCombined" else
{table2.product}

Insert a crosstab and use {@prod} as the column, orderID as the row, and sum of quantity as the summary field.

-LB

 

[gagani]

I didn't understand by "BCCombined" , there is no column as B and C in table2, but I should get the quantity correspoinding to that product row for that orderid.

output should be like this with the consideration of table2:

100 1 3
101 1 1

And I cannot insert a crosstab as it has some other columns joining
with other tables.

 

[lbass]

Please look at my formula. You are showing that your new table has a field product that shows A, B or C. To get the combined value for B and C, I created the formula and named it BCCombined.

Your final statement does not rule out a crosstab. However, you could instead create two formulas like this:

//{@A}
if {table2.product} = "A" then
{table2.qty}

//{@BandC}:
if {table2.product} in ["B","C"] then
{table2.qty}

Insert a group on order ID, and then add each formula to the detail section and insert a sum on it at the group level. Drag the groupname into the group footer and suppress the details and group header.

-LB