Old CABI Field migration to Jaspersoft BI

[D'Arcey Taylor]

I have the folowing query in our current CABI reporting server and I am attempting to recreate this in Jaspersoft but I cannot seem to get it to work correctly.

The purpose of this is to display all our active problem records and their post recent log comments only. Right now my report show each active problem and all of the log comments associated to it.

"=Max(If([Type Symbol] = "Log Comment")=Max(If([Type Symbol] = "Log Comment") Then (Concatenation (Concatenation (FormatDate([Last Mod Dt];"yyyy/MM/dd hh:mm:ss a");Char(13)); [Problem Activity].[Description] ) ) )"

If anyone has any experice doing something similar to this and has any advice please let me know.

------------------------------
Thanks,
D'Arcey
------------------------------

[James Campbell]

Hi D'Arcey,
personally I'd attack this requirement in SQL - something like this (syntax is for a direct SQL connection to the MDB):

Hope that helps!
Regards,
James

------------------------------
Consultant
IDC Technologies
------------------------------

[James Campbell]

In case you can't get a direct SQL connection, here is the equivalent for a connection to the object layer (screenshot from Jaspersoft Studio).  This driver apparently doesn't support 'JOIN' so the joins are done the old-fashioned way :-)

Regards,
James

------------------------------
Consultant
IDC Technologies
------------------------------

[James Campbell]

...and taking advantage of the 'attribute alias' fields attached to the alg object, you can reduce that to:

... assuming that you want just the latest Log Comment and not the latest Activity Log entry of any type.  Omit the two predicates 'type = 'LOG' if the latter is the case.
Hope that helps.
Regards,
James

------------------------------
Consultant
IDC Technologies
------------------------------