Help needed in a report.

[YashBadiani]

Scenario
********

I have data from two different data providers which are from two different universes.

The data in the two providers looks something like below :-

Please note that the columns are separated by pipes(|).

Data provider 1(DP1)
====================

ActNum | RepCd| StCde1
----------------------
123456 | YB21 | IL
123460 | YC45 | NC

Each account can be associated with multiple representative codes(RepCd) and a single StCde1.

Data provider 2(DP2)
====================

Each representative(RepCd) is licensed for a list of states.

RepCd | StCde2
----------------------------------------------------

YB21 | MI
YB21 | US
YB21 | NY
YB21 | NJ

YC45 | MI
YC45 | NC


What is needed
--------------

I need to check for each account(ActNum),each representative code(RepCd)in DP1, whether StCde1 is present in list of StCde2
for each RepCd.

For ex :- Account number 123456 is associated with rep code YB21 and StateCode IL. I need to check whether IL is in the list of states for YB21 . In the above example YB21 does not have IL in its list in Data provider 2. Hence i should show this record in Data provider 1.

But for account 123460 which is associatied with NC and rep code YC45, the record should not be shown because NC is present in the list
of StCde2 for YC45.

To summarize, for each RepCd in the first data provider i need to check whether the corresponding StCde1 is present
in the list of StCde2 for the same RepCd code in DP2.

The common dimension between the two data providers is the RepCd.

Final Output
------------

ActNum | RepCd| StCde1
----------------------
123456 | YB21 | IL

Please let me know if the above is possible in BO.
If yes, then please let me know the detailed approach of how to go about it.

Any pointers to solve the above would be of great help. Hope the requirements are clear from the above.

Thank you in advance,
Yash

 

[tijimathew]

I guess you can try insertng a filter on the table containing rows from the first dataprovider

Filter defintion : = StCde1 = StCde2

Hope this helps!!
Regards
Tiji

 

[YashBadiani]

Hi Tiji,

The StCde1 for RepCde in the first data provider needs to be checked for the same RepCde in the second data provider.
Hence just filtering on the StCde would not help i guess.

For the first record in DP1, i need to check whether IL is present in the list of StCde2's for YB21.

Let me know if my understanding is incorrect of your suggestion.

Thanks,
Yash

 

[tijimathew]

Dear Yash

I am unable to find a way as such in Business Objects at the report level. But you can rewrite the first DP1 n such a way that the WHERE clause has a condition like stCde1 InList StCde.

Will this help ?

Regards
Tiji

 

[YashBadiani]

Hi Tiji,

I have been trying to do this since three days.

I havent had any luck as yet.

If you have any specific suggestions let me know.

I have tried everything i have known :

Contexts,
Where clause,
linking/unlinking dimensions of data provider

Let me know if you can help.

Thank You,
Yash