Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Record Selection and Subreports Post Reply Post New Topic
Author Message
aeckaren
Newbie
Newbie
Avatar

Joined: 24 Jul 2012
Online Status: Offline
Posts: 26
Quote aeckaren Replybullet Topic: Record Selection and Subreports
    Posted: 14 Jan 2015 at 10:26am
My main report is an order acknowledgement. It contains a subreport which looks at an audit file and returns the time the order was last changed. The audit file could have any number of records for each individual order, but I'm only interested in the latest record.

I can display the subreport on the main report and it's linked by order number. Everything works perfectly.

The problem is that I want to select only those orders that have had changes within the past 30 minutes (for example). I can share the variable from the subreport (with the time calculation) onto the main report, but I can't filter the main report to select those records.

Any suggestions on how to accomplish this? I can't link the audit file directly onto the main report because it causes duplicates for all the line items on the acknowledgement (because the audit file can have multiple copies of the same order number).
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Jan 2015 at 11:13am
use a command (or stored proc or sql view)  to select the only the max datetime per ordernum from your audit log and join that result to your main report (or incorporate the whol edata pull into into the command). eliminates the subreport entirely.

Edited by DBlank - 14 Jan 2015 at 11:14am
IP IP Logged
aeckaren
Newbie
Newbie
Avatar

Joined: 24 Jul 2012
Online Status: Offline
Posts: 26
Quote aeckaren Replybullet Posted: 15 Jan 2015 at 5:33am
Thanks for the suggestion. This will be my first stored procedure but I will give it a try. Smile  
IP IP Logged
Vimal Nair
Newbie
Newbie
Avatar

Joined: 29 Dec 2014
Location: United Arab Emirates
Online Status: Offline
Posts: 26
Quote Vimal Nair Replybullet Posted: 27 Jan 2015 at 7:01pm
Write an Sql using WITH clause or case condition. Or take the order table as a subquery join with the time condition in where clause which should be inner join to the main query.
Born To Live
IP IP Logged
aeckaren
Newbie
Newbie
Avatar

Joined: 24 Jul 2012
Online Status: Offline
Posts: 26
Quote aeckaren Replybullet Posted: 28 Jan 2015 at 3:31am
Good suggestion. I did get it to work out but I ended up doing it a bit differently. I wrote an SQL statement and used it as a command. I'm not sure if this is the most efficient way, but it worked for me. I have an IBM iSeries Server. My statement in case anyone else needs it as an example:

SELECT ROW_NUMBER() over (partition by tb.ordn40 order by tb.adat40, tb.atim40 desc) as COUNT, tb.cono40, tb.adat40, tb.ordn40, tb.atim40

FROM osld1f3.oep40a as tb

WHERE tb.arct40=2 and tb.aprg40='OE340' and tb.actf40<>'X'


IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.014 seconds.