Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 262 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
OData & Excel Powerview structure
rxqrxqrxqrxqrxq
Member Posts: 22
Hi,
I got a question about the Structure based on the OData webservice, my Boss want me to implement some reports based on Odata webservice and Powerview, Powerpivot in Excel,but I found if I use the Odata webservice connect to excel directly, we can't do any coding in the OData side, so I come out with the method: publish the Page in OData, and I connect Odata Page from Excel through OData, but we can't dynamic define the filter based on this structure(Page->OData->Excel), I will give one samples to clarify my issue:
For example, we want to generate the report,this report is time based:
Item No., Inventory, Qty. on Purchase Order,Safety Stock Qty., Qty. on Sales Order, Available Qty.(Inventory + Qty. on Purchase Order - Qty. on Sales Order), Reorder Qty(Safety stock + Available Qty.)
then How can I design the structure to implement the report, and the date filter should be dynamics choosing by the user. thanks for helping me with this.
I got a question about the Structure based on the OData webservice, my Boss want me to implement some reports based on Odata webservice and Powerview, Powerpivot in Excel,but I found if I use the Odata webservice connect to excel directly, we can't do any coding in the OData side, so I come out with the method: publish the Page in OData, and I connect Odata Page from Excel through OData, but we can't dynamic define the filter based on this structure(Page->OData->Excel), I will give one samples to clarify my issue:
For example, we want to generate the report,this report is time based:
Item No., Inventory, Qty. on Purchase Order,Safety Stock Qty., Qty. on Sales Order, Available Qty.(Inventory + Qty. on Purchase Order - Qty. on Sales Order), Reorder Qty(Safety stock + Available Qty.)
then How can I design the structure to implement the report, and the date filter should be dynamics choosing by the user. thanks for helping me with this.
0
Comments
Prior to thinking about a specific question such as how to filter you should be clear about the data structure of your report and how to source it in NAV.
I guess you would want to "export" daily snapshot of each of your measures, right?
You could use Power Query as an intermediary layer between Excel's Power Pivot engine and OData to do that sort of filtering based on an Excel-fieldj such as this:
In Power query you could reference this Parameter-table by Power Query's functions:
//just a snippet:
Param_Table = Excel.CurrentWorkbook(){[Name="Param_Table"]}[Content],
//Date parameters
Param_FromDate = Date.ToText(DateTime.Date(Param_Table{5}[Value]), "MM-dd-yyyy"),
Param_ToDate = Date.ToText(DateTime.Date(Param_Table{6}[Value]), "MM-dd-yyyy")
....
and use this to filter your oData-Query. You can read more about Power Query on Chris Webb's blog here or related to NAV and Power Query on navida's blog
Solutions that use Power Query would imply that the user first changes the parameter table and to subsequently refresh the Power Pivot file in order to retrieve the data according to the filter (similar to the approach of Jet Reports).
If you are open to alternative ways I would recommend to take a look at the possibilites that slicers and pivot table filters provide to solve your problem. Using a filter field or a date field (even the date slider in Excel 2013) you could just import "all" data (what is normally no probably due to Power Pivot's compression) and define your measures to take the date filter/date field into consideration. This approach provides full flexibility of pivot tables since you could place your date field on rows, columns or on the filter. The tricky part is to get the measure right.
Have you been able to create a page that provides the OData-feed? That should be the starting Point.
I hope this helps.
Regards,
Jut