Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / SSRS Report Tmp table
Finance | Project Operations, Human Resources, ...
Answered

SSRS Report Tmp table

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

I am working on customizing Purchase Req report customizations. As part of requirement i need to add list of approvers to PR report and hence i have to add another Tmp table to report objects.

The main Tmp table( insertIntoTempTable) which is being used by report is updated by an Private method.

I would like to extend DP class (PurchReqDP)'s  insertIntoTempTable() method to add logic but since this method is private i can not use COC. So i ended up COC on insertIntoTempTable's Insert method.  as following code

[ExtensionOf(tableStr(PurchReqTmp))]

final class PurchReqTmpOI_Extension
{
    public void insert()
    {
        str userName;
        OIPurchReqApproversTmp oiPurchReqApproversTmp;
        next insert();
        
        PurchReqTmpOI_Extension::oiPopulateApprovalOwner(PurchReqTable::findPurchReqId(this.PurchReqId));
        
    }

    private static void oiPopulateApprovalOwner(PurchReqTable _purchReqTable)
    {
        WorkflowWorkItemTable   workflowWorkItemTable;
        WorkflowElementTable    workflowElementTable;
        OIPurchReqApproversTmp  oiPurchReqApproversTmp;
        

        while select  ElementId,UserId,Status from workflowWorkItemTable
                    where   workflowWorkItemTable.RefTableId    == _purchReqTable.TableId &&
                            workflowWorkItemTable.RefRecId      == _purchReqTable.RecId &&
                            (workflowWorkItemTable.Status       == WorkflowWorkItemStatus::Pending
                                || workflowWorkItemTable.Status == WorkflowWorkItemStatus::Delegated
                                || workflowWorkItemTable.Status == WorkflowWorkItemStatus::Completed) &&
                            workflowWorkItemTable.IsClaimed     == true
                join ElementId, ElementType from workflowElementTable
                    where   workflowElementTable.ElementId      == workflowWorkItemTable.ElementId &&
                            workflowElementTable.ElementType    == WorkflowElementType::Approval
        {   

            ttsbegin;
            oiPurchReqApproversTmp.PurchReqId                   = _purchReqTable.PurchReqId;
            oiPurchReqApproversTmp.OIPROwnerApprovalStatus      = enum2Str(workflowWorkItemTable.Status);
            oiPurchReqApproversTmp.OIApprovalOwner              = workflowWorkItemTable.userName();
            oiPurchReqApproversTmp.OIApprovalDate               = DateTimeUtil::date(workflowWorkItemTable.DueDateTime);
            oiPurchReqApproversTmp.insert();
            ttscommit;

        }

    }

The Extension of PurchReqDP looks like as follow

[ExtensionOf(classStr(PurchReqDP))]
final public class PurchReqDPOI_Extension
{
   
    private oiPurchReqApproversTmp oiPurchReqApproversTmp;

    

    [SRSReportDataSetAttribute(tablestr(OIPurchReqApproversTmp))]
    public OIPurchReqApproversTmp getPurchReqApproversTmp()
    {
        select * from oiPurchReqApproversTmp;
        return oiPurchReqApproversTmp;
    }

Now issue is, I do see newly added oiPurchReqApproversTmp table get populated but when it comes to getPurchReqApproversTmp() to fetch data to show on report it does not have anything in it. 

Please note DP class uses SRSReportDataProviderBase as base class.

Any help is really appreciated.

Thank you.

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: SSRS Report Tmp table

    And regardless of the nr of records it would still work because you can rum your code immediately after the tmp table is populated. 

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SSRS Report Tmp table

    I realized there is always going to be just One record in PurchReqTmp, so I can  pick up that after loop finishes.

    Thank you for your feedback.

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: SSRS Report Tmp table

    I think you achieve exactly the same outcome by iterating PurchReqTmp after processReport, and running your logic to populate your own table.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SSRS Report Tmp table

    This is how ProcessReport() looks like.. I need to insert records my new Tmp table everytime purchReqTmp get updated.

    public void processReport()
        {
            RecId                   versionRecId;
            utcDateTime             versionDateTime;
            PurchReqContract        contract;
            QueryBuildDataSource    queryBuildDataSource;
            QueryBuildRange         queryBuildRange;
            QueryRun                queryRun;
            Query                   query;

            query                   = this.parmQuery();
            contract                = this.parmDataContract() as PurchReqContract;
            versionRecId            = contract.parmRecordId();
            versionDateTime         = contract.parmVersioningTimeStamp();
            requisitionPurpose      = contract.parmRequisitionPurpose();

            query.validTimeStateAsOfDateTime(versionDateTime);

            if (requisitionPurpose == RequisitionPurpose::Replenishment)
            {
                queryBuildDataSource = query.dataSourceTable(tablenum(PurchReqTableHistoryTotals));
                queryBuildDataSource.enabled(false);
            }

            queryBuildDataSource    = query.dataSourceTable(tablenum(PurchReqTableVersion));
            queryBuildRange         = SysQuery::findOrCreateRange(queryBuildDataSource,fieldnum(PurchReqTableVersion, RecId));
            queryBuildRange.value(Sysquery::value(versionRecId));

            queryRun = new QueryRun(query);

            while (queryRun.next())
            {
                purchReqTableHistory        =  queryRun.get(tablenum(PurchReqTableHistory));
                purchReqLineHistory         =  queryRun.get(tablenum(PurchReqLineHistory));
                if (requisitionPurpose == requisitionPurpose::Consumption)
                {
                    purchReqTableHistoryTotals  =  queryRun.get(tablenum(PurchReqTableHistoryTotals));
                }
                this.insertIntoTempTable();
            }
        }

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: SSRS Report Tmp table

    Remember that the data in a temporary table exists only as long as the table buffer variable is in scope.

    So you must keep the same variable in scope when inserting and when reading the data. In your code that's not the case. You should rather hook in the processReport method in PurchReqDP. This way you can use the same class level variable when writing the temp table, and when getting it for the report.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans