Looping - Groups or subreports?

[Secretgeek]

Hi,

I have a report that contains 3 linked tables. Currently the report produces the sequence shown in Fig 1.

Fig 1.
For a case that has two departments and two questions associated with it:

CaseID----->Dept----->Quest--->
-->CaseID----->Dept(n+1)--->Quest--->
-->CaseID----->Dept----->Quest(n+1)-->
-->CaseID----->Dept(n+1)--->Quest(n+1)-->
-->CaseID(n+1)--->etc etc

What I need is for the sequence to be:

CaseID-->Dept-->Dept(n+1)-->QuestID-->QuestID(n+1)
CaseID(n+1)--->etc etc

My problem is that I'm getting multiple iterations of CaseID greatly increasing the size of my report. I'm unsure as to the most appropriate method of resolving this and any pointers would be much appreciated.

I hope my question is clear but if it isn't please let me know.

 

[Secretgeek]

Actually, after some experimenting I've achieved what I need with subreports.

Now all I need to sort out is the overlapping issues and we're done!

 

[Secretgeek]

It's obviously one of those days.

On further checking what I've actually now got is:

(This example is for a case with two departments and three questions)

-->ID-->Dept-->Dept(n+1)-->Quest-->Quest(n+1)-->Quest(n+2)--->
-->ID-->Dept-->Dept(n+1)-->Quest-->Quest(n+1)-->Quest(n+2)--->
-->ID-->Dept-->Dept(n+1)-->Quest-->Quest(n+1)-->Quest(n+2)--->
-->ID(n+1)--> etc etc

As, I hope, is evident, what I'm getting now is a full repeat of the entire case record, departments, questions and all a number of times equal to the number of questions.

I'm sure it's something to do with the 'Questions' detail section in the main report but I can't see what.

Any pointers please?

For info, I'm on XP and CRXI.

Thanks in advance.

 

[IanWaterman]

You need to know what are the max number of Depts and Questions you can have for a case.

Having defined that you can then use Stringvar arrays, one each for Depts and questions.

Populate the array elements with each dept and question and then in your case group footer display each element

@Dept1
whileprintingrecords;

global stringvar array dept[1}

If you can have any number of dept and questions then you are going to have to rethink your method of display.

Ian

 

[Secretgeek]

Thanks Ian,

I've got a limited number of departments, currently 6 but that may change in the future (they do like their reorganising ), however there can be any number of questions.

All the questions go to each of the specified departments.

And a correction to my previosu post it appears to be repeating itself based on the number of specified departments and not questions.

I've got Deptartments and Questions as sub-reports in seperate detail sections I don't know if this may be relevant.

Thanks.

 

[Secretgeek]

Well, I just spotted the 'Select Distinct Records' button in the database menu, selected it and abra-blinkin-cadabra it now looks to be running perfectly.

I've some checking to do but hopefully that's sorted.

Thanks.