Jorn Lindhard Mortensen's blog

Moving on

You may have noticed that things have been quiet for a while on this blog, well here is why:

Microsoft Office Accounting will no longer be distributed after November 16, 2009.

As a consequence I have moved on to new challenges within Microsoft, now focusing on Dynamics AX, a product I had worked quite a bit with before my time with Office Accounting. I appreciate the interest, comments and questions about Office Accounting here on the blog, and personally I’m truly sorry to see the end of the product.

cnet

pcpro

I’m planning to keep blogging, as you can imagine the topic will change to Dynamics AX related things. 

Office Accounting 2009 US now available - FREE upgrade!

How can I create new Office Accounting reports that can be seen by low rights users?

A few people have had this problem now: They create a report add-in for Office Accounting but when they deploy to the end users the reports does not show up in the menu. One of those ”but it works for me!!” problems…

A typical reason can be that you forgot to set the AreaAccess property when you registered the report. The value of this property determines what access rights the user needs to be able to see the report:

descriptor.AreaAccess = new PermissionArea[] {

                PermissionArea.ManageCompanyFA, PermissionArea.GeneralLedgerTransactionsFA}

Naturally there is another side to this; remember to ensure that a user with the rights you assign actually has access to the data you are trying to show, if not the report may just fail when you try to get the data from the database.

My Office Accounting report’s aging filters need to add new columns

I just got this question:

“The problem is that I am creating a new custom report using the INativeReportV2 interface.  I am trying to apply Aging Options Filter to the report.  The problem is, how I do capture those values in the CreateDesign method?  The CreateDesign method only accepts the IReportDesign parameter, it does not pass in the IReportFiltersV2 parameter.  If you can provide any advice on this issue, I’d really appreciate it.”

The only answer I could find is: … you don’t

You can’t do that in the CreateDesign method that is. You will have to cheat and create the columns in another method where you do have access to the IReportFiltersV2 parameter.

CreateData would be a good option for such a method – but then I don’t have access to the IReportDesign !?!

Accessing data in an Office Accounting report

I was asked how you can access the data in an Office Accounting report in code. As the code shows below this is very simple – you just have to remember a few things:

·         There is no guarantee that the columns in the data view won’t change between versions

·         If you use the groups make sure to iterate through the sub groups as that may be where the data lives

ISmallBusinessInstance instance = (ISmallBusinessInstance)sbaObjects.SmallBusinessInstance;

IReportEngineV2 reportEngine = (IReportEngineV2)sbaObjects.ReportEngine;

// Get a report descriptor

IReportDescriptorV2 reportDescriptor

    = reportEngine.ReportDescriptorView.GetByName("CustomerTransactionHistory");

// Use the report descriptor to create a report executor

INativeReportExecutorV2 reportExecutor

    = (INativeReportExecutorV2)reportEngine.GetReportExecutor(reportDescriptor);

// Let's change a filter on the report

// just to show that you can modify the report before running the report

IReportFilterDateV2 filterDate =

    (IReportFilterDateV2)reportExecutor

    .ReportProperties.ReportFilters.GetFilter(ReportFilterEnum.DateFilter);

filterDate.DateFilter = DateFilter.All;

// Access the dataView, this will automatically

// run the report if it hasn't been run

DataView dataView = reportExecutor.ReportDocument.DataView;

// Another way To access the data would be through the groups:

foreach (IReportGroupV3 group in reportExecutor.ReportDocument.ReportGroups)

{

    // A dataView with the data in this group

    DateView dataInThisGroup = group.GroupDataViewRows;

    // In some reports groups may have sub groups

    // You can easily iterate these as well

    foreach (IReportGroupV3 subGroup in group.SubGroups)

    { }

}

New blog on Office Accounting

I just wanted to let you know that my friend  John Thuneby has started a new blog about Office Accounting. My guess is that he will write more about the functionality in the application and less about the technical stuff. I know that John welcomes feedback on Office Accounting so if you have any head over there and check out the shiny new blog.

His first post is about the ability to create a template for a company go there to read more about it.

Set up Office Accounting for multiple users

I just saw this article on how to set up Office Accounting for multiple users. As I know this is a topic that people has a lot of questions about I thought I would post a link to it...

At the same time you can find a lot of usefull information in the How To section on that site.

Enjoy

How can I export an Office Accounting report to Excel in code

It has been a while since my last post and I have gotten a few questions. One question was related to exporting reports to Excel.

This is actually pretty simple; you may have noticed the IReportExportToExcelV2 interface. This is the key to this task.

Basically the system report executor implements this interface and therefore you can use it to export.

Time for an example:

// First we need the engine

ISbaObjects sbaObjects = loader.GetSbaObjects(.........);

ISmallBusinessInstance instance

    = (ISmallBusinessInstance)sbaObjects.SmallBusinessInstance;

IReportEngineV2 reportEngine

    = (IReportEngineV2)sbaObjects.ReportEngine;

// Get a report descriptor

IReportDescriptorV2 reportDescriptor

    = reportEngine.ReportDescriptorView

          .GetByName("CustomerTransactionHistory");

// Use the report descriptor to create a report executor

INativeReportExecutorV2 reportExecutor

    = (INativeReportExecutorV2)reportEngine

           .GetReportExecutor(reportDescriptor);

// Let's change a filter on the report

// just to show that you can modify the report before exporting

IReportFilterDateV2 filterDate

    = (IReportFilterDateV2)reportExecutor.ReportProperties

    .ReportFilters.GetFilter(ReportFilterEnum.DateFilter);

filterDate.DateFilter = DateFilter.Today;

// Now everything is ready and we can cast the

// executor to an IReportExportToExcelV2

IReportExportToExcelV2 exportToExcel

    = reportExecutor as IReportExportToExcelV2;

if (exportToExcel != null)

    exportToExcel.ExportToExcel();

Pretty easy eh?

Fresh off the press: Office Accounting 2008

Time for some exciting news on brand new Office Accounting versions!!

Today we are releasing the new Office Accounting 2008.

Among the great news we are now releasing new versions; besides the new US version we have now added a new US-Spanish version for the Spanish speaking population in the US…

And many have been asking for a UK version of Office Accounting for a while now, I’m happy to say that you can now download this product as well!

Official press release

How do I use the ReportColumnType and ReportColumnPreferenceType values when I build custom Office Accounting reports?

The question about the ReportColumnType came up in a comment for a prior post, and yeah I guess it’s fair that I tell you about it :o)

First a brief description of the values, from the quick overview you can see that you really do not have that many ColumnTypes to choose from as many of the values are really for internal use only…

ReportColumnType values:

So really you should focus on four Column types: RowLabel, Data, CurrencySymbolColumn and NotVisible.

To give the full story about the CurrencySymbolColumn value I need to describe another enumeration in a bit more detail:

ReportColumnPreferenceType values:

Hope this little overview helps a bit…

Add custom filters to your custom Office Accounting reports

What if I have created a INativeReport and I want to filter it, but the build in filters are not exactly what I need?

Well you can create your own filters! It does require some coding though (but we like code, right?).

As an example let’s add a “Region” filter to the “Customers Grouped by Region” report I created in my last post. To do this I need to create a new class that implements IReportFilterGenericV2 and ISerializable.

If you use Visual Studio to implement the IReportFilterGenericV2 interface you will notice a few sections. I’ll run through them quickly:

•  IReportFilterGenericV2 Members
• public void ClearAll() - This method is called if the user clicks the clear button in the filter dialog. Basically you should set the state of the filter back to the default value or “All”.
• public IFilterOptionV2[] GetFilterBarOptions(IReportFiltersV2 reportFilters) - The filter can be shown in two places: the filter bar (above the report) or in the filter dialog. This method should return the options to show in the filter bar.
• public IFilterOptionV2[] GetFilterDialogOptions(IReportFiltersV2 reportFilters) - This method should return the options to show in the filter dialog

• IReportFilterV2 Members
• public ReportFilterEnum FilterEnumValue - This Property is used to identify the filter, for Generic filters it should always return ReportFilterEnum.GenericFilter.
• public string GetDisplayValue(ISmallBusinessInstance instance) - This method should return the value you want to show in the Filter text in the header of the report.
• public string Label - This is the label used in the filter dialog on the left hand side
• public void SetValues(IReportFilterV2 filter) - This method is used to move the values from one instance of the filter to another. Basically you should make sure all of your instance data is copied from the argument to “this”.
• public bool Visible - A property used to hide the filter. You should simple save this state in a variable so you can return it in the getter.

• ICloneable Members
• public object Clone()- Standard implementation of ICloneable, here you would typically use the SetValue method for an easy implementation.

For the ISerializable implementation is pretty standard, the serialization is used when users save the report:

• ICloneable Members
• public void GetObjectData(SerializationInfo info, StreamingContext context) - This method should add the instance data from the filter to the SerializationInfo object (used when serializing).
• public ctor(SerializationInfo info, StreamingContext context) - You need to create a special constructor for the de-serialization, the constructor should set the instance data from the SerializationInfo object (this is used when the user launches the saved report).

I have put a sample implementation of IReportFilterGenericV2 on this page if you would rather look at code…

With all of that work we now have a new filter that will show in the top filter bar in a report and in the filter dialog. To use it we have to add it to the report.

First we need to add the new filter to the report, to do that we add a few lines in the CreateFilters method:

// Add the region filter to the filter collection as a generic filter

RegionFilter regionFilter = new RegionFilter();

reportFilters.AddGenericFilter(regionFilter);

To actually use the filter we will need to apply the filter in the CreateData method. Naturally the implementation here will vary based on what you expect your filter to do, but I hope you get the idea from this sample:

if (reportFilters.ContainsFilter(ReportFilterEnum.GenericFilter))

{

    RegionFilter reportRegionFilter

        = reportFilters.GetFilter(ReportFilterEnum.GenericFilter)

            as RegionFilter;

    // apply region filter

    if (String.IsNullOrEmpty(reportRegionFilter.State) == false)

    {

        if (filterString.Length > 0)

            filterString.Append(" AND ");

        filterString.Append("State = '");

        filterString.Append(reportRegionFilter.State);

        filterString.Append("'");

    }

    else if (reportRegionFilter.CensusRegion != (int)CensusRegions.All)

    {

        if (filterString.Length > 0)

            filterString.Append(" AND ");                   

        filterString.Append(

            RegionFilter.GetCensusRegion(reportRegionFilter.CensusRegion));

    }

}

// Apply row filter

if (filterString.Length > 0)

    view.RowFilter = filterString.ToString();

The result is a nice report with a custom filter:

How to create Office Accounting reports with totals?

Ok - it’s been way too long since my last post, and I now feel forced to cover a topic that several people has asked me:

“native reports are great and all, but can I add a total to my report”.

The good news is the answer is yes, the bad news is it requires some work.

Let’s assume I want to create a report that can show customers grouped by state and zip code… After using the template to generate the class for me I start out by filling in CreateData:

return engine.SmallBusinessInstance.CustomerAccounts.DataView;

then I fill in the CreateDesign method:

// Add a title to the report

iReportDesign.ReportHeader.ReportTitle.Text = "Customers Grouped By Region";

//  Add the data columns to the report

//  Note:  the columns must correspond to columns in the DataView returned

//  by the CreateData method

// Add the Name data column to the report

groupByNameColumn = iReportDesign.ReportColumns.CreateReportColumn(

    "Name",                     //Column name in the dataview

    "Customer Name",            //Caption for the column

    ReportColumnType.RowLabel,  //Column type

    ReportColumnDataType.Text); //Data type

// Add the Address data column to the report

iReportDesign.ReportColumns.CreateReportColumn(

    "Address",                   //Column name in the dataview

    "Address",                   //Caption for the column

    ReportColumnType.Data,      //Column type

    ReportColumnDataType.Text); //Data type

// Add the City data column to the report

groupByCityColumn = iReportDesign.ReportColumns.CreateReportColumn(

    "City",                   //Column name in the dataview

    "City",                   //Caption for the column

    ReportColumnType.Data,      //Column type

    ReportColumnDataType.Text); //Data type

// Add the State data column to the report.  Save a reference to

//  the column so that it can be added to the GroupByColumns collection

//  later.

groupByStateColumn = iReportDesign.ReportColumns.CreateReportColumn(

    "State",                   //Column name in the dataview

    "State",                   //Caption for the column

    ReportColumnType.Data,     //Column type

    ReportColumnDataType.Text);//Data type

// Add the ZipCode data column to the report.  Save a reference to

//  the column so that it can be added to the GroupByColumns collection

//  later.

groupByZipColumn = iReportDesign.ReportColumns.CreateReportColumn(

    "ZipCode",                 //Column name in the dataview

    "ZipCode",                 //Caption for the column

    ReportColumnType.Data,     //Column type

    ReportColumnDataType.Text);//Data type

// Add the Balance data column to the report

groupByBalanceColumn = iReportDesign.ReportColumns.CreateReportColumn(

    "Balance",                  //Column name in the dataview

    "Balance",                  //Caption for the column

    ReportColumnType.Data,      //Column type

    ReportColumnDataType.Currency); //Data type

// Add the Active data column to the report

iReportDesign.ReportColumns.CreateReportColumn(

    "Active",                   //Column name in the dataview

    "Active",                   //Caption for the column

    ReportColumnType.Data,      //Column type

    ReportColumnDataType.Boolean); //Data type

This is all that is needed for a regular list report but we want the report grouped so we implement the IReportHasGroupsV2 interface and fill in the CreateGroupByColumns method

/// <summary>

/// This method creates the GroupByColumns whose values are used to create

/// row groups in the report.  Rows that have identical values in the first

/// column of the groupByColumns collection are grouped first.  Within each

/// group, rows with identical values in the second column become subgroups,

/// and so on.

/// </summary>

public void CreateGroupByColumns(IReportGroupByColumnsV2 groupByColumns)

{

    // Create groups by state

    groupByColumns.CreateGroupByColumn(groupByStateColumn);

    // Create subgroups by zip code

    groupByColumns.CreateGroupByColumn(groupByZipColumn);

}

To sort the groups in a particular order I need to fill the CreateGroupSortByColumns method, in this case I will sort the groups by state.

public void CreateGroupSortByColumns(IReportGroupSortByColumnsV2 groupSortByColumns)

{

    groupSortByColumns.CreateGroupSortByColumn(groupByStateColumn, true);

}

The report now has groups but to actually see the groups we need to put values into the group itself, that is done in the GroupAdded method. In this method I am using a couple of properties that may need a little explanation:

-       reportGroup.GroupByColumnValues This is a collection on the group that contain the group by values this group is based on.

-       reportGroup.Header.GroupValues This is a collection with all the values for a group header, by using the indexer with the column name you can specify a value for the group header for a specific column, below I’m setting values in the “name” column

/// <summary>

/// This method is called whenever a row group or subgroup is created. 

/// It allows header and footer elements to be added to each group.

/// </summary>       

public void GroupAdded(IReportGroupV2 reportGroup)

{

    String stateValue = reportGroup.GroupByColumnValues[groupByStateColumn.Name] as String;

    String zipValue = reportGroup.GroupByColumnValues[groupByZipColumn.Name] as String;

    if (stateValue != null)

    {

        if (string.IsNullOrEmpty(stateValue))

        {

            stateValue = "None";

        }

        // Create the report group header

        // The value "Name" determines the horizontal location of each

        // header value within the report. It does not indicate that the

        // data in the "Name" column is available.

        if (zipValue != null)

        {

            if (string.IsNullOrEmpty(zipValue))

            {

                zipValue = "None";

            }

            reportGroup.Header.GroupValues["Name"] = String.Format("Zip code: {0}", zipValue);

        }

        else

        {

            reportGroup.Header.GroupValues["Name"] = String.Format("State: {0}"), stateValue);

        }

    }   

}

Yeah yeah yeah, we have seen it all before what about the total? I’m sure this is your question at this point…

OK Let’s get to the total then

The GroupAdded method is also the place you can add a total to your report! Just like you can set values for the reportGroup.Header you can also set values for the reportGroup.Footer so adding something like this to the method will actually give you a total…

reportGroup.Footer.GroupValues["Name"] = String.Format("Customers: {0}", CountGroupMembers(reportGroup));

reportGroup.Footer.GroupValues["Balance"] = GetGroupBalance(reportGroup);

Yes I know I’m cheating as I’m using a couple of methods I haven’t defined yet, The name of the methods should be pretty much self explanatory, however how to get to the values may not be. I’m sure you can find a much faster way of doing this by using the view created in CreateData but this demonstrates a few of the methods available and it works.

private int CountGroupMembers(IReportGroupV2 reportGroup)

{

    int count = reportGroup.GroupDataViewRows().Count;

    foreach (IReportGroupV2 group in reportGroup.SubGroups) {

        count += CountGroupMembers(group);

    }

    return count;

}

private Decimal GetGroupBalance(IReportGroupV2 reportGroup)

{

    Decimal balance = 0;

    foreach (DataRowView row in reportGroup.GroupDataViewRows())

    {

        balance += (Decimal) row.Row["Balance"];

    }

    foreach (IReportGroupV2 group in reportGroup.SubGroups)

    {

        balance += GetGroupBalance(group);

    }

    return balance;

}

Let’s look at the result of our effort (Click the image to see full view):

Nice eh?

More than one report in one Office Accounting add-in

Office Accounting 2007 Service Pack 1

I know I'm a little late with this announcement, but for those who haven't seen it we now have a service pack ready for download from

http://www.microsoft.com/downloads/details.aspx?familyid=F5A69C78-39A0-4B10-9072-456031457EB5&displaylang=en

If you are interested in the details of the service pack you can find them here

http://support.microsoft.com/kb/934431/

What is taking time in my MS SQL database?

Just thought I would post this info as I have been talking to some people that did not have all the MS SQL tools installed and then did not know how to get performance information out of the server … maybe just maybe other people in the world would have a similar question :o) ….

When you are developing applications running on databases you will often want to know what is actually taking time in the database.

If you are running on MS SQL Server 2005 (or MS SQL Server 2005 Express) you have a couple of ways to get to the data:

·         SQL Server Profiler, this is my tool of choice if it is installed on the machine.

·         Object Execution Statistics report, this may be a little more unknown. If you so not have the profiler you can still run the report, with this report you can get to some of the same data as if you are running a profile, this time presented in a nice report. You can get to the report in Microsoft SQL Server Management Studio in the top of the summary page.

·         Select it from the system views. If you don’t have any tools except the server you can still get to some information. When SQL Server runs it saves some execution statistics that you can access through system views (the report retrieves data from these views). The SQL Statement below is able to pull quite interesting data from a database…

Select  s3.name as [Obj Name]

,s3.type as [Obj Type]

,(select top 1 substring(text,(s1.statement_start_offset+2)/2,

 (case

    when s1.statement_end_offset = -1 

    then len(convert(nvarchar(max),text))*2

    else s1.statement_end_offset

  end - s1.statement_start_offset) /2  )

  from

    sys.dm_exec_sql_text(s1.sql_handle)) as [SQL Statement]

,execution_count

,plan_generation_num

,last_execution_time

,((total_worker_time+0.0)/execution_count)/1000 as [avg_worker_time]

,total_worker_time/1000.0 total_worker_time

,last_worker_time/1000.0 last_worker_time

,min_worker_time/1000.0 min_worker_time

,max_worker_time/1000.0 max_worker_time

,((total_logical_reads+0.0)/execution_count) as [avg_logical_reads]

,total_logical_reads+0.0 total_logical_reads

,last_logical_reads+0.0 last_logical_reads

,min_logical_reads+0.0 min_logical_reads

,max_logical_reads+0.0 max_logical_reads

,((total_logical_writes+0.0)/execution_count) as [avg_logical_writes]

,total_logical_writes+0.0 total_logical_writes

,last_logical_writes+0.0 last_logical_writes

,min_logical_writes+0.0 min_logical_writes

,max_logical_writes+0.0 max_logical_writes

,((total_logical_writes+0.0)

   /execution_count + (total_logical_reads+0.0)/execution_count)

 as [avg_logical_IO]

,total_logical_writes + total_logical_reads+0.0 total_logical_IO

,last_logical_writes +last_logical_reads+0.0 last_logical_IO

,min_logical_writes +min_logical_reads+0.0 min_logical_IO

,max_logical_writes + max_logical_reads+0.0 max_logical_IO

from    sys.dm_exec_query_stats s1

cross apply sys.dm_exec_sql_text(sql_handle) as  s2

inner join sys.objects s3  on ( s2.objectid = s3.object_id )

left outer join sys.schemas sch on(s3.schema_id = sch.schema_id)

where s2.dbid = db_id()

order by  s3.name, s1.sql_handle 

To run such a statement with no special tools installed I often use SqlCmd. With this tool I can quickly run a script on the database from the command prompt with something like this:

SQLCMD -S"(local)\MSSMLBIZ" -d"sampleproductcompany" -Q"select * from CustomerAccountTable" > select.txt

Naturally you have to put in your own server and database information ;o)  As you can see it is not a hard thing to use, simply state the server (-S) the database (-d) and a SQL query (-Q) or if your query is big you can put it in a file and execute it with a –i parameter instead of the -Q