Newbie Q: Access a column in an SQL row via a parameter

[gershon.adams]

Hi,

I have a sql table with N columns of data. I need to run the same basic report on the table - each time ordering and summing on a particular column. I saw wiki page for Dynamically+Generating SQL but i dont think that will work for me - as my report needs to access to all the columns in the table. I would also only like use use the report designer interface.

I was wondering if one can access a particular column in the row via a parameter?

For example - i create a string field and i give it a value of something like`mytablename`.${columnName} where ${columnName} has the column i want to work on(`col1`). Or maybe the value is defined as just ${columnName} and that has the full path to the data (like `mytable`.`col1`}

thanks for any help
GA

[Taqua]

Use PRD-3.9 and you have scripting integrated into the major datasources. That allows you to easily create queries on the fly based on your own parameters.

Read about it here: http://www.sherito.org/2011/11/penta...tasources.html
This same functionality is available for SQL, Metadata, Mondrian and OLAP4J datasources.

You cannot use prepared-parameters to modify the query structure. So the ${..} syntax is out of the question. You need to compute/assemble the SQL query yourself.

String query = "SELECT table." + dataRow.get("columnName") AS alias FROM table ..

If you use column aliases smartly, you can then use different fields under the same name then.

If you are not using PRD-3.9, then well, you can do similar stuff, but with by far more manual work and in a more hacky/fragile way.

[gershon.adams]

i found a great webcast on the topic

http://wiki.pentaho.com/display/COM/...+the+BI+Server

[gershon.adams]

thanks