How to build a two-level "where used" query with Query Builder

Forum|Forum|11 years ago
April 27, 2015
4 replies
1884 views

I can get some basic queries working using Query Builder, but much of it is still a matter of much trial and error for me. I am trying to build a query which will give me results on the following structure:

The structure has three levels: Caring Unit, Structure and Member, all of which are custom Parts. I can get a report which will give me Caring Unit and Structure combinations, or Structure and Member combinations, but not all three. I am trying to answer the question, "For each member inside a shelter inside a caring unit, what are the attributes of the member, shelter and caring unit?"

Here are the two queries I have working so far. What must I do to combine them and get data on all three levels?

Members in Shelters:

Shelters in Caring Units:

Other

T

TomU

23-Emerald IV

I think you need to use a sub-select and then the results of that get joined against one of the other criteria. You should be able to find what you need somewhere on this page: Resource for reporting

MarcoTosin

21-Topaz I

Rudi,

like Tom said you can use sub-select to filter your report but, as far as I know, you can't get a multilevel report using query builder.

Marco

R

rvonstaden

Author

1-Visitor

Thanks Tom, Marco,

My understanding of sub-select is that I wouldn't be able to use it to get the kind of report that I want. I would like a report which gives, say, Caring Unit Number, Shelter Number, Member Number, Member Name where Member is contained in Shelter and Shelter is contained in Caring Unit. Limited testing would seem to bear this out, or I'm not skilled enough with sub-selects to figure out how to do it.

So, if I can't get this report with Query Builder, what other options do I have? Info*Path? Custom jsp?