NAV 2013 Query Object

BOBSMART
BOBSMART Member Posts: 4
edited 2014-03-30 in NAV Three Tier
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?

Comments

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    I think you'll need a pivot table in Excel.
    Marije Brummel | Freelance Business Central Specialist
  • BOBSMART
    BOBSMART Member Posts: 4
    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
    Jut Member Posts: 72
    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
    cnicola Member Posts: 181
    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.
    Apathy is on the rise but nobody seems to care.

Categories