0

Im trying to pull sql script that builds Aged Debt Report on Sage200 Report Designer. This report is out of the box

I have a powerbi model which pulls data from Sage Datawarehouse, however I am struggled to replicate the above report into powerbi because I couldnt access sql behind this report. When open this report in Sage200 Report designer, I could see that the data source is of LinQProvider with the connection string LinqDataProvider Sage200Accounts

Data Model=Sage 200 Accounts;Root Path=\SERVERNAME\Sage\REPORTING;Report Types=\SERVERNAME\Sage\REPORTING\DEFAULT\REPORTS\Sage200Accounts.reporttypes;Default Report=\SERVERNAME\Sage\REPORTING\DEFAULT\DEFAULTS\Default.report;Timeout=1800

I couldnt locate where this data model lives or I have enough understanding of how this report connects to the data model.

Anyone with Sage200 experience I would be so grateful if you can advice please? Basically I have access to SQL server datawarehouse, but the above report has some "variables" that somehow were built and saved inside this Data Model.

Appreciate your help

kcrisman
  • 4,374
  • 20
  • 41
PeddiePooh
  • 403
  • 8
  • 17

1 Answers1

2

The report designer is reading the datamodel from %userprofile%\appdata\local\sage\sage200\assembly in Sage.accounting.datamodel.dll

The model source is available on the server however in SLAgedTransaction.cs however it is protected by licence agreement so I can't post it.

In Sage 200 2011 it was possible to use the datamodel through LINQPad which may still work in the current version. With this in mind perhaps it would be a better idea to connect PowerBI to the datamodel rather than trying to extract SQL script? Also take a look at the good work Pan Intelligence are doing with regards to BI reporting

Adam Byrne
  • 71
  • 2