Log in to AnswerLog in to Follow
UserImage.jpg
Leon Holmes
26Views
13Comments
Solved
How to create a master/detail report
Question
Traditional Web
Application Type
Traditional Web
Service Studio Version
11.14.13 (Build 59808)

I am using the Forge module, Ultimate PDF, which works very well.  I need help creating a report with control breaks, which are common in standard report writers.

Here is a simple example of what I am trying to achieve:

Let's say I have 3 entities:

CustomersOrdersOrderLines
CustIdOrdersIdOrderLinesId
CustNameCustIdOrdersId
AddressOrderDateProductId
DelAddressDel DateQuantity


Price

If I use an aggregate in which I join these 3 entities, I get a report looking like this:

REPORT(1st 2 columns from Customers, next 2 columns from Orders, next 3 columns from OrderLines, last column is calculated)
CustomerDelivery AddressOrder NoOrder DateProduct IdQuantityPriceOrder Value








ABC 12 Smith Str, Knoxville12421-03-20224SDE562256.50513.00
ABC 12 Smith Str, Knoxville12421-03-20223RWED641 084.004 336.00
ABC 12 Smith Str, Knoxville12421-03-2022DWA34532 598.507 795.50
DEFUnit502, Bluedowns12522-03-2022UYTY675505.502 527.50
DEFUnit502, Bluedowns12522-03-2022OIU341456.54456.54
DEFUnit502, Bluedowns12522-03-2022IOIU3418 574.108 574.10
DEFUnit502, Bluedowns12522-03-2022DWA34522 598.505 197.00
DEFUnit502, Bluedowns13528-03-20224SDE5610256.502 565.00
DEFUnit502, Bluedowns13528-03-20223RWED621 084.002 168.00
DEFUnit502, Bluedowns15131-03-2022UYTY672505.501 011.00
DEFUnit502, Bluedowns15131-03-2022OIU3412456.545 478.48
DEFUnit502, Bluedowns15131-03-2022IOIU3438 574.1025 722.30
GHI109 Hill Str, Industria15431-03-2022NJG6546 845.0027 380.00
GHI109 Hill Str, Industria15431-03-2022SDFDE32564.501 129.00
GHI109 Hill Str, Industria15431-03-2022DWA34522 598.505 197.00








What I need is a report that looks like this (ignore the formatting):

CustomerDelivery AddressOrder NoOrder DateProduct IdQuantityPriceOrder Value








ABC 12 Smith Str, Knoxville12421-03-20224SDE562256.50




3RWED641 084.00




DWA34532 598.5012 644.50
DEFUnit502, Bluedowns12522-03-2022UYTY675505.50




OIU341456.54




IOIU3418 574.10




DWA34522 598.5016 755.14


13528-03-20224SDE5610256.50




3RWED621 084.004 733.00


15131-03-2022UYTY672505.50




OIU3412456.54




IOIU3438 574.1032 211.78
GHI109 Hill Str, Industria15431-03-2022NJG6546 845.00




SDFDE32564.50




DWA34522 598.5033 706.00

How can I accomplish this in OutSystems?

Any ideas would be appreciated.

0
0
13 Apr
2020-01-24 05-26-13
Vikas Sharma
Solution

For static value you can use currentRowNumber. Like below. If still face any issue let me know .


See solution in context
0
0
14 Apr

Yes, thank you Vikas.  This is the syntax I was looking for but could not find.  It is working!

Much appreciated.

Leon

0
0
14 Apr

Thanks. But be careful if sometime you are going to use current+1. In that case it will throw exception for last element in this list.

0
0
14 Apr

I think its not issue of  pdf report. Its the issue of aggregate or data which you want to fetch from database. 

So as per your requirement you want multiple orders and products under those orders for single customer. I would suggest to go for SQL query rather than to aggregate to get the desired output.


0
0
13 Apr

if you want to use 3 aggregates with join in that case also you can do one workaround and on table you can apply some conditions to print the value.

Like for below records :

DEFUnit502, Bluedowns12522-03-2022UYTY675505.50




OIU341456.54




IOIU3418 574.10




DWA34522 598.5016 755.14


13528-03-20224SDE5610256.50


You can mention some temp variables to store the last process value. Like for first record customer is DEF then while entering the customer name in second row. You can compare if its equal to previous customer or not. If equal then just put "" in the cell other wise take new value. Now this new value will become last process entry. In this you need to sort results by customer name and so on. Same logic can be applied for other columns.

0
0
13 Apr

Thank you Vikas.  

Yes, I am using an SQL. When I speak of a report, it is a report on the screen using a Table Records widget.  I thought I could eliminate the duplicated data by testing if the current row of the Table Record expression is equal to the previous row.  My problem is I do not know how to access the previous row.  If you can tell me how to do that I would be grateful.

Leon

0
0
13 Apr

There may be many approaches :

1. Assume you take 2 local variables like PreviousCusotomerName or PreviousCustomerId and PreviousOrderId. Now when enter first record, values will be like this : 

PreviousCustomerName = DEF , PreviousOrderId = 125

Now when writing second record, we apply condition like this If Current Customer Name == PreviousCustomerName, leave blank other wise write value in customer Name column. Same logic can be follow for customerAddress. Same logic we can follow for orderId.

Also in a record list we can access, next or previous record like this : 

current record : list.current.customerName

next record : list[current+1].customerName

previous record : list[current-1].customerName

But in this you need to keep the track of list length, otherwise you will get indexOutofBound exception.

Hope this helps, if still facing difficulty let me know.

Regards

0
0
14 Apr

Replying to Vikas Sharma's comment on 14 Apr 2022 00:00:53

Vikas,

All I know is to use the following statement on the expression for the CustName (for example):

If(CustOrdersTable.List.BOF, CustOrdersTable.List.Current.SqlCustOrders.CustName,

    If(CustOrdersTable.List.Current.SqlCustOrders.CustName = 

        CustOrdersTable.List.Current-1.SqlCustOrders.CustName, " ",             CustOrdersTable.List.Current.SqlCustOrders.CustName))


But this does not work because of the "-1"


Are you suggesting using JavaScript to accomplish this?

Thanks

Leon

0
0
14 Apr

you need to pass it as a static value. Means if current is 5. 

Then current-1 = 5-1 = 4

or 


0
0
14 Apr

Have you managed to do it ?

0
0
14 Apr

Vikas,

This is my problem.  I understand the logic but don't know how to implement your suggestion in OutSystems.

I have an expression in a table record widget.  All I know is that I can set the value of that expression using an if statement in the expression editor. But I don't know how to access the previous record in that if statement.  If this is the place to do it, please could you give me a sample statement.

Thanks

Leon 

0
0
14 Apr

Replying to Leon Holmes' comment on 14 Apr 2022 18:35:29

I have already given one sample expression screenshot. Please check my last reply.

0
0
14 Apr
2020-01-24 05-26-13
Vikas Sharma
Solution

For static value you can use currentRowNumber. Like below. If still face any issue let me know .


0
0
14 Apr

Yes, thank you Vikas.  This is the syntax I was looking for but could not find.  It is working!

Much appreciated.

Leon

0
0
14 Apr

Thanks. But be careful if sometime you are going to use current+1. In that case it will throw exception for last element in this list.

0
0
14 Apr
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.