Link Subreport with 2 unrelated date fields

[ncpridge]

I have 2 reports both based on the last 6 weeks. However, the main report is filtering date field {Referral__c.Referral_Date_Time__c} & the subreport is filtering date field {Last_90_Days_Activity_ALF_for_Crystal.Date} from a salesforce stored procedure. Is there a formula to convert both date fields to a common type so I can link the subreport? Because the way salesforce sets up their environment, its not possible to do this in one report.

Any help would be much appreciated!

 

[fisheromacse]

right click on the subreport, choose Change Subreport links, select the value from the main report ({Referral__c.Referral_Date_Time__c}) from the upper left pane (Available Fields), and add it to the upper right pane (Fields to Link), then from the dropdown near the bottom of the window (Select data in subreport based on this field), select the value in your subreport ({Last_90_Days_Activity_ALF_for_Crystal.Date})

 

[ncpridge]

Hi, thanks for the speedy response. however, that is not an option. 'subreport based on field' doesn't let me choose {Last_90_Days_Activity_ALF_for_Crystal.Date}, I'm guessing because the two tables are unrelated as well as the fields. I tried converting both {Last_90_Days_Activity_ALF_for_Crystal.Date} & {Referral__c.Referral_Date_Time__c} to similar fields using the formula cdate(totext(...) which allowed them to actually link correctly. However, now my data is wrong. The subreport data is a running total.

 

[lbass]

What is the datatype of each field? Is one a string? If so, how does it display? Or is one a datetime and one a date?

I also wonder whether you really want to link the two fields so that only matching dates show in the sub or whether you want to use the same range of dates. If the latter is the case, you could link on the two fields once converted to the same datatype (date), and then go into subreport->report->selection formula->record and edit the formula to that it matches the criteria used in the main report.

-LB