Writing SQL in Report studio "SQL object"

[pooja]

what are the PROS and CONS while Writing SQL in Report studio "SQL object"?

thanks,
P

[blom0344]

It's mostly CONS I guess.  You inject SQL directly without taking advantage of the metadata model. When adjustments are due, you need to effectuate them at report level instead of in the 'sole'model.  You need to explicitly set capabilities for users to use report using 'straight' SQL. I'm sure there are others

Please don't contemplate it  ;) . almost anything is possible (not exactly everything) can be done in a proper model. I recall having to go through endless scripts , simply cause some developer never learned how to do things properly  :o

[cognostechie]

Another CON is that when your company decides to use another database then all those reports will have to be re-written because every DB has it's syntax for a few functions and the SQL you write will use functions specific to your current DB.

People who are familiar with DBs and SQL coders are the ones who write SQL in Report Studio so that they don't have to learn Cognos tools. I can write pretty good SQL too but I don't take the shortcut of writing SQL in reports. I prefer to learn Cognos tools and use their features and follow a proper practise.

[blom0344]

Yes, that is very true!  Even without using SQL directly within reports we ran into trouble with the first Oracle customer. Our models were not compliant enough (somewhere t-sql had crept in) and I had to rework a model to make it Oracle ready.  This is were Cognos really shines. If you do it right from the start you'll have endless 'gratification' in the long run..

[pooja]

Thank you all so much.
Just wondering if there are at least any PROS?


Thanks,
P

[blom0344]

Prototyping might be one and I could think of some technical other opportunities which I will not mention. It might give other people ideas. Don't want to, actually   ???

[Todd129]

Thanks for your interesting post. I have thought about this issue too.

Here is a PRO for you.

Recently, I developed a report for my *own* use that required 11 data items, queried with slight variations in filter, and the same joins.

I wrote 1 short SQL query. Then, I copied it into 11 quick SQL query boxes, each differing by filter values.

It took very little time and voila -- I had my data in about 11 minutes.

Of course, one could do the same thing with data items (I usually do!), but I thought this was an efficient way to accomplish this particular task.

Hope this helps.

Todd

[blom0344]

And one day you will discover how to use  union/union all and you write just the one piece of SQL   ;D

[CognosPaul]

I suppose you could use Cognos  SQL for all your custom SQL needs. That way you can still rely on Cognos to migrate from one RDBMS to another. But who on earth wants to learn yet another SQL language? I know at least 6 variations at different levels, and I'm constantly mixing them up.

Learn from one of my (admittedly recent) failures: A client was insistent on a certain way of doing something, and was swearing up and down that they would never switch databases as they were locked into a contract and the slightest thought hadn't even begun to speculate about the merest possibility of crossing his mind. So we optimized it for Oracle, with some very specific Oracle windowing functions embedded directly into the data items of some reports. The number  of designed reports counted in the low thousands. Everything was running as smoothly and efficiently as you could expect from running Oracle when they suddenly announced that they're upgrading to a new fangled columnar database that is based on Postgres.

[blom0344]

I suppose you could use Cognos  SQL for all your custom SQL needs. That way you can still rely on Cognos to migrate from one RDBMS to another. But who on earth wants to learn yet another SQL language? I know at least 6 variations at different levels, and I'm constantly mixing them up.

Learn from one of my (admittedly recent) failures: A client was insistent on a certain way of doing something, and was swearing up and down that they would never switch databases as they were locked into a contract and the slightest thought hadn't even begun to speculate about the merest possibility of crossing his mind. So we optimized it for Oracle, with some very specific Oracle windowing functions embedded directly into the data items of some reports. The number  of designed reports counted in the low thousands. Everything was running as smoothly and efficiently as you could expect from running Oracle when they suddenly announced that they're upgrading to a new fangled columnar database that is based on Postgres.

Sounds to me that the failure was with the client, not with you  :o

On the other hand, he might not have understood that not all SQL is interchangable.