Topic
19 replies Latest Post - ‏2013-09-27T16:25:52Z by KennyTo
HJAO
HJAO
14 Posts
ACCEPTED ANSWER

Pinned topic Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

‏2012-06-04T11:50:24Z |
Hello,

In my current TRIRIGA platform, I have Note field that I want to use in my BIRT Report. When I create the base query, the Note field is not showing up for selection. How am I able to retrieve that field ? Please advise.
Updated on 2012-07-26T15:09:18Z at 2012-07-26T15:09:18Z by HJAO
  • EricBatzdorff
    EricBatzdorff
    17 Posts
    ACCEPTED ANSWER

    Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

    ‏2012-06-06T22:42:54Z  in response to HJAO
    As of now, Note fields aren't selectable from a TRIRIGA Query. Note fields are only available in Form data sets.

    One way to accomplish a similar result is to also include a Form dataset which includes the Note field you are interested in using. Then you can "join" the Query dataset to the Form dataset using the query result's recordId field to pass as input to the Form data set.

    As a performance consideration... if all you need is a single Note field from a Form dataset, I recommend removing the unneeded columns.

    Hope that helps. Please let me know if it does not.

    Eric
    • HJAO
      HJAO
      14 Posts
      ACCEPTED ANSWER

      Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

      ‏2012-06-07T19:09:34Z  in response to EricBatzdorff
      Hi Eric,

      Thank you for the wonderful advice. I see it required recordId as input to retrieve single record.
      How do I achieve what you have mentioned : "join" the Query dataset to the Form dataset using the query result's recordId field to pass as input to the Form data set"

      Thank you,

      Howie
  • EricBatzdorff
    EricBatzdorff
    17 Posts
    ACCEPTED ANSWER

    Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

    ‏2012-06-08T00:49:38Z  in response to HJAO
    I've uploaded a file that demonstrates the "join" I'm discussed:
    https://www.ibm.com/developerworks/mydeveloperworks/files/form/anonymous/api/library/3bab5588-fb79-432a-aa2c-9282d79605d0/document/2fafaaeb-efaa-4e01-90e0-38b0ad9de690/media/triLeaseAbstractNote.rptdesign

    Inside the table of v2QRYtriItem__triLeaseClause__triLeaseClause_-_Display_-_Associated_to_LA results is nested a report element to expose the associated Lease Clause note. This note is coming from a different data set v2BOtriItem__triLeaseClause. In order for the appropriate note to be selected, the v2BOtriItem__triLeaseClause grid's Data Set Parameter binding must take the parent row's record ID for its input parameter.

    It's a bit easier to show with screenshots, but hopefully the explanation will suffice for now.

    (Note that I did not actually run the report I uploaded... I simply put it together as an illustration of how this technique works.)

    Hope that helps,

    Eric
  • EricBatzdorff
    EricBatzdorff
    17 Posts
    ACCEPTED ANSWER

    Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

    ‏2012-06-08T15:49:20Z  in response to HJAO
    I uploaded a new version of the sample report, one that actually works ... :-). It is located in the same location:

    https://www.ibm.com/developerworks/mydeveloperworks/files/form/anonymous/api/library/3bab5588-fb79-432a-aa2c-9282d79605d0/document/2fafaaeb-efaa-4e01-90e0-38b0ad9de690/media/triLeaseAbstractNote.rptdesign
    • HJAO
      HJAO
      14 Posts
      ACCEPTED ANSWER

      Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

      ‏2012-06-10T19:16:44Z  in response to EricBatzdorff
      Hi Eric,

      I imported your source file but when I run it, it still prompt for entering system_recordIds parameter. Is there a way to feed in all the LA recordIds so that it can displayed all the Notes for each Lease Clause for each LA ?

      Sorry if I steal too much time from you.

      Many Thanks,

      Howie
      • EricBatzdorff
        EricBatzdorff
        17 Posts
        ACCEPTED ANSWER

        Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

        ‏2012-06-11T17:23:20Z  in response to HJAO
        The prompt for system_recordIds is expected behavior for a Form report when previewing in Eclipse Report Designer. In the Eclipse Report Designer, the id(s) (i.e. system_recordIds) is something the report writer enters manually. In the online TRIRIGA environment, that input is populated automatically from the context within which the report is run.

        All that to say... if you can locate the ID of a Lease Abstract containing a Lease Clause with a Note, then you will be able to see this scenario working that you were inquiring about: how to pull a Note field along alongside the results of a Query.
        • HJAO
          HJAO
          14 Posts
          ACCEPTED ANSWER

          Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

          ‏2012-06-15T18:59:41Z  in response to EricBatzdorff
          Hi Eric,

          Thanks again for all the help.

          Regards,

          Howie
  • KennyTo
    KennyTo
    20 Posts
    ACCEPTED ANSWER

    Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

    ‏2012-07-18T18:45:47Z  in response to HJAO
    I had trouble with the triLeaseAbstractNote.rptdesign example. For some reason it did not return associated records when I tried it. I extended a previous Note field report example you had by adding a query to it. So now the query data set feeds the form BOs data set, resulting in a list of records with the Note fields displayed in RAW data and in HTML format.

    https://www.ibm.com/developerworks/mydeveloperworks/files/form/anonymous/api/library/928ee078-0932-4aaf-9fde-9f71104bf82e/document/d878091c-bd39-47d8-956f-f6008ada8f71/media/CapitalProjectNoteExample.zip
    • HJAO
      HJAO
      14 Posts
      ACCEPTED ANSWER

      Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

      ‏2012-07-18T19:46:52Z  in response to KennyTo
      Hi Kenny,

      I ran your report and it worked! So in the Query data set, all we need to do is to include the recordId and it will automatically fit in the form data set ? or is there any other setting that needed to be done ?

      Thanks

      H
      • KennyTo
        KennyTo
        20 Posts
        ACCEPTED ANSWER

        Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

        ‏2012-07-19T01:44:50Z  in response to HJAO
        You would need to make sure that the Query data set is feeding the recordId to the form data set in the Form Data set's Table's Data Set Parameter Binding. In the report, there are two tables, the parent is the query data set, and the nested one is the form data set. I set the form data set's 'system_recordIds' parameter to the parent table's system_recordId, like so row.toString(). You can see what I did clicking on the nested table > Property Editor > Binding > Data Set Parameter Binding.
    • HJAO
      HJAO
      14 Posts
      ACCEPTED ANSWER

      Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

      ‏2012-07-18T19:53:03Z  in response to KennyTo
      How did you have 2 dataset in one rptdesign ?
      • KennyTo
        KennyTo
        20 Posts
        ACCEPTED ANSWER

        Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

        ‏2012-07-19T01:46:47Z  in response to HJAO
        For the 2 dataset in one rptdesign question, the BIRT report designer allows you to copy and paste data sets across different reports, as long as they are from the same data source. I just took the form data set, copied it from the form starter report, and pasted into my query starter report.
        • HJAO
          HJAO
          14 Posts
          ACCEPTED ANSWER

          Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

          ‏2012-07-26T15:09:18Z  in response to KennyTo
          Hi Kenny,

          I still can't get my report working using your method. This is what I did:
          1. I copy the form dataset to my existing query dataset report
          2. Create a sub-table and add the Note field to it
          3. In the sub-table's Data Set Parameter Binding,set the dataset to the form dataset and set the system__recordId = row.toString()
          Question: There's only one table that I can pick from the list and I don't know if this "row" is actually the parent table's system_recordId or I am just doing the circular reference.

          When I run the report through TRIRIGA, the whole table is empty, not even the data from the query set. There's no error showing up.

          What might be the error that I missed in my step ?

          Please advise

          Thanks

          H
          • ashu451
            ashu451
            4 Posts
            ACCEPTED ANSWER

            Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

            ‏2013-09-26T06:22:01Z  in response to HJAO

            Hi Kenny,

            I am also facing the same issue as faced by HJAO.

            Particularly in my case i have query report in which all the details of the work task are retrieved.

            In my 1st subreport i retrieved all the procedure and procedure steps or the task.

            In my 2nd sub report i retrieved all the assests requested for in work task.

            Luckily i am able to bind 1st subreport using edit dataset parameter binding property,

            Unfortunately my 2nd report doesnt show up any record when i bind it to the parent work tasks' record_ID

             

            Please guide !!

            Thank you

            • KennyTo
              KennyTo
              20 Posts
              ACCEPTED ANSWER

              Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

              ‏2013-09-26T20:03:50Z  in response to ashu451

              Hi ashu451,

              It sounds like you are using the correct method since you are able to get your 1st subreport to work. For your 2nd subreport, does it return the work tasks? If so, it looks like the binding is not set correctly somewhere. A way to test it out manually is to hard code the record id of the parent into the parent record id parameter of the child. If that works, then you may be feeding the parent record id to the wrong place.

              • ashu451
                ashu451
                4 Posts
                ACCEPTED ANSWER

                Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

                ‏2013-09-27T06:58:11Z  in response to KennyTo

                Thank you kenny for your help.

                I tried doing the way you suggested, but i think still there is some problem with my report.

                I tried a other way around which worked :)

                My 2nd subreport is in a table and in that table properties i added a filter with a expression.

                field1(sub report's task ID) - like - field (main report's task id)[row._outer["triTask__triWorkTask____triIdTX"]]

                 

                This is working.

                Thanks for your help.

                 

                • KennyTo
                  KennyTo
                  20 Posts
                  ACCEPTED ANSWER

                  Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

                  ‏2013-09-27T16:25:52Z  in response to ashu451

                  Glad it is working for you!

          • KennyTo
            KennyTo
            20 Posts
            ACCEPTED ANSWER

            Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

            ‏2013-09-26T19:55:39Z  in response to HJAO

            To answer your question, the "row" is referring to the row in which the sub table is located in. In the example I provided, you can see the sub table is nested within the row of the parent table.

            As for your issue, I would take a step back and not include the note field yet and see if you get any data back. I would also check the your environment and make sure there is a capital project data.

          • KennyTo
            KennyTo
            20 Posts
            ACCEPTED ANSWER

            Re: Cannot retrieve Note field from TRIRIGA in the base query for BIRT Report

            ‏2013-09-26T19:59:01Z  in response to HJAO

            Here's a screenshot of the setup as a reference

             

            Attachments