With SharePoint web services and dataview web part, You can aggregate the lists/document libraries/etc between sub-sites/site collections/web applications/even across forms.
So, Here I'm going to walk through how to achieve this using a simple example. Lets say I've two sites namely: Site1 and Site2 and I've a Announcement List 1 in Site 1 and Announcement List 2 in Site2.
Now, What I want to create is: in my portal, I would like to create a consolidated view which has both the announcements from site 1 and site2. Follow the below steps.
1. Open the SharePoint designer and Create a Aspx page
2. Go to Dataview >> Click on "Manage data sources"
4. Enter the web service URL: http://sitename/_vti_bin/lists.asmx , click on "Connect Now" then, select the GetListItems Method. Enter the List Name. Supply the Login parameters.
5. Do the same for all other lists you would like to connect. Finally you will have the data sources like this:
6. Now expand the "Linked sources" in data source Library and add the web services sources you have created.
7. Insert a dataview into the Aspx page you created.
8. Drag and Drop the z:row From Linked source to Dataview webpart.
9. Finally you will get the merged data.
Here is my another post on comparing third party tools for the same purpose:
List Aggregation/Rollup In SharePoint -Third Party Products Comparison
You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.