Cross-Tab Text Fields Not Aligning with Summarized Fields 1

[Craig Wright]

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.

 

[lbass]

How did you apply the custom style? I can see that you have one by looking in the style tab of the crosstab expert, but can't see how you applied it. I have CRXI, and you seem to have a later version. If you remove the style in the custom style tab, the alignment is corrected. If you apply the teal style option, the alignment is also correct.

-LB

 

[Craig Wright]

LB,

Man, I should have caught that - you are correct. Once the custom style is removed, everything aligns.

I'm using CR 2008 v12.

Thanks!

 

[Craig Wright]

LB (or anyone else),

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!

 

[lbass]

You can use the formatting areas of the summaries to create variables that will do the calculation, like this:

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

 

[Craig Wright]

Brilliant and perfect response LB. Assume the key to the solution lies with 'whileprintingrecords.' That's less surprising than the display string - haven't used that before. Sincere thanks.

 

[Craig Wright]

LB,

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.

 

[lbass]

When I export to Excel (the regular export, NOT data only) the calculations are correct, and the display looks perfect--no misalignment, although there are extra columns, etc. Not sure why you are having trouble since I am testing with your report. At any rate, I don't have a solution for the issue that arises if you use the data only export method.

-LB

 

[Craig Wright]

LB,

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!

 

[Craig Wright]

LB,

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!

 

[Craig Wright]

LB,

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!

 

[lbass]

Need to know what constitutes "year to date"--do you mean for your fiscal year? Is it different from the calendar year?

You should be able to build the calculation right into the formatting formulas. I need more info to be helpful regarding this though.

-LB

 

[Craig Wright]

Thanks LB - yes, there are two versions of the report (well, actually, more but once I have the recommended solution, I can apply variations), but let's focus on the calendar year and fiscal year. Fiscal starts on 11/01. Calendar of course starts on 1/1.

Sincere thanks!

 

[lbass]

First, create a Fiscal Year formula (assuming you identify your fiscal year by the end year):

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

 

[Craig Wright]

LB,

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!

 

[lbass]

I think you implemented my formula incorrectly, since I get the correct 6.913%.

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

 

[Craig Wright]

Aha! You are correct, LB! I was missing some ()s, and I now get the correct answers on the Fiscal YTD Report.

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!

 

[lbass]

There are 30 days in November, 31 in December--61 before the new calendar year. So if you add 62 days to a relevant date, it will return the fiscal year within which the date falls.

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

 

[Craig Wright]

LB,

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!

 

[lbass]

Change the first line to:

If res*(12/month({Command.EOMDATE}))%adj>100 then
"100%" else

-LB