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
