Merging Detail Records in Actuate

[andykeleher]

I am writing Actuate Reports for a Siebel 7.8 implementation.

I have been asked to do something that I haven't done before.
We would like to run a master-detail report with multiple child objects,
however instead of displaying the objects sequenitally one after the other
we would like to merge all the un-related ojbects into a chronilogical list
based on their Created date.

For example, the parent would be Account, and we would have all the notes and activities
related to the account displayed in a list based only on their created date.
So you would have

Account
Note
Activity
Activity
Note
Note
Activity

Could this be achived in actuate, using lists?

Any help would be greatly appreciated.

Many Thanks

Andy

 

[h0h0h0]

I would do this in SQL if possible. Can you write a sql Statement that created a union of the two sets of objects (or more) and used the create_date as the grouping mechanism.


SELECT CREATEDATE, NOTE, 'n' as type
from NOTES
UNION
SELECT CREATEDATE, activity, 'a' as type
from ACTIVITY

 

[rkin005]

Andy,
As h0h0h0 suggests, it'd be easier to do in SQL with a UNION and ORDER BY, but you probably want to do this using Siebel datasources so you don't need to access the db directly.

This is how I'd do it.

First, structure the report definition in Siebel Tools like a normal master detail report with Account as the Business Component and two subreports: Notes and Activity. I'd then create a quick simple Master-Detail report in Actuate to ensure your data is coming through correctly, without doing any merge of Notes and Activity. ie have separate ssRpt objects for Account, Notes and Activity.

Once you're happy the data is coming through to Actuate correctly, you want to make the report merge the Notes data and the Activity data together and display it using a single ssRpt (which is a child of the Account ssRpt). You also want to sort the data, which can be done using an ssMemoryDataSorter on the merged data.

I'd structure the report like this:

- YourReportTopLevelObj
- Content - rptAccount (ssRpt class)
- DataStream - ssAccount (generated by Siebel Tools)
- Content - ssGrp1 (ssGrp class. Set property Key = [RowNumber])
- Before - frmAccountHeader (ssFrm class, with fields to show Account details)
- Content - rptCombinedNotesAndActivities (ssRpt class)
- DataStream - sortCombinedNotesAndActivities (ssMemoryDataSorter class)
- Input - fltrMergeNotesAndActivities (ssMultipleInputFilter class)
- Input - ssAccount_Activity_1 (generated by Siebel Tools)
- Input - ssAccount_Notes_2 (generated by Siebel Tools)
- DataRow - CombinedNotesAndActivitiesDataRow (DataRow class)
- Content - frmCombinedNotesAndActivitiesContent (ssFrm class, with fields to show details)

The tricky bits are:
* Create CombinedNotesAndActivitiesDataRow as a DataRow with the fields you want to display on the report. ie go to the Variables tab and add all the necessary fields.
* Create a global variable on the top-level component called MergedNotesAndActivitiesList of type AcSingleList (or your favourite list class)
* Override the OnRow() method of rptAccount to set MergedNotesAndActivitiesList to a new AcSingleList if it's Nothing, otherwise to call RemoveAll() to clear the list (so that subsequent Account records don't get the merged data of the previous ones).
* Override the Start method of fltrMergeNotesAndActivities to retrieve all data from the Activity and Notes datastreams. For each Activity and each Notes row you should create a new CombinedNotesAndActivitiesDataRow, set all the fields you want, then add to the end of MergedNotesAndActivitiesList.
Code will look something like this:

****
Dim daNote as AcDataAdapter
Dim daActivity As AcDataAdapter
Dim rowActivity As ssActivityDataRow
Dim rowNote As ssNoteDataRow
Dim rowCombined As CombinedNotesAndActivitiesDataRow

Set daActivity = InputAdapters.GetAt(1)
Set daNote = InputAdapters.GetAt(2)

Set rowActivity = daActivity.Fetch()
Do While Not rowActivity Is Nothing
Set rowCombined = NewDataRow()
rowCombined.XXXX_field = rowActivity.XXXX_field 'for each field you want
MergedNotesAndActivitiesList.AddToTail( rowCombined )

Set rowActivity = daActivity.Fetch()
Loop

Set rowNote = daNote.Fetch()
Do While Not rowNote Is Nothing
Set rowCombined = NewDataRow()
rowCombined.XXXX_field = rowNote.XXXX_field 'for each field you want
MergedNotesAndActivitiesList.AddToTail( rowCombined )

Set rowNote = daNote.Fetch()
Loop
*****

* Override the Fetch method of fltrMergeNotesAndActivities to return MergedNotesAndActivitiesList.GetAt( Position ).
Code will look something like:
****
Set Fetch = MergedNotesAndActivitiesList.GetAt( Position )
AddRow( Fetch )
****

* Override the Compare() method of sortCombinedNotesAndActivities to compare the created by date.
Code will look something like:
****
Dim rowCombined1 As CombinedNotesAndActivitiesDataRow
Dim rowCombined2 As CombinedNotesAndActivitiesDataRow
Set rowCombined1 = row1
Set rowCombined2 = row2
Set Compare = CompareKeys( CDate( rowCombined1.CreatedDate ), CDate( rowCombined2.CreatedDate ) )
****

The explanation above assumes that you want to display the same data for Notes and for Activities, or at least you can merge the data into the same fields. ie you can have one ssFrm to display both. If that's not the case then I think you could implement something like what I've described, but in rptCombinedNotesAndActivities you'd conditionally create the appropriate ssFrm (I'd guess in the BuildFromRow() method) depending on whether the CombinedNotesAndActivitiesDataRow represented a Note or an Activity.

I hope this helps. Let me know if you want more details on any of these steps or if you have any problems setting this up.

- Rory

 

[andykeleher]

Rory

That is excellent and works a treat.

We I was adding the fields in the Fetch method I included a type for 'Notes' and 'Activites' which I then used a conditional component to split into the correct form. Works perfect.

Thanks for your help.

Hey, while im on the Actuate subject. You don't know how to display the contents on a Siebel attachment in a report do you.

I am writing a contact report which displays all the information about a particular contact. One of the requirements is to store a jpeg photo as an attachment, and display this in the report. We can use the type field on the attachment BC to determine it is a photo.

Only issue is that the attachment is stored as a SAF file in the siebel file system and can't be linked too directly.
Any ideas?

This ones doing my head in!!!

Thanks

Andy

 

[rkin005]

sorry, haven't had the fortune to look at retrieving attachment files.
Good Luck.
- Rory

 

[gdwood]

I can tell you that we do just what you are asking but it's not easy.

First we create a link on the report on the appropriate item. Then we create the link to pass values via a javascript function.

That javascript function takes the values and then passes them into a url with a few more values and displays an attachment that we need in a popup. The URL also has a JSP behind it to instruct it how and what to open.

I know it's not much, but it's an idea

 

[andykeleher]

at gdwood

you couldn't provide me with some sample code or an example rod file could you?

sounds great

Andy

 

[gdwood]

I can send the code behind the rod and maybe some of the javascript function, but nothing of the url and the javascript behind that. do you still want to see the 'front' end stuff?