Tabular SQL question

[MFGF]

Pooja posted:

my issue is--

suppose, you pull all the required field(columns) from different table to report studio by using tabular SQL.
Now, my concern is,
Can we able to define prompt, APPLY the calculation and define filter on thsese columns/field, which we are
getting from writing direct tabular sql.

I KNOW WE CAN APPLY ALL FUNCTION WHEN WE PULL THE FILED FROM PACKAGES.
BUT I IS IT POSSIBLE TO APPLY USING SQL

Hi,

Yes, you can add filters (with prompt parameters too) to queries fed by tabular SQL, and you can also add calculations.  If you need to use any functions in these, be aware that only the Cognos function set will be available (not database functions). Also be aware that all the work for this - calculations and filters - is then being done on the Cognos 8 server after the entire tabular SQL data set has been retrieved.

If you know you are going to need the report to be filtered, a better approach would be to add these filters into the tabular SQL as a predicate (WHERE clause).  For prompts, you could use the Prompt() or Promptmany() macro function in the SQL query.  This would be more efficient than filtering locally on the C8 server.  Similarly, for calculations, you could also add these into the SQL query if they would run more efficiently in the database.

Hope that helps!

MF.

[blom0344]

Yep, it is possible and I have seen it done   ;). however it requires you to write Cognos style SQL for Cognos to be able to use the prompt/promptmany expressions.
As said before, it may take considerable less time to build a model and let Cognos generate it's own SQL through the model definition..

[MFGF]

Yep, it is possible and I have seen it done   ;).

You have my deepest sympathy Blom!  Have you recovered from the trauma of witnessing such a shocking event yet?  ;) ;D

[blom0344]

Sorry mate, I posted my reply prior to your editing of the OP. (which makes my reply look like I'm repeating after you  :-\

Does one ever recover from running into Cognos ? (said by ex-BO developer   ;D)

[MFGF]

Sorry about that.  No worries though - far better to get two similar replies than none at all.  ;D

Does one ever recover from running into Cognos?  Sadly not, I'm afraid - it's a life-changing event!  ;)