Hi,

We recently began creating reporting objects to be used in our dashboard through the Report Assistant tool. However, we began having restrictions when we wanted to create data files using SQL Passthrough statements.

Is there a way we can create either a Master file or a Reporting object using SQL Passthrough?

Thanks in advance.

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

By definition, SQL Passthru doesn't use a master.

I would think you would have to hold the data and master and write this code in the editor or Other and then do the Report Assist piece on the hold file.

Someone more hip to MRE than I am could probably give you a better answer.

That is correct Ginny. Reporting objects are based minimally on a master file description. I suppose it could be a currently existing MFD or one that has been created in a preliminary hold file generate from some SQL.

Having said that, I doubt that it would work putting the passthru into the "Other" area. When a user wants to create a new report from a reporting object, the first thing it looks for is the master file specified for the desired object. No code is run first. Thus, if the MFD only exists by executing the SQL passthru, and that code has not yet been executed, you would probably get a Master File not Found error.

This probably won't help at all here, but you can use SQL on Masters - this happens when you don't specify the engine in the SQL statement or don't set the engine before the SQL statement. I'm not sure if this is called SQL Passthru but it is SQL.

This SQL code uses the Master for the CAR file:

SQL SELECT * FROM CAR
END

When you specify the engine name, that is the key for WF to use passthru and no MFDs are necessary. If you just SQL Select, that is not passthru and you must have an MFD. In either case, that will not help for reporting objects.

Are there any updates on Reporting Objects and SQL Passthru?

I'm wondering if anyone has developed a technique or two for this.

If the 'sql passthru' code is static, you can have the dba store it as a sql View; than create a webfocus synonym, and base your reporting object on that.

True, But I need to push some filtering into an in-line view and I'm hoping I can provide that within a Reporting Object.

The filter condition would effectively applied as ...

select * from [ select that defines the view ] where [ logical screening condition ]

so the TABLE's WHERE would in effect be treated as WHERE TOTAL.

If the filtering logically needs to be done at the incoming detail level, that's a problem.

But if WHERE TOTAL (or HAVING) would generate the intended net result for your filters, my suggestion is feasible, and the sql server would probably optimise appropriately.

Hi

You can create a master file to an SQL script
1. Create .Sql file with your SQL statements
2. Place this script in app path folder
3. When you create the webfocus synonym you can select to create it for SQL script

in this scenario you don't need a DBA to create a view on the database level

As far as I know there are going to be new features which will support webfocus variables passed to the SQL scripts so in the future you could define filters on the master file level or filters in the reporting object which will prompt the user for these type of variables