Highlighted
Acclaimed Contributor.
Acclaimed Contributor.
147 views

SM 9.41 reporting - Pivot table supports cross-table queries?

Jump to solution

I'm building a report with a new SM 9.41 Reports tool - it's a really big leap forward, thanks guys.

My goal is to create a Pivot table, that shows how different items (table: svcCartItem) have been ordered montly.

I succeeded to do this - but a limitation is that svcCartItem contains catalog item's "technical name". I'd like to use a same (display) name which the users see in a catalog. (We use no other languages than English, so there are no multiple display names.)

I tried to build a cross-table query between svcCatalog and svcCartItem (after adding a relation to relatedObjectMap table and other steps*), but I could not get svcCatalog fields to "Row fields" in "Pivot table properties".

So basically question is - does Reports' Pivot Table support cross-table queries or is "Row fields" limited solely to fields in a one core table the report is about?

*) I used the instructions in SM help article "Use case: Incident Analyst". Even it's meant for old views, the same steps worked for a new Report as well.

---
Moving on, this account is no longer active. Best regards, Kelalek
- So Long, and Thanks for All the Fish
0 Likes
1 Solution

Accepted Solutions
Highlighted
Outstanding Contributor.
Outstanding Contributor.

Re: SM 9.41 reporting - Pivot table supports cross-table queries?

Jump to solution

Hi Kelalek2,

I perform an investigation to see if you can use a join table for this purpose but unfortunately the dashboard and report works only for data inside the Source Data table.

So I think one option you can consider is adding a custom field to svcCartItem table and populate the value on a trigger to get the Display Name value from svcCatalog when the svcCartItem record is created so it will have the data required for your report.

Good Luck!

Regards,

SM Support Engineer

Thank you for using the Micro Focus Community. If you find that this or any post resolves your issue, please be sure to mark it as an "accept as solution".

View solution in original post

1 Reply
Highlighted
Outstanding Contributor.
Outstanding Contributor.

Re: SM 9.41 reporting - Pivot table supports cross-table queries?

Jump to solution

Hi Kelalek2,

I perform an investigation to see if you can use a join table for this purpose but unfortunately the dashboard and report works only for data inside the Source Data table.

So I think one option you can consider is adding a custom field to svcCartItem table and populate the value on a trigger to get the Display Name value from svcCatalog when the svcCartItem record is created so it will have the data required for your report.

Good Luck!

Regards,

SM Support Engineer

Thank you for using the Micro Focus Community. If you find that this or any post resolves your issue, please be sure to mark it as an "accept as solution".

View solution in original post

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.