Query Builder Criteria

srector · ‎Jan 12, 2011

So, my PDMLink headache du jour is;

Why is it that all of the attributes available in the 'Select' tab are not also all available in the 'Criteria' tab? Isn't it logical that I might want to filter on the very columns I'm interested in displaying?

I have a report that links together our Changeable objects with their corresponding Change Task, Change Notice, and Change Request. In the report I list the 'Number' for these various change objects along with the 'Context Name' associated with the Change Record.

On the Criteria tab I am able to add filters so that users can look for specific change object numbers, but I can't figure out how to add a filter to limit the Context. The Context attribute I'm displaying isn't available in the Criteria tab. Consequently, users can't search for all change objects within a specific Product.

Can anyone point me toward a straightforward solution to this problem?

Thanks,

Steve

AL_ANDERSON · ‎Jan 12, 2011

Add Context in the From tab.

Join Context to the object you want to filter by context in the join tab.

Then you can select, filter, add as criteria with wildcards, etc. your
context values.

Al Anderson

Steven Rector <steven.rector@drs-sts.com>
01/12/2011 02:31 PM
Please respond to
Steven Rector <steven.rector@drs-sts.com>

To
-
cc

Subject
[solutions] - Query Builder Criteria

Caterpillar: Confidential Green Retain Until: 02/11/2011

So, my PDMLink headache du jour is;
Why is it that all of the attributes available in the 'Select' tab are not
also all available in the 'Criteria' tab? Isn't it logical that I might
want to filter on the very columns I'm interested in displaying?
I have a report that links together our Changeable objects with their
corresponding Change Task, Change Notice, and Change Request. In the
report I list the 'Number' for these various change objects along with the
'Context Name' associated with the Change Record.
On the Criteria tab I am able to add filters so that users can look for
specific change object numbers, but I can't figure out how to add a filter
to limit the Context. The Context attribute I'm displaying isn't available
in the Criteria tab. Consequently, users can't search for all change
objects within a specific Product.
Can anyone point me toward a straightforward solution to this problem?
Thanks,
Steve

Site Links: View post online View mailing list online Send new post
via email Unsubscribe from this mailing list Manage your subscription
Use of this email content is governed by the terms of service at:

jessh · ‎Jan 12, 2011

Other responses have already pointed you to the solution to this
particular problem.

As for the reason that some attributes listed in the Select tab are not
also available in the Criteria tab....

The Select tab lists items that directly correspond to database columns.
It also lists items that are "derived", i.e. that correspond to the
results of executing a get*() method on a Java Persistable object
representing the entire database row. These are distinguished by their
icons -- the items corresponding to columns have green triangles on
their icons whereas derived items have a circle with a yellow line on
their icons.

In general you really should endeavor not to select derived attributes
if you're going to return a substantive number of results -- as the
performance will suffer due to the need to pull all columns of data back
from the database, create a Persistable for each row, and then make
additional Java calls which may do additional expensive work (e.g.
retrieving and creating another object and calling a method on it).

Criteria are restricted to those which can be evaluated in the database,
which automatically restricts the criteria to operating on items which
actually correspond to database columns. As in this case there's often
a database column around containing what you want, e.g. in another table
for you to join into the query.

--
Jess Holle
>
> So, my PDMLink headache du jour is;
>
> Why is it that all of the attributes available in the 'Select' tab are
> not also all available in the 'Criteria' tab? Isn't it logical that I
> /*might*/ want to filter on the very columns I'm interested in displaying?
>
> I have a report that links together our Changeable objects with their
> corresponding Change Task, Change Notice, and Change Request. In the
> report I list the 'Number' for these various change objects along with
> the 'Context Name' associated with the Change Record.
>
> On the Criteria tab I am able to add filters so that users can look
> for specific change object numbers, but I can't figure out how to add
> a filter to limit the Context. The Context attribute I'm displaying
> isn't available in the Criteria tab. Consequently, users can't search
> for all change objects within a specific Product.
>
> Can anyone point me toward a straightforward solution to this problem?
>
> Thanks,
>
> Steve
>
>

ddemay · ‎Jan 12, 2011

The whole tool (query builder), useful in concept, needs a overhaul.it's
dated.I really hate it and avoid it at all costs, particularly since I know
more and easily grow impatient, but that's no reason not to have something
better (just because one may not know how to make something better). It
would be nice if PTC motivated it's user community to create unsupported
addons that were distributed with the product. I could imagine quite a fancy
query builder that provides for reports without thinking in SQL terms. If I
recall we can hook Info*Engine queries into Cognos which is better vs. query
builder long term, but it's been well over a year since I investigated this.
I recall when I worked for NASA that we built a really nice setup
participants GUI for workflow tasks that was generic and wide open and I
believe we tried to get PTC to incorporate it into the general product and
were even willing to give them the right to the code, but there was a
refusal or sorts. (It was much better than the one in 9.x too!) Don't mean
to light a match next to TNT with that statement, but it irks me that there
is no motivation to allow good ideas from those outside of PTC to
incorporate either code into the general product.

If it's stable, tested, and useful to everyone. Why the heck not????

David DeMay

jkent · ‎Jan 13, 2011

We need an "app store" for PTC products.

RonThellen · ‎Jan 13, 2011

There is (kind of). It's the Public File Areas (

jessh · ‎Jan 13, 2011

For PTC products themselves or for add-ons to those products (often
being written by 3rd-parties)?

A store, with support for reliable payment, etc, (which is a substantive
undertaking to set up by all accounts from those who have set up app
stores) or a site for freely sharing?

It has long surprised me that there's not a well organized, searchable
site of free Windchill add-ons (ranging from customizations to utilities
to workflows to ReportTemplates) out there -- and in particular that the
PTC/User site does not seem to have an active and well organized
sub-site along these lines. Such a site should have review comments on
each item -- much like Amazon.

--
Jess Holle

jkent · ‎Jan 13, 2011

For PTC products themselves or for add-ons to those products (often
being written by 3rd-parties)?

I say for PTC products, 3rd party companies, and the user community.

A store, with support for reliable payment, etc, (which is a substantive
undertaking to set up by all accounts from those who have set up app
stores) or a site for freely sharing?

No doubt that setting up the infrastructure for an "app store" is a huge
undertaking. I'm not trying to minimize the fact that it is not by
simply stating "create an app store".

Why not have both pay and free apps. If I'm a business that makes money
selling applications for PTC products then I'm going to sell you my
product. If I'm a user that created a

little utility then maybe I place that utility on the site and allow
users to download it for free. First thing that comes to mind here is
the Windchill Cache Recovery Tool on PTC User written by Ron Thellen. He
is simply giving it away.

AL_ANDERSON · ‎Jan 13, 2011

I appreciate that there is a steeper learning curve with the Query Builder
tool than most people would like, but having gotten over that curve, I now
find the combination of the HTML Rose Model and the Query Builder tool to
be outstanding.

I can get almost anything that is gettable, directly from production,
without the need for development activities, code promotions, or any other
"middle man" between me and the data.

I can put the output directly into excel that can update from excel as
embedded web queries. And I can tweak them iteratively as often as I like
until I get the data that my users need. Once the query is set, the users
can simple maintain their own excel sheet to get raw data, and add macros
and formulas on other tabs to parse that raw data to their heart's content
without any need of "IT" programmers.

I realize that it requires the ability to understand UML, and a little
trial and error. But once you get the hang of it, it is very powerful,
especially now that you can get IBAs for light types, and include light
types in your From tab.

UML for Dummies, or some similar starter book for understanding UML would
be a good start. That, and maybe SQL for dummies, to understand how
database queries work. But with a very basic understanding of those
things, you can then do quite a bit. I use it at least once a week to do
things that would have otherwise required months of waiting for a
developer to then spend weeks designing, building, testing, and deploying
reports that are then hard to change once in production.

Al Anderson
Solar Turbines Incorporated

"Thellen, Ronald" <->
01/13/2011 06:20 AM
Please respond to
"Thellen, Ronald" <->

To
"Joe Kent" <jkent@rdleverage.com>, David DeMay
<->, "-"
<->
cc

Subject
[solutions] - RE: Query Builder Criteria

Caterpillar: Confidential Green Retain Until: 02/12/2011

There is (kind of). It?s the Public File Areas (

MikeLockwood · ‎Jan 13, 2011

Totally agree.
We used Windchill for 2 full years before "discovering" this capability. The tutorial was enough to get started; lots of brute force hours got us a bit further, then we started using some of the tools / techniques that you mention. These reports are now indispensible to daily usage and monthly metrics reporting.

It's another area that would be probably be used a lot more if it was explained / documented a bit better.

We're just starting to convert many of the reports to Cognos by the way, eliminating most of what we have to do separately in Excel up to now (some reports are definitely better run from within Excel though).

ddemay · ‎Jan 13, 2011

I never said it wasn't useful.

I said it's outdated.

Perhaps to describe my thought more clear; query builder SHOULD be as easy as the JCA configurable tables in the UI.

I find SQL easier to use then query builder because I have more power there.

I'm certainly not advocating customization all the time. In fact I'm saying if query builder was redesigned for modern times, you'd build query like a configurable table UI using real production data and it would let you continually refine it until you had the exact data set you wanted.

No SQL mindset, no code, no models, no UML. An non technical end user could create the report and have assurance that the query written behind the scenes is as optimized as possible.

This can still enforce ACL's during query creation.

It would be a marriage of query builder, configurable search, and the JCA config tables.

Dave

Sent from my Verizon Wireless BlackBerry

SteveVinyard · ‎Jan 13, 2011

That sounds pretty great. Building a Query live over a small set of data would be nice. I certainly like the idea of not having to remember all the class names and what not like I would have to using SQL though.

[cid:image001.gif@01CBB330.DAC496F0]

Steve Vinyard
Application Engineer

MikeLockwood · ‎Jan 13, 2011

Just one more on this...
Attached is the list of "types" that one can select for building query builder reports - just FYI.