Dynamic Crosstab Row Height? 1

[wesleycrusher]

I created a report listing model numbers and their associated footnotes. I have the explanation key for footnotes displayed in a group footer using a crosstab. It is rigged so that a single column is based on a formula that numbers the footnotes and a summarized maximum that displays the footnote text/string.

Since users can customize their footnotes, the lengths vary. Although most are short enough to fit on one line, there are plenty that do not. Is there a way to expand row height when this occurs while leaving the row height the same when it doesn't? I essentially need the "can grow" function to work with a crosstab if that explains it better.

FYI I have the report page split vertically so that there are two columns of complete data per page.

Any help is appreciated. Thank you.

 

[lbass]

I was the one who originally suggested the crosstab, but if you know the maximum number of footnotes that any group could have, you could do the following instead:

First concatenate the footnote number with the footnote text by using a formula like {@concat}:

totext({table.footnoteno},0) + {table.footnotetext}

Then insert a summary on {@concat} using "NthMostFrequent" and N=1. Repeat, increasing N each time up to the maximum number of footnotes. These will appear in the group footer. Arrange them as you like, and then format each one with the "Can Grow" feature. This will allow you more flexibility in terms of spacing. Sorry I didn't think of it earlier.

-LB

 

[wesleycrusher]

Thank you for the tip. I haven't had a chance to apply it yet but will give try to give it a shot tomorrow. Thanks again!

 

[wesleycrusher]

I have tried what you suggested above. I definately see this working out really well. However, I don't understand where the "can grow" feature comes in. It sounds like the above is instructing me to create a crosstab with the Nth most frequent, but a crosstab doesn't allow one to check the can grow box, it is grayed out. Am I understanding correctly?

 

[lbass]

No, this method replaces the crosstab method. You would be inserting multiple summaries on {@concat}, which is placed in the detail section. Then select each summary in the group footer, right click->format field->common and check "Can Grow".

-LB

 

[wesleycrusher]

I understand now. Thanks for the help. This will work but I have to order a couple hundred footnotes sections by hands ince they don't appear in numerical order. I will mess around with this solution and hopefully work it out. Thanks again!

 

[wesleycrusher]

I was hoping this would work, but all it does is return a "true". How can I get it to display what you have suggested above? fnumber is set to 6 because that is where custom footnote numbering begins.


NumberVar fnumber:=6;
NumberVar i;
StringVar output:="";

for i:=fnumber+1 to 20 do
(
if {form_custom_footnote.footnote_nbr}=i
then output:=totext({form_custom_footnote.footnote_nbr}+fnumber,0) + "." + CHR(160) + {form_custom_footnote.footnote_txt}
else output:="";
if {form_custom_footnote.footnote_nbr}=i
then i:=i+1;
);

 

[lbass]

I think your formula is returning a "true" because you didn't add the final line:

output;

I don't understand why you need to "order a couple hundred footnotes sections by hand since they don't appear in numerical order." Why can't you just use {@concat} (my formula) as a sort or a group field?

Also I don't see what you are trying to do by using a looping variable--I'm not saying it's the wrong approach, I just am not following why you are doing this...

-LB

 

[wesleycrusher]

I doubt I am understanding your help correctly.

I created the @concat like you said, then created a summary for it in the footer of the tradename group. However, I need to create several summaries to show all the footnotes. I did this but they were out of order on an Nth most frequent sort where I increased N each summary. Moreover, each section has different numbers of footnotes. So while on has none, another might have 7. The footer space needs to shrink with this as well, can't have huge blank sections.

The loop effort was to help cycle through all the footnotes up to a certain number while controlling the footer space.

Thanks for your help. I do appreciate it since I am no programmer. I will figure something out, I don't want to take your expertise away from others.

 

[lbass]

I just tested my earlier suggestion (should have done this before). If you use NthSmallest, changing the N up to the maximum number of footnotes with {@concat}, then the footnotes should appear in order, and if you use "Can grow" on each, you will save some space, since the longest footnote will no longer determine the number of lines per footnote. But, the group footer still will be extended to fit all N footnotes, even if they don't exist for a particular group. So, while this is a better solution than the crosstab, it still doesn't quite do the trick.

The best way to do this would be to accumulate the footnotes in one long string, but I'm guessing you would run up against the 254-character limit, since I believe you have 8.5. If you have 9.0 this shouldn't be a problem. The following formula adjusts for the 254 character limit and will allow for up to 762 characters per set of footnotes in a group footer. It is an adaptation of a formula developed by Naith for 508 characters in another thread.

First sort on {@concat} and then create these formulas:

{@resetfootnotes} to be placed in the group header:
whileprintingrecords;
stringvar footnotesx := "";
stringvar footnotesy := "";
stringvar footnotesz := "";

{@footnotesdetail} to be placed in the details section:
whileprintingrecords;
stringvar footnotesx;
stringvar footnotesy;
stringvar footnotesz;

if instr(footnotesz, {@concat}) = 0 then
if instr(footnotesy, {@concat}) = 0 then
if footnotesy = "" then
if length(footnotesz) + length({@concat} + chr(13)) > 254 then
footnotesy := footnotesy + {@concat} + chr(13) else
footnotesz := footnotesz + {@concat} + chr(13) else

if instr(footnotesx, {@concat}) = 0 then
if footnotesx = "" then
if length(footnotesy) + length({@concat} + chr(13)) > 254 then
footnotesx := footnotesx + {@concat} + chr(13) else
footnotesy := footnotesy + {@concat} + chr(13) else

if length(footnotesx) + length({@concat} + chr(13)) > 254 then
footnotesx := footnotesx else
footnotesx := footnotesx + {@concat} + chr(13) else
footnotesx := footnotesx else
footnotesy := footnotesy else
footnotesz := footnotesz;

The following three formulas should be positioned in a text box in the group footer in order z,y,x. The fields should be nested right up against each other horizontally.

{@displayz}:
whileprintingrecords;
stringvar footnotesz;

{@displayy}:
whileprintingrecords;
stringvar footnotesy;

{@displayx}:
whileprintingrecords;
stringvar footnotesx;

Right click on the text box->format field->common->and check "can grow". Resize your group footer so that it is only one line in height. Then it will resize itself to fit the field depending on the number of footnotes in that section.

-LB

 

[wesleycrusher]

Awesome. A million thanks for the outstanding help. This is exactly what I needed for this application. Thanks for coming through, lbass!

 

[wesleycrusher]

Oops. I just checked out a string longer than the limit and it is still cut-off. I did exactly a you said, so I am wondering what the problem could be. The first 254 chars are displaying fine but the chars above the limit are not. I have followed your instructions exactly. Any idea what is causing this?

 

[lbass]

I actually created these formulas in a test report, which is displaying fine and which uses all three display formulas. Did you try copying these formulas from the thread and then pasting into your formula editor? If you use {@concat} for the name of your concatenation, then you should be able to use them exactly.

When I was testing this, I initially had a problem like yours because I had added a second string to {@concat}, as in {@concat} + "xxxxxxxxx" in order to make the string long enough to exceed the 254-limit for test purposes. The problem arose when I didn't include the "xxxxxx" and the chr(13) in the length parts of the formula, so I would double check whether you are including the chr(13) within the length formula, since that character will take up one space.

Also, did you use my {@concat} formula?

totext({table.footnoteno},0) + {table.footnotetext}

If you didn't use this, please let me know what you used.

Please let me know if the above suggestions resolve the issue.

-LB

 

[wesleycrusher]

Thanks for getting back to me. I used my own names at first but changed to your exact names when it didn't work. This is my @concat formula:

totext({form_custom_footnote.footnote_nbr}+5,0) + "." + chr(160) + {form_custom_footnote.footnote_txt};

I added the +5 for numbering purposes and the other stuff for formatting. All other formulas are named and contain your exact code. Also, I did add the three display(z,y,x) formulas in a single text box in the footer section that will display them in the report. The @footnotesdetail formula is in a suppressed details section.

Thanks for the guidance.

 

[lbass]

Your formula looks fine. I've tried, and I can't recreate the problem you are having. Did you double check where you have the parens for the length clauses? This is the only thing I can think of, but if you did in fact copy and paste the formulas into your report, that can't be it either. And I gather that you are not getting any error messages re: length of a string?

-LB

 

[wesleycrusher]

The error I get (which I just discovered) says "A string can at most be 254 characters long". I get that when in preview, trying to jump to the section with the long footnote.

 

[lbass]

I'm guessing that {@concat} by itself is sometimes longer than 254 characters. You could test whether it's {@concat} by removing the other formulas from the report canvas. The formula that is causing the error should pop up in the display. Since you can use the footnote text field in {@concat}, I'm guessing that it doesn't exceed 254 characters alone, but in combination does. You could try removing the chr(160) or limiting the footnote text by using the left or other functions. Are there any characters in the text that could be sacrificed? (Not an ideal solution, I know).

-LB

 

[wesleycrusher]

Yes, you are correct. In the @concat formula:

totext({form_custom_footnote.footnote_nbr}+5,0) + "." + chr(160) + {form_custom_footnote.footnote_txt};

form_custom_footnote.footnote_txt can be longer than 254 characters. The database allows for a footnote that is longer than this Crystal limit. I thought this was covered in your suggestion, though?

 

[wesleycrusher]

I just got approval to upgrade to Crystal 9. I know this will definitely solve the issue. It appears that Crystal won't even pull the strings > 254, regardles of concatenation attempts.

Thanks for the help, lbass. I do appreciate it immensely.