Here's what I need to do:
I have 30 Customers I need to send excel spreadsheets to each month. Each workbook in excel has 3 tabs. Instead of running this program 30 times to create 30 workbooks, I want to run once and distribute to each Customer. Can Reportcaster do this? And I thought I would either try a looping program or try McGyver technique to get the FEX written correctly. Does any one have thoughts on which is best with Reportcaster?

This message has been edited. Last edited by: Kerry,

Are the workbooks different or the same? How are they sorted? Have you looked into bursting?

The workbooks are same, with same data on each tab. They are sorted by Customer Number. And No I have not looked into bursting. Will this be a problem with bursting?

I guess what I'm really asking is does every customer get the same data or data specific for their site? I suspect it is the latter. If so, you might want to look at doing a coordinated compound report with the PDF Layout Painter (which would give you the tabs) and then look into bursting to send to each customer.

To confirm your question. Each Customer has the same format output for each tab (3), but it is Customer specific with their own data. That is why I thought a McGyver or Looping technique of some kind is necessary. I have been asked that the output be in excel. I did ask if it could be PDF output, and they said "No".

Cheryl

looping is also possible, not not easy to explain here
ik will look it up next year and if you need it, i will expain it

Thank you for your response.
I would like some assistance with the looping, so this will work in Reportcaster. As I mentioned above, I would like to distribute these reports. Any help is greatly appreciated.

Frank,
When you have time, please explain the looping.
thank-you.

Will the PDF Layout Painter (Document Composer) work here with excel output with 3 tabs per excel spreadsheet for 30 different Customers?

If frank or someone else doesnt get back to you by the time im inm the office tomorrow I will paste up some logic for you.

this could be a good start point for you in the meantime http://forums.informationbuild...831058331#4831058331

Just burst the output according to a NOPRINTed BY field.

Take the fairly standard fex -

TABLE FILE CAR
SUM RCOST
    DCOST
 BY COUNTRY
 BY CAR
 BY MODEL
ON TABLE PCHOLD FORMAT EXL2K
END

I want to send the output of European Countries to Richard and and Hans and the Asian Countries to Suki.

I could just add the names in the distribution list along with their burst values -

Burst Value          Address
ENGLAND              Richard@domain.com
ENGLAND              Hans@domain.com
FRANCE               Richard@domain.com
FRANCE               Hans@Domain.com
JAPAN                Suki@domain.com
etc. etc.

Or I could make it easier by adding a burst value of my own -

DEFINE FILE CAR
  CONTINENT/A7 = DECODE COUNTRY('ENGLAND' 'EUROPE' 'FRANCE' 'EUROPE' 'ITALY' 'EUROPE' 'W GERMANY' 'EUROPE'
                                'JAPAN' 'ASIA' ELSE 'UNKNOWN');
END
TABLE FILE CAR
SUM RCOST
    DCOST
 BY CONTINENT NOPRINT
 BY COUNTRY
 BY CAR
 BY MODEL
ON TABLE PCHOLD FORMAT EXL2K
END

and then burst it with a little ease -

Burst Value          Address
EUROPE               Richard@domain.com
EUROPE               Hans@domain.com
ASIA                 Suki@domain.com

T

This message has been edited. Last edited by: Tony A,

I believe the problem with using standard bursting techniques here is that CherylB has a compound EXL2K report. According to this post from July 2009, bursting of compound EXL2K reports is not currently supported.

http://forums.informationbuild...=387105371#387105371

That's very true Dan.

For that scenario I just write the files out to a network folder and then use CDONTS to email them as attachments via a VBScript called within the RC job.

T

Dan or Tony,
Yes I am using a Compound EXL2K report and want to burst it. Each Excel workbook has 3 tabs for 30 different Customers. I would like to run this program and have it loop 30 times, BUT then have the seperate workbooks distributed to 30 different Customers. OR, if easier, sent to me and then I can take 30 different attachments and send them to each seperate Customer. Is this possible? What does PDF Layout Painter (or "Document Composer") have to offer? I do not want PDF output but I need Excel, can PDF Layot Painter do this? I thought I heard I can have Exel output. Any suggestions?

Cheryl,

As Dan mentioned, "bursting of compound EXL2K reports is not currently supported", or at least on the version you are using. Not sure if later releases have had this addition or not. Perhaps Kerry could check with the product team and advise?

In the meantime, I guess that you will have to get down to programming this by hand to place the various report outputs onto a network share that you then access to send manual emails.

However, to automate this completely, you could use the various apps available, WebFOCUS (obviously ) to create the report files plus, perhaps, a scripted batch process to process the output files, then attach them to emails to be sent out via your SMTP(?) server

Looping is the easy part, just use variables and a REPEAT loop (you'll have to double check the syntax here as I am unable to test currently) -

-SET &Country1 = 'ENGLAND';
-SET &Country2 = 'FRANCE';
-SET &Country3 = 'ITALY';
-SET &Country4 = 'JAPAN';
-SET &Country5 = 'W GERMANY';
FILEDEF File1 DISK path etc.
FILEDEF File2 DISK path etc.
FILEDEF File3 DISK path etc.
FILEDEF File4 DISK path etc.
FILEDEF File5 DISK path etc.
-RUN
-REPEAT :Loop FOR Cnt FROM 1 TO 5;
TABLE FILE CAR
SUM RCOST
    DCOST
 BY COUNTRY
 BY CAR
 BY MODEL
WHERE COUNTRY EQ &Country&Cnt.EVAL.QUOTEDSTRING
ON TABLE HOLD AS File&Cnt.EVAL FORMAT EXL2K
END
-:Loop

The testing bit will be to get the emails automatically created with the right attachment on your op system. On windows, as I mentioned, I use CDONTS within a VBScript batch file built within and then submitted from the WebFOCUS program.

T

Tony,
I will try this. I'm familiar with this -REPEAT but I'm not familiar with CDONTS and VBScript batch file. Any help on this would be great. Maybe some sample code would help, thank-you. I will get started on my coding in the meantime.
CB

Cheryl,

You are on on Unix/Solaris and CDONTS is used on Windows platforms so sample code will not help you. Perhaps someone more familiar with the platform(s) you use might be able to help there.

T

quote:

Burst Value Address
ENGLAND Richard@domain.com
ENGLAND Hans@domain.com
FRANCE Richard@domain.com
FRANCE Hans@Domain.com
JAPAN Suki@domain.com
etc. etc.

Does anyone know if the distribution list can point to a file, ie a customer master file?
&cusno &cusemail &sourcefile

This could save Sheryl all the trouble of looping. Especially if she has 3000 customers instead of 30.

Report Caster can use an Address List, external file or procedure that creates a file on the fly. There are posts in this forum, or look at the Report Caster user manual.

Thanks N.

I found some posts. will use distribution file next time when I needed it.


Hua

I have some of the looping working. I was not able to get the Address List to work. I have to look at the posts. Thank you all for your help. This gave me many ideas and examples.
Again thanks.