Need a report to combine a main record table with a user defined table

[KLJernigan600]

I have an Asset table that I am looking to pull the Asset Id and a few fields but also from a separate dynamic user defined field table in a single row format.  In the raw form I get multiple rows for the same asset .

This is what I have:

Table ID   User-Defined Label Key   User-Defined Label   
FA      31            LOAN PROVIDER
FA      32            LOAN NUMBER
FA      33            TOTAL LOAN AMOUNT
FA      34            PAYMENT FREQUENCY
FA      35            PAYMENT AMOUNT   
FA      36            LOAN INTEREST RATE
FA      37            LOAN PAYOFF DATE      
FA      38            BLUE BOOK VALUE
FA      39            PAYOFF AMOUNT      
FA      40            LOAN INCEPTION DATE

How Report looks with raw data from table

Label ID      User-Defined Key   User-Defined Label   User-Defined Text        User-Date       User-Defined Amount
0000001074   31            LOAN PROVIDER      Truist      
0000001074   32            LOAN NUMBER      9511838921-90035      
0000001074   33            TOTAL LOAN AMOUNT                             10,545.93
0000001074   36            LOAN INTEREST RATE                     7.65
0000001074   37            LOAN PAYOFF DATE               Feb 21, 2023    
0000001074   39            PAYOFF AMOUNT                        1,755.02
0000001074   40            LOAN INCEPTION DATE            Nov 1, 2018
0000001075   31            LOAN PROVIDER      Truist      
0000001075   32            LOAN NUMBER      9511838921-90035      
0000001075   33            TOTAL LOAN AMOUNT                             9,807.99
0000001075   36            LOAN INTEREST RATE                     7.65
0000001075   37            LOAN PAYOFF DATE               Feb 21, 2023
0000001075   39            PAYOFF AMOUNT                        1,632.21
0000001075   40            LOAN INCEPTION DATE            Nov 1, 2018
0000001076   31            LOAN PROVIDER      Truist      
0000001076   32            LOAN NUMBER      9511838921-90035      
0000001076   33            TOTAL LOAN AMOUNT                             9,807.99
0000001076   36            LOAN INTEREST RATE                     7.65
0000001076   37            LOAN PAYOFF DATE               Feb 21, 2023
0000001076   39            PAYOFF AMOUNT                        1,632.21
0000001076   40            LOAN INCEPTION DATE            Nov 1, 2018


How I want the report to look

Label ID      Loan Provider   Loan Number           Total Loan Amount   Loan Interest Rate   Loan Payoff Date   Payoff Amount   Loan Inception Date
0000001074   Truist      511838921-90035   10545.93              7.65                      21-Feb-23              1755.02      01-Nov-18
0000001075   Truist      9511838921-90035   9807.99              7.65                      21-Feb-23              1632.21      01-Nov-18

[cognostechie]

This is the result of not having a Data Warehouse with a star schema format. What you have is a transactional table with different things pertaining to the loan in rows and in a Data Warehouse you would have a table which will store all those in columns with the Label ID as it's ID. Anyway, you can still do what you want but you will have to create multiple queries, set filters and then combine them together to present all attributes of one Label ID in one row.

1> Create a query (query 1) with only Label ID
2> Create a query (query 2)  with all columns of your second table and create a filter for user defined key to be 31
3> Create a query (query 3)  with all columns of your second table and create a filter for user defined key to be 32
      ... and so on till the last key

Join Query 2 with Query 1 on LabelID
Join Query 3 with Query 1 on LabelID
     .. and so on

Do this in Framework Manager so that you can join all these queries. In Report Studio, you can join only 2 queries and then join the resulting 3rd query with another query. 

Create a Model Query subject in Framework which will have all rows as one row showing all attributes as columns.

[KLJernigan600]

This is a hosted database and access to the framework manager is difficult.  Is there a way to use a case or an if statement to accomplish this based on the user defined label key and populating unbound fields with the data based on each key.

[MFGF]

I have an Asset table that I am looking to pull the Asset Id and a few fields but also from a separate dynamic user defined field table in a single row format.  In the raw form I get multiple rows for the same asset .

This is what I have:

Table ID   User-Defined Label Key   User-Defined Label   
FA      31            LOAN PROVIDER
FA      32            LOAN NUMBER
FA      33            TOTAL LOAN AMOUNT
FA      34            PAYMENT FREQUENCY
FA      35            PAYMENT AMOUNT   
FA      36            LOAN INTEREST RATE
FA      37            LOAN PAYOFF DATE      
FA      38            BLUE BOOK VALUE
FA      39            PAYOFF AMOUNT      
FA      40            LOAN INCEPTION DATE

How Report looks with raw data from table

Label ID      User-Defined Key   User-Defined Label   User-Defined Text        User-Date       User-Defined Amount
0000001074   31            LOAN PROVIDER      Truist      
0000001074   32            LOAN NUMBER      9511838921-90035      
0000001074   33            TOTAL LOAN AMOUNT                             10,545.93
0000001074   36            LOAN INTEREST RATE                     7.65
0000001074   37            LOAN PAYOFF DATE               Feb 21, 2023    
0000001074   39            PAYOFF AMOUNT                        1,755.02
0000001074   40            LOAN INCEPTION DATE            Nov 1, 2018
0000001075   31            LOAN PROVIDER      Truist      
0000001075   32            LOAN NUMBER      9511838921-90035      
0000001075   33            TOTAL LOAN AMOUNT                             9,807.99
0000001075   36            LOAN INTEREST RATE                     7.65
0000001075   37            LOAN PAYOFF DATE               Feb 21, 2023
0000001075   39            PAYOFF AMOUNT                        1,632.21
0000001075   40            LOAN INCEPTION DATE            Nov 1, 2018
0000001076   31            LOAN PROVIDER      Truist      
0000001076   32            LOAN NUMBER      9511838921-90035      
0000001076   33            TOTAL LOAN AMOUNT                             9,807.99
0000001076   36            LOAN INTEREST RATE                     7.65
0000001076   37            LOAN PAYOFF DATE               Feb 21, 2023
0000001076   39            PAYOFF AMOUNT                        1,632.21
0000001076   40            LOAN INCEPTION DATE            Nov 1, 2018


How I want the report to look

Label ID      Loan Provider   Loan Number           Total Loan Amount   Loan Interest Rate   Loan Payoff Date   Payoff Amount   Loan Inception Date
0000001074   Truist      511838921-90035   10545.93              7.65                      21-Feb-23              1755.02      01-Nov-18
0000001075   Truist      9511838921-90035   9807.99              7.65                      21-Feb-23              1632.21      01-Nov-18

Hi,

This is messy, but it can be done. As cognostechie indicated, the best option would be to have the data in a suitable form to make reporting easy, but sometimes it just isn't possible.

The approach to take is to create query calculations for each item - looking for the appropriate User Defined Label values and including the appropriate target value.

Create a new report and add a List object to the report.
Add Label ID as the first column in the list.
Add a Query Calculation as the second column. Give it a name of Loan Provider, and add the expression

Code: [Select]

if ([User-Defined Label] = 'LOAN PROVIDER') then ([User-Defined Text]) else (null) and then in the properties pane for this list column, set the Detail Aggregation property to Maximum.
The third column will be very similar - call it Loan Number and code the expression as

Code: [Select]

if ([User-Defined Label] = 'LOAN NUMBER') then ([User-Defined Text]) else (null) - and set the Detail Aggregation to Maximum.
Follow the same pattern for the other columns you need - so Total Loan Amount would be

Code: [Select]

if ([User-Defined Label] = 'TOTAL LOAN AMOUNT') then ([User-Defined Amount]) else (null)
It's messy, but it should give you the results you need. To use the simple item names in the expressions like above (eg [User-Defined-Label]), you will need these items to exist in the query of the report - you can simply drag them into the query if you need to. Alternatively you can drag them in to the expressions from the source, and they will show as fully qualified names (eg [Your Namespace].[Your Table].[User-Defined-Label])

Cheers!

MF.

[KLJernigan600]

I now have access to framework manager but have never set the query options inside of framework manager always in the report writer.  What layer do I create the query subject and set the filters and how do I create it and tie it back to the framework?