Problems with Master / Detail in End User Reporting
Problems with Master / Detail in End User Reporting
I?m trying to make a report that will take advantage of Report Builders
nested sub report capability however I am running into some problems.
Using: Delphi 2006 (update 2) & RBuilder Server Edition 10.05 & End
User Reporting on Windows XP(sp2)
Sorry about this post being so lengthy but I wanted to ensure enough
information so the problem could be reproduced.
While testing the End User reporting solution being built for our
product, I was trying to build what I thought would be a simple master
/ detail report however I could not get the sort order to work
correctly with the Visual Query Designer.
The report I was attempting to design would print the Menu System for
our software package. The menus branch out into a tree and go about 12
nodes deep. I was just starting out with a simple report then would
proceed later into making it a drill down, or drill into type report.
Example Table ? Contains root and braches of the menu system records.
Example Table: AppMenu
Field 1: AppMenuName ? primary key (string 40)
Field 2: ChildPos ? order of menu items (integer)
Field 3: Caption ? caption of menu items (string 40)
Field 4: Parent ? parent menu item (string 40)
In the DataTab View I connect using an IBXSession Session Type to the
database defined in my application. Database Type is Interbase. I
don't believe this problem to be database related.
Attempt / Method A
------------------------------
In the Report Designer -> File -> New -> Query Designer
- Ok working on the first query (pipeline APPMENU).
- Added Table APPMENU
- Added Fields listed above.
- No Calc and no Groups.
- Added criteria AppMenu.Parent = ?? in order to filter the first set
of results to the root of the menu listing.
- Added Sort Field ChildPos
- The SQL tab produced the following which was ok:
SELECT APPMENU_1.APPMENUNAME, APPMENU_1.CHILDPOS,
APPMENU_1.CAPTION, APPMENU_1.PARENT
FROM APPMENU APPMENU_1
WHERE ( APPMENU_1.PARENT = '' )
ORDER BY APPMENU_1.CHILDPOS
- The data preview gave me the expected data.
- Ok working on the 2nd query (pipeline ? APPMENU2) This will get
each root menu?s sub menu list.
- Added Table APPMENU
- Added Fields listed above.
- No Calc and no Groups and no Criteria
- Added Sort Field ChildPos
- The SQL tab produced the following which was ok:
SELECT APPMENU_1.APPMENUNAME, APPMENU_1.CHILDPOS,
APPMENU_1.CAPTION, APPMENU_1.PARENT
FROM APPMENU APPMENU_1
ORDER BY APPMENU_1.CHILDPOS
I then went and linked the second query (parent field) to
the first (AppMenuName field) The view displayed the one to many link
correctly.
The data preview gave me the expected data grouped by parent however it
was not sorted by ChildPos.
I proceeded to build the report.
- On the main page I set the data pipeline to APPMENU.
- Added the caption field to the detail section.
- Added a sub report to the detail section.
- Switched to the sub report
- Set the data pipeline of the sub report to APPMENU2
- Added the ChildPos and Caption fields to the detail section.
- Suppressed the header and footer of the sub report
Previewed the Report
Shows the Root menu items in the correct order.
Show the correct child items but in the wrong order.
From posts on the new groups I discovered the Magic SQL window that
produces the following result:
SELECT APPMENU_1.APPMENUNAME APPMENUNAME_2,
APPMENU_1.CHILDPOS CHILDPOS_2,
APPMENU_2.APPMENUNAME, APPMENU_2.CHILDPOS,
APPMENU_2.CAPTION, APPMENU_2.PARENT
FROM APPMENU APPMENU_1
INNER JOIN APPMENU APPMENU_2 ON
(APPMENU_2.PARENT = APPMENU_1.APPMENUNAME)
WHERE ( APPMENU_1.PARENT = '' )
ORDER BY APPMENU_1.CHILDPOS, APPMENU_2.PARENT
I now see the problem here is that the SQL generated is missing the
APPMENU_2.CHILDPOS that was specified in the 2nd pipeline (APPMENU2)
I went and created another version of the report trying to use another
technique that combines the two queries from the start.
Attempt / Method B
------------------------------
In the Report Designer -> File -> New -> Query Designer
- Ok working on the query (pipeline APPMENU).
- Added Table APPMENU (SQL Alias APPMENU_1)
- Added the four Fields listed above for alias APPMENU_1
- No Calc and no Groups.
- Added criteria AppMenu.Parent = ?? in order to filter the first set
of results to the root of the menu listing.
- Added Sort Field APPMENU_1.ChildPos
- Added Table APPMENU again (SQL Alias APPMENU_2)
- Specified: Left Outer Join
- On fields APPMENU_2.Parent = APPMENU_1.APPMENUNAME
- Added the four Fields listed above but for alias APPMENU_2
- No Calc and no Groups and no additional Criteria
- Added Sort Fields APPENU_2.Parent and APPMENU_2.ChildPos
- The SQL tab produced the following which was ok:
SELECT APPMENU_1.APPMENUNAME, APPMENU_1.CHILDPOS,
APPMENU_1.CAPTION,
APPMENU_2.APPMENUNAME APPMENUNAME_2,
APPMENU_2.CHILDPOS CHILDPOS_2,
APPMENU_2.CAPTION CAPTION_2
FROM APPMENU APPMENU_1
LEFT OUTER JOIN APPMENU APPMENU_2 ON
(APPMENU_2.PARENT = APPMENU_1.APPMENUNAME)
WHERE ( APPMENU_1.PARENT = '' )
ORDER BY APPMENU_1.CHILDPOS, APPMENU_2.PARENT,
APPMENU_2.CHILDPOS
The data preview gave me the expected data grouped by parent however it
was not sorted by APPMENU_2.ChildPos as I expected.
Again I went to check the results of the Magic SQL window:
SELECT APPMENU_1.APPMENUNAME, APPMENU_1.CHILDPOS,
APPMENU_1.CAPTION,
APPMENU_2.APPMENUNAME APPMENUNAME_2,
APPMENU_2.CHILDPOS CHILDPOS_2,
APPMENU_2.CAPTION CAPTION_2
FROM APPMENU APPMENU_1
LEFT OUTER JOIN APPMENU APPMENU_2 ON
(APPMENU_2.PARENT = APPMENU_1.APPMENUNAME)
WHERE ( APPMENU_1.PARENT = '' )
ORDER BY APPMENU_1.CHILDPOS
What wrong here is that the SQL generated is missing all the specified
sort fields for APPMENU_2.
I proceeded to build the report.
- On the main page I set the data pipeline to APPMENU.
- Added report group on Added the APPMENU_1.AppMenuName
- Added the APPMENU_1.Caption field to the group header section.
- Added the APPMENU_2.Child field to the detail section.
- Added the APPMENU_2.Caption field to the detail section.
Previewed the Report
Shows the Root menu items in the correct order and grouped.
Show the correct child items but still in the wrong order.
Finally I decided to see what would happen if I forced the query into
the data view.
Attempt / Method C
------------------------------
SQL Tab -> right-click -> Edit -> Answer Yes to the prompt that
disables visual design.
SELECT APPMENU_1.APPMENUNAME, APPMENU_1.CHILDPOS,
APPMENU_1.CAPTION,
APPMENU_2.APPMENUNAME APPMENUNAME_2,
APPMENU_2.CHILDPOS CHILDPOS_2,
APPMENU_2.CAPTION CAPTION_2
FROM APPMENU APPMENU_1
LEFT OUTER JOIN APPMENU APPMENU_2 ON
(APPMENU_2.PARENT = APPMENU_1.APPMENUNAME)
WHERE ( APPMENU_1.PARENT = '' )
ORDER BY APPMENU_1.CHILDPOS, APPMENU_2.PARENT,
APPMENU_2.CHILDPOS
Everything is sorted and grouped correctly.
Summary of Problems:
---------------------------------------------------
1.) In the first scenario the Magic query generated missed the fact
that I wanted to sort by APPMENU_2.CHILDPOS.
2.) In the second scenario the Magic query generated completely lost
the fact I wanted to sort by results of the second table in the join.
3.) Event though the third attempt was successful the problem with
sorting from both attempts would have prevented the effective use of
the Visual Data Design by an End User who is not SQL aware.
Other issues:
---------------------------------------------------
4.) When returning fields back to the Available Fields list, it did so
incorrectly and lost the correct origin of the fields.
For example: if both APPMENU_1.CHILDPOS and APPMENU_2.CHILDPOS were
added to the sort list, and then revoked. APPMENU_1.CHILDPOS would be
listed twice and APPMENU_2.CHILDPOS would be missing.
A workaround is to force the Query Designer to refresh by closing and
opening it again.
5.) I could not find a way to link two data views together if one of
them is using Visual Design and the other is not. It would be nice to
be able to do this in the Visual Design for creating master / detail
reports that involve normal tables, and queries that use stored
procedures.
6.) After some time working in the Designer I would get A/V?s when
trying to minimize or resize the designer. (still trying to track down
the cause)
7.) When linking to Query Designers together. Changing the join type
does not seem do anything to change the join type of the SQL on the
second designer. (Check using the Magic SQL window)
Thanks for looking at this long post. If you can checkout the issues
encountered that would be great. I can possibly provide sample
database and reports that reproduce the problem upon request.
I believe that some of the problems are based on the same table having
been joined multiple times using the Query Designer. I suspect the
cause to be problems distingusing between table alias 1 and table alias
2 in the building of the sort order.
If I had been programming the report using datasets, rather than trying
to build it as an end user, this issue probably would not have come up
simply by the difference in the data access.
-- Ray
I?m trying to make a report that will take advantage of Report Builders
nested sub report capability however I am running into some problems.
Using: Delphi 2006 (update 2) & RBuilder Server Edition 10.05 & End
User Reporting on Windows XP(sp2)
Sorry about this post being so lengthy but I wanted to ensure enough
information so the problem could be reproduced.
While testing the End User reporting solution being built for our
product, I was trying to build what I thought would be a simple master
/ detail report however I could not get the sort order to work
correctly with the Visual Query Designer.
The report I was attempting to design would print the Menu System for
our software package. The menus branch out into a tree and go about 12
nodes deep. I was just starting out with a simple report then would
proceed later into making it a drill down, or drill into type report.
Example Table ? Contains root and braches of the menu system records.
Example Table: AppMenu
Field 1: AppMenuName ? primary key (string 40)
Field 2: ChildPos ? order of menu items (integer)
Field 3: Caption ? caption of menu items (string 40)
Field 4: Parent ? parent menu item (string 40)
In the DataTab View I connect using an IBXSession Session Type to the
database defined in my application. Database Type is Interbase. I
don't believe this problem to be database related.
Attempt / Method A
------------------------------
In the Report Designer -> File -> New -> Query Designer
- Ok working on the first query (pipeline APPMENU).
- Added Table APPMENU
- Added Fields listed above.
- No Calc and no Groups.
- Added criteria AppMenu.Parent = ?? in order to filter the first set
of results to the root of the menu listing.
- Added Sort Field ChildPos
- The SQL tab produced the following which was ok:
SELECT APPMENU_1.APPMENUNAME, APPMENU_1.CHILDPOS,
APPMENU_1.CAPTION, APPMENU_1.PARENT
FROM APPMENU APPMENU_1
WHERE ( APPMENU_1.PARENT = '' )
ORDER BY APPMENU_1.CHILDPOS
- The data preview gave me the expected data.
- Ok working on the 2nd query (pipeline ? APPMENU2) This will get
each root menu?s sub menu list.
- Added Table APPMENU
- Added Fields listed above.
- No Calc and no Groups and no Criteria
- Added Sort Field ChildPos
- The SQL tab produced the following which was ok:
SELECT APPMENU_1.APPMENUNAME, APPMENU_1.CHILDPOS,
APPMENU_1.CAPTION, APPMENU_1.PARENT
FROM APPMENU APPMENU_1
ORDER BY APPMENU_1.CHILDPOS
I then went and linked the second query (parent field) to
the first (AppMenuName field) The view displayed the one to many link
correctly.
The data preview gave me the expected data grouped by parent however it
was not sorted by ChildPos.
I proceeded to build the report.
- On the main page I set the data pipeline to APPMENU.
- Added the caption field to the detail section.
- Added a sub report to the detail section.
- Switched to the sub report
- Set the data pipeline of the sub report to APPMENU2
- Added the ChildPos and Caption fields to the detail section.
- Suppressed the header and footer of the sub report
Previewed the Report
Shows the Root menu items in the correct order.
Show the correct child items but in the wrong order.
From posts on the new groups I discovered the Magic SQL window that
produces the following result:
SELECT APPMENU_1.APPMENUNAME APPMENUNAME_2,
APPMENU_1.CHILDPOS CHILDPOS_2,
APPMENU_2.APPMENUNAME, APPMENU_2.CHILDPOS,
APPMENU_2.CAPTION, APPMENU_2.PARENT
FROM APPMENU APPMENU_1
INNER JOIN APPMENU APPMENU_2 ON
(APPMENU_2.PARENT = APPMENU_1.APPMENUNAME)
WHERE ( APPMENU_1.PARENT = '' )
ORDER BY APPMENU_1.CHILDPOS, APPMENU_2.PARENT
I now see the problem here is that the SQL generated is missing the
APPMENU_2.CHILDPOS that was specified in the 2nd pipeline (APPMENU2)
I went and created another version of the report trying to use another
technique that combines the two queries from the start.
Attempt / Method B
------------------------------
In the Report Designer -> File -> New -> Query Designer
- Ok working on the query (pipeline APPMENU).
- Added Table APPMENU (SQL Alias APPMENU_1)
- Added the four Fields listed above for alias APPMENU_1
- No Calc and no Groups.
- Added criteria AppMenu.Parent = ?? in order to filter the first set
of results to the root of the menu listing.
- Added Sort Field APPMENU_1.ChildPos
- Added Table APPMENU again (SQL Alias APPMENU_2)
- Specified: Left Outer Join
- On fields APPMENU_2.Parent = APPMENU_1.APPMENUNAME
- Added the four Fields listed above but for alias APPMENU_2
- No Calc and no Groups and no additional Criteria
- Added Sort Fields APPENU_2.Parent and APPMENU_2.ChildPos
- The SQL tab produced the following which was ok:
SELECT APPMENU_1.APPMENUNAME, APPMENU_1.CHILDPOS,
APPMENU_1.CAPTION,
APPMENU_2.APPMENUNAME APPMENUNAME_2,
APPMENU_2.CHILDPOS CHILDPOS_2,
APPMENU_2.CAPTION CAPTION_2
FROM APPMENU APPMENU_1
LEFT OUTER JOIN APPMENU APPMENU_2 ON
(APPMENU_2.PARENT = APPMENU_1.APPMENUNAME)
WHERE ( APPMENU_1.PARENT = '' )
ORDER BY APPMENU_1.CHILDPOS, APPMENU_2.PARENT,
APPMENU_2.CHILDPOS
The data preview gave me the expected data grouped by parent however it
was not sorted by APPMENU_2.ChildPos as I expected.
Again I went to check the results of the Magic SQL window:
SELECT APPMENU_1.APPMENUNAME, APPMENU_1.CHILDPOS,
APPMENU_1.CAPTION,
APPMENU_2.APPMENUNAME APPMENUNAME_2,
APPMENU_2.CHILDPOS CHILDPOS_2,
APPMENU_2.CAPTION CAPTION_2
FROM APPMENU APPMENU_1
LEFT OUTER JOIN APPMENU APPMENU_2 ON
(APPMENU_2.PARENT = APPMENU_1.APPMENUNAME)
WHERE ( APPMENU_1.PARENT = '' )
ORDER BY APPMENU_1.CHILDPOS
What wrong here is that the SQL generated is missing all the specified
sort fields for APPMENU_2.
I proceeded to build the report.
- On the main page I set the data pipeline to APPMENU.
- Added report group on Added the APPMENU_1.AppMenuName
- Added the APPMENU_1.Caption field to the group header section.
- Added the APPMENU_2.Child field to the detail section.
- Added the APPMENU_2.Caption field to the detail section.
Previewed the Report
Shows the Root menu items in the correct order and grouped.
Show the correct child items but still in the wrong order.
Finally I decided to see what would happen if I forced the query into
the data view.
Attempt / Method C
------------------------------
SQL Tab -> right-click -> Edit -> Answer Yes to the prompt that
disables visual design.
SELECT APPMENU_1.APPMENUNAME, APPMENU_1.CHILDPOS,
APPMENU_1.CAPTION,
APPMENU_2.APPMENUNAME APPMENUNAME_2,
APPMENU_2.CHILDPOS CHILDPOS_2,
APPMENU_2.CAPTION CAPTION_2
FROM APPMENU APPMENU_1
LEFT OUTER JOIN APPMENU APPMENU_2 ON
(APPMENU_2.PARENT = APPMENU_1.APPMENUNAME)
WHERE ( APPMENU_1.PARENT = '' )
ORDER BY APPMENU_1.CHILDPOS, APPMENU_2.PARENT,
APPMENU_2.CHILDPOS
Everything is sorted and grouped correctly.
Summary of Problems:
---------------------------------------------------
1.) In the first scenario the Magic query generated missed the fact
that I wanted to sort by APPMENU_2.CHILDPOS.
2.) In the second scenario the Magic query generated completely lost
the fact I wanted to sort by results of the second table in the join.
3.) Event though the third attempt was successful the problem with
sorting from both attempts would have prevented the effective use of
the Visual Data Design by an End User who is not SQL aware.
Other issues:
---------------------------------------------------
4.) When returning fields back to the Available Fields list, it did so
incorrectly and lost the correct origin of the fields.
For example: if both APPMENU_1.CHILDPOS and APPMENU_2.CHILDPOS were
added to the sort list, and then revoked. APPMENU_1.CHILDPOS would be
listed twice and APPMENU_2.CHILDPOS would be missing.
A workaround is to force the Query Designer to refresh by closing and
opening it again.
5.) I could not find a way to link two data views together if one of
them is using Visual Design and the other is not. It would be nice to
be able to do this in the Visual Design for creating master / detail
reports that involve normal tables, and queries that use stored
procedures.
6.) After some time working in the Designer I would get A/V?s when
trying to minimize or resize the designer. (still trying to track down
the cause)
7.) When linking to Query Designers together. Changing the join type
does not seem do anything to change the join type of the SQL on the
second designer. (Check using the Magic SQL window)
Thanks for looking at this long post. If you can checkout the issues
encountered that would be great. I can possibly provide sample
database and reports that reproduce the problem upon request.
I believe that some of the problems are based on the same table having
been joined multiple times using the Query Designer. I suspect the
cause to be problems distingusing between table alias 1 and table alias
2 in the building of the sort order.
If I had been programming the report using datasets, rather than trying
to build it as an end user, this issue probably would not have come up
simply by the difference in the data access.
-- Ray
This discussion has been closed.
Comments
I would first recommend upgrading to the latest version of ReportBuilder (RB
10.06).
Although we appreciate a certain amount of detail, we would prefer it if you
would send us an example of the issue you are experiencing if the
explanation is as long as the one posted. This may be one of the longest
posts we have ever received
run on our machines is much more helpful and allows us to track down any
possible issues or bugs quicker. Please send all examples in .zip format to
support@digital-metaphors.com. Also, try to please keep each post focused
on a single issue. If you have more than one problem, please start a new
thread so we, and others can see and respond to them accordingly.
Some quick notes...
I tested a similar scenario to yours with the DBDEMOS database and the Magic
SQL appeared to generate correctly. Have you tried this with another DB or
other data?
Linking manually edited dataviews is not supported in the Query Designer.
There is a warning about this when the Edit option is selected in the Query
designer.
Are the AV's and other issues occurring at design time or run time?
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com