Tuesday, July 22, 2008

Video on Creating a Customized Version of Stonefield Query

We created a short (6 minutes) video on how to create a customized version of Stonefield Query to work with any application's database. As this video shows, setting up the data dictionary for your database is mostly automated, with you doing additional customization work as necessary.

Monday, July 14, 2008

Stonefield Query for SalesLogix 3.2 Released

Stonefield Software Inc. is pleased to announce the release of version 3.2 of Stonefield Query for SalesLogix.

Stonefield Query is a user-friendly report writing, business intelligence, query, and data mining tool. With its intuitive wizard driven design, Stonefield Query makes report writing a snap for even the most novice user. Elegant and persuasive reports can be created in minutes with little or no technical knowledge required.

Some of the new features in this release are:

  • If it is installed, Stonefield Query will use the SalesLogix OLE/DB provider for reporting. This means that Stonefield Query now adheres to SalesLogix record based security (you only see those records that your SalesLogix username is allowed to see) and all date/time fields will automatically be converted to your local time zone. If the OLE/DB provider is not available then Stonefield Query will connect via ODBC.
  • When using the OLE/DB provider, Stonefield Query automatically retrieves a list of the SalesLogix datasources that have been defined on your computer by your SalesLogix administrator.
  • Exclusion filters: this allows you to query on the absence of data, such as "give me a list of customers I didn't contact last year" or "give me a list of everyone who did not buy product X". Behind the scenes, Stonefield Query generates a NOT IN subquery, but from the end-user's perspective, it's just as easy as creating a normal filter.
  • Modeless report wizards: the report wizards are no longer modal, so you can modify more than one report at a time.
    Single ask-at-runtime dialog: all ask-at-runtime filter conditions now appear in a single, wizard-like dialog rather than one dialog per condition.
  • Workstation installer: Stonefield Query now has a separate WSSetup.EXE installer, installed by Setup.EXE, that does workstation installations with the minimum of user interaction.
  • More grouping options: you can now specify that a non-grouped field should be included in the group header for another field, you can have all fields in the group header on a single line rather than stacked on separate lines, and all fields in the group header now appear in summary reports.
  • Multi-line headings: many fields now have multi-line headings so they don't take up as much room in a report, and you can change field headings to use multiple lines.
  • Customize Report Wizard for all report types: all report wizards now have an Advanced button, allowing access to TOP N, custom SQL statements, and other features.
There are dozens of other new features and bug fixes. For more information about version 3.2, please see the What's New in This Version topic in the online documentation.

Tuesday, July 8, 2008

Antivirus Software Causing Errors in Stonefield Query

We've received several error reports from customers over the past couple of days about errors when running reports in Stonefield Query. The most common error is "Cannot load XFRXLIB.FLL". It turns out that a recent change in the definition files for the Trend Micro anti-virus software was erroneously flagging one of our DLLs, HNDLIB.DLL, as a suspect file and quarantining it. Since XFRXLIB.FLL relies on that DLL, it fails to load so Stonefield Query can't run reports.

Apparently Trend Micro has fixed the problem, so updating to the latest definition files should resolve the problem, although you'll have to manually remove HNDLIB.DLL from quarantine.

Even if you're not using Trend Micro, it's likely a good idea to configure your anti-virus software to not scan the Stonefield Query program folder.

Update: see http://esupport.trendmicro.com/support/viewxml.do?ContentID=EN-1036613&id=EN-1036613 for instructions on configuring Trend Micro to allow or block specific programs.

Blog Moved

We've moved our blog from http://stonefieldquery.blogspot.com to http://blog.stonefieldquery.com. The old URL automatically redirects to the new one.

Friday, July 4, 2008

Calling Stonefield Query from .Net Applications

As described in our online documentation, Stonefield Query includes a lightweight COM object called SQProxy. This allows other applications to run reports, get the names of folders and the reports in them, and so on. Even though SQProxy is a COM object, it can be used in .Net applications such as ASP.Net.

You can easily add a reference to the SQProxy Type Library to a .Net project and use the interop class Visual Studio creates for you in your application. Simply right-click references in the Solution Explorer, choose Add Reference, select the COM page, and choose the SQProxy Type Library.

addref

However, there's a complication: object members of SQProxy, such as SQApplication, aren't included in the type library so they don't have the proper type information that .Net needs. As a result, these members are cast as Object and you therefore cannot access their properties or call their methods.

To get around this, use reflection. This demo class has two helper methods, GetProperty and CallMethod, that get the value of a property or call the method of a Stonefield Query object. Thanks to C# MVP Rick Strahl for providing this technique and code.

using System;
using System.Collections.Generic;
using System.Text;
using sqproxy;
using System.Reflection;
 
namespace SQProxyTest
 
{
  class SQProxyTest
  {
    static void Main(string[] args)
    {
      SQProxyTest loTest = new SQProxyTest();
      loTest.TestSQProxy();
    }
 
    private void TestSQProxy()
    {
 
      //Create the SQProxy object.
      sqproxyClass SQP = 
        new sqproxy.sqproxyClass();
 
      //Load a project.
      SQP.LoadProject("C:\\Test", "admin",
"admin", "Northwind");
 
      if (SQP.ProjectLoaded)
      {
        System.Console.WriteLine("Project Loaded");
 
        //Get references to some objects.
        object loApp = 
          this.GetProperty(SQP, "SQApplication");
        object loRE = 
          this.GetProperty(loApp, "ReportEngine");
 
        //Find out how many folders there are.
        object loFolders = 
          this.CallMethod(loRE, "GetFolders");
        int lnFolders = (int)
this.GetProperty(loFolders, "Count");
        System.Console.WriteLine("There are " +
lnFolders.ToString() + " folders");
 
        //Display the folder names.
        for (int i = 1; i <= lnFolders; i++)
        {
          object loFolder = 
            this.CallMethod(loFolders, "Item",
new object[] { i });
          string lcFolder = (string)
This.GetProperty(loFolder,
"FolderName");
          System.Console.WriteLine
(lcFolder.ToString());
        }
 
        //Run a report.
        bool llOK =
(bool) this.CallMethod(loRE,
"RunReportToFile", new object[]
{"Customers", "C:\\Temp\\Test.pdf"} );
        if (llOK)
            System.Console.WriteLine
("The Customers report was output " +
to PDF."
);
        else
            System.Console.WriteLine
("The Customers report was not " +
              run successfully.");
        System.Console.ReadLine();
        }
 
      }
 
    //This method uses reflection on the specified
//object to call the desired method,
passing
//it any necessary parameters.
    private object CallMethod(object toObject,
string tcMethod, params object[] toParams)
    {
      return toObject.GetType().InvokeMember(
tcMethod, BindingFlags.InvokeMethod,
null, toObject, toParams);
    }
 
    //This method uses reflection on the specified
//object to read the value of a property.
    private object GetProperty(object toObject,
string tcProperty)
    {
      return toObject.GetType().InvokeMember(
tcProperty, BindingFlags.GetProperty,
null, toObject, null);
    }
 
  }
}

Updating the Stonefield Query for Sage Pro ERP Data Dictionary

Stonefield Query knows all about your Sage Pro ERP database because it has a data dictionary. A data dictionary defines the fields and tables in a database, providing features such as descriptive names so you don't have to know the real names, how tables are connected, or joined, together, and calculated values such as extended price that aren't normally stored in the database.

However, if you've added custom fields or tables to the Sage Pro database, you'll want to refresh the Stonefield Query data dictionary so you can query on those new fields or tables. This is easily done using the Stonefield Query Software Development Kit (SDK). You may also want to use the SDK if you want to report on other databases you have linked to your Sage Pro system.

The Customizing the Stonefield Query Data Dictionary topic in the Stonefield Query for Sage Pro ERP help file has complete information on how to refresh the data dictionary. However, one important thing to note is that if you're using a SQL Server database, you have to tell Stonefield Query which database to read the data structure from. You can't use the DSN that Sage Pro automatically creates because that DSN sets the default database to the Sage Pro system database, which doesn't contain the tables you're interested in. Instead, create a DSN that sets the default database to the Sage Pro data database.

Alternatively, you can change the connection type to "ODBC - Connection String" and enter a connection string similar to the following:

driver=SQL Server; server=ServerName; database=DatabaseName

where ServerName is the name of the server and DatabaseName is the name of the Sage Pro database.

You can then click the Refresh button in the toolbar to refresh the entire data dictionary or right-click a table and choose Refresh Table from the shortcut menu to refresh just that table.

Wednesday, July 2, 2008

Stonefield Query Version 3.2 Build 3105

We posted updated versions of the Stonefield Query SDK, Stonefield Query for Sage Accpac ERP, and Stonefield Query for Sage Pro ERP today with the following changes.

All Versions

  • A bug that causes a problem with filtering on the second of two fields with the same name was fixed.
  • A bug that resulted in a bogus "The exclusion filter involves tables that appear in the report" warning when creating an exclusion filter was corrected.
  • Row fields in cross-tabulation report now respect the bold setting for the fields.
  • Field and expression filters now work properly with the "contains" operator.
  • Typos in some Dutch translations were fixed.

SDK

  • Refreshing the data dictionary no longer converts fields you've change to Logical back to the Integer data type they're actually stored as. The same is true for Date fields no longer being converted back to DateTime fields.
  • A bug that prevented Setup.Changed from being called when the Setup dialog is closed was fixed.
  • The Data Groups dropdown in the table properties page now drops down as expected. Previously, under some conditions, it failed to do so.

Sage Accpac ERP

  • Sage Accpac ERP version 5.5 is now supported.
  • Accpac databases are now shown as the company name rather than the data source name in the Open Database dialog and the status bar of the Reports Explorer.
  • The descriptive names of many tables now matches those in the Accpac documentation.
  • A bug causing the wrong company name to appear in the header of reports was corrected.
  • Most modules now have a default table. For example, if you select Accounts Receivable when creating a quick report, the Customers table is selected by default

Sage Pro ERP

  • Sage Pro ERP version 7.5 is now supported.
  • Relationships between a number of tables have been improved. For example, ARMAST is now a preferred over ARCUST as a relation to ARTRAN, ARYTRN, ARTRN, ARCASH, ARYCSH, and ARCSH when all three used.
  • Relationships were added  for SOHSHP-SODSHP, SOHSP-SODSP, SOYSHP-SOYDSP, SODSHP-SOTRAN, SOYDSP-SOYTRN, and SODSP-SOTRN. Relationships for SODSHP-SOMAST, SODSHP-SOMST, SODSP-SOMST, and SOYDSP-SOMAST were removed; these relations should go through SOHSHP instead.
  • Refreshing the data dictionary from the Sage Pro system database in the SDK Configuration Utility no longer adds system tables to the Stonefield Query data dictionary.
  • Selecting an invalid directory for the Sage Pro folder in the Locations page of the Options dialog now displays the correct directory name in the error message that appears.