web
You’re offline. This is a read only version of the page.
close
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 / Finance | Project Operations, Human Resources, ... / Display Vend Group Nam...
Finance | Project Operations, Human Resources, ...
Suggested Answer

Display Vend Group Name in RDP Report - D365

(0) ShareShare
ReportReport
Posted on by Dynamics365 70

Hi All,

I'm developing a RDP Report on Purhcase order history and I want to display the Vend Group Name in the report and also create a row group on Vend Group Name.

Here is what I'm trying but not working

POHistoryStatusReportLinesTmp.VendGroupName           = PurchTable::find(purchid).VendGroup; 

Can anyone help me on how to show the name in report? 

Regards,

I have the same question (0)
  • Suggested answer
    vinitgoyal2005 Profile Picture
    vinitgoyal2005 6,332 on at

    Hi,

    Right now do you see the VendGroup Id instead of name? You need to get Name from VendGroup Table. VendGroup::find( PurchTable::find(purchid).VendGroup).Name or rather use select query and join the VendGroup to get the Name.

  • Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    Can you elaborate your problem, please? "Not working" can mean anything.

    Also, your code tells us little without context - we don't know where you use it, if you have any value in purchid variable and so on. You can use the debugger to check if your code does what you want.

  • Dynamics365 Profile Picture
    Dynamics365 70 on at

    I'm using multi select lookups and purchid is also coming from PurchID lookup.

    here is my query in dp class

     while select purchTable

                   where purchTable.PurchId == purchIDVar

                   join purchLine

                   where purchLine.PurchId == purchTable.PurchId

                    join levelView

                       where levelView.ItemID   == purchLine.itemId

                       join cfzLevel2

                       where (cfzLevel2.Level2  == levelView.Level2 || cfzLevel2.Level2 == 'All')

                       join cfzLevel3

                       where (cfzLevel3.Level3  == levelView.Level3 || cfzLevel3.Level3 == 'All')

                       join cfzLevel4

                       where (cfzLevel4.Level4  == levelView.Level4 || cfzLevel4.Level4 == 'All')

                       join cfzLevel1

                       where (cfzLevel1.Level1  == levelView.Level5 || cfzLevel1.Level1 == 'All')                  

                       join inventDim

                       where inventDim.inventDimId == purchLine.InventDimId

                       join inventTable

                       where inventTable.ItemId == purchLine.ItemId

                       join inventTrans

                       where inventTrans.ItemId == inventTable.ItemId

                 outer join vendPackingSlipTrans

                       where vendPackingSlipTrans.ItemId == inventTable.ItemId

    I've used the line of code Vinit provided but report is taking too much time to load.

    even though i'm applying check on fromdate and todate in the PurchID lookup

    where can I use the join to get the VendGroupName ?

  • vinitgoyal2005 Profile Picture
    vinitgoyal2005 6,332 on at

    Hi,

    With all those joins it is definitely going to take time. I don't know what is customisation here but do you need all those joins? Are you using data from all the joined tables? Can you use Exists join?

    for VendGroup name you can add one more join as below:

    join VendGroup
    where VendGroup.VendGroup == purchTable.VendGroup

    and get the value from VendGroup.Name

  • Dynamics365 Profile Picture
    Dynamics365 70 on at

    Thanks Vinit for the answer

    Can you tell me what can do to speed up ?

    Yes all these joins are providing some fields but what if I select only those fields and then use group by clause?

    Can this solve the issue?

  • Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    By the way, please always use Insert > Insert Code (in the rich-formatting view) to paste source code. Look at the result:

    while select purchTable
	where purchTable.PurchId == purchIDVar
	join purchLine
		where purchLine.PurchId == purchTable.PurchId
		join levelView
			where levelView.ItemID   == purchLine.itemId
			join cfzLevel2
				where (cfzLevel2.Level2  == levelView.Level2 || cfzLevel2.Level2 == 'All')
			join cfzLevel3
				where (cfzLevel3.Level3  == levelView.Level3 || cfzLevel3.Level3 == 'All')
			join cfzLevel4
				where (cfzLevel4.Level4  == levelView.Level4 || cfzLevel4.Level4 == 'All')
			join cfzLevel1
				where (cfzLevel1.Level1  == levelView.Level5 || cfzLevel1.Level1 == 'All')
		join inventDim
			where inventDim.inventDimId == purchLine.InventDimId
		join inventTable
			where inventTable.ItemId == purchLine.ItemId
			join inventTrans
				where inventTrans.ItemId == inventTable.ItemId
			outer join vendPackingSlipTrans
				where vendPackingSlipTrans.ItemId == inventTable.ItemId

    What's the point of returning all the fields from all cfzLevel* records? And where do you get the group name? Don't you repeat it for every level record for the same purchase order? It would be an obvious waste of resources. Again, the debugger is your friend; it'll tell you what your code actually does.

  • Suggested answer
    vinitgoyal2005 Profile Picture
    vinitgoyal2005 6,332 on at

    Hi,

    Yes definitely you need to use field list to improve the performance but you cant use Group by, The result set will change drastically depending on data. Also, try using preprocessing of report data, Check: axraja.blogspot.com/.../report-time-out-settings.html

  • vinitgoyal2005 Profile Picture
    vinitgoyal2005 6,332 on at

    Hi,

    Also write a separate query as mentioned by Martin, joining is a waste of resources definitely. 

    select Name from VendGroup where VendGroup.VendGroup == PurchTable.VendGroup;

  • Dynamics365 Profile Picture
    Dynamics365 70 on at

    please have a look at my join.

    why its not working when i join it with vend group?

    otherwise its working

    while select PurchId, PurchName, PurchStatus, PurchaseType, CreatedDateTime, InventLocationId, VendGroup from purchTable

                   where purchTable.PurchId == purchIDVar

                   join DeliveryDate, ItemId, PurchPrice, PurchUnit, QtyOrdered from purchLine

                   where purchLine.PurchId == purchTable.PurchId

                   join Name from vendGroup

                   where vendGroup.VendGroup == purchTable.VendGroup

                    join levelView

                       where levelView.ItemID   == purchLine.itemId

                       join level2 from cfzLevel2

                       where (cfzLevel2.Level2  == levelView.Level2 || cfzLevel2.Level2 == 'All')

                       join level3 from cfzLevel3

                       where (cfzLevel3.Level3  == levelView.Level3 || cfzLevel3.Level3 == 'All')

                       join level4 from cfzLevel4

                       where (cfzLevel4.Level4  == levelView.Level4 || cfzLevel4.Level4 == 'All')

                       join cfzLevel1

                       where (cfzLevel1.Level1  == levelView.Level5 || cfzLevel1.Level1 == 'All')

                       join InventColorId, InventSizeId from inventDim

                       where inventDim.inventDimId == purchLine.InventDimId

                       join inventTable

                       where inventTable.ItemId == purchLine.ItemId

                       join Qty from inventTrans

                       where inventTrans.ItemId == inventTable.ItemId

                 outer join Ordered, Qty, DeliveryDate from vendPackingSlipTrans

                       where vendPackingSlipTrans.ItemId == inventTable.ItemId

  • Suggested answer
    vinitgoyal2005 Profile Picture
    vinitgoyal2005 6,332 on at

    Hi,

    As mentioned earlier, do not use VendGroup in this join query instead inside your while select loop use the independent select query: select Name from VendGroup where VendGroup.VendGroup == PurchTable.VendGroup;

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 503 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 434 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 278 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans