Microsoft Dynamics AX 2012 Reporting Services - Issue deploying OLAP SSRS reports on SharePoint 2013


Technorati Tags: ,,

Problem: When you try to view a Dynamics AX 2012 R2/R3 SSRS report which is deployed on SharePoint 2013, you may receive the following error message:

The report server cannot process the report or shared dataset. The shared data source 'DynamicsAXOLAP' for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source.

SSRSWithOLAP

In this situation, you find that the data source link for this report is empty on SharePoint 2013.

OLAPSourceLink

This is due to deploying all reports in SharePoint integrated mode fails to set the shared data source link correctly for all OLAP reports causing them to fail when executed.

Solution: The following PowerShell script was proposed to publish the OLAP SSRS reports to SharePoint.

----------------------Script Starts-------------------

function ImportAXModule($axModuleName, $disableNameChecking, $isFile)
{
    try
    {
        $outputmessage = "Importing " + $axModuleName
        Write-Output $outputmessage
 
        if($isFile -eq $true)
        {
            $dynamicsSetupRegKey = Get-Item "HKLM:\SOFTWARE\Microsoft\Dynamics\6.0\Setup"
            $sourceDir = $dynamicsSetupRegKey.GetValue("InstallDir")
            $axModuleName = "ManagementUtilities\" + $axModuleName + ".dll"
            $axModuleName = join-path $sourceDir $axModuleName
        }
        if($disableNameChecking -eq $true)
        {
            import-module $axModuleName -DisableNameChecking
        }
        else
        {
            import-module $axModuleName
        }
    }
    catch
    {
        $outputmessage = "Could not load file " + $axModuleName
        Write-Output $outputmessage
    }
}
 
$dynamicsSetupRegKey = Get-Item "HKLM:\SOFTWARE\Microsoft\Dynamics\6.0\Setup"
$sourceDir = $dynamicsSetupRegKey.GetValue("InstallDir")
$dynamicsAXModulesPath = join-path $sourceDir "ManagementUtilities\Modules"
 
$env:PSModulePath = $env:PSModulePath + ";" + $dynamicsAXModulesPath
 
ImportAXModule "AxUtilLib" $false $true
 
#AxUtil uses "Optimize" verb.
#Therefore we use -DisableNameChecking to suppress warning about uncommon verb being used.
ImportAXModule "AxUtilLib.PowerShell" $true $false
 
ImportAXModule "Microsoft.Dynamics.Administration" $false $false
ImportAXModule "Microsoft.Dynamics.AX.Framework.Management" $false $false

cls

Start-Transcript c:\temp\CaptureSSRSDeploymentLog.txt

Publish-AxReport -ReportName VendPurchasePastXPeriods
Publish-AxReport -ReportName CreditAndCollectionOperationalEfficiency
Publish-AxReport -ReportName CustExpectedCashNextXDays
Publish-AxReport -ReportName CustRecivableClassificationPeriodWebpart
Publish-AxReport -ReportName CustSalesPastPeriodChart
Publish-AxReport -ReportName CustTopCustomersbyYTDSales
Publish-AxReport -ReportName TreasurerAssetAnalysis
Publish-AxReport -ReportName TreasurerLiabilitiesAnalysis
Publish-AxReport -ReportName SalesAnalysis
Publish-AxReport -ReportName SalesVsTarget
Publish-AxReport -ReportName smmCampaignMonthDetails
Publish-AxReport -ReportName smmLeadsAnalysis
Publish-AxReport -ReportName smmNewLeadsTrend
Publish-AxReport -ReportName smmPipelineAnalysis
Publish-AxReport -ReportName smmPipelineByProcessStage
Publish-AxReport -ReportName smmPipelineMonthOverMonthClosingTrend
Publish-AxReport -ReportName smmPipelineWinAnalysis
Publish-AxReport -ReportName smmSalesDetails
Publish-AxReport -ReportName TS_PercentVendorsInvoicedAmountDetail
Publish-AxReport -ReportName TS_PercentVendorsInvoicedAmountWebpart
Publish-AxReport -ReportName AccountantInvestmentAnalysis
Publish-AxReport -ReportName AccountantLongTermSolvency
Publish-AxReport -ReportName AccountantOperationalEfficiency
Publish-AxReport -ReportName AccountantProfitabilityAnalysis
Publish-AxReport -ReportName AccountantShortTermSolvency
Publish-AxReport -ReportName AccountingManagerInvestmentAnalysis
Publish-AxReport -ReportName AccountingManagerLongTermSolvency
Publish-AxReport -ReportName AccountingManagerOperationalEfficiency
Publish-AxReport -ReportName AccountingManagerProfitabilityAnalysis
Publish-AxReport -ReportName AccountingManagerShortTermSolvency
Publish-AxReport -ReportName CFOInvestmentAnalysis
Publish-AxReport -ReportName CFOLongTermSolvency
Publish-AxReport -ReportName CFOOperationalEfficiency
Publish-AxReport -ReportName CFOProfitabilityAnalysis
Publish-AxReport -ReportName CFOShortTermSolvency
Publish-AxReport -ReportName ControllerInvestmentAnalysis
Publish-AxReport -ReportName ControllerLongTermSolvency
Publish-AxReport -ReportName ControllerOperationalEfficiency
Publish-AxReport -ReportName ControllerProfitabilityAnalysis
Publish-AxReport -ReportName ControllerShortTermSolvency
Publish-AxReport -ReportName LedgerCashInflowvsCashOutflow
Publish-AxReport -ReportName LedgerControllerActivityAnalysis
Publish-AxReport -ReportName LedgerControllerRatios
Publish-AxReport -ReportName LedgerGrossProfitinthePastXPeriods
Publish-AxReport -ReportName LedgerTotalAcctReceivablesPastXPeriods
Publish-AxReport -ReportName LedgerTotalAcctsPayablePastXPeriod
Publish-AxReport -ReportName LedgerTotalExpensesByPeriod
Publish-AxReport -ReportName OutstandingDebtByPeriod
Publish-AxReport -ReportName ProdBusinessOverviewWebpart
Publish-AxReport -ReportName ProdDefectRatioDetail
Publish-AxReport -ReportName ProdDefectRatioWebpart
Publish-AxReport -ReportName ProdEfficiencyDetail
Publish-AxReport -ReportName ProdLeadTimeDetail
Publish-AxReport -ReportName ProdOnTimeProdRateDetail
Publish-AxReport -ReportName ProdPlannedVsActualDetail
Publish-AxReport -ReportName ProdPlannedVsActualWebpart
Publish-AxReport -ReportName EMSEnergyConsumption
Publish-AxReport -ReportName EMSEnergyConsumptionDetail
Publish-AxReport -ReportName EMSEnergyCost
Publish-AxReport -ReportName EMSEnergyCostGraph
Publish-AxReport -ReportName EMSGreenhouseGasEmissions
Publish-AxReport -ReportName EMSGreenhouseGasEmissionsByProcess
Publish-AxReport -ReportName TS_InvoicedPurchaseAmountDetail
Publish-AxReport -ReportName TS_InvoicedPurchaseAmountWebpart
Publish-AxReport -ReportName TS_PurchaseReturnsDetail
Publish-AxReport -ReportName TS_PurchaseReturnsWebPart
Publish-AxReport -ReportName ProjKPI
Publish-AxReport -ReportName ProjTotalBudgetCost
Publish-AxReport -ReportName ProjTotalBudgetCostDetail
Publish-AxReport -ReportName ProjTotalBudgetCostProject
Publish-AxReport -ReportName ProjTotalBudgetCostProjectDetail
Publish-AxReport -ReportName ProjUtilizationEfficiency
Publish-AxReport -ReportName ProjUtilizationEfficiencyDetail
Publish-AxReport -ReportName CustReceivablePeriodDetailReport
Publish-AxReport -ReportName YearToYearExpensesByPeriod
Publish-AxReport -ReportName RetailBottom10Products
Publish-AxReport -ReportName RetailGMROIIByCategory
Publish-AxReport -ReportName RetailGMROIIByProducts
Publish-AxReport -ReportName RetailSalesByCategoryByStore
Publish-AxReport -ReportName RetailSalesByHour
Publish-AxReport -ReportName RetailSalesByRetailCategory
Publish-AxReport -ReportName RetailSalesByRetailCategoryEP
Publish-AxReport -ReportName RetailSalesByStaff
Publish-AxReport -ReportName RetailSalesByStore
Publish-AxReport -ReportName RetailSalesByStoreRoleCenter
Publish-AxReport -ReportName RetailSalesByTerminal
Publish-AxReport -ReportName RetailSalesPerformanceByProduct
Publish-AxReport -ReportName RetailSalesPerformanceByProductEP
Publish-AxReport -ReportName RetailSalesRetailCategoryRoleCenter
Publish-AxReport -ReportName RetailSalesYearOverYear
Publish-AxReport -ReportName RetailStoreMetrics
Publish-AxReport -ReportName RetailTop10Products
Publish-AxReport -ReportName RetailTop10Stores
Publish-AxReport -ReportName RetailTrendByChannel
Publish-AxReport -ReportName RetailYoYByChannel
Publish-AxReport -ReportName TS_NetsalesVsCOGSDetail
Publish-AxReport -ReportName TS_NetsalesVsCOGSWebpart
Publish-AxReport -ReportName TS_SIFOTDetailed
Publish-AxReport -ReportName TS_SIFOTDetailedPerProduct
Publish-AxReport -ReportName TS_DeliveryPerformanceDetail
Publish-AxReport -ReportName TS_DeliveryPerformanceWebpart
Publish-AxReport -ReportName TS_TopTenReturnedItemsWebPart
Publish-AxReport -ReportName TS_TopTenVendHighReturnsRateWebpart
Publish-AxReport -ReportName TS_TopTenVendorInvoicedAmount
Publish-AxReport -ReportName TS_VendorPerformanceByProduct
Publish-AxReport -ReportName TS_VendorPerformanceDetail
Publish-AxReport -ReportName TS_VendorPerformanceTimeDetail
Publish-AxReport -ReportName TS_VendorPerformanceTimeWebpart
Publish-AxReport -ReportName VendSpendCategory
Publish-AxReport -ReportName VendSpendCategoryDiversity
Publish-AxReport -ReportName VendSpendCategoryInvoice
Publish-AxReport -ReportName VendSpendCategoryYears
Publish-AxReport -ReportName VendSpendDiversity
Publish-AxReport -ReportName VendSpendLocation
Publish-AxReport -ReportName VendSpendOptimization
Publish-AxReport -ReportName VendSpendReviewByCategory
Publish-AxReport -ReportName VendSpendReviewByCompany
Publish-AxReport -ReportName VendSpendReviewByInvoiceType
Publish-AxReport -ReportName VendSpendReviewByTopVendor
Publish-AxReport -ReportName VendSpendReviewByVendorType
Publish-AxReport -ReportName VendSpendTopInvoice
Publish-AxReport -ReportName VendSpendVendorLE
Publish-AxReport -ReportName VendTopXVendorsByYTDPurchases
Publish-AxReport -ReportName WorkflowAutomationReport
Publish-AxReport -ReportName WorkflowElementsAutomationReport
Publish-AxReport -ReportName WorkflowElementsPerformanceReport
Publish-AxReport -ReportName WorkflowParticipantPerformanceReport
Publish-AxReport -ReportName WorkflowPerformanceReport

Stop-Transcript

----------------------Script Ends----------------

Disclaimer: The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.

-- Author: Fred Shen

-- Date:     25/Jan/2015

Fred is a Technical Architect from Microsoft Dynamics Global Practice team

Comments (1)

  1. Cindy Cindy says:

    Thanks for the post!

Skip to main content