I have a nice compound Excel report that the users love, but knowing users, they always ask for more.

Here's my situation...

I have DEFINEd fields that serve as placeholders for the users to enter data. Other DEFINE fields use report data and placeholder data to perform various calculations. The Excel report is used as a working spreadsheet template ready to receive input in certain columns and perform calculations.

One of the columns contains a percent that will be used in another calculation. Whatever value is typed in is the same for all the rows.
Right now the user must enter a value in the percent column, then copy the value down to populate all the rows. What the user would like is to type in the value on the first row and have the value automatically populate down to all the remaining rows.

From what I know about using Excel .mht templates, it won't work, because this is a compound report with a variable number of tabs based on the BYTOC setting. mht templates only accept one dump of data, so I don't think using Excel templates are an option.

I know this may be pushing the envelope with what you can do with Excel formulas in an Excel-formatted report, but I thought it would be possible to use the WebFOCUS LAST function in the DEFINE statement to pull down the value of the percent field from the previous row. What I ended up getting was a circular reference error when the report opened up in Excel. I still think there might be a way to do this, so I was wondering if anyone has tried this sort of Excel trickery using DEFINE fields before.

Dennis

Dennis,
Could you please update your signature with the WF version you use and the platform. This will help other people to help you better. Also some of your code would help to understand the problem better.

I don't quite understand what you'll try to accomplish by using the Last function?

Dennis,

If I understand you correctly, your user is typing into a cell on the Excel worksheet and "would like" (aka demands) that the rest of the cells in that column are updated in line with their input, but only as far as the end of the data rows?

Rather than look for a WebFOCUS function, why not use VBA in the Excel workbook to achieve it?

T

Yes, I know I could program a VBA macro to do that (and I have for other Excel applications), but the users, being typical users, have asked if WebFOCUS could do this for them.

I had a co-worker show me the Excel shortcut for copying a cell's contents to the end of the column data, and it's super simple. I may have to just educate the end users by showing them some Excel shortcuts.

Dennis

Dennis,

I would argue with the users that the right tool for the right job is the rule and WebFOCUS isn't it in this case. Mainly because once the data has been loaded into Excel then WF has finished and disconnected and LAST fieldname doesn't get passed. As you are using a web archive type of excel file then the solution should be there from pure logic point of view.

Good luck

T

Tony,

I'm not actually using a .mht template. This is a straight up ON FORMAT PCHOLD AS FORMAT EXL2K type of report.

Your point is well taken though. If I were using a .mht template, I would definitely add a macro with whatever post-WF functionality that the end users needed/required.

Thanks,

Dennis

Local: AppStudio 8.2.03 - Win7 - Tomcat Server: WebFOCUS 8.2.03 - Windows 2008 R2 Enterprise on VMWare 2 CPU, 3.2GHz, 4GB of RAM Output Formats: HTML, Excel, PDF, PPT, Active Reports