Hi all,

It's been a while. I apologize for that. I've been super busy with various things. Hoping to show my face a lot more here soon enough.

Anyways, we have a need for storing Excel data sheets in a location where end users can get to to update them, but where we can tie to them within WebFOCUS to report from via a synonym description of them from the Data Servers apps directory. Has anyone here on the forums come up with a good solution for this type of scenario? How did you go about it? Ideally this would be like a secured spot in that this would be the only place the end users could get to once they do have access to where ever it needs to be. We've got a userfiles directory share on the reporting server already. Would we be able to place an Excel sheet therein maybe? Then, utilize ReportCaster to run a job to refresh a synonym from an Excel file from there?

Any thoughts/solutions are welcome. Thanks!

This message has been edited. Last edited by: FP Mod Chuck,

Hi CoolGuy

You have the right idea. A secured shared drive on the reporting server is the easiest way to accomplish this. If it is not under the \ibi\apps folder you can put an APP MAP to the location in the server profile. Since reporting against these is a big requirement having them on the reporting server is the best place. Let me know if you need more details

Hi Chuck,

Thanks for the information on APP MAP and the server profile! Tested that out and it works great! Now, the only missing piece is whether there's a way to schedule a refresh of a synonym generated from these Excel sources? I looked at creating a RC job, but there isn't a task option for such a thing. Do you know of a way we can schedule a synonym to refresh itself?

Thanks so much for chiming in!

Here is the syntax for creating an excel synonym via a fex


! del c:\ibi\apps\baseapp\retaildb.*
-RUN
CREATE SYNONYM baseapp/retaildb FOR retail/retaildb_llf2009.xls PARMS ' WORKSHEET "RetailDB?RetailDB" HROWS
1 RECORDS 0' DBMS DIREXCEL CHECKNAMES UNIQUENAMES


You should be able to schedule this fex to run.

Chuck, you rock.

Do you know where I can find some documentation on the syntax you gave me? Not seeing it in the 8201M docs, so I'm guessing I'll have to jump back a few versions of documentation before I actually find it if it is documented? haha

Trying to get an understanding of what the last bit does and what options are available for PARMS, etc.

... PARMS ? WORKSHEET <worksheetName?> HROWS <-- specifies to use first row as header row?
1 RECORDS 0' DBMS DIREEXCEL <-- says we're getting data from Excel? CHECKNAMES <-- ?? UNIQUENAMES <-- ??

Thanks again!

CoolGuy

I could not find documentation either. I turned on traces on the reporting server and did a create synonym for a direct excel adapter and found what it generated.

The worksheet parm is the actual worksheet name, HROWS is the number of rows of header information and RECORDS should be 0 so it scans the entire worksheet to get the field attributes

Hope that helps!

Okay, thanks for the insight! Will have to try that then. Which trace did you enable by chance? Any particular one?

Hi

On the reporting server under Workspace / Traces I used the option enable trace. You should time this based on having the create synonym ready to run from a separate browser session. Then once you run the create synonym disable the traces as you will get a bunch of them and may be hard to find the trace. They are located under the edatemp directory and begin with ts. Look for the syntax CREATE SYNONYM in the trace file.

Regards