display first subreport

[tbear6602]

Using Crystal Reports 10 on Oracle 9i.


I have created a report that passes a date and a system ID to a subreport to for processing.

In the subreport, I have created a (clumbsy I admit) record select formula that basically searches 30 prior to the record and to the end of the month the record occurs in. It is as follows:

local NumberVar EndDate;
local StringVar Temp;
if (Month ({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE})=1 or
Month ({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE})= 3 or
Month ({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE})= 5 or
Month ({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE})= 7 or
Month ({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE})= 8 or
Month ({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE})= 10 or
Month ({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE})= 12)
then EndDate := 31
else if (Month ({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE})= 4 or
Month ({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE})= 6 or
Month ({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE})= 9 or
Month ({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE})= 11)
then EndDate :=30
else EndDate :=28;

Temp := CStr(Year({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE}),0,"") + '/' + CStr(Month({?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE}),0)+ '/' + CStr(EndDate,0);


{TROUBLEINSTANCETABLE.FAILUREENDDATE} in {?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE}-30 to DateValue (Temp)
and
{TROUBLEINSTANCETABLE.TID} = {?Pm-@GroupHeader}

-- @GroupHeader is literally the Group Header from the main report pushed into a formula that is passed to the subreport


However, what I'd like to do is change my main report so that it only passes the earliest record to the subreport and does the manipulation.

I hope this makes sense. I'd even love to do it without doing the subreport buy I can't get it to work without one.

Can anyone help me out with this?

Thanks!

 

[synapsevampire]

Why 30 days prior from the date passed and not the actual month before?

Anyway, you mix terms and probably confuse things.

You want 30 DAYS prior to the parameter passed, and a month afterwards, not 30 prior to a record.

Try simplifying:

(
{table.date} >= {?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE}-30
and
{table.date} <= dateadd("m",1,{?Pm-TROUBLEINSTANCETABLE.FAILUREENDDATE})-1
)

The first states subtract 30 days from the parameter, the second statement says to go to the first of the next month and subtract one day.

-k

 

[tbear6602]

Well, the reason I'm doing this way is kinda hashed.

Basically, we have a definition of a chronic problem:

3 or more incidents occurring in a rolling 30 day window. Once a system crosses the 3 incidents it is to be counted into a monthly total, so to simplify things, I'm trying to go back 30 days from the earliest record in the month up to the end of the month and count the number of records to see if it crosses the threshold.

The reason I can't do it by month is the following senario:

System A has 2 incidents in January and one in Feb within 30 days of each other - by my definition, this is chronic, but won't be picked up if I total by month.

Does this clarify for you?

I can get this working going forward from today, but I need to apply this regressively to the past year. That's where the difficulty arrives.

 

[synapsevampire]

My post returns data 30 days prior to the parameter, and one month afterwards.

Since your requirements are still unclear to me, try posting technical information:

Example data (as it appears in the table(s)
Expected output (based on the example dtaa

-k

 

[tbear6602]

This is a sample of the table data:

FAILUREENDDATETIME	TICKETNUMBER	TID
12/31/2004 19:39	026567A		System A
12/31/2004 21:07	026589		System A
01/01/2005 06:45	008101		System A
01/01/2005 07:30	008102		System A
01/01/2005 08:17	026580B		System B
01/01/2005 12:00	026560A		System C
01/01/2005 13:05	026580C		System C
01/01/2005 16:50	008109		System D
01/02/2005 06:15	026606		System D
01/02/2005 14:55	008115		System D
01/03/2005 01:17	026620		System D
01/03/2005 01:40	008127		System D


And this is what I would like to see:

January YYYY [GH1]
 System A IncidentCount: 4 [GH2A]
FAILUREENDDATETIME	TICKETNUMBER	TID [GH2B]
12/31/2004 19:39	026567A		System A [Details]
12/31/2004 21:07	026589		System A
01/01/2005 06:45	008101		System A
01/01/2005 07:30	008102		System A

 System D Incident Count:5 [GH2A]
FAILUREENDDATETIME	TICKETNUMBER	TID [GH2B]
01/01/2005 16:50	008109		System D [Details]
01/02/2005 06:15	026606		System D
01/02/2005 14:55	008115		System D
01/03/2005 01:17	026620		System D
01/03/2005 01:40	008127		System D

Currently I'm accomplishing this by taking each record in the proper month (i.e. January) pulled by the main report and passing the date and TID as parameters to the subreport to pull the window (previous 30 days) and I had to exetend the window to the end of the month because otherwise the report was pulling duplicate data. It still is to a certain extent because of the multiple occurrences within the main report.

Is there a way to limit this to the first occurrence of each TID so that it returns the way I'd like it to look? or do I just need to supress the details?