Start Group with Specific Value - Need Help! (1 Viewer)

[kmbunz1]

Start a Group with a Specific Value - Need Help!

I am creating a report with has the following data fields in Crystal Reports XI:

ID
Name
Program
Enter Date
Exit Date

I have a prompted parameter to pull anything from the Enter Date to the Exit Date. I also have the report Grouped by ID Number

Each ID may have multiple entries with many different programs. Especially if my parameters are for over a month. So I may get some data like this:

001
001 - John C. - ARP - 7/1/2010 - 7/05/2010
001 - John C. - DTX - 7/06/2010 - 7/15/2010
001 - John C. - RHB - 7/16/2010 - 7/19/2010
001 - John C. - INT - 7/20/2010 - 7/31/2010

OR - some may just display with one field like this:

002
002 - Katie M. - RHB - 7/1/2010 - 7/15/2010

OR - some may display results like this with nothing related to the DTX Program:

003
003 - Doug H. - RHB - 7/1/2010 - 7/7/2010
003 - Doug H.- ARP - 7/8/2010 - 7/12/2010
003 - Doug H. - INT - 7/13/2010 - 7/15/2010
003 - Doug H. - BCB - 7/16/2010 - 7/19/2010


What I want my report to do is only pull the ID clients starting with the DTX value, and listing anything that have been entered after that date, and if they dont have the DTX value at all, have them supressed. So from the data I provided above, I would get results like these below:

001
001 - John C. - DTX - 7/06/2010 - 7/15/2010
001 - John C. - RHB - 7/16/2010 - 7/19/2010
001 - John C. - INT - 7/20/2010 - 7/31/2010

Any ideas?

 

[jzwp22]

I'm not familiar with Crystal Reports, but I'm hoping that you can write some SQL queries to extract info. Some of what you want to do can be done with a nested query. To pull only those ID's that have a specific program, the nested query would look like this:



SELECT ID, [Name], Program, [Enter Date], [Exit Date]
FROM yourtable (or datasource)
WHERE ID IN (SELECT Q2.ID FROM yourtable as Q2 WHERE Q2.Program="DTX")

The subquery (SELECT Q2.ID FROM yourtable as Q2 WHERE Q2.Program="DTX") pulls those ID's that are associated with the DTX program. Those results are then used as criteria in the main query.

In general, using the IN construct is not very efficient, but I don't see another way to do it in this particular case.



As to ordering the results such that DTX is shown first, may or may not be possible in Crystal Reports. If all of the programs are 3 letter codes and there are no codes that start with A, B or C (you did not include any in your example), then you can just set the order as alphbetical (ascending) based on the program code. If there are other programs that start with A, B or C then the issue is more difficult. I would probably approach it with a custom function in Visual Basic for Applications, but I don't know if Crystal has that capability; otherwise, you would have to alter the structure of the underlying table to assign a sequence number to each program code that you can use to conduct a sort.

 

[boblarson]

You can also set up custom groups in Crystal Reports, so you could go make a group for that field and set the one group to be DTX and then all others would either be in the other bucket (or you can select DISCARD other values).