I got a comment on one of the prior post asking if I would create a new post on how to query the data in the Office Accounting database directly, thank you for the question. I would like to encourage other readers to ask for other posts on other things that could help you. I won’t promise that I’ll get around to create posts on everything but I will do my best…

 

Back to accessing data directly from SQL.

 

Really if we compare that to any other Native Report, the only difference is the way we access the data – for us that means the CreateData method

 

public DataView CreateData(IReportFiltersV2 reportFilters, IReportEngineV2 engine)

{

    string connectionString = @"Persist Security Info=False;Integrated Security=SSPI;server={0};database={1};Connect Timeout={2};Pooling='no'";

    connectionString = string.Format(

        connectionString,

        engine.SmallBusinessInstance.ServerName,

        engine.SmallBusinessInstance.CompanyName,

        30);

 

    DataTable table = new DataTable();

 

    string sqlStatement =

        @"Select Name, TotalBalanceCompanyCurrency, CreditLimit from CustomerAccountEntityView";

 

    if (reportFilters != null)

    {

        //In this section we can add a where clause to the statement

    }

 

    using (SqlConnection connection = new SqlConnection(connectionString))

    {

        using (SqlCommand command = new SqlCommand(sqlStatement, connection))

        {

            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))

            {

                dataAdapter.Fill(table);

            }

        }

    }

 

    return table.DefaultView;

}

 

So all I’m doing is creating a new connection to the server/database from the SmallBusinessInstance and querying through that connection.

In some special scenarios this code may fail; like if the SQL server is installed as the default instance, in this case we would have to special case the naming, or if we are accessing a remote database we have to make sure the correct port number is applied to the database name.

 

The hardest thing about this is to write the right query against the Office Accounting database, and then handling any filters that you would want on the report.

 

Naturally to make things work for this report I have to add a few lines for the design:

 

iReportDesign.ReportColumns.CreateReportColumn(

    "Name",

    "Customer Name",

    ReportColumnType.RowLabel,

    ReportColumnDataType.Text);

 

iReportDesign.ReportColumns.CreateReportColumn(

    "TotalBalanceCompanyCurrency",

    "Balance",

    ReportColumnType.Data,

    ReportColumnDataType.Currency);

 

iReportDesign.ReportColumns.CreateReportColumn(

    "CreditLimit",

    "Credit Limit",  

    ReportColumnType.Data,

    ReportColumnDataType.Currency);

 

A quick comment on what data you can access directly: In the SDK documentation we mention 10 useful views, you can use other Entity views however potentially these views could change in future versions of Office Accounting. You should always go against the views in stead of the tables as the views have logic to only show valid data and the tables can only be accessed by the owner / administrator.

 

Btw. The code shown above was all the code I needed to create manually as the rest came for free from the template. (I just had to fit the link to templates in somehow :o)  )