Hi, with the gooddata ui sdk on GD platform, I'm able to do a pivot table passing, rows, columns, metrics and filters. However with useExecutionDataView, I cannot transpose the table within the query. This is particularly useful when I switch an attribute to a column. Any parameters I can use on useExecutionDataView so I don't transpose in JS on the front end? useExecutionDataView( { execution: { seriesBy, slicesBy, filters }, } );

Hi @Michael Serres. Have you tried just interchange attributes in seriesBy and slicesBy?

What do you mean? I can put an attribute in seriesBy? I though that was for metrics only? seriesBy={[Md.FinRental.Avg]} slicesBy={[Md.VersionStd, Md.CustId, Md.CorrMake, Md.CorrModel]} change to seriesBy={[Md.FinRental.Avg, Md.CustId]} slicesBy={[Md.VersionStd, Md.CorrMake, Md.CorrModel]}

@Michael Serres Yes seriesBy can contain also attribute apart of metric

@Ivan Nejezchleb It doesn't seem to work with useExecutionDataView Do you have an example with a measure and attribute?

IF I understand correctly: Before you have: maker, model, custId in Rows and value in metrics After you want to have: maker, model in Rows and custid in Columns So the maker and model attributes should go to the first dimension (by adding to the slicesBy) and custid+metric to second dimension (by adding to seriesBy) in execution definition.

@Ivan Nejezchleb I think this would require to define resultSpec, wouldn't it? @Michael Serres I'd like to direct you to https://sdk.gooddata.com/gooddata-ui/docs/result_specification.html. There are calls

.forItems()

and

.withDimensions()

that are closely related to

seriesBy/slicesBy

and

resultSpec

props respectively. Hopefully this will shed some light on the issue.

@Jiri Zajic useExecutionDataView hook can consume as param either preparedExecution (IPreparedExecution) or IExecutionConfiguration. First one is instance with methods you have mentioned. Second one is just config which is transformed to the preparedExecution internally. But there is relationship between placing attributes into

seriesBy

and

slicesBy

and their placement to the correct dimension in created execution

Hi @Jiri Zajic @Ivan Nejezchleb Really like that trick of looking at the Fetch/XHR to see the executeAfm details. I created the insight. But I’m stuck again, I don’t know how to specify the ResultSpec parameters in useExecutionDataView Do you have an example? … seriesBy={[Md.CustId, Md.FinRental.Avg]} slicesBy={[Md.VersionStd, Md.VehSegment, Md.BodyStyle, Md.Fuel, Md.CorrMake, Md.TrimName, Md.CorrModel]} filters={currentFilter} const { result, error, status } = useExecutionDataView( { execution: { seriesBy, slicesBy, filters }, }, )

@Michael Serres if execution’s ResultSpec created automatically for your executionConfig is not what you want, then you need to replace whole execution part of

config

param by execution instance created manually by you. To do so, you need to call

createExecution

similarly to https://github.com/gooddata/gooddata-ui-sdk/blob/master/libs/sdk-ui/src/execution/useExecutionDataView.ts#L151 and then use its

.withDimensions

method to get ResultSpec as you need

Failed. This is really hard to do… @Ivan Nejezchleb Do you have an example for this? I wasn’t able to fix. There’s no example in the docs for useExecutionDataView, with dimensionality that pivots one attribute to columns.

I understand you needed to move forward @Michael Serres, but I still wanted to give it one last shot 🙃 This is an older example I found that does not use the

useExecutionDataView()

hook, but the analogy should be the same. I attached 3 screenshots. In the first picture the dimensions are defined automatically, so there's only one dimension by default. In the second picture there's two dimensions (

newTwoDimensional()

) defined programatically,

stackBy

vs

measures + viewBy

. In the third picture there's two dimensions again, this time

viewBy

vs

measures + stackBy

. Please observe how the data output changes in the console on the right. Even without knowing anything about the data, it should be clear that the dimensions of those computations are different.

@Jiri Zajic super helpful, thanks. It’s in the backlog, I’ll come back to it.

Hello @Michael Serres! I checked one more time and @Ivan Nejezchleb is right: The

useExecutionDataView()

you currently use is not "powerful" enough to accept a custom

resultSpec

that defines custom slicing by dimensions. However, if you look under the hood of our own

useExecutionDataView()

that @Ivan Nejezchleb linked at https://github.com/gooddata/gooddata-ui-sdk/blob/master/libs/sdk-ui/src/execution/useExecutionDataView.ts#L151, you'll see that the

useExecutionDataView()

internally uses the approach I described above that looks like this:

backend
    .workspace(workspace)
    .execution()
    .forItems(seriesBy.concat(slicesBy), filters)
    .withSorting(...sortBy)
    .withDimensions(...dimensions);

It looks more complex but no need to worry! It's almost exactly the same, it's just the construct that's a bit different.

Hi @Jiri Zajic My colleague tried the suggestion above, works for simple measures but not for arithmetic ones. I’ll let him explain below.

Hi @Jiri Zajic, indeed, we tried to pivot the data following the example mentionned on the GD community website, but infortunately it raise an error when we specify a complex metric (

newArithmeticMeasure

) in the execution

Hello @Michael Serres and @Tarik! I'm on PTO today but I'll do my best to get to it tomorrow when I'm back. Thank you for your patience!

Thanks for your help

Could you please provide your full payload? How do you define the new arithmetic measure and what are you slicing it by?

Hi @Jiri Zajic It’s been a while, hope all is well. Was finally able to help client implement the pivot in the query, rather than the front end, works well with sample code in article. However, I don’t understand why adding measures increases the number of rows returns and eventually gives an error. I know there limitations in the engine, rows, columns and cells but I don’t know how to compute. => Am I doing something wrong? Additional measures should be columns not rows? How do I keep rows to 9k in this query? // const measures = [Md.FinRental.Avg]; // result returns 9k rows // const measures = [Md.FinRental.Avg, Md.AdjDeposit.Avg]; // result returns 18k rows // const measures = [Md.FinRental.Avg, Md.AdjDeposit.Avg, Md.Tyres.Avg, newArithmeticMeasure([Md.FinRental.Avg, Md.AdjDeposit.Avg, Md.Tyres.Avg], “sum”)]; // result returns 36k rows const measures = [Md.FinRental.Avg, Md.AdjDeposit.Avg, Md.Tyres.Avg, Md.ReplacementVehicle.Avg, Md.Wintertyres.Avg, Md.Breakdown.Avg, newArithmeticMeasure([Md.FinRental.Avg, Md.AdjDeposit.Avg, Md.Tyres.Avg, Md.ReplacementVehicle.Avg, Md.Wintertyres.Avg, Md.Breakdown.Avg], “sum”)]; // should return 63k rows but errors, too large to compute const execution = backend .workspace(workspace) .execution() .forItems([...measures, Md.VersionStd, Md.CustLabel], currentFilter) .withDimensions(...newTwoDimensional([Md.CustLabel], [MeasureGroupIdentifier, Md.VersionStd])); const { result, status, error } = useExecutionDataView({ execution, });

@Michael Serres Hi, if Insight created via Gooddata UI is ok then you can use it directly in your app via InsightView component https://sdk.gooddata.com/gooddata-ui/docs/visualization_component.html Or you need to tweak your execution definition until it is the same as the execution request in UI. For finding the issue you can share these two execute request bodies here with us. But your expectation about metrics is correct. Number of rows is given by cardinality of attribute used in Rows (first dimension). Adding new measures should increase mainly the number of columns BUT if newly added metric causes that report now has value for some new attribute elements number of rows may increase too. So maximal size of your report is given by cardinality of your Md.CustLabel. How many elements does it have?

Hi @Ivan Nejezchleb thanks for the reply. Also looping in @Jiri Zajic. The data is all in one table. That query should return 9k rows. There are 17 cust_labels. It runs with 2 attributes (row vehicle, column cust_label) and works with 6 measures but fails with 7 measures. Because 9k * 7 is 63k which is over 60k rows limit. However, I believe there’s 2 possible solutions here: 1. useDataExecutionView: is there a trick to pass the parameters differently so the pivot adds columns, not row for measures? 2. use execution / execution Afm just like the insight page in GoodData (I did the exact same query, see screenshot, and it works returns 9k rows) The query that fails is like this const measures = [Md.FinRental.Avg, Md.AdjDeposit.Avg, Md.Tyres.Avg, Md.ReplacementVehicle.Avg, Md.Wintertyres.Avg, Md.Breakdown.Avg, newArithmeticMeasure([Md.FinRental.Avg, Md.AdjDeposit.Avg, Md.Tyres.Avg, Md.ReplacementVehicle.Avg, Md.Wintertyres.Avg, Md.Breakdown.Avg], “sum”)]; // should return 63k rows but errors, too large to compute const execution = backend .workspace(workspace) .execution() .forItems([...measures, Md.VersionStd, Md.CustLabel], currentFilter) .withDimensions(...newTwoDimensional([Md.CustLabel], [MeasureGroupIdentifier, Md.VersionStd])); const { result, status, error } = useExecutionDataView({ execution, });

@Michael Serres Hi, can you post here execution request payload from right image in form of full JSON and the same for request done from your UI SDK application which ends with error? I am just curious what is the difference between these two executions if any.

@Ivan Nejezchleb Hi Ivan, these are the payloads (fails in react but works on insight, 7 measures) 1. From react app with useExecutionDataView { “execution”: { “afm”: { “measures”: [ { “localIdentifier”: “m_fact.re_all_secure.fin_rental_avg”, “definition”: { “measure”: { “item”: { “identifier”: “fact.re_all_secure.fin_rental” }, “aggregation”: “avg” } } }, { “localIdentifier”: “m_fact.re_all_secure.adj_deposit_avg”, “definition”: { “measure”: { “item”: { “identifier”: “fact.re_all_secure.adj_deposit” }, “aggregation”: “avg” } } }, { “localIdentifier”: “m_fact.re_all_secure.tyres_avg”, “definition”: { “measure”: { “item”: { “identifier”: “fact.re_all_secure.tyres” }, “aggregation”: “avg” } } }, { “localIdentifier”: “m_fact.re_all_secure.replacement_vehicle_avg”, “definition”: { “measure”: { “item”: { “identifier”: “fact.re_all_secure.replacement_vehicle” }, “aggregation”: “avg” } } }, { “localIdentifier”: “m_fact.re_all_secure.wintertyres_avg”, “definition”: { “measure”: { “item”: { “identifier”: “fact.re_all_secure.wintertyres” }, “aggregation”: “avg” } } }, { “localIdentifier”: “m_fact.re_all_secure.breakdown_avg”, “definition”: { “measure”: { “item”: { “identifier”: “fact.re_all_secure.breakdown” }, “aggregation”: “avg” } } }, { “localIdentifier”: “m_dab26944c4782044e46d0a43f54ebfcd”, “definition”: { “arithmeticMeasure”: { “measureIdentifiers”: [“m_fact.re_all_secure.fin_rental_avg”, “m_fact.re_all_secure.adj_deposit_avg”, “m_fact.re_all_secure.tyres_avg”, “m_fact.re_all_secure.replacement_vehicle_avg”, “m_fact.re_all_secure.wintertyres_avg”, “m_fact.re_all_secure.breakdown_avg”], “operator”: “sum” } } } ], “attributes”: [ { “displayForm”: { “identifier”: “label.re_all_secure.version_std” }, “localIdentifier”: “a_label.re_all_secure.version_std” }, { “displayForm”: { “identifier”: “label.re_all_secure.cust_label” }, “localIdentifier”: “a_label.re_all_secure.cust_label” } ], “filters”: [ { “positiveAttributeFilter”: { “displayForm”: { “identifier”: “label.re_all_secure.mth_code” }, “in”: { “values”: [“2023 02”] } } }, { “positiveAttributeFilter”: { “displayForm”: { “identifier”: “label.re_all_secure.ctryid” }, “in”: { “values”: [“UK”] } } }, { “positiveAttributeFilter”: { “displayForm”: { “identifier”: “label.re_all_secure.channel1" }, “in”: { “values”: [“B2B”] } } }, { “positiveAttributeFilter”: { “displayForm”: { “identifier”: “label.re_all_secure.duration” }, “in”: { “values”: [“36/30000"] } } } ] }, “resultSpec”: { “dimensions”: [{ “itemIdentifiers”: [“a_label.re_all_secure.cust_label”] }, { “itemIdentifiers”: [“measureGroup”, “a_label.re_all_secure.version_std”] }] } } } 2. From GoodData Insight { “execution”: { “afm”: { “measures”: [ { “localIdentifier”: “4f3d4ce840254317985865359d0e6a8c”, “definition”: { “measure”: { “item”: { “uri”: “/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/445” }, “aggregation”: “avg” } }, “alias”: “Avg Fin Rental”, “format”: “#,##0.00” }, { “localIdentifier”: “a27921751dba4a3c90eddaed5875bc20”, “definition”: { “measure”: { “item”: { “uri”: “/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/434" }, “aggregation”: “avg” } }, “alias”: “Avg Adj Deposit”, “format”: “#,##0.00" }, { “localIdentifier”: “148a8cf93f874efb9c8a5559ecd42303", “definition”: { “measure”: { “item”: { “uri”: “/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/469” }, “aggregation”: “avg” } }, “alias”: “Avg Tyres”, “format”: “#,##0.00” }, { “localIdentifier”: “3ece5ce838b44744ac8b74d6d91b3b63”, “definition”: { “measure”: { “item”: { “uri”: “/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/465" }, “aggregation”: “avg” } }, “alias”: “Avg Replacement Vehicle”, “format”: “#,##0.00" }, { “localIdentifier”: “f7a486c3bef74956a43d00eec394c0bb”, “definition”: { “measure”: { “item”: { “uri”: “/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/470” }, “aggregation”: “avg” } }, “alias”: “Avg Wintertyres”, “format”: “#,##0.00” }, { “localIdentifier”: “2d8d272b8e3a4805a8297f30d807283a”, “definition”: { “measure”: { “item”: { “uri”: “/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/435" }, “aggregation”: “avg” } }, “alias”: “Avg Breakdown”, “format”: “#,##0.00" }, { “localIdentifier”: “1f60155c12be46edb9efdac1553e83fc”, “definition”: { “arithmeticMeasure”: { “measureIdentifiers”: [“4f3d4ce840254317985865359d0e6a8c”, “a27921751dba4a3c90eddaed5875bc20”], “operator”: “sum” } }, “alias”: “Sum of Avg Fin Rental and Avg Adj Deposit”, “format”: “#,##0.00” } ], “attributes”: [ { “displayForm”: { “uri”: “/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/418” }, “localIdentifier”: “bc86d67530e4432fb853077e2454fbfb” }, { “displayForm”: { “uri”: “/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/432" }, “localIdentifier”: “a9e22559178d48b0a549e49ac8d74f98" } ], “filters”: [ { “positiveAttributeFilter”: { “displayForm”: { “uri”: “/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/376” }, “in”: { “uris”: [“/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/375/elements?id=170146"] } } }, { “positiveAttributeFilter”: { “displayForm”: { “uri”: “/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/386" }, “in”: { “uris”: [“/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/385/elements?id=83”] } } }, { “positiveAttributeFilter”: { “displayForm”: { “uri”: “/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/366” }, “in”: { “uris”: [“/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/365/elements?id=136795"] } } }, { “positiveAttributeFilter”: { “displayForm”: { “uri”: “/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/398" }, “in”: { “uris”: [“/gdc/md/x41u6cse81mqd8fo00da3oxsv3ww3ye6/obj/397/elements?id=15947608”] } } } ] }, “resultSpec”: { “sorts”: [{ “attributeSortItem”: { “attributeIdentifier”: “bc86d67530e4432fb853077e2454fbfb”, “direction”: “asc” } }], “dimensions”: [{ “itemIdentifiers”: [“bc86d67530e4432fb853077e2454fbfb”] }, { “itemIdentifiers”: [“a9e22559178d48b0a549e49ac8d74f98”, “measureGroup”] }] } } }

Yes shape of result should be changed, as the execution definition changed as well

@Ivan Nejezchleb Thank you very much for the help, good learning here, great trick to look at the payload 😍

@Michael Serres Network panel is your friend :)