Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: Formula Columns in a Report

    I had a situation and I would like to hear if it had ever happened to anyone:

    I have a report with one query made to two tables (lets call then master and detail). That report receives as parameter the primary key of the master table. In order to display properly the report i ve separated the query in 2 groups (gr_master y gr_detail). On the gr_master group y needed to calculate a value (an initial amount) from data no visible on the report, so I created a Formula column that made the select to the DB. I placed this FC on the gr_master group and then did the layout of the report.
    Executing this report It seemed to be very slow (it took 20 minutes to retrieve and display 300 records!!!). Exhausted all other possibilities I did a SQL_TRACE of it, and it showed that the query inserted in the FC was repeated 300 times (one for each record brought) instead of 1 time (the amount of records in the group gr_master).
    Its that usual method in the report (reports 6.0.8)???? Does it have any strange condition that yo see??? Did it ever ocurred to anyone???

  2. #2
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: Formula Columns in a Report

    Originally posted by dprado
    I had a situation and I would like to hear if it had ever happened to anyone:

    I have a report with one query made to two tables (lets call then master and detail). That report receives as parameter the primary key of the master table. In order to display properly the report i ve separated the query in 2 groups (gr_master y gr_detail). On the gr_master group y needed to calculate a value (an initial amount) from data no visible on the report, so I created a Formula column that made the select to the DB. I placed this FC on the gr_master group and then did the layout of the report.
    Executing this report It seemed to be very slow (it took 20 minutes to retrieve and display 300 records!!!). Exhausted all other possibilities I did a SQL_TRACE of it, and it showed that the query inserted in the FC was repeated 300 times (one for each record brought) instead of 1 time (the amount of records in the group gr_master).
    Its that usual method in the report (reports 6.0.8)???? Does it have any strange condition that yo see??? Did it ever ocurred to anyone???
    Do not put formular column in reports with selects. Those selects will be exceuted as how many rows from where it is called, which will definitedly create a lot of unessary traffic and possibly a lot unnecessary pareses.

  3. #3
    Join Date
    Mar 2004
    Posts
    4

    Angry Re: Formula Columns in a Report

    Originally posted by lynden.zhang
    Do not put formular column in reports with selects. Those selects will be exceuted as how many rows from where it is called, which will definitedly create a lot of unessary traffic and possibly a lot unnecessary pareses.
    But that is the whole point.... where it was put, the FC must have executed only once, since the group only had one record.

  4. #4
    Join Date
    Feb 2004
    Posts
    108
    If possible, include the formula/calculation in the master table query.
    Call a function that does this calculation in your select statement itself.
    It's a better idea to include such calculations in select statements rather than have FC for it.

  5. #5
    Join Date
    Mar 2004
    Posts
    4
    Originally posted by pinakdb
    If possible, include the formula/calculation in the master table query.
    Call a function that does this calculation in your select statement itself.
    It's a better idea to include such calculations in select statements rather than have FC for it.
    That hint conflicts directly with the explanation given above, because I say there that it is 1 select with 2 groups, not 2 querys.
    Beside that, why is better to include calculations taken from records outside the query in the query???? If you mixed two operations entirely different in a query (such as the query itself and a calculated field that cames from somewhere completely different), it would hurt the performance (having to ressolve in memory the two of then at the same time), and it takes out clarity of the problem.
    Finally, with that concept, how or rather when would you use a FC???

  6. #6
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by dprado
    That hint conflicts directly with the explanation given above, because I say there that it is 1 select with 2 groups, not 2 querys.
    Beside that, why is better to include calculations taken from records outside the query in the query???? If you mixed two operations entirely different in a query (such as the query itself and a calculated field that cames from somewhere completely different), it would hurt the performance (having to ressolve in memory the two of then at the same time), and it takes out clarity of the problem.
    Finally, with that concept, how or rather when would you use a FC???
    Your problem is actually the formula has been executed 300 times though you put it in a group, and assume it will be executed only once.
    I believe the report executes the formula before records are grouped, and that creates 300 roundtrip.

    So if you don't change anything else, but put the formula in the select will definitely improve performance a lot. Try it and you will see.

Posting Permissions

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