Hi All,
My report data has parent-child structure.
The parent data is for insurance premium and its child records are for endorsement reasons. Their relation is 1:m.
I have tried to aggregate the parent level premium with condition that doesn't have the dendorsement reason 'COVID-19'.
Let me show the example.
Parent records
--------------------------------------------------------
Ins_No. | Premium | Quoted Date(YYYY-MM)
--------------------------------------------------------
P1 | 1,500 | 2021-04
P2 | 1,000 | 2021-05
P3 | 500 | 2021-05
===============================
Child Reocrd
---------------------------------------------------------------
Ins_No. | Reason Code | Reason
---------------------------------------------------------------
P1 | R1 | Change to Expiry Date
P1 | R2 | Change to Policy Clauses
P2 | R1 | Change to Expiry Date
P3 | R3 | COVID-19
===================================
Should have the result report
--------------------------------------------------------------------------------
Ins_No. | Premium | Quoted Date(YYYY-MM) | Reason Count
--------------------------------------------------------------------------------
P1 | 1,500 | 2021-04 | 2
P2 | 1,000 | 2021-05 | 1
--------------------------------------------------------------------------------
2,500
=============================================
I developed a query for child records for using 'Join' from Toolbox;
Child Reocrd without 'COVID-19' reason
---------------------------------------------------------------
Ins_No. | Reason Count(using total functionality)
---------------------------------------------------------------
P1 | 2
P2 | 1
===================================
And I added 'Join' functionality. But UDA-SQL-0219 "date_trunc" error occured.
How can I resolve this?
Shoul I change the way to result? or Is there any way to escape from UDA-SQL-0219 "date_trunc" error?
Please help me.
Quote from: yjmoon56 on 14 Jun 2021 12:31:59 AM
Hi All,
My report data has parent-child structure.
The parent data is for insurance premium and its child records are for endorsement reasons. Their relation is 1:m.
I have tried to aggregate the parent level premium with condition that doesn't have the dendorsement reason 'COVID-19'.
Let me show the example.
Parent records
--------------------------------------------------------
Ins_No. | Premium | Quoted Date(YYYY-MM)
--------------------------------------------------------
P1 | 1,500 | 2021-04
P2 | 1,000 | 2021-05
P3 | 500 | 2021-05
===============================
Child Reocrd
---------------------------------------------------------------
Ins_No. | Reason Code | Reason
---------------------------------------------------------------
P1 | R1 | Change to Expiry Date
P1 | R2 | Change to Policy Clauses
P2 | R1 | Change to Expiry Date
P3 | R3 | COVID-19
===================================
Should have the result report
--------------------------------------------------------------------------------
Ins_No. | Premium | Quoted Date(YYYY-MM) | Reason Count
--------------------------------------------------------------------------------
P1 | 1,500 | 2021-04 | 2
P2 | 1,000 | 2021-05 | 1
--------------------------------------------------------------------------------
2,500
=============================================
I developed a query for child records for using 'Join' from Toolbox;
Child Reocrd without 'COVID-19' reason
---------------------------------------------------------------
Ins_No. | Reason Count(using total functionality)
---------------------------------------------------------------
P1 | 2
P2 | 1
===================================
And I added 'Join' functionality. But UDA-SQL-0219 "date_trunc" error occured.
How can I resolve this?
Shoul I change the way to result? or Is there any way to escape from UDA-SQL-0219 "date_trunc" error?
Please help me.
Hi,
What are the data types of the items in each query? You show an item called "Quoted Date" in your post, but it doesn't looks like a complete date? Is this just the way you are formatting it, and it really is a date, or is it a string item?
Are you using any functions or expressions in your report - particularly for the Quoted Date item?
What is the exact detailed error message you see?
Cheers!
MF.
Hi MF,
I am sorry I can't show you the full error message because I found another way to go to the goal.
And since the report has already been modified, it has not shown the error message.
Thank you for your answer.
Young