With CQM, user defined SQL was allowed, and with the option to get the Native SQL generated from expressions, it was possible to apply a simple user-defined SQL COUNT(*) to get a rowcount for the data queried.
However, with DQM, only Native MDX is generated, and user defined MDX is not supported. Is there any other way to get a "rowcount" for DQM reports?
You know there is a count aggregate, right? Or you could add a data item whose expression is the number 1 and sum it.
I am pretty much never a fan of using SQL in Cognos reports. There are nearly always other solutions.
Couldn't agree more ! Writing your own SQL creates different versions of the truth and a whole bunch of other problems. Make a change to the model and it doesn't reflect in reports which have developer written SQL. Change something in the DB and those reports
might stop working etc. Developing reports is different than writing code in the DB to query data.
Actually, I'm working with this in Java, and automatically generating the SQL for me. Which is what the project I'm working on is. It analyzes the XML Specification, the Native SQL, and the Model Spec to gain information about the report being run.
I'm well aware there's better ways to do things using the UI, but programatically my options are more limited.