Forgot password? | Forgot username? | Register

Multiple Creators in Excel reports

Multiple Creators in Excel reports

Has anyone figured out how to write a report in Excel that includes the Creator field and displays items only once, whether they have only one or more than one Creator? It seems that ours will display the record twice if there are two creators--once for each creator--and three times if three creators, etc. I have tried fiddling with the table join types in the Microsoft SQL Wizard, and none of the options seemed to resolve this.

Reporting in Crystal and then exporting does not always work so well either because often to get the creator data to print properly you have to put it in a subreport, and subreports do not seem to export well or consistently from Crystal. So you wind up with a gap where you want the creator data to appear.

Any advice appreciated.

Will Real
Carnegie Museum of Art

Edited by: - 01-Jan-70 09:00:00

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Multiple Creators in Excel reports

Hi Will,
We are having similar trouble and for quite some time now.
We are hoping for a solution too.
The problem exists it seems across tables where more than one link is
made and also with fields that contribute to history tables where more
than one listing/history exist. The problem and solution, if it is the
same one, is perhaps better explained by KE though as my understanding
is limited to our own situation.
In anticipation though,
Lee-Anne

Lee-Anne Raymond
Project Officer
Collection Information

Lee-Anne Raymond
DAMS Manager (Acting)
useravatar
Offline
21 Posts
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Multiple Creators in Excel reports

Hi Will and others,

Here at the Smithsonian Institution's National Museum of Natural History,
we have gotten around this issue by using a Crystal Report solution then
exporting from Crystal to Excel. Within Crystal the field or fields that are
in grids are combined together using a subreport. The subreport contains
three formulas. Here is an example for combining multiple collector names
into a single string of collectors. (Could easily substitute creator instead
of collector)

First formula defines and initializes a Shared StringVar variable to a
blank string. This first formula is placed in the suppressed report header
section.

Shared StringVar ss_collectors := "";

Second formula is the processing formula to combine the fields and rows of
the grid into one long string. The long string in this examble is formated
as follows: Collector: With:
name>, .... and .

WhilePrintingRecords;
Shared StringVar ss_collectors;
If ss_collectors = '' Then
ss_collectors := "Collector: " + {Collecto_csv.NamFullName}
Else
If InStr (gs_collectors, "With:") = 0 Then
ss_collectors := ss_collectors + " With: " +
{Collecto_csv.NamFullName}
Else If NextIsNull ({Collecto_csv.NamFullName}) Then
ss_collectors := ss_collectors + " and " +
{Collecto_csv.NamFullName}
Else
ss_collectors := ss_collectors + ", " +
{Collecto_csv.NamFullName};

This formula is placed in the Detail section. The WhilePrintingRecords
processing function is important so don't forget to include it. The Details
section is also suppressed. This may seem weird but what we are doing is
using the detail section for processing only. This will then allow us to
just have one row of data generated for what normally would be multiple
rows.

Last formula is simply the formula that prints out our nice long string
shared variable. This is placed in the unsuppressed report footer field.
WhilePrintingRecords;
Shared StringVar ss_collectors;
ss_collectors;

Again, the WhilePrintingRecords is important here so don't forget to
include it.

Now the tricky part is how to get this all lined up properly in Crystal so
when it is exported to Excel there are no blank columns or rows in the
output. This part is time consuming and involves a lot of trial and error
but is not impossible. Using the alignment and sizing tools in Crystal you
can assure that alll of your fields and subreports are the same height and
top aligned to each other and the top of the section. The other trick is to
bump each field and subreport right up against the one before and after it,
leaving no white space between fields. For fields in subreports, make sure
the printing field is at the top left most position of the section it is in
and is as long as the subreport field length. In other words have it take
up the entire width of the subreport size.

One other trick I use if I only want the first row of a multi row grid to
print is to create a subreport, put the fields desired in the detail section
and in the detail section's suppression formula I put the following
command:

Not OnFirstRecord;

This evaluates to True for all records except the first one meaning the
detail section will be suppressed for every record except the first. Only
the first record's data is printed.

I hope I have done an adequate job of explaining this technique. It isn't
ideal but it does work. If you have any further questions don't hesitate to
ask.

Beth :-)

Informatics Office,
Office of the Assistant Director
for Information Technology
National Museum of Natural History
Smithsonian Institution

--- Beth L. Gamble ----------------------------------
Senior Systems Analyst
National Museum of Natural History
Smithsonian Institution

Beth Gamble
Senior Systems Analyst
useravatar
Offline
29 Posts
Female 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Multiple Creators in Excel reports

Hi Beth,
Thanks for the time you put into this. We've had trouble because we need
to do both tricky things from within the same report/s (as it seems you
might as well). That is we need to print the first instance only (as
with taxon name - not the full history) and all the multiples per
specimen/record for collector and identifier etc while at the same time
not duplicating the other values per record over and over until the
multiples are exhausted. It has been exhausting. :)
Might take time, but I'll try this.
Thanks,
Lee-Anne


Lee-Anne Raymond
Project Officer
Collection Information Management Systems
Museum Victoria

Lee-Anne Raymond
DAMS Manager (Acting)
useravatar
Offline
21 Posts
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Multiple Creators in Excel reports

Beth, thanks from us at CMA as well for your detailed post. I'm on it!

Will

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Multiple Creators in Excel reports

Hi All,

A few extra notes on ths technique. Crystal's export to Excel can be very
very slow and also seems to bomb on exports of large record sets. So if you
are exporting a large set of records (say over 2500) you may need to run
the report and export several times on subsets of the record set and then
merge them after export. As I said, this is not an ideal solution but a
geeky kludge to at least get something.

Lee-Anne, multiple tricky things in the same report is something we do all
the time. Basically each multiple links field trick is a different
subreport linked to the master report. The first taxon name trick would be
the suppressing the details section formula I mention toward the end of my
post. The collector and identifier tricks are the main technique I
described and each would be in their own subreport. This may be overkill to
mention this but if a particular CSV file is used in the subreport it does
not and should not be included in the main report. This is very important
to avoid the duplication of your main report data fields. My rule of thumb
is if it is a one to one relationship it goes in the main report. One to
many relationships go in subreports or you must create appropriate groups in
the main report to avoid duplication.

Many of our reports turn out to be only a few key catalog fields and a boat
load of subreports. This can make the report processing slow but it seems
to be the only way so slow is better than nothing in the grand scheme of
things.

Happy Holidays everyone,
Beth :-)

--- Beth L. Gamble ----------------------------------
Senior Systems Analyst
National Museum of Natural History
Smithsonian Institution

Beth Gamble
Senior Systems Analyst
useravatar
Offline
29 Posts
Female 
Administrator has disabled public posting. Please login or register in order to proceed.
There are 0 guests and 0 other users also viewing this topic

Board Info