Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Report Writer and Virt...
Microsoft Dynamics GP (Archived)

Report Writer and Virtual table and calling run report from Dexterity

(0) ShareShare
ReportReport
Posted on by bwholl 727

I created a virtual table ABC01V with SOP_HDR_WORK and a custom table XYZ01 with the relationship being SOP_HDR_WORK.SOPTYPE = XYZ01.SOPTYPE and SOP_HDR_WORK.SOPNUMBE = XYZ01.SOPNUMBE.  XYZ01 has multiple records per SOPNUMBE.  I created a RW report with the main table as the virtual table ABC01V.  I set some sort options and there are no restrictions on the report definition.

Now I'm trying to run the report from Dexterity and only display the records for the SOPNUMBE that I'm working on.

If I do this, I get all the records in the table:

run report 'SOP PPM SN WORK Report'  destination true, false;

If I try this (data table) I get an INTERNAL R-TREE error:

run report 'SOP PPM SN WORK Report' with restriction (('SOP Number' of table XYZ01 = '(L) SOP Number' of window 'SOP SerialNumber') and ('SOP Type' of table XYZ01 = '(L) SOP Type'  of window 'SOP SerialNumber')) destination true, false;

If I try this (virtual table), I do not get any records:

run report 'SOP PPM SN WORK Report' with restriction (('SOP Number' of table ABC01V = '(L) SOP Number' of window 'SOP SerialNumber') and ('SOP Type' of table ABC01V = '(L) SOP Type'  of window 'SOP SerialNumber')) destination true, false;

Every example I can find seems to use constants for the restrictions and all the reports seem to use temp tables.

What is the best way to filter the data?

*This post is locked for comments

  • Verified answer
    bwholl Profile Picture
    bwholl 727 on at
    RE: Report Writer and Virtual table and calling run report from Dexterity

    I have a work around that's not pretty but works.  I used my virtual table to collect the data and then copied it to a temp table.  I let the report run with not passed in restrictions and it works.  I think the virtual table solution was a cleaner solution but if you can't get it to work....

  • bwholl Profile Picture
    bwholl 727 on at
    RE: Report Writer and Virtual table and calling run report from Dexterity

    I give up on virtual tables.  The report display all records without restrictions and no records when I try to restrict it to one SOPNUMBE.  I've tried just about everything I can think of.  I hardcoded a SOPNUMBE in and it displayed no records even though I can create a range using the virtual table and loop through the records.  So either my key isn't setup correctly on either the virtual table or something isn't right on my custom table.  It's time to find another way around the problem.

  • bwholl Profile Picture
    bwholl 727 on at
    RE: Report Writer and Virtual table and calling run report from Dexterity

    I think I created the virtual table correctly in Dexterity.  I have SOP_HDR_WORK as the Primary table with my table as the secondary.  The relationship is SOPNUMBE and SOPTYPE as the equals join.

    Is there anything I have to do in GP to make the table available?  I did a range clear table and a range table command just in case I needed something to force the creation.

    I tried to do a drop/create from the Maintenance->SQL window but I get these errors:

    The stored procedure GrantAccess() of form SQL Maintenance : 69 : Pass Through SQL returned the following results: DBMS: 15151, Microsoft Dynamics GP: 0.

  • bwholl Profile Picture
    bwholl 727 on at
    RE: Report Writer and Virtual table and calling run report from Dexterity

    I tried this and got a blank report but I think it's a problem with my virtual table.  If I look at it using the Debug/Table Buffers screen all the fields are either blank or 0.  So maybe I did something wrong on the virtual table creation.

  • Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: Report Writer and Virtual table and calling run report from Dexterity

    Have you added the restrictions to the report?

    Restriction 1:

    ABC01V.SOP Number = ABC01V.SOPNumber

    Restriction 2:

    ABC01V.SOP Type = ABC01V.SOP Type

    In addition, do the following:

    local string sopNumber;

    local integer sopType;

    sopNumber = '(L) SOP Number' of window 'SOP SerialNumber';

    sopType = '(L) SOP Type' of window 'SOP SerialNumber';

    run report 'SOP PPM SN WORK Report' with restriction (('SOP Number' of table ABC01V = sopNumber) and ('SOP Type' of table ABC01V =  sopType)) destination true, false;

    Hope this helps.

  • Suggested answer
    Bernie Holl Profile Picture
    Bernie Holl 5 on at
    RE: Report Writer and Virtual table and calling run report from Dexterity

    I've basically given up on using Virtual and temp tables on this report.  I found a article by Dave Musgrave that even though it didn't address my problem it gave me a work around.  The article was titled "Finding out how to call an existing Report" and this is the link:

    blogs.msdn.com/.../finding-out-how-to-call-an-existing-report.aspx

    What I decided to do is just use my custom table as the Main Table and just pass the data I need from the SOP Headers as legends.

    If anyone knows a better way or what I'm doing wrong with the virtual tables, I'd appreciate knowing.

    Thanks Dave, you help me more than you know.

  • bwholl Profile Picture
    bwholl 727 on at
    RE: Report Writer and Virtual table and calling run report from Dexterity

    I tried that and I still get all the records not just the ones for the order I'm working in.  I've tried a bunch of other ways to get there without success so far.

  • Naga Kiran Profile Picture
    Naga Kiran on at
    RE: Report Writer and Virtual table and calling run report from Dexterity

    Could you try once without any restrictions and destination clause and see you are getting all the records.

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,494 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,305 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans