Help with grouping

[ccnetwork]

Hello

I would like to group my report by the following groups based on the below database design but I don't appear to be getting anywhere. Any help to get me up and running would be greatly appreciated.


Expected Milestones
Achieved Milestones
Expected Qualification
Achieved Qualification


Funding Table

Learner_ID
Product_ID < --- Product code 1 = Milestones , Product code 2 = Qualifications ,
DateStart <--- Expected Date
DateClaim <--- Acheived Date
Units <--- Payment amount

Thanks

 

[kskid]

Is this a case of double posting? Are you the same person? It is identical to the one posted by meanjoegreen13 on Crystal Forum 1

 

[MeanJoeGreen13]

That was my error - i was trying to post a new thread and had his thread on my clipboard and when I pasted my code in it took his by accident. My bad. Still new to this site and cant see how to remove mine.

 

[lbass]

So this if really ccnetwork's post?

If you group on the product code and then place the date start in detail_a and the dateclaim in detail_b next and then add the product code field to each detail section, would that give you the display you want? You didn't explain how the learner ID and units fit in.

Maybe you should show how the data is displaying currently, and then how you would like it to display.

-LB

 

[ccnetwork]

Hi

The only way I can think of solving this problem is to create the SQL command below so my data will look like:

Learner_ID Product_Ref mdate type
1 1 01/04/2007 Expected
1 1 01/05/2007 Expected
1 1 29/30/2007 Acheived
1 1 15/12/2007 Acheived


SQL Command

SELECT L_FUNDING.Learner_ID,L_FUNDING.Product_Ref,L_FUNDING.DateStart as mdate, 'Expected' AS type
FROM L_FUNDING where
(L_FUNDING.Product_Ref='1' or L_FUNDING.Product_Ref = '2') and L_FUNDING.DateStart >= (select PERIODS.DateTo from PERIODS where Period = 1 and PERIODS.Year_Ref={?Year} and PERIODS.SetID='SE') and L_FUNDING.DateStart<= (select PERIODS.DateTo from PERIODS where Period =12 and PERIODS.Year_Ref={?Year} and PERIODS.SetID='SE')

UNION SELECT L_FUNDING.Learner_ID,L_FUNDING.Product_Ref,
L_FUNDING.DateClaim as mdate, 'Acheived' AS type
FROM L_FUNDING where (L_FUNDING.Product_Ref='1' or L_FUNDING.Product_Ref ='2') and
L_FUNDING.DateClaim >= (select PERIODS.DateTo from PERIODS where Period =1 and PERIODS.Year_Ref={?Year} and PERIODS.SetID='SE') and L_FUNDING.DateClaim <= (select PERIODS.DateTo from PERIODS where Period =12 and PERIODS.Year_Ref={?Year} and PERIODS.SetID='SE')

I can then group the report by Product_Ref,type. Do you think this is the best method to use?

There is a slight problem if I add a few more tables to the report and group the report by advisor as the report will take 3-4 mins to load, however if I remove advisor from the group then the report loads straight away. Any ideas why its taking that long to load?

Group 1: Contacts.DisplayName
Group2: Command.Product_Ref
Group3: Command.type


Tables used in the report:

Command Table

Learner_ID <--- Linked to learners table.
Product_Ref
mdate
type

Learners Table
Learner_ID <--- Linked to Command.Learner_ID
Firstname
Lastname
Advisor < -- Linked to Contacts.ID
etc...

Contacts Table (Advisors)
ID <----- Linked to Learners.Advisor
Firstname
Surname
Displayname

 

[lbass]

You should build all of the tables into the command if you want to optimize the report speed. If you link other tables to a command, the linking occurs locally.

-LB

 

[ccnetwork]

Report is working perfectly by building all the tables into the command. Thanks lbass