Cross-Tab Text Fields Not Aligning with Summarized Fields
Cross-Tab Text Fields Not Aligning with Summarized Fields
(OP)
I have a relatively basic cross-tab report that has one 'row' named 'group' and one 'column' named 'officer.' I have 25 summarized fields (and likely more before I'm done). The 'descriptions' or 'text' fields don't come anywhere close to aligning with the summarized fields; specifically, they are far shorter in height than the summarized fields. What's interesting to note is that when I flip the columns and rows, they align perfectly. But that's not an option because of the required format of the report.
Does anyone have an idea on how to 'correct' the alignment of rows on a crosstab?
Sincere thanks.
Does anyone have an idea on how to 'correct' the alignment of rows on a crosstab?
Sincere thanks.
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
-LB
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
Man, I should have caught that - you are correct. Once the custom style is removed, everything aligns.
I'm using CR 2008 v12.
Thanks!
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
I'm trying to finish up the same crosstab I noted above, but the last summarized field, 'Resolution Rate' equals:
sum of @Total_Resolutions /
sum of @Adjusted_Balance
The problem is that in the cross-tab, the current calculation doesn't work. I am guessing because I need to do the calculation by grouping on the column, Scorecard_Data_By_Group.Officer_Name - is that correct, or is there another solution? Either way, I can't seem to get the calculation to work.
Sincere thanks for any insight!
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
Within the crosstab, select the Adjusted Balance cell (this will automatically select all summaries in this row), right click->format field->common tab->suppress (don't check)->x+2 formula area and enter:
whileprintingrecords;
numbervar adj := currentfieldvalue;
false //this prevents the suppression from occurring
Then select the Total Resolutions cell->right click->format field->common tab->suppress->x+2 and enter:
whileprintingrecords;
numbervar res := currentfieldvalue;
false
Next, select the Resolution Rate summary (this could actually be the sum of a formula that just contains: whilereadingrecords; 0
...but you can leave the current summary as is, since you are going to override the display by right clicking->format field->common tab->DISPLAY STRING->x+2 and entering:
whileprintingrecords;
numbervar adj;
numbervar res;
if adj=0 then
"--" else
totext(res%adj,1)+"%" //replace the 1 with the number of decimals you want in the percent, if different.
-LB
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
I encountered an interesting problem using the above solution and exporting the Crystal Report to Excel (required): the 'resolution rate' number in Excel isn't the same as the correct one in Crystal. When I expert it to Excel, Data Only, and 'Typical' or 'Minimal' or 'Custom' formatting applied, all result in the incorrect calculation for 'resolution rate.'
I can't export it with formatting as I get a bunch of extra columns, misalignment, etc. I'm assuming I'm getting this result because of the calculation for resolution rate being in the display string and that isn't carrying over, but obviously, I need it to carry over.
I would sincerely appreciate any final recommendations.
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
-LB
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
You're correct - you get formatting or you don't - in which case the calculation doesn't come through. I've combed other forums to confirm. I'll be working to eliminate as many extra columns as I can.
Sincere thanks!
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
Only extra columns I'd like to eliminate are those that appear under the '--TOTALS--' column. I don't understand why this column would not generate one narrow column before one wide column encompassing the numbers below it as it is no different than the other columns.
I event went so far to use a formula to define redefine '--TOTALS--' to simply add more dashes thinking that the field name needed to be longer, but that hasn't changed the resulting extra columns.
It might be subtle thing, but it would be ideal if I could get it to render the same as it does for the Officers. It's worth noting that the 'total' is calculated in the same way as the officers - it is not the total the crosstab calculates - that is suppressed.
Thanks again!
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
whileprintingrecords;
numbervar adj;
numbervar res;
if adj=0 then
"--" else
totext(res%adj,1)+"%"
Again, this is a brilliant solution. As noted, no longer exporting data only, and I have only a few minor extra columns.
More importantly, the calculation for 'Resolution Rate' above technically should be using YTD annualized resolutions, as it is no different than calculating an interest rate - $ interest for two months can't simply be divided by outstandings to get the correct interest rate.
Using that logic, I would need to annualize resolutions = divide resolutions by the number of months passed and multiply by 12. The obvious problem is that I am not reporting annualized resolutions in the crosstab, so I can reference that 'row' or total to adjust the calculation.
Should I create annualized resolutions, put it in the crosstab and suppress it (so it doesn't appear) and use that line to adjust the calculation above?
Thanks again!
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
You should be able to build the calculation right into the formatting formulas. I need more info to be helpful regarding this though.
-LB
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
Sincere thanks!
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
year({Command.EOMDate}+62)
Then change the display string formula to:
whileprintingrecords;
numbervar adj;
numbervar res;
if adj=0 then
"--" else
totext(res*(12/(datediff("m",date({@FY}-1,11,1),{Command.EOMDATE})+1))%adj,1)+"%"
For the data in your sample, this would multiple the resolutions by 6 to annualize the two months of resolutions. But the formula should work for any time period. I added a 1 because your EOMDate is at the end of the month, so just subtracting the months would omit the days in the current month otherwise.
-LB
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
That doesn't work for my Fiscal Year (starts 11/01/16) or Calendar Year (starts 01/01/17), so I likely gave you some bad info.
Applying the above to the Fiscal Year Report which includes 11/16 and 12/16:
@FY = 2017
Fiscal Year Actual Math:
Months Elapsed 2
{Command.EOMDATE} 12/31/2016
Resolutions (R) 2,259,098.04
Adjusted Balance 196,085,253.50
Resolution Rate - Simple % 1.152%
Resolutions - Annualized $ (R/2*12) 13,554,588.24
Resolution Rate - Annualized 6.913%
Resolution Rate Using Recommended Formula 14.977%
Obviously for Calendar Year YTD, the report when run today is for all of 2016. The annualization gets all whacked out.
@FY = 2078 - ?
Sincere thanks for the insight!
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
I don't understand why you mention the calendar date. Are you saying you want the formula to work for fiscal OR calendar date? You would have to add in a parameter to say which option you were using.
-LB
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
The Calendar YTD Report will actually be set up as a separate report for publishing purposes.
So rather than employ a parameter, I think we can just use a different formula for "Fiscal_Year".
How would that work, and as importantly, I'm not sure I understand the meaning of the 62 added in the Fiscal Year formula.
Sincere thanks - again!
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
For the calendar year, you just need to change the last line of the formula above to:
Totext(res*(12/month({command.EOMDate}))%adj,1)+"%"
-LB
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
This has been great stuff - sincere appreciation! I have a twist I need to add when the resolution rate is in excess of 100% that it is 100%, but the following doesn't work. Thoughts?
whileprintingrecords;
numbervar adj;
numbervar res;
if adj=0 then
"--" else
//new-----
if (totext(res*(12/month({Command.EOMDATE}))%adj,3)+"%") > "100%"
then "100%"
//--------
else
totext(res*(12/month({Command.EOMDATE}))%adj,3)+"%"
Sincere thanks - as always!
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
If res*(12/month({Command.EOMDATE}))%adj>100 then
"100%" else
-LB
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
RE: Cross-Tab Text Fields Not Aligning with Summarized Fields
-LB