NAV 2013 Query Object

BOBSMART

I am working on a query using the data (simplified) below. Reporting this using Excel from Odata feed.

Resource Type Amount
General Yellow 100
General Blue 150
Other Yellow 50
Other Blue 75


I want the resulting data to be

Resource Yellow_Amount Blue_Amount
General 100 150
Other 50 75

Is this possible in the Query Object?

Marije_Brummel

I think you'll need a pivot table in Excel.

BOBSMART

Thanks Mark, I have done this this using a pivot table, unfortunately I am dealing with a report with 187,000 rows and 30 columns. When created the Pivot Table can take up to 30 minutes to calculate. I need the data to come from the query in the summarized format.

Jut

When you say that the Pivot Table can take up to 30 minutes to calculate do you mean the data refresh from the OData source or any interaction with that Pivot table? Are you using Power Pivott? 187000 rows and 30 columns certainly should not result in any performance issues when done correctly.

However it would be possible to use a Page object that is published as a web Service instead of a query. Using a page allows all sort of calculations in NAV - take a look at the new page Acc. Sched. KPI Web Service in NAV 2013 R2. It does some calculations in NAV in order to export the Information as an OData-Feed.

cnicola

Hi Bob,

I would need to see the data structure you use but I would imagine if you are OK with the data as:

General
Yellow
Blue
Other
Yellow
Blue

you could do it in the Query.

Otherwise, with Pivottable, are you using Power Pivot? That should be able to handle that many rows with no problem.