Report to pull multiple Product Categories/Organizational Units per project

[ritikbaral]

Hi Experts,

We are facing the following issue.

We have created a Project and have associated multiple Product Category & Organization to this project, many to One relationship. This we have achieved through customization. When we are pulling report for this project through Reports-> Project Summary Extract. Itu2019s giving us only one Product Category & one Org Unit per Project. The business wants us to pull all the Org Units & the Product categories associated with the Project, when we are displaying the report. This org units & product categories will be shown in Org Unit & Product category columns respectively separated by commas.

Looking forward for your help.

Thanks,

Ritik

[vikram_shanmugasundaram]

Hi Ritik,

It sounds like you have created custom fields/tables to capture multiple product categories and Org Units.

The Project Summary Extract report, which is a standard (out of the box) report only queries the standard Product Category and Org Unit fields on the Project. In order to get the values from custom fields, you would have to create a custom query and update the SQL to query the custom fields.

Regards,

Vikram

[ritikbaral]

Hi Vikram,

We have created any custom fields neither for the Product Categories nor for the Org Unit/Business Unit(T1.BUSINESS_UNIT_OBJECT_NAME). These two fields are already there in the Result fields for the report Project Summary Extract . I am just looking for some way to pull all the Product categories & Business Units associated with the project in the Product Categories column & in Business Unit column separated by comma or show it as in example 2 given below.

e.g. 1:

Project: Product Categories:

PROJ1 PC1,PC2,PC3

Or

e.g. 2:

Project: Product Categories:

PROJ1 PC1

PROJ1 PC2

PROJ1 PC3

Thanks,

Ritik

[ritikbaral]

Hi Vikram,

We have not created any custom fields neither for the Product Categories nor for the Org Unit/Business Unit(T1.BUSINESS_UNIT_OBJECT_NAME). These two fields are already there in the Result fields for the report Project Summary Extract . I am just looking for some way to pull all the Product categories & Business Units associated with the project in the Product Categories column & in Business Unit column respectively separated by comma or show it as in example 2 given below(in multi rows).

e.g. 1:

Project: Product Categories:

PROJ1 -> PC1,PC2,PC3

Or

e.g. 2:

Project: Product Categories:

PROJ1 -> PC1

PROJ1 -> PC2

PROJ1 -> PC3

I have already copied the existing query & modified it to achieve the above, but still this is not working. Currently i am querying FCI_MAS_INTERNAL_CAT for Product Categories & FCI_PRO_PROJECTS for projects.

Can you please provide me any sample query to achieve this functionality. Any help on this is greatly appreciated.

Thanks,

Ritik

[Former Member]

Hi

What Vikram has rightly pointed out is that you must have created some extension field/ collection in projects to accomodate multiple org units/ product categories. In standard Projects header tab you can enter one org unit and one product category only.

I would suggest going to Set up-> System Setup-> Configuration-> Extension Definition and open extension definition for projects. Here you'll find the extension field along with database table used fo storage Attribute Database Table. You can write a report to pick up required data from these tables performing join with standard tables (as you have mentioned).

Regards

Mudit Saini

[ritikbaral]

Hi Mudit,

We dropped the idea of showing the Product Category & Business Unit separated by comma(the example i have given in my earlier post). Now we want the the report in this format.

Lets say Project P1 has 4 different Product Categories & Project P2 has 3 diff Product Categories. We want to display our report in the following format.

Project: --Product Categories:

-

---

P1----

---

PCAT1

P1----

---

PCAT2

P1----

---

PCAT3

P1----

---

PCAT4

P2----

---

PCAT5

P2----

---

PCAT6

P2----

---

PCAT7

Following is the query i am trying right now.

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_PRO_PROJECTS T1 FULL OUTER JOIN <%SCHEMA%>.FCI_MAS_INTERNAL_CAT T3 ON T1.INTERNAL_CAT_OBJECT_ID= T3.OBJECTID WHERE T1.CREATED_AT >= ? AND T1.CREATED_AT <= ? <%ORDERBY%>.

Its giving me the only Product Category for each project not all.

Thanks,

Ritik