I apologize in advance for the length of this post:
I have created an Excel user form and which has an has a connection to a SQL server database by way of Analysis Services. The data connection is feeding customer information to the form based on user input of customer account number. I have had good success with several "CUBEVALUE" formulas providing measure data like account balance. I have run into a problem though in trying to feed a member value based on mutliple member values, (account number) and (Data Date). After some searching I found the CUBERANKEDMEMBER(CUBESET()) combo below. The formula provided below ("FIRST ATTEMPT") actually gives me the "[Risk Rating].children" member that I desired which was great. My enthusiasm waned when I realized that the value it returned was a value from a different date period than the date period I wanted which is the most recent month end.
Below is a link a fictitious example of the source table. Based on the formula below, if I leave the CUBEDRANKMEMBER "rank" as a "1", I get Risk Grade = 3, "rank" as a "2" I get Risk Grade = 6, and so on. The value I actually need is the risk grade associated with 10/31/18 which in this example has a Risk Rating = "6". The problem is that accounts will often have different risk grades across various dates which is why I need to refer to the Risk Grade member value on a specific date.
MY FIRST ATTEMPT:
CUBERANKEDMEMBER("cubedb",CUBESET("cubedb","([Account Loan].[Account Number].["&A7&"],[Account Loan].[Risk Rating].children)"),1)
I am using a date period parameter in my CUBEVALUE formulas on my form so I wondered if I could do the same with the formula above and so I copied the date member formula from CUBEVALUE formulas on my form and pasted it into the formula above to get the formula below.
SECOND ATTEMPT:
CUBERANKEDMEMBER("snldb SNLBanker SNL Banker Primary",CUBESET("snldb SNLBanker SNL Banker Primary","([Account Loan].[Account Number].["&A7&"],[Date].[Calendar Period].[Date].&["&BM5&BK5&BL5&"],[Account Loan].[Risk Rating].children)"),3)
This formula produced the same result as the first attempt, so I am back to square one and clueless as what else to try. Thanks in advance for any ideas on this.
