effective date for 1

[John1Chr]

I have two tables and I am linking by CT Value. In Table 2 for each CT Value there can be multiple values per effective date. I want to pull in data without it doubling up into the detail of the report.
Table 2:
CT Value effectdate CT Attrib CT ATTrib Value
292635 7/1/2005 CITE2 007 003
292635 7/21/2011 CITE2 007 004
292635 7/1/2006 CITE3 111 012
292635 7/21/2011 CITE3 111 013

I want in a single row(record) for display purposes in the detail section of the report without doubling up:
CITE2 007 004 CITE3 111 013
I can easily pull in the two fields using the Maxeffect date formula but it causes two detail lines and all of my amounts for the other records in the first table double up. Can this be done with just a formula? I’m open to any solution but have stepped away from working with Crystal for a while.

 

[briangriffin]

This will be easier using a combination of SQL and CR formulas. Try a command object to deliver the filtered recordset to Crystal:

-- SQL Server Syntax
select x.CTValue, x.EffDate, x.CTAttrib, x.CTAttribValue

from YourTable x inner join

(select CTValue, CTAttrib, MAX(EffDate) as maxdate
from YourTable group by CTValue,CTAttrib) y

on x.CTValue = y.CTValue
and x.EffDate = y.maxdate
and x.CTAttrib = y.CTAttrib

You'll still have multiple records for each CT Value, so in the report group on CT Value and put this formula in the detail section:

//{@CTAttrib}
whileprintingrecords;
stringvar v_display;

v_display := v_display + " " + {Command.CTAttrib} + " " + {Command.CTAttribValue}

To display the values put this formula in the group footer:

//{@Display}
whileprintingrecords;
stringvar v_display;
v_display

You'll have to reset it for each CT value, so this formula goes in the group header:

//{@Display}
whileprintingrecords;
stringvar v_display := ""

Suppress the group header and detail sections and you should have what you're looking for.

 

[John1Chr]

My fault - I should have mentioned that I'm working in crystal 8.5. I haven't used that feature but can give it a try if it is available in 8.5.

 

[briangriffin]

I don't remember if 8.5 supports command objects, but you could take that same SQL code and save it as a database view - then add it to the report as you would any other table.

 

[John1Chr]

I've stepped away from crystal a little. Do you have a link or can you explain how to do that? I'm assuming that you are saying that I would pull in the info from Database/Add Database to Report/Data Explorer after saving.

 

[kray4660]

I you can edit the SQL directly in 8.5. It has been a long time since I have done it.

 

[briangriffin]

If you can save the SQL code as a database view, then it will show up in the Database Expert - under the connection properties you should see entries for Tables, Views, and Stored Procedures.

Find the view you created and add it to the report. I don't know if you need to add additional tables for this report, but if you do then you can join them to the view. Views behave identically to tables.

 

[lbass]

Another approach would be to write a SQL expression {%maxdt} (field explorer->SQL expression) like this:

(
select max(A.EffDate)
from table A
where A.CTValue=table.CTvalue
)

Then go to report->edit selection formula->record and enter:

{table.EffDate}={%maxdt}

This would return only the most recent effective date records per CTValue.

You could then insert a crosstab that uses CTValue as the row, CTAttribute as the column, and maximum of CTAttributeValue as the summary field. This assumes there is only one CTAttributeValue per date per combination of CTValue and CTAttribute.

-LB

 

[John1Chr]

LB,

I tried your technique and it looks real nice and is doing what we want in the header. Problem is that it triples the detail line. Is there a way to have one detail line per the crosstab?

There is actually 3 CT.values in table 2 (CITE1, CITE2, AND CITE 3). There is also a CITE1 in table 1 in another separate field if that helps.

So in my example table2 there is three 292635's when we use the max effect date. One for each Cite. Somehow I would like to treat the 3 CTAttributeValues as separate fields in a record like the crosstab and not cause the detail amount values from table1 from tripling.

 

[lbass]

Are you doing other calculations with the detail fields? i thought you only wanted the type of display achieved in the crosstab.

If you group on CT Value and then on CT Attribute, you could use running totals that evaluate on change of group: CT Attribute.

-LB

 

[John1Chr]

LB,
The method you speak of works. I should have mentioned that I am also summing/grouping for two levels above it. So for a CT Value I group it by Org2 and Org1 at the highest level. Not every CT Value has three CT Atrrib’s. Some CT Values only have a CITE1 or a CITE1 and CITE2. I wish I could pinpoint those that have a CITE2 and divide by 2 and those that have a CITE3 and divide by 3 for the Org grouping. Then when I sum and group at the Org1 and Or2 levels it will be the true amount. Maybe, there’s a better method.

 

[lbass]

Why can't you use running totals? You would create one running total for each group level, and change the reset based on the group where you want the result. They must be placed in the group footers.

-LB

 

[John1Chr]

LB,

Yes, the Running Totals works fine. I will let you know if I run into any more problems. Other than that good to go, I'm thinking.

Thanks,