How to create a list of values in Business Objects

[Bill LaCroix]

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]

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]

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!

To view this discussion on the web visit https://groups.google.com/d/msgid/umichbo/D39ED9DE-FF99-4F2C-9A6C-6EA9F603C88A%40umich.edu.

[Laura Dickey]

Hi Michelle,

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

Laura

To view this discussion on the web visit https://groups.google.com/d/msgid/umichbo/CAMrR5B83MN6DcnGpyNkxv-EJnyRDX1ojEhgzdrsof3N0bwqYsw%40mail.gmail.com.

--

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]

😊🌸

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!

To view this discussion on the web visit https://groups.google.com/d/msgid/umichbo/CAA-rPPQRsnHAO80kYSZM5%2BvUzjipcFnLnVDDmOwYDOnq6%3DTF8w%40mail.gmail.com.

[Bill LaCroix]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/umichbo/CAA-rPPQRsnHAO80kYSZM5%2BvUzjipcFnLnVDDmOwYDOnq6%3DTF8w%40mail.gmail.com.

[Douglas Hovey]

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:

[Bill LaCroix]

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.

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/umichbo/65dfc737-14c8-4dce-ac9c-2038b55ae129n%40googlegroups.com.