US and Worldwide: +1 (866) 660-7555
Results 1 to 4 of 4

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

  1. #1

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

    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

  2. #2
    Join Date
    Mar 2003
    Posts
    7,969

    Default

    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.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3

    Default

    i found a great webcast on the topic

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

  4. #4

    Default

    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •