Collect data from SharePoint and make from that a report

Question

I want to create a report , the data source will be a SharePoint list . I tried to use the PowerBI (unfortunately , its configuration is not going so well) , then I thought about Excel (a combination of the three lists , each on a separate sheet + pivot table - unfortunately, it receives system is not satisfactory ) . Eventually, I was thinking of using InfoPath , but have not yet tried it.

In general, based on three tables , Applications , Tasks, packages.
Entries (fields : Id , Subject , Customer , Date , Priority , Status , Division , Task Type , Task Time ( all times are summed tasks ) , Cost, Amount billing month - a calculated column )
Tasks (fields : Title tasks , Priority , Assigned To , Department , Planned , Date, Time , Amount , job status )
Packages (fields : Name, Customer Value Package , Valid from , Valid to )

The relationship between the tables are as follows:
Submissions ( Post ) < --- > Tasks ( Application ).
Application (Client) < --- > Package (Client).

The result table should include:
Post notification tasks (all of which only apply to the Application ) , Customer Name , billing amount , the remaining amount (the value of the package less the amount of the invoice ) .

This type of report , for each customer is presented every month.

Do you have any suggestions for a solution?

Answer 1

(Reporting Services would probably be simplest as it can connect to multiple SharePoint lists - particularly if you want a hard copy option.) - not sure this is good in O365.

Another alternative could be to use SPD to create a new data source and combine the three lists. This would essentially give you all the columns in one list view but the XSLT to present it would be fun.

If you are happy with an interactive screen-based solution then you could consider using jQuery. Use Ajax/REST to get the data from the lists into arrays and then decide how you want to present it. In this case I would be tempted to add dynamic filters so you could choose to just look at the items for a single Client or Package etc.

EDIT: just realised you are on O365.

In this case I would certainly say using PowerPivot in PowerBI would be a good option. You can still have the data in SharePoint lists and connect them to PowerPivot. If you get the PowerPivot model right then you could probably use PowerView to present it.