How to create a list of values in Business Objects

113 views
Skip to first unread message

Bill LaCroix

unread,
May 26, 2023, 1:32:29 PM5/26/23
to umi...@googlegroups.com
Does anyone know how to create a list of values  - in a single variable - in Business Objects?

I'm trying to associate Project Grants to rooms.  A room may have zero, one, or more PGs.  I want to display the list of PGs on a single row, something like this:

Building Room DeptID Room Type Project  Grant(s)
1000158 2034 173500 250 F051001;F051002;F051003
1000158 3316 173500 250 F051111
1001451 102 172500 250  

I seem to recall doing this years ago but I can't remember how.

Thanks,
- Bill
Bill LaCroix
Business Systems Analyst / ITS Financial Systems
University of Michigan / bl...@umich.edu

Laura Dickey

unread,
May 27, 2023, 8:49:43 PM5/27/23
to umi...@googlegroups.com
Hi Bill

I can send you a formula you can try. I use it when merging a variable -with multiple values-into a report. 
 I have a report with an example using a P/G number I’ll send to your Inbox. 


Laura 
Sent from my iPhone

On May 26, 2023, at 11:32 AM, Bill LaCroix <bl...@umich.edu> wrote:


--
You received this message because you are subscribed to a topic in the Google Group: U-M BusinessObjects Users Forum.
 
If you want to remove yourself from this group, visit https://groups.google.com/g/umichbo/membership.
---
You received this message because you are subscribed to the Google Groups "U-M BusinessObjects Users Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to umichbo+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/umichbo/CAAvCyiU2bvpnTk2QdomP3HgLsbdqw%2BHdw5LgtyZME0AV5PEEdw%40mail.gmail.com.

Michelle Terrell

unread,
May 30, 2023, 11:26:30 AM5/30/23
to umi...@googlegroups.com
I think we're all intrigued by your formula.  Any chance you would share with the class?  😊🌸


Michelle Pnacek Financial Manager/Accountant Lead
School of Music, Theatre & Dance
1100 Baits Drive 
Ann Arbor, MI 48109-2085
ZOOM Phone: 734-936-1628  E: mst...@umich.edu
UM - We act with Integrity. We act with Respect. We create Inclusion. We create Equity. We welcome Diversity.  We promote Innovation. Go Blue!



Laura Dickey

unread,
May 30, 2023, 11:35:02 AM5/30/23
to umi...@googlegroups.com
Hi Michelle,

I am having Bill look at it first to see if it meets his needs. Stay tuned.
Laura



--

Laura Dickey
dic...@umich.edu | 734.936.1368
Manager, Business Intelligence and Analytics
University of Michigan | Office of Research and Sponsored Projects (ORSP)
Office Hours - 9:30am-5:00pm (EST) Monday-Thursday

Michelle Terrell

unread,
May 30, 2023, 11:40:13 AM5/30/23
to umi...@googlegroups.com
😊🌸


Michelle Pnacek Financial Manager/Accountant Lead
School of Music, Theatre & Dance
1100 Baits Drive 
Ann Arbor, MI 48109-2085
ZOOM Phone: 734-936-1628  E: mst...@umich.edu
UM - We act with Integrity. We act with Respect. We create Inclusion. We create Equity. We welcome Diversity.  We promote Innovation. Go Blue!



Bill LaCroix

unread,
May 30, 2023, 12:37:21 PM5/30/23
to umi...@googlegroups.com
Thanks to all who have provided input and/or expressed interest.
I'm working on it and will certainly let this group know the result!

- Bill
Bill LaCroix
Business Systems Analyst / ITS Financial Systems
University of Michigan / bl...@umich.edu


Douglas Hovey

unread,
May 31, 2023, 8:40:49 AM5/31/23
to U-M BusinessObjects Users Forum
If you are up to changing the SQL, you could go about it with the Oracle function LISTAGG:
SELECT
  JRNL_LN_CURR_FN_DEPT_VW.DEPT_GRP_DESCR,
LISTAGG(JRNL_LN_CURR_FN_DEPT_VW.DEPTID, ',') WITHIN GROUP (ORDER BY JRNL_LN_CURR_FN_DEPT_VW.DEPTID) Depts  
FROM
  M_GLDW1.CURR_FN_DEPT_VW  JRNL_LN_CURR_FN_DEPT_VW
WHERE
  JRNL_LN_CURR_FN_DEPT_VW.DEPT_GRP  =  'PROCUREMENT_SERVICES'
GROUP BY JRNL_LN_CURR_FN_DEPT_VW.DEPT_GRP_DESCR

You would get the following:
listagg.png

Bill LaCroix

unread,
May 31, 2023, 4:53:56 PM5/31/23
to umi...@googlegroups.com
Thanks again to all who have provided suggestions and/or expressed interest in this problem.  It was all very helpful !!

What ultimately worked for me is the approach proposed below from Doug Hovey:  to customize the SQL and use the function LISTAGG.
SELECT
  M_SMDW2.RM_ORA_CHARTFIELDS.RMRECNBR,
LISTAGG(M_SMDW2.RM_ORA_CHARTFIELDS.PROJECT_GRANT, ',') WITHIN GROUP (ORDER BY M_SMDW2.RM_ORA_CHARTFIELDS.PROJECT_GRANT) Rooms
FROM
  M_SMDW2.RM_ORA_CHARTFIELDS
WHERE
  (
   M_SMDW2.RM_ORA_CHARTFIELDS.FISCAL_YEAR  =  2023
   AND
   M_SMDW2.RM_ORA_CHARTFIELDS.RMRECNBR  IN  @dpvalue('A', DP0.DO7da)        /* needed to use values from another query as input */
  )
GROUP BY M_SMDW2.RM_ORA_CHARTFIELDS.RMRECNBR

Then I merged the RMRECNBR field from this query and another query so I could add the list of PG's to my table.

The intent of this BO report was to gather data for the Cost Reimbursement Office on all rooms of Type 250 or 255 for a specified department.

image.png

Much obliged to Doug and everyone else,
- Bill


--
You received this message because you are subscribed to a topic in the Google Group: U-M BusinessObjects Users Forum.
 
If you want to remove yourself from this group, visit https://groups.google.com/g/umichbo/membership.
---
You received this message because you are subscribed to the Google Groups "U-M BusinessObjects Users Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to umichbo+u...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages