• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 646
  • Last Modified:

Is it possible to combine data from subreports in Crystal Reports 10?

This may be impossible, but I'm holding out hope...

I'm trying to report on project activity for my team. Each project record could have a related record in any of the following three tables: Notes, Tickets, Tasks.

Right now, the main report I have is grouped by project ID. Then I have three Details sections pulling in subreports for the Notes, Tickets and Tasks for each project. Each subreport is sorted by Create Date.

This is working alright, but what I would love to be able to do is to see all activity (Notes, Tickets, Tasks) for each project listed in chronological order, but I can't think of any possible way to do this.

I know this is a shot in the dark, but any guidance would be much appreciated.

Cheers,
0
kingsmantone
Asked:
kingsmantone
  • 4
  • 3
  • 2
2 Solutions
 
James0628Commented:
This is not something I usually do (link to different tables to pull separate sets of similar data), but can you just link the project table to the Notes, Tickets and Tasks tables in the same report, pulling in all 3 at once?  I have the feeling that it may not be that simple, but thought I'd ask.  Like I said, it's not something that I normally do, and I really can't make up my mind if that should work or not.

 If that won't work, the next best thing would be to create a query that performs a separate query on each table and uses UNION [ALL] to combine the results into one dataset.  I'd use a stored procedure, because that's what I'm used to, but it could be a db view, or a CR command.

 The only way to sort the data from your subreports would be to have each subreport save the data that it reads in shared variables, presumably arrays, and then have the main report, or another subreport, sort the data in the arrays by the date and output the sorted data.  If the subreports aren't reading too many records and fields, that could be done, but as the number of fields and records goes up, it's likely to get real complicated real fast.

 James
0
 
Zlatko KuzmanovskiprogrammerCommented:
Hi,
This is how it should be done:

- In the main report create "On-demand subreport" subreports for each  Notes, Tickets and Tasks.
- Right click on each newly created subreport and "Change SubReport Links..."
- Create subreport links like this: rptMain.ProjectID = subReport.ProjectID

When the main project is executed, it will present a link to click and show the requested subreport object (drill-down) in a new window-tab.

Regards,
Zlatko.
0
 
PCIIainCommented:
To have the details listed in chronological order, you need to create a report/subreport based around a command.

The command god something like.

select notes.datefield as sortdate, notes.*, tickets.*, tasks.*
from notes
left outer join tickets on 1=0
left outer join tasks on 1=0
union all
select tickets.datefield as sortdate, notes.*, tickets.*, tasks.*
from tickets
left outer join notes on 1=0
left outer join tasks on 1=0
union all
select tasks.datefield as sortdate, notes.*, tickets.*, tasks.*
from tasks
left outer join tickets on 1=0
left outer join notes on 1=0

the joins on 1=0 are deliberate, a union requires all the columns to be the same, and this allows the database to work out what the field names are without fetching any data.

A more efficient version (for the database fetch) is if you actually specify what you want fetching.
Assuming notes, tickets and tasks all have a staff member associated with them you could go wiith.

select notes.datefield as sortdate, notes.staffmember as staff....
from notes left outer join .....
union all
select tasks.datefield as sortdate, tasks.staffmember as staff....
from tasks left outer join .....


0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
James0628Commented:
It's hard to say.  I posted a couple of possibilities.  If nothing else, the new query idea should work.  And PCIIain posted more details for creating a new query.  There's no way to know for sure if any of the suggestions would have worked for the OP, but the new query idea is pretty solid.  I guess split the points between PCIIain and myself?

 James
0
 
Zlatko KuzmanovskiprogrammerCommented:
Hi,
It is obvious that my solution is the most clean and easy one.

Zlatko.
0
 
PCIIainCommented:
Except that on demand subreports only work for live viewing of the report, and are of no popint if the OP wants to print it.

I'm with James.
0
 
James0628Commented:
 Zlatko,

 The whole point was that the OP wanted to sort the records from the 3 subreports chronologically, which means that the records from all 3 subreports have to be combined in some way.  Your post did not address that at all.

 James
0
 
Zlatko KuzmanovskiprogrammerCommented:
The three subreports can be created, maintained and displayed separately (as needed).

No problem, let the points go to James0628 and PCIIain.
0
 
James0628Commented:
> The three subreports can be created, maintained
 > and displayed separately (as needed).

 That's the whole point.  In this case 3 subreports really won't work, because he wanted to sort the records from all 3 subreports chronologically.  In order to do that, the records that are in the subreports have to be combined into one data set.  As mentioned in my first post, you could possibly save the necessary data from the subreports in variables and sort the values in the variables, but unless you're dealing with a very limited data set, that's going to be very cumbersome, if not impossible.  Apart from that, there's no way to use 3 separate subreports (on-demand or not) and have the records from all of them sorted together.

 James
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now