How to share multiple information from the main report to a subreport

[TryingToLearn101]

Hi all,

I am currently on Crystal XI professional. Here's my issue: I have a main report with a list of dates when that item is certified. Let say item ABC has 2 dates there. Now, I have a subreport that shows bunch of transactions in chronological order for that same item. What I need to get is to have the certification date "inserted" into the transaction dates accordingly.

Let say I have 2 cert dates 1/1/2008 and 1/1/2009 and let say I have 4 transaction dates: 6/1/2007, 6/1/2008, 4/1/2009, 6/30/2009. What I need to have is this:

6/1/2007
1/1/2008 - cert date
6/1/2008
1/1/2009 - cert date
4/1/2009
6/30/2009

I know how to share information to a subreport per se. But It only took the last cert date. I don't know how to take all cert dates and move them into the subreport.

Any input is greatly appreciated. I hope I am explaining this clearly.

Thanks in advance.

 

[lbass]

I don't think there is a way to get the fields to sort together like that unless you use a command for your datasource instead of your current approach. In a command you could use a union all statement which would merge the date fields into one so that you could sort them appropriately. Your command would look something like:

select 'tablename' as tabletype, `table`.`date` as date
from `table`
union all
select 'certtable' as tabletype, `cert`.`date` as date
from `cert`

You can then sort on {command.date}. If you need to know which date is from which table, you can use {command.tabletype} to distinguish them.

-LB

 

[TryingToLearn101]

Thank you lbass - I thought of creating a table view after posting this using UNION ALL. At least, you just gave me a confirmation that is indeed the route I need to take. Thanks again.