Creating a grid like report using running totals

[gkw123]

I have to create a grid like report. I already made a thread regarding doing this in a crosstab a few days earlier and got some great advice from the user 'lbass'.
However it did not come out exactly as planned and I tried creating a grid using running totals. I decided to post in a different thread since I am no longer attempting to use a crosstab for this. Here is a description of exactly what I want. This somewhat similar to a traceability matrix.

The infromation from the database looks like this.

table.name table.requirement table.status
ABC AST Y
ABC FAT N
ABC CPR Y
ABc CPS Y
XYZ AST N
XYZ CPR Y
XYZ TBS N

I have to make the report look like this.


ABC XYZ

AST Y N
CPR N Y
CPS Y
FAT N
TBS N
GHT
CTS
AXT

There are a total of 8 requirements. All 8 have to be displayed for each group.
I tried doing this with a cross tab but it only returned the relevent requirements and not
all of them, like this

ABC XYZ

AST Y N
CPR N Y
CPS Y
FAT N
TBS N

I have tried a different approach and tried to use running totals to create a grid.
I am able to get the names to line up the way I want. However I am having trouble getting the appropriate status fields. I think I am using formula wrong.
I have hard coded the names of the requirements since they are fixed.

The formula I have right now is -


if {table.name}=Rtotal0 and {table.requirement}='AST'
then
{table.status}

Where Rtotal0 is the running total for the first name.
I have done this for each requirement and each running total.
This works fine when there is only one name per group (i.e ABC),
but when the second name comes in (XYZ) something goes wrong and it does not display the {table.status} field for the first name anymore. It does however display it for the second name.

I have created the running totals as Nth smallest of the {table.name} field and specified N to be 1,2... for each case.
Can someone let me know what I am doing wrong here? Or any alternative approach to accomplish this?
Thank you
gkw

 

[lbass]

Really, this is not the way to go, and you should have stayed in the original thread. The problem here is that you will need one running total for EACH combination of requirement and name, where you do a maximum of {status}, evaluate using a formula:

{table.name} = "ABC" and
{table.requirement} = "AST"

Reset never. I suggested pivoting the layout in the original thread because you said the names would keep changing, so I'm not sure why you think your running total approach will work--you would have to create a new set of running totals for every new name that was added to the mix.

Did you try the manual crosstab using the names as the group and requirements as the column? What problem did you run into?

-LB

 

[gkw123]

Hi,
I tried it and I can get it to work that way except for a few alignment issues. Unfortunately, that is not the template that the client wants to see.
Are you saying that it is impossible to get the layout that I want in crystal reports? I am thinking of using opus for this purpose. I understand that the running total approach will be very complicated. I was thinking about that because I just have to get the design that is specified. I have also been trying to use the "format with multiple columns" option in section expert. I ran into trouble there too because the {table.name} field repeats for each status and I'm not sure how to get all the status fields under one name.
Do you have any suggestions to do it that way?
Thanks a lot for your help.
gkw

 

[gkw123]

I have gotten the running total to work for the first name.
I am having trouble getting it to work for the second name onwards.

I have grouped by {table.name} and created a running total of {table.status}
evalutate when
table.requirement='AST'
reset on change of group table.name
I created 8 such totals and placed them in the group footer and they work fine for the first name.
The problem comes when I try to relate the second set of
running totals to the second name. Is there any kind of count method or groupnumber method that I can use to differentiate between each name in the group that I can use to evaluate the running totals for the second name and so on.
Thanks,
gkw123

 

[lbass]

If you are now grouping by name, you might as well use the inserted crosstab which is much simpler and achieves the same thing. I'm not sure what kind of alignment issues you would have run into.

If you still want to use running totals (much less efficient), and you have now grouped by name, with columns for requirements, your running totals should be set up like this:

maximum of status
evaluate using a formula:
{table.requirement} = 'AST'
Reset on change of group: name

Create one running total for each requirement. Place the running totals in the group footers and suppress the details and group header.

It appears that this is what you did though. I don't know what you mean by

The problem comes when I try to relate the second set of
running totals to the second name. Is there any kind of count method or groupnumber method that I can use to differentiate between each name in the group that I can use to evaluate the running totals for the second name and so on.

Can you clarify? If you grouped on name, the groups are already identified. Are you trying to do some kind of comparison between names? What is the comparison?

-LB

 

[gkw123]

Yes. That is what I did. So my report will look like this.

Rtotal1(name) Rtotal2 .......

AST #AST1 #AST2
FAT #FAT1 #FAT2
CPR #CPR1 #CPR1
CPS #CPS2 #CPS2
.
.
.
.

and so on.
There is another field called {table.org} which is one of the higher groups. one org can have many names. So the grouping goes - group #1 - {table.org}
group#2-{table.name}
In some cases when the {table.org} has only one name that section of the report looks like this.

RTotal#1

AST #AST1
CPR #CPR1
CPS #CPs1
FAT #FAT1
.
.
.
.

When this is the case it works perfectly.

However when there is more than one name , the running totals do not display as they should. ususally the running totals for the first name do not display and the ones for the second name do. I am not sure why this is occuring.
So my question is whether or not I can link the first set of running totals to the RTotal1 and the second set of running totals to Rtotal2 and so on.
Once again, thanks a lot for your help.
gkw

 

[lbass]

You said you grouped by name, but you are showing groups by requirement. In that case the running total has to specify the name (NOT the requirement) in the evaluation section--which is the problem I was pointing to earlier. Also, you should name your running totals so you know what they refer to instead of using the default.

-LB

 

[gkw123]

I did group by name.
I cannot specify the name in the evaluation section because it could be anything.
There are no fixed values for the name field.
I tried doing -

if {table.name}=Rtotal1 and {table.requirement}='AST'
then
{table.status},
But that didn't work as well.
There are only fixed values for the requirements. If that is not possible to do, then I will try something else or tell my supervisor that it is not possible to get the exact layout they want.
Thanks for your help
gkw

 

[lbass]

Please look at your first post where that series was called requirement. This isn't that complicated, but you have to be clear about your data. Since the name field changes, you should be grouping on that, and specifying the requirement (not the name) in each of eight running totals, and then placing that in the group footer, with a reset on change of footer. At least try that so you can see how this works.

-LB

 

[gkw123]

I have to agree with what you said earlier that this process is unnecessarily long. I had a long talk with my supervisor and convinced him to allow me to pivot the table and display data the way we discussed earlier. It looks fine right now.
Thank you very much for your help.
gkw