script to iterate columns in a dataset

observerobserver Junior Member
edited December 31, 1969 in Designing Analytics Reports
Hi All,

I've a dataset which contains columns like say MISC, CAR_FUEL, DRIV_REIMB etc. I need to iterate this dataset and alter each column. Say for example i need to compute a value called amount which is
amount = ROUND(ISNULL(MISC ,0) +ISNULL(CAR_FUEL, 0) +ISNULL(DRIV_REIMB, 0) ,2)) AS AMOUNT

How do a write a script to achieve the same?
Thanx in advance

Comments

  • VirgilVirgil Administrator EM admin
    edited December 31, 1969
    Hi observer,



    You can accomplish this with a computed column. You set these up on the Data Set, and they can consist of any type of logic like you have below. You can find a couple of examples on the DevShare area of this site by seaching for 'computed' One example that converts dates in a computed column is located here:

    http://www.birt-exchange.com/modules/wfdownloads/singlefile.php?cid=2&lid=203

    Virgil Dodson
    OpenText Analytics Developer Community
    https://forums.opentext.com/forums/categories/analytics

  • observerobserver Junior Member
    edited December 31, 1969
    Thanx a lot. I've tried using computed columns. But i think I've failed to explain the problem.
    Let me try again. Actually i have two datasets. One is containing columns like id, itemName, etc

    Dataset1: id itemname
    ______________
    1 MISC
    2 CAR_FUEL
    3 DRIV_REIMB
    4 :

    The other table has the actual report data. It has a extractor table with columns like MISC, CAR_FUEL, etc
    Dataset2 : (Report Data ): ______________________________________________________
    name amount MISC CAR_FUEL DRIV_REIMB ..... ______________________________________________________
    Aaa 10,500 500 10,000 0
    Bbbb 11,500 500 10,000 0
    Ccc 11,500 500 10,000 0
    : : : :

    The amount field here is computed using the formula
    amount = ROUND(ISNULL(MISC ,0) +ISNULL(CAR_FUEL, 0) +ISNULL(DRIV_REIMB, 0) ,2)) AS AMOUNT.
    Here my problem is not about altering a column. I need to iterate the column itemname in table1
    and alter each of the row. I need to work on the rows of a dataset, but not the columns. I think "cell"
    is the right word that i should be using.

    If I'm not wrong, then i think i need to write a script in the beforeOpen() event for the Dataset2, where i perform these computations. I hope this time i have expressed my problem more clearly. Kindly guide me as how i should achieve the same. Thanx a million for any kind of help.
Sign In or Register to comment.