I need to generate a report from a json that comes from a query in a database.

[moes]

Hi:

I tell you, I need to make a report on data coming from a json that is stored in a column of a database. So far I have managed to do the following but I have not been able to complete the last part.

I have three reports:

1-testJson: This is the main report in the dataset, it performs the query against a SQL Server database and brings the following fields CONTEXT_ID and DATA_JSON, both are String, but the second is a json object with the following structure :

{
    "data": {
        "LAST_AGENT": "312018",
        "IVR": [
            {
                "DOCUMENTO": "Transf2",
                "VDN": "",
                "ULTIMA_OPCION_HU": "",
                "LACID": "",
                "ORIGEN_RETORNO": "",
                "NODO": "",
                "FECHA_HORA_INICIAL": "Z",
                "CUENTA": "111 222 333",
                "CHASIS": "",
                "RENTA": "",
                "ID_Salesforce": "",
                "ANI_MIN": "",
                "CMTS": "",
                "COID": "",
                "CIUDAD": "",
                "CELLID": ""
            }
        ],
        "CTI_BAR": [
            {
                "DIRECTION": "a",
                "UUI": "xxxxxtyyyyyzzzzzz",
                "DATESTART": "10/09/2024 11:06:51 a.Ã? m.",
                "COMPCODE2": "",
                "COMPCODE3": "",
                "EXTENSION": "220009",
                "CALLDURATION": "27",
                "COMPCODE1": "99-Transfer",
                "TENANT": "CLAROMOVIL",
                "UCID": "123435345",
                "DATEFINISH": "10/09/2024 11:07:19 a.Ã? m.",
                "AGENT": "312017",
                "HUNT": "130521",
                "DNIS": "441006",
                "CALLID": "9785",
                "ANI": "3214477938",
                "ACWDURATION": "0"
            }
        ],
        "ENCUESTA": 
            {
                "ID_ENCUESTA": "114060466",
                "TIPO_FIN": "Finaliza Llamada",
                "FECHA_ENCUESTA": "2024-09-10 10:43:29.191",
                "RTA_2": "9",
                "RTA_3": "1",
                "VDN_DETRACTOR": "0",
                "RTA_1": "5",
                "RTA_4": "5"
            }
        ]
    }
}

2-principalJson: This second report has a datasource that points to a json file with the structure indicated above.

3-subreportJson: This last report displays two fields selected from the "CTI_BAR" object within the json.

So far I have managed to make the second and third report work, basically what it does is connect to the json stored in a path on my computer, extract the "CTI_BAR" object and display them in a subreport.

Report number 2 has two fields LAST_AGENT and CTI_BAR and when it invokes subreport 3 it sends the following parameter in the datasource expression: ((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource(" data.CTI_BAR"), basically takes only the CTI_BAR fields so that the third report simply shows them through the configured fields. The above is displayed this way in the report:

 

 

What I need to implement now is that from report 1 the json resulting from the database query (DATA_JSON Field) is sent to report 2 so that it can perform its corresponding task. 

The idea is that the DATA_JSON data can become the datasource used by the second report and that it will not consult the .json on my computer but rather use the field resulting from the query.

If you can think of another way to work on this requirement, I would also appreciate it if you could tell me how to try to implement it.

Attached I will send you the three reports as well as the json file. I think the requirement could be simplified into 2 reports, I would be very grateful if you could help me.

By the way, the version of jasperstudio that I am using is 5.6.0 and I cannot use the most current one because the project is old and has many other reports compiled with this version. I previously tried to use a report with version 6 but these presented error at runtime precisely due to version inconsistency.

 

 

 

 

 

ivr.json principalJson.jrxml subreportJson.jrxml testJson.jrxml