PAG_Promax
asked on
Cross Tab with SELECTIVE total
Hi folks,
I'm in desperate need of some help here. I've created a new cross tab report in Crystal 8.5, and have a problem with the total. Please see example in image (not real names or values).
I have made a variable which I add too when the column names are equal to only column1 or column2 which works nicely, but when I add it as a summary field, I get a new row. Is there any way I can use this value instead and not use the Crystal provided total?
Many thanks for any help!
Stuart. eeCR85.PNG
I'm in desperate need of some help here. I've created a new cross tab report in Crystal 8.5, and have a problem with the total. Please see example in image (not real names or values).
I have made a variable which I add too when the column names are equal to only column1 or column2 which works nicely, but when I add it as a summary field, I get a new row. Is there any way I can use this value instead and not use the Crystal provided total?
Many thanks for any help!
Stuart. eeCR85.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Lets pretend this report only returns one row with three columns (the report can return up to 8 columns wide). We only want data from column 1 and 2.
The way the report worked was, for each cell on row 1, we'd get a value: say 1, 2, 3 for a total of 6.
Because I only wanted column 1 and 3 I wrote a function that would only add the values if the data type was for column 1 or column 3. I then created another row out of that which gives me values in cells under column 1 and 3 and a zero for column 2, and the total equals 4.
That makes it a bit confusing. So what I did was suppressed the values that I was calculating, and suppressed the true total, but displayed my calculated total. Again, this works and works really well.
The issue is, I have a row with 3 values, and in the total cell I have nothing, then a value under the nothing. Unfortunately I can't give you a screen shot of it because the data is very sensitive. I'll do you another MS Paint Knock-up if you wish.
What I would really love is to be able to hide the row completely that holds my calculated values, and then have the calculated total display where the cross tabs' total is.
Give me a sec and I'll knock something up. It'll make much more sense..
Cheers!
The way the report worked was, for each cell on row 1, we'd get a value: say 1, 2, 3 for a total of 6.
Because I only wanted column 1 and 3 I wrote a function that would only add the values if the data type was for column 1 or column 3. I then created another row out of that which gives me values in cells under column 1 and 3 and a zero for column 2, and the total equals 4.
That makes it a bit confusing. So what I did was suppressed the values that I was calculating, and suppressed the true total, but displayed my calculated total. Again, this works and works really well.
The issue is, I have a row with 3 values, and in the total cell I have nothing, then a value under the nothing. Unfortunately I can't give you a screen shot of it because the data is very sensitive. I'll do you another MS Paint Knock-up if you wish.
What I would really love is to be able to hide the row completely that holds my calculated values, and then have the calculated total display where the cross tabs' total is.
Give me a sec and I'll knock something up. It'll make much more sense..
Cheers!
ASKER
OK, here's an image.
If it's not clear what I mean, let me know and I'll try to expand a bit more.
Cheers!
eeCR852.PNG
If it's not clear what I mean, let me know and I'll try to expand a bit more.
Cheers!
eeCR852.PNG
OK, I follow that. I don't see a better solution off hand. Maybe mlmcc will have some ideas.
James
James
Should those 8s be 6 in the total?
Is is always column2 you don't want?
Can you add a formula
-{Column2Field}
Then use that formula as a 4th column in the report and suppress is?
mlmcc
Is is always column2 you don't want?
Can you add a formula
-{Column2Field}
Then use that formula as a 4th column in the report and suppress is?
mlmcc
ASKER
Thanks for your efforts, James. I appreciate you taking the time out to help.
mlmcc. What 8s and 6s are you talking about?
What you are proposing is what I am actually trying to achieve, but I cant figure out how to do that in a cross tab. Can you give me some pointers?
Cheers!
Stuart.
mlmcc. What 8s and 6s are you talking about?
What you are proposing is what I am actually trying to achieve, but I cant figure out how to do that in a cross tab. Can you give me some pointers?
Cheers!
Stuart.
In your last picture you show the total as 8 which is column 2 + 3
Are column 1,2,3 the same record or are they different records?
mlmcc
Are column 1,2,3 the same record or are they different records?
mlmcc
ASKER
THey are different records. The data looks something like this
ID,NUMBER,DATE,VALUE,TYPE
1,12345,1/1/2010,100,'IN'
1,12345,1/1/2010,200,'OUT'
1,12345,1/1/2010,50,'TOP UP'
So what I'd want is the total of out and top up.
The dates are used as column groupings is is type. There could be 31 date columns under IN and 31 in Out. and Top Up - but there could also be none. The cross tab handles this nicely and provided a total column for each group of date values.
I hope this makes some sort of sense.
Cheers!
ID,NUMBER,DATE,VALUE,TYPE
1,12345,1/1/2010,100,'IN'
1,12345,1/1/2010,200,'OUT'
1,12345,1/1/2010,50,'TOP UP'
So what I'd want is the total of out and top up.
The dates are used as column groupings is is type. There could be 31 date columns under IN and 31 in Out. and Top Up - but there could also be none. The cross tab handles this nicely and provided a total column for each group of date values.
I hope this makes some sort of sense.
Cheers!
Re: The 8's and 6 that mlmcc referred to
In the last picture that you posted, you showed the total on the mostly suppressed "calculated" line and the "what I would love to see" line as 8, which would be column 2 + column 3. Should those totals have been 6 instead (column 1 + column 3)?
I still don't really see a better way to handle this with the columns the way that you have them now. If you could rearrange things, there might be some other options. For example, create a new group in the cross-tab that puts columns 1 and 3 in one group and column 2 in a group by itself. Then a sub-total for the group would give you the total of columns 1 and 3. Then column 2 would come after that. Of course then you'd also have a sub-total for the column 2 group, which you don't need.
James
In the last picture that you posted, you showed the total on the mostly suppressed "calculated" line and the "what I would love to see" line as 8, which would be column 2 + column 3. Should those totals have been 6 instead (column 1 + column 3)?
I still don't really see a better way to handle this with the columns the way that you have them now. If you could rearrange things, there might be some other options. For example, create a new group in the cross-tab that puts columns 1 and 3 in one group and column 2 in a group by itself. Then a sub-total for the group would give you the total of columns 1 and 3. Then column 2 would come after that. Of course then you'd also have a sub-total for the column 2 group, which you don't need.
James
ASKER
Hi James,
We came to the same conclusion. We've spoken to our client and told them that we can't deliver the report with the Total field, and that considering they export the data into Excel, creating the total column shouldn't be an issue for them. We'll also change the report in our next release which uses SSRS instead of Crystal.
Thanks all for your persistence and help with this. I appreciate it even though we didn't end up with a win. I'll split the points between you all.
Cheers!
Stuart.
We came to the same conclusion. We've spoken to our client and told them that we can't deliver the report with the Total field, and that considering they export the data into Excel, creating the total column shouldn't be an issue for them. We'll also change the report in our next release which uses SSRS instead of Crystal.
Thanks all for your persistence and help with this. I appreciate it even though we didn't end up with a win. I'll split the points between you all.
Cheers!
Stuart.
ASKER
Although we didn't get a win on this, the comments and posts were most helpful and accurate. What I was trying to achieve in this older version of Crystal was impossible.
Thanks once again.
Stuart
Thanks once again.
Stuart
Sorry it didn't work out, but that's the way cross-tabs work in CR. They can be quite useful, but they're generally not very flexible. Trying to alter what's going on in the cross-tab can be next to impossible.
James
James
ASKER
I agree. They're great if you don't want to do anything overly funky. The transition to SQL Server Reporting Services is going to resolve these issues as we have just so much more flexibility. Crystal has served us all very well, and we certainly have no complaints with its passed history with us.
Thanks again, James.
Thanks again, James.
No problem.
James
James
ASKER
I've sort of going it working. I'm using a calculated value now, and just say if it equals column2, don't add it in. And that works. But it looks ugly as sin.