|
| Re: Lookup/Code tables with optional values [message #525802 is a reply to message #525591] |
Wed, 07 April 2010 19:50   |
|
Barrie,
I am not certain I understand the problem, but with your xml snippet I
would create one dataset that is mapped to transactions. It would have
a categoryId, subc.., thirdpar.. columns. I would then create a
parameterized dataset for the categories. Its xpath for the row would
look something like:
/categories/category[@category id="{?p1?}"]
set the default value for the dataset parameter to 1. Next put the
first dataset on the report, and then nest your parameterized second
dataset in the first. Select the nested table and select the binding
tab in the properties editor, and click the Data Set Parameter Binding
button. Using the expression builder choose the outer tables categoryid.
What do you want to do when the category id is 0? In the case above the
inner table will just be empty.
Jason
Barrie Treloar wrote:
> I'm still new and clueless to BIRT, so any clues appreciated.
>
> I'm trying to do a code table lookup where one row contains the id for
> the code in another data set.
> BUT the id values are optional, where a "0" indicates it is optional.
> (I dont have control of the XML data source - but I can preprocess via
> xslt as a last resort.)
>
> My XML Data source looks something like this:
>
>
> <data>
> <transactions>
> <transaction categoryId="0" subcategoryId="0" thirdPartyId="0"/>
> <transaction categoryId="2" subcategoryId="0" thirdPartyId="20"/>
> ...
> </transactions>
> ...
> <categories>
> <category id="1" name="Gardening">
> <subcategory id="1" name="Lawns"/>
> <subcategory id="2" name="Trees"/>
> ...
> </category>
> <category id=2" name="Furniture">
> <subcategory id="1" name="Bedding"/>
> ...
> </category>
> </categories>
> <thirdparties>
> <thirdparty id="1" name="My Furniture Shop"/>
> ...
> <thirdparty id="1" name="Gardens Galore"/>
> </thirdparties>
> </data>
>
>
>
> I have four data sources:
>
> Transactions
> Categories
> Subcategories (with a computed column from the concatenation of
> category/id and subcategory/id)
> ThirdParties
>
>
> My first attempt was using an inner join.
> After spending a couple hours trying to debug why I wasn't getting all
> the data I was expecting and thinking there was a bug handly the 29th of
> February I finally remembered what an inner join was!!! (and hence the
> loss of data)
>
> I tried a left outer join - which is I think what I want.
> The problem is that Birt rightly gives me an error when the id's are "0"
> since the lookup datasources don't contain a key for "0" and hence
> accessing the name column fails.
>
> Currently my join data sets look like:
>
> TransactionsWithThirdParties
> TransactionsWithThirdPartiesWithCategories
> TransactionsWithThirdPartiesWithCategoriesWithSubcategories
>
>
> Which makes me wonder whether this is the right way at all.
> So how should I be handling this?
>
> A couple of thoughts on work arounds.
> 1) Preprocess with xslt and remove id's that are "0" hoping that a null
> attribute will work correctly in the left outer join to give me a blank
> string.
> 2) Attach script to the correct event to insert a row for "0" in each
> lookup data source (this will require me to do a lot more reading as so
> far I have not found a way to do this)
> 3) Use nested tables and protect the lookup for an if express (needs
> more reading to see how to do this as well)
>
> Suggestions welcome
>
> Cheers
> Barrie
> p.s. I'm having trouble finding more than basic documentation on BIRT.
> The BIRT wiki page doesn't link to this detail. Where else should I be
> looking?
|
|
|
| Re: Lookup/Code tables with optional values [message #525854 is a reply to message #525802] |
Thu, 08 April 2010 01:28 |
 Barrie Treloar
Messages: 55
Registered: July 2009 |
Member |
|
|
Thanks Jason,
| Jason Weathersby wrote on Wed, 07 April 2010 15:50 |
I am not certain I understand the problem, but with your xml snippet I
would create one dataset that is mapped to transactions. It would have
a categoryId, subc.., thirdpar.. columns. I would then create a
parameterized dataset for the categories. Its xpath for the row would
look something like:
/categories/category[@category id="{?p1?}"]
|
Excellent. I couldn't find the docs on parameterizing xml datasources.
I was doing a similiar thing but using filters - but I think the problem with filters, unlike what you describe below, is the table is not empty and hence I get the error when attempting to reference the name field.
I think from your explanation your understanding is correct.
I want to take a transaction line like:
<transaction categoryId="2" subcategoryId="0" thirdPartyId="20"/>
and view in my report
CategoryName, SubcategoryName, ThirdPartyName
(not the id's)
So I need to convert the id's into their strings.
| Jason Weathersby wrote on Wed, 07 April 2010 15:50 |
set the default value for the dataset parameter to 1. Next put the
first dataset on the report, and then nest your parameterized second
dataset in the first. Select the nested table and select the binding
tab in the properties editor, and click the Data Set Parameter Binding
button. Using the expression builder choose the outer tables categoryid.
What do you want to do when the category id is 0? In the case above the
inner table will just be empty.
|
I'm still working on it - and I dont have a copy at work - but what I have done instead is to create associative arrays as global variables in the initialize event of the report and then on the dataset onFetch event populate the associative arrays. I've created hidden tables on the report so the dataset gets used. I'll look at doing this via script next.
Then in the table I've placed a label and for the label's onRender I am retrieving the global variables and doing a lookup.
This is much faster than the left outer join situation was. I'd have to try the parameterized data set to compare speeds as well.
(e.g. instead of seconds the join was minutes)
One problem I still have yet to resolve is that I can correctly output the variables value via printn but when setting the labels text the value is "undefined". I'm not sure what the error is there.
|
|
|
|
|
|
| Re: Lookup/Code tables with optional values [message #526362 is a reply to message #526143] |
Fri, 09 April 2010 20:06 |
|
Barrie,
All the ones that work are only using one process runandrender task.
The webiviewer uses two processes run then render. It appears the
object is not persisting.
reportContext.setPersistentGlobalVariable("categoryLookup", {});
What is {} in the line above?
Jason
Barrie Treloar wrote:
> Jason Weathersby wrote on Thu, 08 April 2010 10:42
>> Barrie,
>>
>> reportContext.getPersistentGlobalVariable should work fine in 2.5.2.
>> Is the object you persisted serializable?
>>
>> Jason
>
>
> Its
>
> Report.inialize
> reportContext.setPersistentGlobalVariable("categoryLookup", {});
>
> DataSet.Categories.onFetch
> var categoryLookup =
> reportContext.getPersistentGlobalVariable("categoryLookup");
> categoryLookup[row["Id"]] = row["Name"]
>
> (where Id is type Number, Name is type String)
>
> Label.onRender
> var categoryLookup =
> reportContext.getPersistentGlobalVariable("categoryLookup");
>
>
> as I've said in debug mode the values are there (but no report is
> generated on screen).
>
> When I "View Report in Web Viewer" the values are undefined.
>
> I just tried "View Report in ..."
>
> DOC (although my Word 2002 gets an xml file but the values are correct
> at least)
> HTML
> PDF
> PPT
> EXCEL
>
>
> And these all work
>
> So its just Web Viewer that fails.
>
|
|
|
|
| Re: Lookup/Code tables with optional values [message #526694 is a reply to message #526385] |
Mon, 12 April 2010 15:40 |
|
Barrie,
Can you not just use a Java HashMap?
Change from initialize as it is fired many times to beforeFactory
importPackage(Packages.java.util);
var myhm = new HashMap();
reportContext.setPersistentGlobalVariable("categoryLookup", myhm);
onFetch
var categoryLookup =
reportContext.getPersistentGlobalVariable("categoryLookup");
categoryLookup.put( row["Id"], row["Name"]);
//you may have to setPersistent again. If so do it beforeClose of //dataset
Jason
Barrie Treloar wrote:
> Jason Weathersby wrote on Fri, 09 April 2010 16:06
>> Barrie,
>>
>> All the ones that work are only using one process runandrender task.
>> The webiviewer uses two processes run then render. It appears the
>> object is not persisting.
>> reportContext.setPersistentGlobalVariable("categoryLookup", {});
>>
>> What is {} in the line above?
>>
>> Jason
>
>
> Isn't {} an associative array in javascript?
> (I'm a newbie at javascript too)
|
|
|
|
| Re: Lookup/Code tables with optional values [message #526967 is a reply to message #526797] |
Tue, 13 April 2010 15:15 |
|
Barrie,
I do not think JavaScript variables are serializable. When using the
the persistent feature the actual variables are written into the
rptdocument. This is so pagination will work and the document can be
rendered on a different machine and at a different time.
Jason
Barrie Treloar wrote:
> Jason Weathersby wrote on Mon, 12 April 2010 11:40
>> Can you not just use a Java HashMap?
>
>
> Sure, just wondering why a javascript associative array doesn't work
> only for the web viewer.
>
> I can give the hashmap a go and see it fhat makes a difference.
>
> Jason Weathersby wrote on Mon, 12 April 2010 11:40
>> Change from initialize as it is fired many times to beforeFactory
>> importPackage(Packages.java.util);
>> var myhm = new HashMap();
>> reportContext.setPersistentGlobalVariable("categoryLookup", myhm);
>
>
> Ok, I can change the event too.
>
> Jason Weathersby wrote on Mon, 12 April 2010 11:40
>> onFetch
>> var categoryLookup =
>> reportContext.getPersistentGlobalVariable("categoryLookup");
>> categoryLookup.put( row["Id"], row["Name"]);
>> //you may have to setPersistent again. If so do it beforeClose of
>> //dataset
>
>
> I can't find the api documentation for setPersistentGlobalVariable.
>
> I assumed (maybe incorrectly) that it was storing a reference to the
> variable and not a copy, so that if I modify the variables content I do
> not need to recall setPersistentGlobalVariable.
>
> Given that every other report works and I am not recalling
> setPersistentGlobalVariable I suspect that it does store the reference.
>
> I'll try the hashmap and see what happens.
>
>
>
|
|
|
|
| Re: Lookup/Code tables with optional values [message #527261 is a reply to message #527084] |
Wed, 14 April 2010 15:32 |
|
We have a little info on this here (we need to update this):
http://www.eclipse.org/birt/phoenix/deploy/reportScripting.p hp#context
Definition of setPersistentGlobalVariable
public void setPersistentGlobalVariable( String name, Serializable obj )
{
context.registerGlobalBean( name, obj );
}
The reason the other types work is because they do not create a
rptdocument, so in effect they are only holding the variable in memory.
When using the frameset mapping a rptdocument is created and the
engine tries to write the variables to this file.
Jason
Barrie Treloar wrote:
> Jason Weathersby wrote on Tue, 13 April 2010 11:15
>> Barrie,
>>
>> I do not think JavaScript variables are serializable. When using the
>> the persistent feature the actual variables are written into the
>> rptdocument. This is so pagination will work and the document can be
>> rendered on a different machine and at a different time.
>>
>> Jason
>
>
> Ok.
> Is there a link to documentation?
> I'm assuming this should be listed there?
>
> I still haven't tried with HashMap, but I find it odd that every other
> report type persists global variables fine.
|
|
|
|
| Re: Lookup/Code tables with optional values [message #527549 is a reply to message #527465] |
Thu, 15 April 2010 14:55 |
|
have you tried
var categoryId = (dimension["Categories"]["CategoryId"]).toString();
Barrie Treloar wrote:
> Ok.
>
> Changed initialize to beforeFactory.
> Storing new instances HashMap in the persistent global variables.
> Updated onFetch to use HashMap.
>
> Tables render fine.
> But Cross Tabs have problems.
> Sprinkling in some debugs and typeofs I can see that the data binding
> for a Data Cube Expression returns an object type when I pull out the data.
>
> var categoryId = dimension["Categories"]["CategoryId"];
> // typeof(categoryId) will be object.
>
> whereas the lookup key for subcategory is a string concatenation, like so:
> var subcategoryId = dimension["Categories"]["CategoryId"] + "." +
> dimension["Categories"]["SubcategoryId"]
> // typeof(subcategoryId) will be string
>
> I've made sure the datacube DataTypes for the groups to be String but
> this doesn't help.
>
> My hack/workaround is to prepend
> "" + to force it to be a string.
>
> I've also tried new String() but that doesn't do the same thing and so
> the lookups return null.
> This is where my complete lack of undertanding of Javascript is not
> helpful.
>
|
|
|
|
| Re: Lookup/Code tables with optional values [message #527813 is a reply to message #527651] |
Fri, 16 April 2010 14:47 |
|
Barrie,
BIRT uses Java data types internally. When using script (Rhino engine)
there are conversions taking place. If you are referencing a data value
that is a string it is a Java string, but when you use it in JavaScript
a conversion takes place. JavaScript has two types of strings: String
objects and String literals. Take a look at this article that does a
good job showing the difference.
http://www.hunlock.com/blogs/The_Complete_Javascript_Strings _Reference
In BIRT if I have a script like:
var gg = cellInst.getDataValue("PRODUCTLINE");
this gg.getClass().toString() will return java.lang.String
if I add
var gg = cellInst.getDataValue("PRODUCTLINE") + "tst";
this gg.getClass().toString() will throw an exception
gg is converted to a javascript string literal
When you persist it will be converted back to a java string.
So it should work in your case.
Jason
Barrie Treloar wrote:
> Jason Weathersby wrote on Thu, 15 April 2010 10:55
>> have you tried
>> var categoryId = (dimension["Categories"]["CategoryId"]).toString();
>
>
> No, as I said, I'm clueless with Javascript.
> Its unfrotunately too close to Java and I dont know enough of how the
> two interact to work out what I've done wrong.
>
> Will toString() return me a javascipt string or a java string?
> Is there a difference?
> The HashMap contains as keys and values javascript strings (from what I
> can tell)
>
> I'm assuming javascript strings are serializable, otherwise I'd have the
> same problem with global persistence.
>
> Do you know why the datacube is returning an object when the dataset
> "CategoryId" is a String?
>
> Thanks very much for the help.
> I've been happy with how quickly I can put together a report in BIRT,
> besides these teething problems.
> I love the data cube, I've been able to pull out transactions bv
> Category and Subcategory by Sum of the Amount over the Month.
> This is exactly what I need for cash flow analysis and it was dead
> simple to setup.
|
|
|
Powered by
FUDForum. Page generated in 0.05226 seconds
] 
Search
Help
Register
Login
Home
