2 related records in same field diff attribute - relate and report on

[John1Chr]

Hopefully, I can explain myself better then I did in a previous post.

tABLE

CTAttrib CTAttribValue Description
Program 01 Accounting Services
Activity 01011 Financial Reporting
Activity 01012 Budget
Activity 01013 General Ledger
Program 02 Human Resources
Activity 02010 Hiring
Activity 02011 Benefits
Activity 02012 Employee Assistance

I would like to group as outlined above. The report would be selected by the first two digits (ie. Program) and would pull in and report just like outlined above - Program/Activity with program in the header and sorted Activity the next header or detail.

I thought that I could do a formula for Program and Activity and that is easy to do and then I grouped with each. The problem is getting the two to relate and making one the subset of the other. Or getting the Program in the header but leave the activity as a subset of the related two digit program.

I grappled with selfjoining the table but I shouldn't need to? I do not have a max effect date to contend with.

 

[briangriffin]

Create two command objects, and then link them on CTAttribValue:

select *
from YourTable
where CTValue = 'Program'


select ctvalue, SUBSTRING(ctattrib,1,2) as CTAttrib, ctattribvalue
from YourTable
where CTValue = 'Activity'

(SQL Server syntax, so you may have to tweak it a bit.)

 

[briangriffin]

Oops, should link on CTAttrib...

 

[John1Chr]

crud...should've told you that I'm working in 8.5

 

[lbass]

Same topic as thread767-1679987.

-LB

 

[hilfy]

When you use two commands and join them, Crystal will load everything from each into memory and do the join there. I would actually try to do it all in one query. Expanding on Brian's example, it would look something like this:

Select
  prg.CTValue as PRG_VALUE,
  prg.CTAttrib as PRG_ATTRIB,
  prg.DESCRIPTION as PRG_DESCRIP,
  act.CTValue as ACT_VALUE,
  act.CTAttrib as ACT_ATTRIB,
  act.DESCRIPTION as ACT_DESCRIP
from YourTable as prg
  inner join YourTable as act
    on substr(act.CTAttrib, 1, 2) = prg.CTAttrib
      and act.CTValue = 'Activity'
where prg.CTValue = 'Program'

You would then group by PRG_ATTRIB and put all of the "PRG" fields in the group header and all of the ACT fields in the details. Suppress the group footer and you'll get the data in the format of the table in your original post.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[John1Chr]

Can I do commands in 8.5? Hilfy, I put your formula in the SQL Expression Editor and I'm getting a missing expression error right before the first prg. Not sure if this is where it should go.

LB - it's a different table that I'm trying to pull from. It doesn't have max effect date in it and it doesn't have CTValue rather it has CTAttribValue.

I know, I did not explain what I wanted well in that last post -I'm going after something else.

 

[hilfy]

This cannot go into the SQL Expression Editor. In 8.5 you have the option to Edit SQL, which is where you can put a SQL Select statement instead of using tables in your report.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[John1Chr]

Do you need the crystal sql designer? I don't think I have.

 

[hilfy]

No, IIRC, you should be able to just enter it under "Edit SQL".

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

In 8.5 you would have to start out by creating a report that uses the same fields so that you can do a union all statement--this is because you can't edit the Select and From Clauses, only the Where clause in the show SQL area. So once you have run the report with the required fields, you can change the SQL like this:

Select
table.CTValue,
table.CTAttrib,
table.DESCRIPTION,
table_1.CTValue,
table_1.CTAttrib,
table_1.DESCRIPTION
from table
inner join table_1
where 0=1 //this undoes the first half of the union which is CR-generated
//you add the following:
union all
Select
table.CTValue,
table.CTAttrib,
table.DESCRIPTION,
table_1.CTValue,
table_1.CTAttrib,
table_1.DESCRIPTION
from table
inner join table_1
table.CTAttrib = substr(table_1.CTAttrib, 1, 2) and
table_1.CTValue = 'Activity' and
table.CTValue = 'Program'

I don't think there is a way to rename the fields in the query, since in a union all the field names come from the first half of the query (which is auto-generated in this case). So you would end up with each field with two instances-with numeric extensions. For ease of working with the fields, you could drop each into a formula with the desired name.

-LB