Change table selection order

edited September 2012 in DADE

I'm a seasoned RB user and I am wondering if there is any way changing
the table selection order in the Query Designer. As far as I'm aware
there isn't, but right now this is proving to be a major headache for me
(as I write a suite of reports) and I know it will be a bone of
contention for my end users.

Lets say I have a report that draws on 14 tables. I've also defined
about 10 quite complex expression based calculated fields (oh how I
wished there was a memo editor for this!). I then realize that I need to
add Table X and that I also need to select (Search tab) the data based
upon table X. Now lets say that selecting results using Table X gives
horrendous performance because its NOT the first table listed in the
SQL's "FROM" clause, the database engine (NexusDB in my case) not being
optimized to work in that way. Now I *know* that making Table X the
first table in the FROM listing will solve this performance problem, but
the only way to do this is to clear all my tables in the Query Designer
and start again, which will lose all the work I've done on the other
tabs (Calcs, Search, Sort, etc). Is there a way around this?

RB v14.06

TIA, Paul


  • edited September 2012
    Hi Paul,

    Unfortunately there is no way to alter the table order for the
    programmatically created SQL other than doing it by manually editing the SQL
    yourself. Looking at the code, it does not look like a trivial addition to
    the Query Designer. I will add this to our list of possible enhancements to
    research for a later release.

    Best Regards,

    Nico Cizik
    Digital Metaphors
  • edited September 2012
    Hey, I love Report Builder but how has this one been missed for all
    these years? Its a big big headache. Its means that rather being able to
    take a single report that contains many common calculated fields,
    database links, layout features etc and use it as a base for generating
    many more reports, adding in tables, swapping around select orders etc,
    you have to start from scratch each time, which really hits productivity
    and is very, very dull :(

    I've had to hack the object source for the template to move things
    around since modifying the SQL renders the Query Designer read only. But
    changing the template source is clearly a non starter for end users.

    Really hope you can find a solution to this soon, although I know what
    wish lists are like.

    P.S. One other suggestion: a reporting table (like rbTables, rbFields,
    etc) to store common calculated fields. Just need a field for FieldAlias
    and the Function code. Then these could be made available in the Query
    Designer when creating new ones. Would save on time as well.

  • edited September 2012
    Hello again

    I'm really sorry to bang on about this, but are you able to give any
    indication as to i) the likelihood of this improvement ever happening,
    and if so, ii) when (14.07, 15.01, etc)?

    I (reluctantly) ask because this particular deployment of RB is in major
    upgrade (due out soon) that we are providing for our customers in which
    we are moving reporting from Crystal Reports to RB. Now in CR, changing
    the select order (for the first table in the SQL, at least) is just a
    matter of changing table links in the Visual Linking Expert, i.e.
    specifying the first table in the tree of datasets. Obviously we are
    going to get some inevitable resistance from customers who have been
    using CR for our 15 years with our app; so I really need to minimize any
    obstacles or grounds to complain and to be champion RB for being the
    truly great product that it is.

    Thanks for you patience, Paul.

  • edited September 2012

    I'm sorry but I cannot give any time frame on when a new feature will be
    added to the product.

    We appreciate the feedback and have added your suggestion to our list of
    possible enhancements. I can tell you that since ReportBuilder is such
    a mature product, almost all of our new features are user-requested and
    added by popular demand. If we have enough demand for the feature you
    requested, it will likely make it into the product.

    Best Regards,

    Nico Cizik
    Digital Metaphors
  • edited February 2014
    Not sure if its poor etiquette or not to resurrect old requests like
    this, but now that we are finally rolling out our product with a nice
    new suite of RB reports, we have (in the first week) a customer who
    wants to run a one of our reports in a different context and against a
    different part of our UI. Shouldn't be a problem, except that the first
    table (A) in the tables list is inner joined to the second table (B) and
    in this new context table A will ALWAYS have no data. But since we can't
    swap the order of A and B and then LEFT JOIN A to B, we have to delete
    A, which cascades a removal of about 10 more tables in the report and
    about 15 complex expression type Calc fields. The report is effectively
    put through a mangle and getting it back together again in painful and
    inefficient. Didn't help that my boss was on site with customer doing
    the work, which took an embarrassing amount of time to sort out.

    I appreciate how code schedules work and how feature requests have to
    get logged and prioritized - I live by the same rules! But I really do
    wonder if DM really grasp how much extra labour this design limitation
    causes. Worse, if it was a customer doing the work on the report, they'd
    have no idea how to put it all back together. Its really does give you a
    feeling of dread when you suddenly realize, "oh no, that first table has
    to go!"

    Wont mention it again (promise) :)

  • edited February 2014

    Thanks for the feedback. We'll look into adding this for the next major
    release. If the tables are re-ordered, then all the join conditions have to
    be analyzed and re-defined.

    Best regards,

    Nard Moseley
    Digital Metaphors

    Best regards,

    Nard Moseley
    Digital Metaphors
  • edited February 2014
    That's great news Nard. Much appreciated.

This discussion has been closed.