Change table selection order

rbuser

Hello

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

nicocizik

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.

rbuser

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.

rbuser

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.

nicocizik

Paul,

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.

rbuser

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)

nardmoseley

Paul,

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
www.digital-metaphors.com

rbuser

That's great news Nard. Much appreciated.