Add carry forward sum to OpenSalesOrders SSRS report

A common requirement is to add a running totals sum and carry foward which displays the actual sum on the next page. Following the tutorials like this by André it’s easy to implement also for Dynamics AX.

  1. Start Visual Studio as Admin and open CustSalesOpenOrders_NA report from the application explorer
  2. Open the report design
  3. Add 2 new columns on the right side and set Hidden property to TRUE
  4. Open the property dialog for the first new column and change the name to RunningTotal
  5. Set the following expression as value
    =RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS")
  6. Open the property dialog for the second new column and change the name to RunningTotalPrev
  7. Set the following expression as value
    =RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS") – Fields!amountRemainingMST.Value
  8. In the report design create a new text field in the report header above the amountRemainingMST
  9. Set the following expression as value
    ="Running Total: " +cstr(First(ReportItems!RunningTotalPrev.Value))
  10. In the report design add a new footer and add a text field in the footer under the amountRemainingMST
  11. Set the following expression as value
    ="Running Total: " +cstr(Last(ReportItems!RunningTotal.Value))
  12. Save and deploy the report
  13. Open Dynamics AX go to Sales and Marketing > Reports > Sales Orders and start the Open Sales Orders report

Your report should look like this, including a page sum and a carry forward on the next page

image

BI Service Error: SSASFrameworkService not generated, Instance of ‚SysSecRole‘ could not be created

I recently faced another problem with SSRS reports. The BI Service (Administration > Setup > AIF > Incoming Ports) was down and could not be activated. Trying to re-activate the service resulted in an error telling me that the SSASFrameworkService could not be generated because an instance of service class „SysSecRole“ could not be generated. AOS restart and Full IL Compilation didn’t solve the problem.

However, the solution was to re-deploy the AIF Service Group: AOT > Service Groups > BI Service > Context Menu > Deploy Service Group. Afterwards the BI Service could be activated and reports were working again.

Send SSRS Report as Email

The SSRS Report Viewer in AX 2012 does not have a built in Email function. However, there are some implementations available to add an email button in SharePoint or ASP.NET pages, e.g. Codeproject . The idea is simple, save the report as PDF, open outlook email and add the PDF file as attachment. Follow these steps to implement an Email button for Dynamics AX.

In the AOT add a reference to the Microsoft.Reporting.WinForms DLL. It contains the SRS Report Viewer class. The DLL can be found here: C:\Program Files (x86)\Microsoft Visual Studio 10.0\ReportViewer. Make sure you are using the corresponding assembly version as your SQL Server.

Open the SrsReportViewer Form in the AOT, and add a button to the ButtonGroup. Call it “SendReport”.

image

Add the following Code to the clicked method:

void clicked()
{
    Microsoft.Dynamics.AX.Frameworks.Controls.ReportViewer.AxReportViewer axviewer;
    Microsoft.Reporting.WinForms.ReportViewer nviewer;
    Microsoft.Reporting.WinForms.LocalReport report;
    System.Exception ex;
    System.Array bytear;
    System.Object no;
    System.IO.File file;
    str tmpPath;
    System.IO.FileStream fs;
    SmmOutlookEmail smmOutlookEmail = new SmmOutlookEmail();
    super();

    axviewer = AxReportViewer.control();
    nviewer = axviewer.get_ReportViewerControl();
    try
    {
  
        //render as PDF
        report = nviewer.get_ServerReport();
        bytear = report.Render("PDF");
        no = bytear;

        //path to temp. files
        tmpPath = System.IO.Path::GetTempPath();
        tmpPath = tmpPath + "report.pdf";


        //save to file
        fs = System.IO.File::Create(tmpPath);
        fs.Write(no,0,bytear.get_Length());
        fs.Flush();
        fs.Close();

        //open outlook email
        if (smmOutlookEmail.createMailItem())
        {
            smmOutlookEmail.isHTML(true);
            smmOutlookEmail.addFileAsAttachment(tmpPath);
            smmOutlookEmail.sendEMail(smmSaveCopyofEmail::No);
        }

    }
    catch(Exception::CLRError)
    {
        ex = CLRInterop::getLastException();
        info(ex.ToString());
    }
}

Save, Compile and open a report. Enlarge the “Options” and you’ll see the new Email button.

image

By clicking the Email button, the report is rendered as PDF, stored locally in your temp. files and attached to a new outlook email.

image

This is a very simple implementation. You may add more customizations, e.g. take the report parameters into account to initialize the TO and CC Email fields.

SSRS Error: Dynamic packer query not found

Once again I came across an SSRS error, telling me the dynamic packed query was not found.
\Classes\SRSQueryBuilder\initalize (21)

The environment has 3 AOS, all of them running on one windows server (2712/8101, 2713/8102, 2714/8103) and 3 SSRS instances all running on another windows server. Since default AX voodoo like restart and incremental IL did not resolve the problem, I check and corrected issues following this checklist

In Dynamics AX

  • All AX instances have the BC Proxy account configured as system service account under
    System Administration > Setup > System > System service account
  • Every AX instance has its own SSRS report server configured
    System Administration > Setup > Business Intelligence > Reporting Services > Report Server
  • There is only one default configuration
  • The name (of the windows server) is correct e.g. VM-SRV-SQL
  • The name of the SSRS instance (!) is correct and configured e.g. MSSQLSERER, SSRSLIVE, SSRSTEST,etc.
  • Check the URL (they are in reverse order in the SSRS configuration manager) *argh*
      The report manager url is correct. That’s where you can browse the folder and set permissions
      The report service url is corret. That’s the web service
  • The configuration is assigned to the correct AOS
    Check the online users form if you are not sure which AOS you are actually using

On the SSRS server

  • All SSRS instances are running and all SSRS instances run as BC Proxy account
  • The default configuration (that cannot be changed in the AX configuration utility) is valid
  • The SSRS instances have a valid Microsoft.Dynamics.AX.ReportConfiguration.axc.configuration file in the Report Server\bin directory. If you are not sure, create a new configuration using the configuration utility, test the configuration by starting AX, save the configuration as file and replace the old one in the Report Server\Bin directory
  • Make sure the Business Connector configuration has a default company name e.g. DAT
  • Restart the SSRS instances

SSRS: Error when opening design of duplicated report

When you duplicate an SSRS Dynamics AX 2012 report in Visual Studio, and you try to open the design node you may get an error message like “object instance not set to an object”. The reason is that the data set parameters contain the name of the report e.g. SalesQuotation. Now that you have duplicated the report its name is CopyOfSalesQuotation and the report is broken.

Broken parameter in duplicated SSRS report

Compiling the report project brings up a list of errors.

Errors when compiling a duplicated report

Navigate to each data set parameter and change the name of the report to match the new name e.g. CopyOfSalesQuotation instead fo SalesQuotation

Broken parameter in duplicated report

The fixed parameter should look like this

Dynamics AX 2012 duplicated report data set parameter

Finally the report designer is working

Dynamics AX 2012 duplicated report in report designer