Banded Report Writer (Banded Report Writer)
At 13 MAR 2012 12:26:27PM Jim Vaughan wrote:
In BRW it is not possible to see all the database fields when editing in the BRW script editor. The most commonly used table in our application has over 280 fields. When the list of database fields pops up, several columns of field names are off the screen with no way to get to them. This is on a fairly high resoluion display (1600x1200) and with the BRW script editor shoved all the way to one side to make as much room as possible.
Many of the fields in the dictionary are Xlates to fields in other tables that are often needed when building reports from this pimary table. The inability to use Xlate in calculated fields exacerbates this situation by causing our dictionaries to grow. If Xlate were a supported function in the calculated fields life would be much easier. Is there some fundamental reason why Xlate functionality is not offered?
At 15 MAR 2012 02:27AM bshumsky wrote:
In BRW it is not possible to see all the database fields when editing in the BRW script editor. The most commonly used table in our application has over 280 fields. When the list of database fields pops up, several columns of field names are off the screen with no way to get to them. This is on a fairly high resoluion display (1600x1200) and with the BRW script editor shoved all the way to one side to make as much room as possible.
Many of the fields in the dictionary are Xlates to fields in other tables that are often needed when building reports from this pimary table. The inability to use Xlate in calculated fields exacerbates this situation by causing our dictionaries to grow. If Xlate were a supported function in the calculated fields life would be much easier. Is there some fundamental reason why Xlate functionality is not offered?
Hi, Jim. Indeed, there is a fundamental reason why Xlate isn't offered as one of the scripting functions - the scripting function is evaluated inside another .NET component that actually renders the output. The "hooks" that we have, for pulling data from the host, performing conversions, performing SELECTs, etc., don't get called when we try to insert non-VBScript code into one of those "scripting" controls, and so our attempts to integrate Xlate into the BRW have been unsuccessful…til now
HOWEVER, I believe we will have a solution that does allow you to use Xlate from the BRW. We're currently working on a way to allow you to define a field as a custom Xlate field, specifying all the bits that we know and love (table name, key, field, and control). Hopefully, if it pans out, you should see it in (or before) the final release of 9.3.1.
Hope that helps,
- Bryan Shumsky
At 15 MAR 2012 08:12PM Jim Vaughan wrote:
Hi Bryan,
That would be fantastic. Thank you for looking at it again, it would be incredibly useful.
BTW, I stll think there is a need to look at how the database fields popup from the script editor. Tables with many fields are a problem. Perhaps the fontsize of the popups can be reduced so that it takes up less space?
Jim R Vaughan
At 16 MAR 2012 12:36AM bshumsky wrote:
Hi Bryan,
That would be fantastic. Thank you for looking at it again, it would be incredibly useful.
BTW, I stll think there is a need to look at how the database fields popup from the script editor. Tables with many fields are a problem. Perhaps the fontsize of the popups can be reduced so that it takes up less space?
Jim R Vaughan
Also in the next release, if there are more than 100 fields to display, the script editor popup should switch to a "scrolling" display (instead of the "wrapping" display that's currently used), which hopefully will make it possible to at least see and access all the fields.
- Bryan Shumsky
At 30 MAR 2012 12:33PM Jim Vaughan wrote:
Hi Bryan,
I am using the 9.3.1 release. Did the Xlate fuctionality that you mentioned make it into the BRW for this release? I can't see any explicit way to use it. Thanks.
Jim R Vaughan
At 30 MAR 2012 12:51PM bshumsky wrote:
Hi Bryan,
I am using the 9.3.1 release. Did the Xlate fuctionality that you mentioned make it into the BRW for this release? I can't see any explicit way to use it. Thanks.
Jim R Vaughan
Hi, Jim. I believe it did make it into the 9.3.1 release. When you have the Banded Report Writer Designer open, and you've got a report you're working on, look at the upper right section of the designer, where you'll see the chart, barcode, etc. "custom controls". You should see something that looks like an X with an arrow underneath it - that should be the icon to add in an XLATE field. Do you see it there?
You'll click on the XLATE icon, and then draw an outline of where you want the XLATE value to go on your report. You'll then need to set the properties for the newly-added XLATE field - you'll specify the table, key, field, and type of XLATE. Note that these values can be based on other fields in the report, or in fields from the 'main' table used in the report, as well as literal strings surrounded by quotes.
Thanks,
- Bryan Shumsky
At 30 MAR 2012 03:27PM Jim Vaughan wrote:
Hi Bryan,
Thanks, yes I see it. I tried it - it is working.
Is there some reason why I would not be able to reference the field created with the Xlate in another calculated field? I tried that and it doesn’t seem to work the way it would if I were referencing one calculated field with another.
Jim R Vaughan
At 12 APR 2012 10:42AM Jim Vaughan wrote:
Bryan,
Can I use the field created with Xlate in the Filter for the report (e.g.WITH Field2 NE "") where Field2 is an Xlate? It does not seem to work. Thanks.
Jim R Vaughan
At 12 APR 2012 12:31PM bshumsky wrote:
Bryan,
Can I use the field created with Xlate in the Filter for the report (e.g.WITH Field2 NE "") where Field2 is an Xlate? It does not seem to work. Thanks.
Jim R Vaughan
The filter is passed directly to RList for processing (with the exception of "prompts" in the filter, which generate a prompt before getting passed to RList). Unfortunately, RList won't do anything special with any calculated field (including your XLATE calculated field), and thus doesn't know how to evaluate it.
I can take a look to see if there's some way to enhance this for another release.
- Bryan Shumsky
At 12 APR 2012 02:26PM bshumsky wrote:
Bryan,
Can I use the field created with Xlate in the Filter for the report (e.g.WITH Field2 NE "") where Field2 is an Xlate? It does not seem to work. Thanks.
Jim R Vaughan
On further thought, I don't think this is something that I can add. I had initially thought that I could have the BRW evaluate the Xlate field before sending it to RList in the filter, but of course the value of the Xlate field will change for every record so (again of course) it can't be evaluated by the BRW _before_ the record selection. What you'd need is for the BRW to write this dynamically-generated field into the dictionary so that RList itself could evaluate it, and I'm not keen on having the BRW add its own fields to your OI dictionary at this time.
- Bryan Shumsky
At 12 APR 2012 04:50PM Jim Vaughan wrote:
Hi Bryan,
We have been using Revelation Reporter (even though it is not supported) for years because it is so capable - save for a few bugs which we have learned to work around. That is exactly what Rev Reporter does when you add a calculated field. It is not something new to be doing this as part of an OI reporting tool. The calculated field is added to the dictionary when the report runs, then deleted.
Related question… how can I format the value returned from the Xlate? The value I retrieved was a date and I can't seem to format it to any of the date formats, rendering it somewhat useless in the report.
Thanks.
Jim R Vaughan
At 17 APR 2012 02:51PM Jim Vaughan wrote:
Bryan,
How do I use VBScript to do a multistep procedure (e.g. loop or if/then of just maniplute text using some local variables) then assign the result to the value of the calculated field (i.e the equialent of @ans for a symbolic in OI)?
Jim R Vaughan
At 17 APR 2012 08:04PM bshumsky wrote:
Bryan,
How do I use VBScript to do a multistep procedure (e.g. loop or if/then of just maniplute text using some local variables) then assign the result to the value of the calculated field (i.e the equialent of @ans for a symbolic in OI)?
Jim R Vaughan
Hi, Jim. Unfortunately, what's expected in the calculated field is an expression - something that yields a value. It's almost as though it's the "@ans = x+y+z" part of a formula, but no "@ans=" is required. So there's no provision for a multistep procedure as you've illustrated it. (Statements, as opposed to expressions, _are_ allowed in the 'event handler' scripts, such as OnPage).
If you needed to do some calculation based on other values, there is a function IIF which takes 3 parameters - the first parameter is evaluated, and if it returns 'true', then the second parameter's value is returned as the result of the expression; if it returns 'false', then the third parameter's value is returned as the result of the expression.
If you click the help icon (the little "?" in the upper right hand corner of the BRW designer), you'll be taken to the on-line help; pages 23 through 35 describe the functions, including IIF, that are available for you to use in your expression.
- Bryan Shumsky
At 18 APR 2012 01:34PM Jim Vaughan wrote:
Hi Bryan,
Thanks for the explanation. I have looked at the on-line help. Unfortunately that really limits what we can do (and have in the past done with Rev Reporter). In this case what I was trying to do is parse some data from a field that has some delimited information imbedded in it. I was hoping to use the VBScript Split function but won’t be able to. If the data were in a MV field, is there any way to do the equivalent of Field1<1,3> in a calculated field?
The bigger question is how to do anything that requires more than one line of code. The Iif will handle some situations but not most.
Jim R Vaughan
At 09 JUL 2012 10:41AM Jim Vaughan wrote:
Hi Bryan,
I thought I remembered seeing a post where you were considering doing this in a future release:
"BRW to write this dynamically-generated field into the dictionary… "Now I can't find mention of it. As I mentioned in the post that follwed this one, this is what Rev Reporter always did. The limitations of the calculated fields in BRW (and the Xlate) are significant as I have mentied in other posts. Is this being considered for the next beta?
Thanks,
Jim R Vaughan
At 09 JUL 2012 10:44AM bshumsky wrote:
Hi Bryan,
I thought I remembered seeing a post where you were considering doing this in a future release:
"BRW to write this dynamically-generated field into the dictionary… "Now I can't find mention of it. As I mentioned in the post that follwed this one, this is what Rev Reporter always did. The limitations of the calculated fields in BRW (and the Xlate) are significant as I have mentied in other posts. Is this being considered for the next beta?
Thanks,
Jim R Vaughan
Hi, Jim. Yes, this is included in the next beta - both the ability to define these dynamic dictionaries "on the fly" in the BRW, and the ability to specify a conversion on an XLATE field.
I was going to add "please be sure to try it out on the upcoming beta", but I'm pretty sure you will even without my prompting…
Hope that helps,
- Bryan Shumsky
At 14 JUL 2012 10:05AM Jim Vaughan wrote:
Thanks Bryan, looking forward to trying it.
Jim R Vaughan
At 17 JUL 2012 09:53AM Jim Vaughan wrote:
Hi Bryan,
Still on 9.3.1 for this question.
Can I use a general expression for SourceKey in the Xlate (e.g. ID&"andy") where ID is a field in the current table? It seems to give syntax errors and not work.
Jim R Vaughan
At 17 JUL 2012 11:32AM bshumsky wrote:
Hi Bryan,
Still on 9.3.1 for this question.
Can I use a general expression for SourceKey in the Xlate (e.g. ID&"andy") where ID is a field in the current table? It seems to give syntax errors and not work.
Jim R Vaughan
Hi, Jim. Yes, you should be able to put a VBScript expression in for the SourceKey (and SourceTable, etc.), and it should be evaluated before it's sent down to the host for processing.
I've just tested using a simple BOOKS report, adding an XLATE field with the following properties
SourceField 2
SourceKey BOOK_ID & "1"
SourceTable BOOKS
and that doesn't show any errors, and does return the expected results. When you say it seems to give syntax errors, what error(s) are you seeing (and where are you seeing them)?
- Bryan Shumsky
At 17 JUL 2012 02:21PM Jim Vaughan wrote:
Hi Bryan,
My fault. I was seeing syntax errors in the BRWScript editor. It was bad quote characters (the double quote sets that face different ways at the beginning and end of the quoted text) probably copied from an email or word doc.
Jim R Vaughan