SSRS | ErpCoder

How to choose the right BI and reporting strategy for Dynamics 365 Finance

9. June 2021 2 Comments

Dynamics 365 for Finance and Supply Chain Management offers a broad range of reporting and business intelligence options. You can utilize the integrated Power BI dashboards, link the Power BI report gallery within Dynamics, use integrated SSRS reports or develop Power BI reports and dashboards that connect to Dynamics 365. Sometimes it can be hard to decided which one to choose. Here is a guideline which one to choose depending on the reporting requirements.

Dynamics 365 Power BI and Reporting strategy

Decision Tree for Power BI / Reporting in Dynamics 365 Finance & SCM

Report Format:

What is the purpose of the report? Is it an interactive report / dashboard or is it a static list or document like artifact? For example, sales analysis is typical an interactive report while a collection letter is a printed document. Power BI is great for interactive data analysis, SQL Server Reporting Services (SSRS) is the right tool for lists and page oriented printable documents.

Real Time:

Do you need to see transactional data as soon as it is generated in Dynamics? For example posting and invoice and immediately printing the document. If so, you need to access the transactional database (AxDB). There are two ways: Use integrated Reporting Services or query entities via OData. However, using entities allows you to access the AxDB but Power BI doesn’t support Direct Query mode for OData, i.e. you have to hit refresh in order to get the latest data.

Multiple data sources:

Is Dynamics 365 Finance the only data source for your report, or do you need to integrated external data sources as well? An example could be to develop a revenue analysis which includes actual sales data from Dynamics 365 as well as demographics and household income. Integrated Power BI dashboards in Dynamics 365 use direct query to access the AxDB and cannot integrate other data sources. It is also not recommended to load external data into Dynamics 365 AxDB because you have a limited cost free database size in your subscription. Additional SQL storage has to be paid.

Additional licenses:

Dynamics 365 Finance and Supply Chain Management includes the rights to view the integrated Power BI dashboards. No additional Power BI license is required. Reports developed using the integrated SQL Server Reporting Services technology are also covered by the Dynamics license. External Power BI reports, dashboards and paginated reports require additional Power BI licenses. At least Power BI Pro for reports and dashboards, Power BI Premium Capacity or Premium per User for paginated Reports.

Examples:

An interactive custom Power BI Report viewed in Dynamics 365 Finance via the users Report Catalog option

Production Performance is part of Dynamics 365 Finance & SCM and directly connects to the entity store (aka. AxDW)

A paginated report in Power BI (Premium)

A static SSRS paginated report in Dynamics 365 Finance and SCM

Conclusion

Before you start working with a certain product, make sure to understand the requirements. Identify the data sources and how to access them. Then choose the right tool for the job. Don’t try to make a printable Power BI or fancy SSRS. By leveraging the full reporting and BI potential you can deliver a great user experience that adds value to Dynamics 365 Finance and SCM.

Filed under BI, Dynamics 365 Finance and Operations, PowerBI Tagged with Dynamics 365 FO, PowerBI, SSRS

Add carry forward sum to OpenSalesOrders SSRS report

2. April 2014 Leave a comment

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.

Start Visual Studio as Admin and open CustSalesOpenOrders_NA report from the application explorer
Open the report design
Add 2 new columns on the right side and set Hidden property to TRUE
Open the property dialog for the first new column and change the name to RunningTotal
Set the following expression as value
=RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS")
Open the property dialog for the second new column and change the name to RunningTotalPrev
Set the following expression as value
=RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS") – Fields!amountRemainingMST.Value
In the report design create a new text field in the report header above the amountRemainingMST
Set the following expression as value
="Running Total: " +cstr(First(ReportItems!RunningTotalPrev.Value))
In the report design add a new footer and add a text field in the footer under the amountRemainingMST
Set the following expression as value
="Running Total: " +cstr(Last(ReportItems!RunningTotal.Value))
Save and deploy the report
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

Filed under BI, Dynamics AX Tagged with SSRS

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

26. December 2013 Leave a comment

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.

Filed under AIF, Dynamics AX Tagged with Error, SSRS

Send SSRS Report as Email

7. January 2013 5 Comments

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”.

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.

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

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.

Filed under Development, Dynamics AX Tagged with Email, SSRS

SSRS Error: Dynamic packer query not found

5. November 2012 Leave a comment

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

Filed under Admin Tagged with Error, SSRS

SSRS: Error when opening design of duplicated report

5. March 2012 1 Comment

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.