Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Return Value from 1 record to display on another Post Reply Post New Topic
Author Message
Jase1980
Newbie
Newbie


Joined: 16 Mar 2011
Online Status: Offline
Posts: 3
Quote Jase1980 Replybullet Topic: Return Value from 1 record to display on another
     Posted: 16 Mar 2011 at 4:15pm

Hi all,

 

I have been looking all over the net for something similar to this but haven’t been able to find anything that can perform this task in a logical, simple way. Hoping I have come to the right place for help!

 

Basically we have 5 different Closed statuses which orders can be closed in. They are;

Closed

Close OSL

Close New ISL

Close Critical ISL

Close Reopen ISL

 

We also have about 10 different Queues which orders get assigned and processed  through.

 

The problem is that I can only currently report on the volume of orders split by Queue which have the status of “Closed”. But I also need to be able to perform reporting for the other 4 Closed status’ by Queue, currently I am unable to do this.

 

Our database currently records the following data for each transaction which takes place for all the orders; Transaction Date, Transaction Time, Order Number, Status, Queue.

 

The problem I have is that when an Order is put into a “Closed” Status by a representative the system automatically and simultaneously flags the Order with one of the other 4 Close status’ (meaning it will flag the order with either Close OSL, Close New ISL, Close Critical ISL, Close Reopen ISL). But it does not track the Queue that this system action took place in. (see the “No Value” text returned under Queue.

 

This is how the data currently looks

Trans Date        Trans Time        Order Number    Status               Queue

27/01/2011        9:05am             110115-000698  Closed               Mobile Tech Support

27/01/2011        9:05am             110115-000698  Close OSL          No Value

27/01/2011        9:07am             110115-000697  Edit                   Mobile Tech Support

28/01/2011        10:11am           110120-000105  New                  Mobile Tech Support

28/01/2011        1:05pm             110115-000697  Closed               LAD Support

28/01/2011        1:05pm             110115-000697  Closed New ISL  No Value

 

So I am hoping that there is some sort of “if statement” or code that can grab/return the Queue the incident was Closed in by a rep and put that Queue next to the Close OSL, Close New ISL, Close Critical ISL or Close Reopen ISL status by matching the Date, Time and Order Number the rep performed the Closed action in.

 

Is this possible? Any assistance is greatly appreciated.

 

Thanks,

 

Jason
IP IP Logged
uzair.awan1
Newbie
Newbie
Avatar

Joined: 16 Mar 2011
Location: Pakistan
Online Status: Offline
Posts: 1
Quote uzair.awan1 Replybullet Posted: 16 Mar 2011 at 8:50pm
I had also this problem while i was working on MRP reports i had assigned this status to a no and den apply in the formula i mean my status should be sort like dis

sale 1
purchase 2
etc....

den i have sort out dese from record sort expert i will send you all code tomorrow . try out this .... use ur mind i vl send dnt wrry :pLOL
NA
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 17 Mar 2011 at 3:35am
if you wrote a stored proc, you should be able to do this with relative ease.  If you are joining to the tables directly, I would think that it would depend on your groupings. 
 
If you are grouping by Queue, the only solution that comes to mind is a subreport that will find the value (but this will cause additional hits to the database, which will make the report run slower).
 
If you are grouing on some other value, if you set the sort order correctly, when you see the 'Closed' you can look at the NEXT() record and use it's value.
 
HTH
IP IP Logged
Jase1980
Newbie
Newbie


Joined: 16 Mar 2011
Online Status: Offline
Posts: 3
Quote Jase1980 Replybullet Posted: 17 Mar 2011 at 11:28am

Thanks for the replies guys,

 

All this data is stored on the one table so that should keep things simple. I am not performing any groupings on any fields. Reason being is that I was under the impression I should be able to accomplish this task with some IF statements. Where a match is performed on the Closed Transaction which takes place by a representative to the Closed Transaction left by the system. I thought the match could take place against the Date, Time and Order Number fields and would grab the Queue the Closed Transaction which was performed in by the representative and display this Queue on the Closed Transaction that the system generated (as the system generated transaction takes place simultaneously hence why I thought a match against these fields would instruct crystal on which Closed Transactions to match against.

 

If I have to go down the path of a stored procedure, what would that look like? I don’t mind a solution taking a while to run, I rather the returned results be accurate than not.

 

Any help on how I might do this would be great
IP IP Logged
Jase1980
Newbie
Newbie


Joined: 16 Mar 2011
Online Status: Offline
Posts: 3
Quote Jase1980 Replybullet Posted: 17 Mar 2011 at 6:05pm
Also I should ad that I am required to use the resulting data in a Cross Tab.
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.