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

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.

  • Verified Answer

    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!