Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Account Schedule KPI W...
Microsoft Dynamics NAV (Archived)

Account Schedule KPI Web service. Is it possible to add columns containing dimension codes?

(0) ShareShare
ReportReport
Posted on by Marius Krajauskas

I am currently working on my company financial BI analysis and one of requirement is to filter by global dimension 1 code and global dimension 2 code.

I am using NAV 2016 web services for data export.

Account schedule KPI Export only data that can be analyzed by Acc. Schedule Names, Lines and time but i cannot analyze data by dimensions.

Is there any way to Setup (or edit page) Account Schedule KPI that it would add multiple dimensions code values in new columns. (I need every Account Schedule Line calculated field to be grouped by Global dimension 1 code and Global dimension 2 code and month)

Example: http://prntscr.com/aoqmub

If not maybe there is any way to calculate Accound Schedule Lines totalings grouping data by global dimension 1 code and global dimension 2 code and month?

*This post is locked for comments

  • Pamura Profile Picture
    Pamura on at
    RE: Account Schedule KPI Web service. Is it possible to add columns containing dimension codes?

    Try to publish Page 198 "Acc. Sched. KPI WS Dimensions" as Web Service (instead of Page 197 "Account Schedule KPI Web Service").

  • Marius Krajauskas Profile Picture
    Marius Krajauskas on at
    RE: Account Schedule KPI Web service. Is it possible to add columns containing dimension codes?

    Those dimensions are included. http://prntscr.com/ar85xy 

    I am BI developer (not used to working with NAV settings only with web service and NAV queries). This may be the reason why I could not configure it on my own.

    I guess NAV totallings calculation formulas does not support grouping by dimensions.

    It would be hard to calculate A / B when grouping by dimensions for example: 

    A =

    [row with dimension code 1]
    [row with dimension code 2]

    B=

    [row with dimension code 3]
    [row with dimension code 5]

    as it would have to calculate values based on every different rows, it is not hard to calculate formulas when operators are + and - but it causes problems when used with ( ) */ operators.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Account Schedule KPI Web service. Is it possible to add columns containing dimension codes?

    What is the setup of Column Layout? Have you included those 3 dimensions in the same?

  • Marius Krajauskas Profile Picture
    Marius Krajauskas on at
    RE: Account Schedule KPI Web service. Is it possible to add columns containing dimension codes?

    Yes you are right, but I need to export data grouped by dimensions (Dimensions values included in collumns)

    My web service settings are: http://prntscr.com/aqvpun

    Account schedule settings: http://prntscr.com/aqvqee

    I setup analysis views settings http://prntscr.com/aqvrbm I use 3 dimensions, however the Acc. Sched. KPI Web Service only returns calculated values http://prntscr.com/aqvwg1

    I consume web service data with excel and use power pivot for BI dashboards but I can not analyze the data by those 3 dimensions. I wonder if there is any way to include dimension values in collumns.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Account Schedule KPI Web service. Is it possible to add columns containing dimension codes?

    You already have "Dimension 1 Filter" & "Dimension 2 Filter" in the Acc. Schedule Line Table. Also, you have "Dimension 1 Totaling"  & "Dimension 2 Totaling" fields.

    Here you can filter the record for specific Dimensions & extract the data.

    Also, you can create Column Layout (For all the Dimensions Value) and can get the required data.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans