Business requirement to create a report in report studio

[geethkal12]

Hello All,

I have a requirement to create a Claims report to display the claims that have been reviewed during the reporting period which is easy and straight forward.
Adding to the above requirement we got to display decisions( approved Claim or Declined Claim) as well for that particulat reviewed claim in the same reporting period.

The requirement is to show the relevant decision at the time of review.

Scenarios for the requirement:
1. one review date( review) and one decision date( also decisison).
2. 1 review date and 2 decisions
3. 1 review date and 3 or 4 decsions.
4. 2 review dates and 2or 3 decsions
5. multiple review dates with multiple decisions.

Also if there are 3 review dates we have to show same claim in three different rows with relevant decsiion at that particular time of that review.

I am able to build the report taking first 3 scenarios into consideration using below logic:
CASE WHEN Decision date <= Review date THEN maximumDecision DAte )
ELSE NULL
END

Please suggest me some solution to get done 4 and 5 scenarios as well.
Please help me as it is very urgent

Thanks in advance
Divya

 

[blom0344]

You can perform a count on review and decision dates against the claim dimension and then use a after aggregation filter to bring in just the required number, like:

1. count review dates [COUNT_REVIEW]:

COUNT([REVIEWDATE] FOR [CLAIM])

2. count decision dates [COUNT_DECISION]:

COUNT([DECISIONDATE] FOR [CLAIM])

add FILTER LIKE:

([COUNT_REVIEW] = 2 AND [COUNT_DECISION] = 2) OR
([COUNT_REVIEW] = 2 AND [COUNT_DECISION] = 3)

Make sure these are after aggregate filter..

Ties Blom