Nested LOV Prompts

[thstockw]

At our institution, we are migrating reporting systems. In our prior system, we were able to have nested prompts, and each aspect of the nested prompt was returned to the report. If we asked for college and department, both prompts would be returned to the report. This allowed searching on both fields, since departments could have multiple colleges associated with them

When attempting to do the same in BO 4.3, I am running into the issue where only the last LOV value is returned. In the college and department example, I can only see department returned by the prompt, not the associated college.

This is sample code from the department LOV:

SELECT 
  SMRPRLE_SOBCURR.SORCMJR_DEPT_DESC, 
  SMRPRLE_SOBCURR.SORCMJR_DEPT_CODE
FROM @DerivedTable("SMRPRLE_SOBCURR")
WHERE SMRPRLE_SOBCURR.SOBCURR_COLL_CODE = ANY @Prompt(STVCOLL - Active Colleges)
GROUP BY 
  SMRPRLE_SOBCURR.SORCMJR_DEPT_DESC, 
  SMRPRLE_SOBCURR.SORCMJR_DEPT_CODE
ORDER BY 
  SMRPRLE_SOBCURR.SORCMJR_DEPT_DESC, 
  SMRPRLE_SOBCURR.SORCMJR_DEPT_CODE

For my report, I need to know the selection for both prompts, college and department. When I use this in Webi, I am only getting the department code.

The only workaround that I can think of is using composite keys, such as:
college$$$department. This would allow parsing out both keys from the selection.

[martensnl]

What kind of LOV is related to the college object? If you relate a hierarchy LOV to this object two things happen:

• Prompt shows a hierarchy departments → colleges
• Where statement would include the both departments and colleges if something out of the colleges is selected

You then need to also include the department object and college object in your report to show them (sadly you can’t get the hierarchy path for the userreponse function).

[thstockw]

I’ve tried experimenting with the hierarchy functionality, but I need to display the description of the record, and return the code. It seems that Hierarchy can only have one or the other.

I’ve accomplished what I am trying to do with composite keys, but then I am running into issues with the prompts not merging together, since the prompt needs to feed code to Free-Hand SQL.

College LOV (SMRPRLE - UMD College):

SELECT SMRPRLE_SOBCURR.UMD_COLL_DESC, SMRPRLE_SOBCURR.UMD_COLL_CODE
FROM @DerivedTable(SMRPRLE_SOBCURR)
GROUP BY SMRPRLE_SOBCURR.UMD_COLL_DESC, SMRPRLE_SOBCURR.UMD_COLL_CODE
ORDER BY SMRPRLE_SOBCURR.UMD_COLL_DESC, SMRPRLE_SOBCURR.UMD_COLL_CODE

Department LOV (SMRPRLE - UMD College - Department)

SELECT DISTINCT
	SMRPRLE_SOBCURR.SORCMJR_DEPT_DESC, 
	SMRPRLE_SOBCURR.SORCMJR_DEPT_CODE,
	SMRPRLE_SOBCURR.UMD_COLL_DESC,
	SMRPRLE_SOBCURR.UMD_COLL_CODE,
SMRPRLE_SOBCURR.UMD_COLL_CODE||'$$$'||SMRPRLE_SOBCURR.SORCMJR_DEPT_CODE KEY_CODE
FROM @DerivedTable(SMRPRLE_SOBCURR)
WHERE SMRPRLE_SOBCURR.UMD_COLL_CODE = ANY @Prompt(SMRPRLE - UMD College)
ORDER BY 
	SMRPRLE_SOBCURR.SORCMJR_DEPT_DESC, 
	SMRPRLE_SOBCURR.SORCMJR_DEPT_CODE,
	SMRPRLE_SOBCURR.UMD_COLL_DESC,
	SMRPRLE_SOBCURR.UMD_COLL_CODE,
SMRPRLE_SOBCURR.UMD_COLL_CODE||'$$$'||SMRPRLE_SOBCURR.SORCMJR_DEPT_CODE

---

With this code, the Universe is returning college code and department code, delimited by three dollar signs. My next issue is that I need to use these combinations in Free Hand SQL. I’m attempting to use the key from the universe and feed it into the Free Hand SQL here:

SELECT 
  sgbstdn_pidm,
  sgbstdn_program_1,
  sgbstdn_dept_code,
  sgbstdn_coll_code_1
FROM sgbstdn
WHERE DECODE(sgbstdn_coll_code_1,'BU','COB','MA','COB','EG','CECS','EN','CECS','ED','CEHHS','EH','CEHHS','CA','CASL','ID','CASL', sgbstdn_coll_code_1)||'$$$'||sgbstdn_dept_code = ANY @Variable('Select a department')

BO does not want to merge the two prompts together, even though the prompt message is the same for both prompts.

[martensnl]

I really can’t get where you want to go.

Why can’t you use the text? The hierarchy will automatically concatenate the text values from the selected node level and match this with the concatenated related objects.

[thstockw]

I don’t believe that works with Free Hand SQL. I was having issues with merging the prompt from the heirarchy with the @Prompt that was in the Free Hand SQL, so the data was never sent to my SQL query. We don’t have universes built out for this particular database, so universes are not currently an option to do this.