OData & Excel Powerview structure

rxqrxqrxqrxqrxq

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.

Jut

Hi.

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