Hi,
I’ve got a couple scenarios when creating reports using stored procedures and am wondering if anyone can provide any suggestions. I have a report that is running some complex SQL and therefore needed to be designed using stored procedures. We are using global temporary tables and are dumping the data when the session is dropped. Initially I thought it was great to use a stored procedure as a data provider directly from the data access step upon creation of a new dp. It lists the parameters and so on and so on. Everything worked great when developing the report and creating the VBA front end to pass input to the stored proc parms. Now when it came to deploying this report outside of my own repository came the problem. I was not aware that the connection to the schema/db in the repository where the sp had been compiled is somehow embedded into the report, I would imagine through some sort of id. So to get around this I had to save my initial report as a template and then rebuild it on our clients machine. Not the most ideal scenario, as time equals money. So I thought I’d try recreating the report using a free hand dp to pass the parms to the stored proc that way. You cannot use a secured connection in FHS, this is another hurdle. I have to be sure that the shared connection/SDAC gets created and resides in the locdata folder for use by everyone and if I export a doc to the repository with this shared connection, that the report then runs when imported. I guess I also have to worry about someone editing the connection, this could cause a problem. If I use our proprietary odbc connection to create this connection it might be a bit safer…because if the ODBC DSN changes then our whole system won’t work. Then within supervisor if I disabled edit connection in the BO config that may help as well. If anyone has any comments or suggestions that would be great, maybe I’ve answered my own question but a bit of input would be great.
Thanks.
JTAMBLYN (BOB member since 2003-05-28)