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
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.
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.
Quote from: KLJernigan600 on 09 Mar 2023 02:08:38 PM
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
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
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
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.
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?