I have created a worksheet in our BI software that is pulling all of it's information from a View in SQL. The View does a Union All to combine current and history info from different tables. Everything works fine, for the most part.
In the BI software, calculations run against different account numbers. Some of the account numbers are Parent Account numbers, and some are Child account numbers.
The worksheet is grouped by Region, Parent Account Number, Customer Name.
When you run the worksheet and drill down to the Parent Account number, the customer name may be the one for the Parent Account number, or it may be for the Child Account number that is attached to the Parent. It's behaving correctly, because if activity was done in the child account it should show the name of the Child account.
However, I would like when the Parent Account number is shown, for it to also show the name of the Parent account, whether or not activity was done.
The code as it is now:
WHEN LEN(ARCF.ARCF_PARENT_CUST_N ) > 1
WHEN LEN((SELECT B.ARCF_ALT_CUST_N FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N)) > 1 THEN ( SELECT LTRIM(RTRIM(B.ARCF_ALT_CUST_N)) FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N )
ELSE ( SELECT LTRIM(RTRIM(B.ARCF_CUST_N)) FROM ARCF B WHERE B.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N )
WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1 THEN LTRIM(RTRIM(ARCF.ARCF_ALT_CUST_N))
END AS Parent_ACCOUNT,
The column in the ARCF table that has the name that corresponds to the account number is called arcf_name.
So if I ran this query, I would like it show: