crystal xi: Incompletely populating a shared array via a subreport

Hello,

I am populating an array in a subreport and using it in the main report. The array is created by reading a local excel spreadsheet with one column consisting of Dates. No matter what I try, if I shrink the subreport (I don't want it visible) the array is incompletely populated. (If I suppress it, it doesn't work at all),

In the subreport,  which is located in the report header, I have an Init formula field  in the reports header and a 'create' formula field in the details section
//INIT
shared datevar array holidaydates;
local numbervar knt := count({Holidays_.DatesOnly});
redim holidaydates[knt];
//just so we don't return an array and cause an error
local numbervar i;
for i := 1 to knt do 
    holidaydates[i] := cdate(#01-01-1970#);

//CREATE in details section
whileprintingrecords;
shared datevar array holidaydates;
holidaydates[recordnumber] := cdate({Holidays_.DatesOnly});

Open in new window


THe knt variable is correct. It's just that if I don't totally display every record in the subreport, (check can grow) The array is not totally populated.

MUST I completely display the subreport for this to work?
OutOnALimbAlwaysAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
In my experience the answer seems to be yes.

Try this to get around the issue

CHange your formulas as shown

//INIT
shared datevar array holidaydates;
local numbervar knt := count({Holidays_.DatesOnly});
redim holidaydates[knt];
//just so we don't return an array and cause an error
local numbervar i;
for i := 1 to knt do 
    holidaydates[i] := cdate(#01-01-1970#);
''

//CREATE in details section
whileprintingrecords;
shared datevar array holidaydates;
holidaydates[recordnumber] := cdate({Holidays_.DatesOnly});
''

Open in new window


Format the sections of the subreport to SUPRESS BLANK SECTION
Right click the section in the left margin
CLick SECTION EXPERT
Click SUPPRESS BLANK SECTION


Format the subreport to SUPPRESS IF BLANK
Right click the subreport
CLick FORMAT SUBREPORT
CLick the last tab
Click SUPPRESS BLANK SUBREPORT

If the subreport is in its own section you can also suppress the section if blank
Right click the section in the left margin
Click SECTION EXPERT
Click SUPPRESS BLANK SECTION

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OutOnALimbAlwaysAuthor Commented:
Thanks mlmcc, that did the job.  I also formatted the detail section of the subreport to print multiple columns and played around with the layout. I guess as long as you can hide the subreport, it's not really a problem, though it does kind of go against the grain to have a "if it ain't shown, it ain't there" kind of situation.
0
mlmccCommented:
If you want the subreport to show something just put a field or text in the report header.  By not having anything show in the details and suppressing it when blank the subreport won't grow very much

mlmcc
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

James0628Commented:
Interesting.  I didn't realize that subreports did that.  I just tried one with a formula that incremented a count for each record.  I suppressed everything but the detail section, and unchecked "Can Grow".  When the subreport was long enough to display all of the records, I got the correct count in the main report.  When the subreport was too short, I got the last displayed count, plus 1, in the main report.  It seems that the subreport reads and "processes" all of the records that will fit, then it reads the next record, prepares the detail section (including evaluating any formulas in the section), but then sees that there's no room for the detail section, so it just quits.  Good to know.

 James
0
OutOnALimbAlwaysAuthor Commented:
I swear 75% of programming is learning the undocumented peculiarities in whatever programming language you are using!
0
OutOnALimbAlwaysAuthor Commented:
It looks like this is a 'peculiarity' in crystal.  As James says, it is something to keep in mind. I should have mentioned earlier that if you try to simply use an excel spreadsheet in your main report along with another database, it won't work. Crystal warns you that more than one database driver is not supported, and in my case, attempting to use both sql server and excel in one report really screwed up the results.
0
James0628Commented:
FWIW, more than one kind of db in a report can work.  It's not fully supported, so some specific features don't work, but it can be done.  Of course it's best to avoid it if possible.  And if you're not just reading the data sequentially, Excel files can be really slow, since they're not indexed, etc.  FWIW, I got around that by having stored procedures read the Excel data into indexed temp tables, or table variables, and then joining those tables to the other tables.  Multiple users were also a problem with Excel files, so I only used them with some specific reports that were only run occasionally, by a few specific people.

 James
0
OutOnALimbAlwaysAuthor Commented:
James, Ty for the extra tips about excel in crystal.
0
James0628Commented:
No problem.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.