How to display multiple dates in one row 3

[choochoowinn2009]

Hi,

Would someone give me some ideas how to go about this? I need to show my data all in one row, with only date1, date2 and date4 showing only. It doesn't matter how many dates an ID has.

ID Date
3 7/2/2011
3 7/6/2011
3 7/7/2011
3 7/10/2011
9 7/1/2011
9 7/3/2011
9 7/8/2011
9 8/1/2011
11 8/3/2011
11 8/4/2011
30 7/15/2011
30 7/28/2011
30 8/1/2011
30 8/2/2011
30 8/3/2011
30 8/4/11


My output should look like this:

ID Date1 Date2 Date4
3 7/2/2011 7/6/2011 7/10/2011
9 7/1/2011 7/3/2011 8/1/2011
11 8/3/2011 8/4/2011
30 7/15/2011 7/28/2011 8/2/2011


Thank you for your help!

C

 

[lbass]

Insert a group on ID and then create these formulas:

//{@reset} for the ID group header:
whileprintingrecords;
datevar dt1;
datevar dt2;
datevar dt4;
numbervar cnt;
if not inrepeatedgroupheader then (
dt1 := date(0,0,0);
dt2 := date(0,0,0);
dt4 := date(0,0,0);
cnt := 0
);

//{@accum} for the detail section:
whileprintingrecords;
datevar dt1;
datevar dt2;
datevar dt4;
numbervar cnt := cnt + 1;
numbervar maxcnt := count({table.date},{table.ID});
if cnt = 1 then
dt1 := {table.date};
if maxcnt >= 2 then (
if cnt = 2 then
dt2 := {table.date}
));
if maxcnt >= 4 then (
if cnt = 4 then
dt4 := {table.date}
));

Drag the groupname into the group footer, and add these formulas to the footer:

//{@dt1}:
whileprintingrecords;
datevar dt1;

//{@dt2}:
whileprintingrecords;
datevar dt2;

//{@dt4}:
whileprintingrecords;
datevar dt4;

Suppress the group header and details section.

-LB

 

[MCuthill]

choochoowinn2009,

At a high-level, I believe you need three date variables conditionally set based on a counter that resets at the "ID" group level.

I don't have any data similarly structured to test on, so there may be some fine-tuning required.

{@Variable_RESETS} -- place this in the ID Group Header

WhilePrintingRecords;
NumberVar Counter:=0;
DateVar Date1;
DateVar Date2;
DateVar Date4;

Note: not sure how one should reset the Date variables... if you can initial as "" or if a default date is needed.

{@Variable_SET} -- place in detail level

WhilePrintingRecords;
NumberVar Counter:=Counter+1;
If Counter=1 THEN Date1:={YourDateField} ELSE
If Counter=2 THEN Date2:={YourDateField} ELSE
If Counter=4 THEN Date4:={YourDateField}

{Variable_DISPLAY_Date1} -- in ID group footer

WhilePrintingRecords;
DateVar Date1;

{Variable_DISPLAY_Date2} -- in ID group footer

WhilePrintingRecords;
DateVar Date2;

{Variable_DISPLAY_Date4} -- in ID group footer

WhilePrintingRecords;
DateVar Date4;

Note: If the resets require a default date (ie: Jan 1 1900), setup IF statements in the "Display" formulae to handle dates that are still the default at the end of a group.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

*chuckles*

Ya... what LBass said.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[choochoowinn2009]

Thank you for all your help!

I am still struggling with this, because I have 2 other groupings that I need to add to this.
Here's my data:

ID Company Services Date
3 C1 S1 7/2/2011
3 C2 S2 7/6/2011
3 C2 S1 7/7/2011
3 C1 S2 7/7/2011
3 C1 S1 7/10/2011
3 C2 S1 7/19/2011
3 C1 S1 7/20/2011
3 C1 S1 7/25/2011

My results should look like this:

ID Company Name Services Date1 Date2 Date3 Date4
3 C1 S1 7/2/2011 7/10/2011 7/20/2011 7/25/2011
3 C1 S2 7/7/2011
3 C2 S2 7/6/2011
3 C2 S1 7/7/2011 7/19/2011

I need to have 3 groupings:
1) by ID
2) by company
3) by services

Where would I put my results in? in which footer?

Please help!!!

C

 

[lbass]

The services footer.

But have your requirements changed? Initially you said you only wanted to display the first, second and fourth services dates, and that is what my formulas did.

-LB

 

[choochoowinn2009]

First, I thought I only need to show date1, date2 and date4 only. Now, I need to show the first four dates from any services.

Thanks!

 

[lbass]

So you are all set now?

-LB

 

[choochoowinn2009]

LB - No, it's not working... I'm still trying to fiddle around with the footer.... for some reasons, the dates got onto different services.... any other suggestions?

 

[lbass]

You are not providing enough information to allow for troubleshooting. What do you mean by "fiddling with the footer"? What results are you getting and how are they different from what you expect? Show sample data of both.

-LB

 

[choochoowinn2009]

If I'm only looking at company 1 and company 2, the results should look like this:
ID Company Services Date1 Date2 Date3 Date4
3 C1 S1 7/2/2011 7/10/2011 7/20/2011 7/25/2011
3 C2 S2 7/6/2011


but my results are looking like this:
ID Company Services Date1 Date2 Date3 Date4
3 C1 S1 7/2/2011 7/10/2011 7/20/2011 7/25/2011
3 C2 S2 7/6/2011 7/10/2011 7/20/2011 7/25/2011

I'm putting the results at the footer of the services. Am I supposed to suppress the detail section and the header?

Please help!

 

[lbass]

Try moving the reset formula to the Services group header. Yes suppress all other sections.

-LB

 

[choochoowinn2009]

I'm looking at my variable_set formula, and the dates are coming in correctly for the services, but when the results are not coming in correctly on the footer. It picked up the other date from the other service. I wonder why...

 

[lbass]

If move the reset formula, that won't happen.

-LB