Greetings

Is it possible at all :
a) Put several WF-reports on one Excel-worksheet?
b) Put WF-report on Excel-worksheet and force Excel to create graph for this report on the same worksheet?

Also question : I know about support of Excel named ranges, but I can't understand how to use it.
Any suggestions, ideas?

a) yes it is possible, you have to keep the excel opened and load the reports in the different worksheets.

b) that seems to be more a question on "how can I run a macro in Excel" it should IMHO be in an other forum (microsoft??)

The disadvantage of your approach is that you use IBI only as a tool to collect the rough data and put it in an excel sheet.
You mis the power of creating complete reports and graph within the webfocus platform.

Hallo Frank!

We use multiple worksheets. I've asked about one worksheet.

And "b" is also not about putting fex on one worksheet and generating graph on another.

Does exists some trick?

Disadvantage is clear for us.
We already have all needed reports in html.
We did them using HTMLTABLE and -HTMLFORM, so they're normal for ReportCaster.

Customer wants export to excel and some reports are multiple reports and graphs on one page.

Now we can't understand how to make it in Excel.

Alex,

If you want to get Excel to create the graph (and let's face it, using WF to do this is not nice), you will have to use Excel Templates and write your own VBA to render the graph.

Same worksheet different reports? Yes. you can but you have to mess around with the data and fool WebFOCUS into thinking that you are creating a single report. I have done this by appending a second set of data onto the first output and having an indicator to identify the report, page breaking on the indicator to give a psuedo look of two reports. Of course you have to be careful to have the same data types in the same columns and therein lies the main problem.

You might be better off pushing all your data extracts into Excel and then use macros to build your reports, you would be able to use some of the Excel styling that WebFOCUS doesn't have such as "barber pole" background etc.

Named ranges within an Excel Worksheet? You can use these as a data source from within WebFOCUS. You need the ODBC adapter licence first! Then your range needs to have column titles (without included spaces). The range name becomes the file name (again, do not use spaces in these) and the column titles become the field names. You have to set up a System DSN in the servers ODBC manager and then refer to that within the WF adapter / synonym tool.

There is documentation about somewhere on IB's site and I am sure that Jennifer will be along anytime now to give you a reference to it.

Finally, I don't completely agree with Frank's comment about missing the power of WF. If your output has to be in Excel (and a lot of users demand this) then it is best to use the tool(s) that give you the ability to produce the output required. If that means using WF for an extract and Excel for formatting - then so be it.

Good luck

T

I can make a long list of reasns why you should not use Excel at all....

But...it's time to go home....

Thanks Tony, Frank

I'm already digging into:

a) Make WebFOCUS put several reports on different worsheets into existing Excel-template.
b) Each report I mark with WebFOCUS IN-RANGES = 'Report1' ( 'Report2', 'Report3').
c) Then I can write VB Macro utilizing :
Application.Goto Reference:='Report1'
Selection.Copy
Sheets("Combined Report").Select
ActiveSheet.Paste
'... so for each report-part
'hide worksheets with report-parts
Sheets("Report1").Visible = 0
Sheets("Report2").Visible = 0

So it must be possible.

.. The only problem is : I DO NOT KNOW AND DO NOT LIKE VISUAL BASIC!

Reason for Excel is simple:
Our customer wants it.

And I must agree with Tony : Excel-graphs are prettier.

Alex,

I never said they were prettier, I just suggested that if you want graphs in Excel then it is easier to render them using the Excel graphing engine rather than the hassle of creating it in WF and then trying to get the graph to a location where Excel can get at it.

However, I generally never supply graphs in Excel output, if the user wants a graph then they can create one in a small amount of clicks - they just have to be bothered about doing it. Most of the time they want Excel is because they wish to perform "what if" calculations and everything else that goes with Excel output. Invariably they do not really require a graph.

T

I would use an Excel Template sending the data to a specific numbered sheet, together with a Excel named range for the data that is graphed. The Excel graph is set up to be generated from the named range.

Back to the original post, is is possible to put the results of two different reports on the SAME worksheet? For example, I want to put the Report Summary at the top of the report, but then put the detail information just below it on the same worksheet.

Thank you.

CWM,

You could possibly do this as one report using the McGyver technique. Look it up; it is very cool.

I'm pretty sure that everything you want to do is possible, although you may not be able to format it they way you want.

as for running two reports in one excel spreadsheet, you can do that by utilizing

-SET COMPOUND = OPEN 'NOBREAK';

or you can declare in on the fly

ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK

make sure that you close the Excel output on the last report

-SET COMPOUND = CLOSE;

OR

ON TABLE PCHOLD FORMAT EXL2K CLOSE

as for the graph, you could experiment in doing something similar to how its done in the pdf layout painter where you would create a graph in a hold file using something like

ON GRAPH HOLD AS TMPGRPH FORMAT GIF

And then create a small report that calls it in the heading of the report:

DEFINE FILE TABLENAME
BLANK/A1=' ';
END
TABLE FILE TABLENAME
" "
BY BLANK NOPRINT
ON TABLE SET PAGE-NUM NOLEAD
IF RECORDLIMIT EQ 1
ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET STYLE *
TYPE=HEADING,
IMAGE=TMPGRPH.gif,
$
END

Try experimenting with that and see what you get.

Also like they said you can always use an excel template, create the graph on one sheet and point it to another sheet where you will populate your data. If you do it this way though, there is no way to get it into one sheet this way because if you have your data go into the same sheet as your graph, the data will wipe out your graph.

You have one more option also. I remember hearing about an excel add-on that you can use to create reports directly in excel instead of in the tool. Although I don't have any experience with the add-on so your on your own there.

Eric

CWM,

As per Eric, the quickest method is to use compound logic -

APP PREPENDPATH IBISAMP
TABLE FILE CAR
SUM RCOST
    DCOST
 BY COUNTRY
HEADING
"Summary at Country Level"
FOOTING
""
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END
TABLE FILE CAR
SUM RCOST
    DCOST
 BY COUNTRY
 BY CAR
HEADING
"Summary at Country and Car Level"
FOOTING
""
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
END
TABLE FILE CAR
SUM RCOST
    DCOST
 BY COUNTRY
 BY CAR
 BY MODEL
HEADING
"Detail at Model Level"
FOOTING
""
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END

This shows what I mentioned to Alex, in as much that the columns hold different formats of data, which are easily handled using this method, but messy looking if you want well formed report layouts. Of course, this can be manipulated by using dummy columns to move the data columns around etc.

It has the advantage that the output looks like three separate reports (which it is). If you go with a mcgyver technique and combine the output to report from then you will likely end up with an output that looks like one report (which it would be). Having said that, my preference is to use mcgyver and control the report layout through styling (as much as you can with EXL2K output).

T

p.s. I still suggest that if end users want Excel then they want it for few reasons -

• They want to use it as a data source - manipulate it or use as input to another process.
• They want to use Excel's ability to force column resizing when printing.

CWM,
Yes, we finally did such reports.

Technique is simple:
You need Excel template
You fill several worksheets with data.
VB Macros make worksheets invisible and glue data on one worksheet.

This is not difficult, but it's more VB than WebFocus.

Regards,
Alex