Banded Report Writer (OpenInsight 32-bit)
At 24 JAN 2012 08:32:16PM Jim Vaughan wrote:
How do I add fields from other tables to a BRW report? I do not see a way to add related tables. I do not see a function like XLATE that would allow me to access other tables from a calculated field.
At 25 JAN 2012 12:38AM bshumsky wrote:
How do I add fields from other tables to a BRW report? I do not see a way to add related tables. I do not see a function like XLATE that would allow me to access other tables from a calculated field.
Hi, Jim. How are the fields from the other tables related to the fields in the main ("primary") table? You might be able to define a "sub report" with the fields from the secondary table, though to be honest I haven't tried that with anything other than multivalued data.
Of course, the simplest alternative is to define dictionaries in the primary table that do the XLATE for you, and then reference those dictionaries in your report definition…
- Bryan Shumsky
At 25 JAN 2012 04:01PM Jim Vaughan wrote:
Hi Bryan,
The records in the other table have keys that appear as columns in the main table. Think of the main table as an order line item table (or order detail table) where there are multiple line items for each order. The orders are in the order table and the line items are in the line item table. The key to the order table is a column in the line item table making it possible to look up the order table columns (e.g. “ship to”) when running a report against the line item table.
I have not been able to grasp how the sub report works. At any rate, it seems like a convoluted way to get a field from a related table.
We have created many symbolics in the dictionaries that do the Xlates to get obvious data and make it available in other tables. The problem is customers always come up with more columns they need from other tables (there are many more than two related tables in our app).
We are in control of the dictionaries in that when we release an enhanced version of the app we may have changed the dictionaries in support of new features. Our customers re-import all their data (using our routines that handle exporting and re-importing) when they upgrade to a version with changed dictionaries. So, even if a customer could modify the dictionary for a necessary Xlate they would lose that item when we release a version with modified dictionaries (and would have to recreate it).
The customers are used to being able to create a calculated field that does an Xlate in Revelation Reporter and that is maintained as part of the report, not the dictionary.
Jim R Vaughan
At 25 JAN 2012 04:27PM bshumsky wrote:
Hi Bryan,
The records in the other table have keys that appear as columns in the main table. Think of the main table as an order line item table (or order detail table) where there are multiple line items for each order. The orders are in the order table and the line items are in the line item table. The key to the order table is a column in the line item table making it possible to look up the order table columns (e.g. “ship to”) when running a report against the line item table.
I have not been able to grasp how the sub report works. At any rate, it seems like a convoluted way to get a field from a related table.
We have created many symbolics in the dictionaries that do the Xlates to get obvious data and make it available in other tables. The problem is customers always come up with more columns they need from other tables (there are many more than two related tables in our app).
We are in control of the dictionaries in that when we release an enhanced version of the app we may have changed the dictionaries in support of new features. Our customers re-import all their data (using our routines that handle exporting and re-importing) when they upgrade to a version with changed dictionaries. So, even if a customer could modify the dictionary for a necessary Xlate they would lose that item when we release a version with modified dictionaries (and would have to recreate it).
The customers are used to being able to create a calculated field that does an Xlate in Revelation Reporter and that is maintained as part of the report, not the dictionary.
Jim R Vaughan
Hi, Jim. It does sound like creating a sub report is exactly what you want to do, and - perhaps with a little explanation - it won't sound so convoluted.
After you've defined your primary report, which contains the fields that are contained in the primary table, you can then define an additional report (by pressing on the New Report button on the "Reports" left-hand Reports/Fields/Properties window). In this additional report, you will specify the fields that should be displayed from your linked, or secondary, table - for example, the various fields that should be displayed for the shipto customer.
Once you have both the main and secondary reports defined, back in the main report you'll choose to "link" the subreport to the main report by clicking the subreport button on the design menu (it's the little button next to the bound picture button in the "Fields" area of that menu; you should be able to hover over it and see "Add Subreport"). When you click the "Add Subreport" button, you'll be presented with the name(s) of any reports that can be used as a subreport; select the secondary report we've just defined. Then, with the cursor set to the crosshairs, draw (on the main report) where you want the subreport's output to go. Finally, right-click on the subreport area you've just defined, and choose "Link Subreport" from the context menu. You'll be presented with a box that will ask you whether the subreport is being used to display associated multivalues in the main table, or (as in your current case) the subreport is being used to link between two different tables. Check that button ("The subreport is a master/child relationship among different tables"), and then select which field in the main report should match which field in the subreport (ie, which field in the main report contains the key to the records in the subreport, and which is the key field in the subreport's table).
While this might _sound_ complicated, it's actually very straightforward; the problem is trying to put into words something that's mostly mouse clicks and selections. I believe if you give it a go, you'll find it's _not_ that difficult to do, and it then allows your users to create these linkages "on the fly". Note that you can of course define multiple different subreports, and link them as appropriate throughout the main report.
Hope that helps,
- Bryan Shumsky
At 25 JAN 2012 05:57PM Jim Vaughan wrote:
I'll give it a try and let you know how I make out.
Jim R Vaughan
At 27 JAN 2012 04:48PM Jim Vaughan wrote:
Hi Bryan,
Ok I’ve gotten the hang of it now. I’ve got some other questions.
1. How would one sort/group on a field if it appears in the report only by virtue of being in a subreport. So in my example, what if you wanted to run a report against the line items but sort them by order “ship to”. In Revelation Reporter you would just add the calculated field (Xlate) to get the “ship to” then sort on it. I can’t figure out how to do the same.
2. How can I get fields that are not multi-value to display their value for each row when I have sorted the report by a multi-value field?
Thanks,
Jim R Vaughan
At 28 JAN 2012 12:20AM bshumsky wrote:
Hi Bryan,
Ok I’ve gotten the hang of it now. I’ve got some other questions.
1. How would one sort/group on a field if it appears in the report only by virtue of being in a subreport. So in my example, what if you wanted to run a report against the line items but sort them by order “ship to”. In Revelation Reporter you would just add the calculated field (Xlate) to get the “ship to” then sort on it. I can’t figure out how to do the same.
2. How can I get fields that are not multi-value to display their value for each row when I have sorted the report by a multi-value field?
Thanks,
Jim R Vaughan
Hi, Jim. Glad that the subreport stuff is making sense now.
However, I can't see any way to let you sort (or especially group) the main report on an element that would be in the subreport - you would _have_ to create a dictionary item in the main table that (via XLATE) got the secondary table's related value.
I'm not certain about what you're asking for in question #2 - can you explain a bit more?
Thanks,
- Bryan Shumsky
At 28 JAN 2012 11:52AM Andrew McAuley wrote:
Item 2. Line items explode out such that MV1 MV2 MV3 etc are on different lines now. Single Valued Column _only has_ MV1 so nothing appears with MV2 and MV3 lin
es. How to address this?
World leaders in all things RevSoft
At 28 JAN 2012 01:01PM bshumsky wrote:
Item 2. Line items explode out such that MV1 MV2 MV3 etc are on different lines now. Single Valued Column _only has_ MV1 so nothing appears with MV2 and MV3 lin
es. How to address this?
World leaders in all things RevSoft
Well, one approach would be to put the non-multi-valued items on one row, and then show the multivalued data in a column underneath it - almost like having a "band" for the regular data and a a "band" for the multivalued data.
Of course, if that's not the look that you're going for, then perhaps using the subreport for the associated multivalue data would give you the results you want. In this case, you'd define a subreport containing only the associated multivalue fields (or, if required, the associated multivalue fields along with any single valued fields that you wanted repeated). Then, add in the subreport to the master report, and when "linking" the subreport to the master report, specify that this is a multivalued subreport by choosing the first radio button in the Link Subreport dialog - the one that says "The subreport is an associated multivalue group within a single table". This should "explode" the multivalues into their own report, and I believe you'll be able to replicate the single-valued data you need there as well.
EDIT: Looking at the code, it seems that the BRW will _always_ use the value number of the exploded multivalue field, so I don't think the above solution will work (it'll still look at value <x> in the single valued fields, thus returning null for the result). So…for 9.3.1, I can try to and 'enhance' the BRW to support this kind of normalization (in the subreport/master report join), though determining _when_ we want only the first value rather than the <x>th value while processing the multivalued list is a bit of a wrinkle…
Hope that helps,
- Bryan Shumsky
At 30 JAN 2012 08:55PM Jim Vaughan wrote:
Hi Bryan,
Regarding item 2, Andrew is exactly right. When sorting on the MV field, MV1could be on line 1 and MV2 could be on line 14 and you want to know what the value is for the corresponding single valued field. We have a number of standard reports that were done in Revelation Reporter that we are trying to re-do with BRW and this is a problem.
Jim R Vaughan
At 30 JAN 2012 11:18PM bshumsky wrote:
Hi Bryan,
Regarding item 2, Andrew is exactly right. When sorting on the MV field, MV1could be on line 1 and MV2 could be on line 14 and you want to know what the value is for the corresponding single valued field. We have a number of standard reports that were done in Revelation Reporter that we are trying to re-do with BRW and this is a problem.
Jim R Vaughan
Hi, Jim.
For the 9.3.1 release, we've enhanced the BRW so that single-valued fields will always pull from the first value, regardless of which value has been "exploded" as the key. So if you use the subreport/main report capabilities of the BRW to join a set of associated subvalues to a main report, you will be able to use single valued data in that subreport as well.
I believe the beta for 9.3.1 will begin in the next week or two; you might want to sign up for that to see what you think…
Hope that helps,
- Bryan Shumsky
At 31 JAN 2012 11:59AM Jim Vaughan wrote:
Hi Bryan,
Thanks for that, we will sign up to get that beta.
FWIW Revelation Reporter had a MV option called “suppress duplicate line info” which would suppress the repeated showing of the single value when successive rows in the report were from the same record. You could select that option on any of the MV fields that were displayed.
Jim R Vaughan
At 01 MAR 2012 07:53PM Jim Vaughan wrote:
Hi Bryan,
I am not seeing this working in the beta. I have a simple report: 1 MV field, 1 single value field. I group and sort by the MV field. The corresponding rows for the single value show a value in only one row when there are multiple rows exploded from a record with more two or more values in the MV field.
Jim R Vaughan
At 02 MAR 2012 12:42AM bshumsky wrote:
Hi Bryan,
I am not seeing this working in the beta. I have a simple report: 1 MV field, 1 single value field. I group and sort by the MV field. The corresponding rows for the single value show a value in only one row when there are multiple rows exploded from a record with more two or more values in the MV field.
Jim R Vaughan
Hi, Jim. And just to clarify, you are using a subreport that contains mixtures of multiple values and single value fields, that's joined to a main report?
EDIT I've just tried it, and it seems to work for me. Here's what I did:
1. Take a table that has a mixture of single and multivalue fields, such as an ORDERS table, and open up the BRW Designer;
2. Create a new report containing the "header" items (like customer number, name, order date, etc.), and call this the ORDERS Report;
3. Create a new report containing the multivalue items (like quantity, item, price, etc.) along with some single valued data that we want to repeat (for my test, I reused customer name), and call this the ORDERS SubReport;
4. On the ORDERS SubReport, change the 'visible' property on the header, footer, and titles section to false;
5. Back on the main ORDERS Report, "stretch" the details section and then click on the "Subreport" button, choosing "ORDERS SubReport" from the list, and then draw the subreport section into the details area;
6. Right-click on the now-embedded subreport, and choose "Link Subreport" - select the ID of the record, and the "master" of the multivalue set (perhaps it's item)
When I then preview or test run this report, I see my single valued data first, then the contents of the associated multivalued set and the single valued data that I put into the multivalued set, repeated on each line.
Do you not see this same behavior, if you follow these steps? And (just to be sure) you did run the client setup on the workstation you're using when you installed the 9.3.1?
Thanks,
- Bryan Shumsky
At 02 MAR 2012 05:11PM Jim Vaughan wrote:
Hi Bryan,
Ok it looks like I had not run the client setup. It is working now.
To be clear, I am not doing what you described. I have no sub report. What I have is a work order record with a bill of material (BOM) that is a MV list of items. There is single work order id therefore associated with several BOM items. I am sorting the report by the (MV) items.
Report before sorting:
BOM Item WORK_ORDER
RAW WO-990
A-TYPE WO-995
GEAR-A
RAW WO-992
B-TYPE WO-997
GEAR-B
RAW WO-991
Report after sorting by BOM Item:
BOM Item WORK_ORDER
A-TYPE WO-995
B-TYPE WO-997
GEAR-A WO-995
GEAR-B WO-997
RAW WO-990
RAW WO-992
In writing this to you, I have noticed an inconsistency between the different outputs. In the print preview (with no sorting) I do not see all the MV values. However in a file output to txt or rtf formats I do see all MV values (in other words, there are only 5 lines on the report not the 7 shown above). The sample I show above is copied from the txt file I produced. In the print preview the 2nd BOM item (e.g. GEAR-A under work order WO-995) is not shown. I would prefer that all the MV values be shown as this was the behavior of Rev Reporter.
Jim R Vaughan
At 02 MAR 2012 05:32PM bshumsky wrote:
Hi Bryan,
Ok it looks like I had not run the client setup. It is working now.
To be clear, I am not doing what you described. I have no sub report. What I have is a work order record with a bill of material (BOM) that is a MV list of items. There is single work order id therefore associated with several BOM items. I am sorting the report by the (MV) items.
Report before sorting:
BOM Item WORK_ORDER
RAW WO-990
A-TYPE WO-995
GEAR-A
RAW WO-992
B-TYPE WO-997
GEAR-B
RAW WO-991
Report after sorting by BOM Item:
BOM Item WORK_ORDER
A-TYPE WO-995
B-TYPE WO-997
GEAR-A WO-995
GEAR-B WO-997
RAW WO-990
RAW WO-992
In writing this to you, I have noticed an inconsistency between the different outputs. In the print preview (with no sorting) I do not see all the MV values. However in a file output to txt or rtf formats I do see all MV values (in other words, there are only 5 lines on the report not the 7 shown above). The sample I show above is copied from the txt file I produced. In the print preview the 2nd BOM item (e.g. GEAR-A under work order WO-995) is not shown. I would prefer that all the MV values be shown as this was the behavior of Rev Reporter.
Jim R Vaughan
When you generated the output to the print preview, did you do this from the BRW Designer itself? Did you run "Preview" or "Test Run"? Or was this from RTI_BRW_GENERATEREPORT?
- Bryan Shumsky
At 03 MAR 2012 01:20PM Jim Vaughan wrote:
This was done from the BRW Designer itself. I ran a print Preview. I did notice that the HTML and Excel files were like the print preview, that is not all MV values show.
Jim R Vaughan
At 03 MAR 2012 05:49PM bshumsky wrote:
This was done from the BRW Designer itself. I ran a print Preview. I did notice that the HTML and Excel files were like the print preview, that is not all MV values show.
Jim R Vaughan
In the unsorted case (where you're "losing" the multivalues), the BRW is not "exploding" the multivalues but is instead just showing them 1 on each line. Do you have the field that shows the multivalues set to "grow if needed"? It's possible that the multivalues _are_ being displayed, but because the output field isn't big enough (and isn't set to grow) they're getting cut off.
- Bryan Shumsky
At 05 MAR 2012 11:09AM Jim Vaughan wrote:
Hi Bryan,
Ok thanks, that's the reason. I did not have it set to "grow". I was unaware of that property.
I have some other questions/concerns but I will put them in a new thread.
Jim R Vaughan