Clarity

Hi guys,

I'm working in Domain Designer and I'm trying to pull from a lookup table and I'm pretty sure I can do it via Calculated Fields tab.  We have a ton of lookups that all come out of the same table and are differentiated with a LOOKUP_CODE attribute.  I've figured out how to make the correct results display but I have to create a derived table for each field which seems like I'm doing it wrong.  Bare with me as we're quite new to this and I'm trying to become our SME.

Example:

Table: Lookups

Table: Projects

Based on the wiki, it would appear that using the Domain Expression Language, I would need a calculated field with Lookups.LOOKUP_NAME where Lookups.LOOKUP_TYPE == 'approval_state' and Projects.Approval_State == Lookups.LOOKUP_ENUM should work, but I can't get it to give me any messages aside from Cannot evaluate expression.  Any help is greatly appreciated.

-Thanks!

Ronnie Roberts - DIRECTV

PS: For the eagle eyed among you, this may look familiar, and it is.  This was also asked on http://community.jaspersoft.com/questions/974996/lookups-calculated-fields  but I have yet to receive any answers.

Message was edited by: Nick Darlington Altered the title if you don't mind, to help disambiguate at a glance between an issue that could be in either PPM Studio or JasperServer Domain Designer.

Hi Ronnie,

Try running your code with Lookups.LOOKUP_TYPE = 'approval_state' and Projects.Approval_State = Lookups.LOOKUP_ENUM i.e. using a single equal to (=) sign. Hope this works!

Suhail.

Hi Suhail,

Thank you for your help with this but it still doesn't seem to be working.  I have also tried running this with the following:

1. Lookups.NAME where Lookups.LOOKUP_ENUM = [Projects].DTV_APR_STATE and Lookups.LOOKUP_TYPE = 'DTV_APR_STATE'
2. Lookups.NAME where (Lookups.LOOKUP_ENUM = [Projects].DTV_APR_STATE and Lookups.LOOKUP_TYPE = 'DTV_APR_STATE')
3. Lookups.NAME where (Lookups.LOOKUP_ENUM = [Projects].DTV_APR_STATE and (Lookups.LOOKUP_TYPE = 'DTV_APR_STATE'))
4. Lookups.NAME where Lookups.LOOKUP_ENUM == [Projects].DTV_APR_STATE and Lookups.LOOKUP_TYPE == 'DTV_APR_STATE'
5. Lookups.NAME where (Lookups.LOOKUP_ENUM == [Projects].DTV_APR_STATE and Lookups.LOOKUP_TYPE == 'DTV_APR_STATE')
6. Lookups.NAME where (Lookups.LOOKUP_ENUM == [Projects].DTV_APR_STATE and (Lookups.LOOKUP_TYPE == 'DTV_APR_STATE'))

The only thing I can ever get to show up Cannot evaluate expression, which is not particularly helpful.  Does anyone perhaps have a working "Calculated Field" from a lookup they would be willing to post the expression for?

-Ronnie

Can you check this ?

Calculate lookups with text as result

NJ

Hi NJ,

Thank you for the link, but this appears to apply within Clarity/PPM whereas I'm trying to do this in Jaspersoft Domain Designer.

-Ronnie

I suppose this is an issue with the join as you seem to be joining the wrong attributes.

Test it step-by-step;

SELECT Lookups.NAME FROM <table_name> as Lookups WHERE Lookups.LOOKUP_TYPE = 'DTV_APR_STATE';

and see if this gives you a proper output?

If yes, add the Lookups.LOOKUP_ENUM = [Projects].DTV_APR_STATE condition in the WHERE clause and see it if works. If not, then this may not be the correct condition.

Is [Projects].DTV_APR_STATE a lookup attribute within the PROJECT object?

Also, please be advised that the string values are case sensitive i.e. check if DTV_APR_STATE is in upper case or lower case or a combination of both within the DB Table.

Suhail.

Hi Suhail,

Thank again for your assistance and patience with me.  I have tried to get this to show up as a calculated field using even the most simple expressions, like Lookups.NAME, which works fine though obviously won't display the correct info as it doesn't know how to join the table.  It seems that in the Calculated Fields tab, using SQL does not evaluate as Lookups.NAME works, but SELECT NAME FROM Lookups does not.  Using that info, I have tried Lookups.NAME where 1=1 (also tried ==, and different sets of ()'s ), and cannot get it to ever evaluate anything.

-Ronnie

Hi Ronnie,

Some simple questions:-

1. What DB Table are you referring to as Lookups? Is it a custom DB Table? There is a DB Table by the name of cmn_lookups that have fields LOOKUP_ENUM and LOOKUP_CODE within it, but, there is no field as NAME in it.

2. Is [Projects].DTV_APR_STATE a lookup attribute within the PROJECT object?

I again feel you are not using the proper primary keys when joining different DB Tables. Please refer to the Technical Reference Guide pdf on the CA website, which may be of some help to you.

Suhail.

The Lookups table is just a Derived table that only pulls in the english lookup codes as we don't have any other.  (I have validated that our CMN_LOOKUP_V table definitely has a NAME column)

SELECT LOOKUP_CODE, LOOKUP_ENUM, ID, NAME, LOOKUP_TYPE FROM CMN_LOOKUPS_V WHERE LANGUAGE_CODE = 'en'

I have checked all of the documentation I can find, and I can't find a single thing about how to use the calculated fields tab except http://community.jaspersoft.com/documentation/tibco-jasperreports-server-user-guide/v60/calculated-fields-tab and that page tells me I need to use Domain Expression Language and I can't get anything to ever work.  So far the only way I've gotten a Lookup to correctly display is to create a derived table for every single lookup type.  This seems excessive and unnecessary.

Does anyone have a single working lookup they've created in Jaspersoft they could walk me through?

Hi Ronnie,

You haven't answered my second question:-

Is [Projects].DTV_APR_STATE a lookup attribute within the PROJECT object?

As I can see from your examples given above, Lookups.LOOKUP_ENUM is an enumeration for a particular Looksups.LOOKUP_TYPE. I am not sure why you are joining Lookups.LOOKUP_ENUM = Projects.Approval_State directly. You must also go through the various Lookup DB Tables including cmn_lookups, cmn_lookup_types, cmn_lookup_flat_hiers and cmn_list_of_values to see if you can gather lookups related data collectively from any of these. Also, cmn_lookup_list_of_values a can be joined to odf_objects b through a.object_code = b.code. And, odf_objects can further be joined to odf_ca_project or inv_investments through ID (just double-check on this). These joins can help you get the data that you need from the lookups and projects related DB Tables.

In case if you still face an issue, I would advise you to open a new ticket/query on the Jaspersoft Community. Good Luck!

Suhail.

Yes, all of those fields are correct.  I am able to get everything to work fine in SQL or via a derived table, so I know everything is correct.  I do have a question out with Jaspersoft, and I have a ticket opened with CA.  If/when I get the answer to this question, I'll be sure to post it for any other wayward souls like myself.

Thanks again for all of your time on this.

-Ronnie

Hi Ronnie - Did you ever get a solution for this one? Thanks, Chris

No, sir.  The closest I've gotten is to make a new table for each and every field, which doesn't make a lot of sense to me (the only way I'm aware of is to join the lookup table and the field via a derived table).  I am participating in instructor lead training shortly, so I was hoping to get my question answered and will report back here.

Any luck?

No, sir.  If you need to close this out to avoid unasnwered questions, that's fine.  I'll post the info if I figure it out at Rego U this year.