Exact Macola 10 Tech Tip: Adding Dynamic Menus to a Single Data Point on Workspaces

Topics: Exact Macola 10, Technical Tips

Posted on Dec 1, 2015 9:00:00 AM by David Dozer

This blog post is intended for users who have experience designing custom Workspaces in SSRS and creating Action links for Workspace menus.

The introduction of Workspaces to Exact Macola 10 has allowed customers to tailor their user’s experience based on the role of that person and the unique functions that they serve. Menus are configured to allow users to have a myriad of choices available to them so they may take action on any pertinent data and operate in a contextual and natural way.

When designing a custom workspace there are some scenarios where you may wish to make the menu even “smarter” and display different options based on certain criteria being met even though the data is in the same grid.

For example, if you are joining data from your open orders and your historical posted orders into a single table, you may want the user to be able to drill down into the detail for those orders/invoices. You may also want them to be able to edit open orders but not see the option for posted orders. In a standard menu you would have to create menu links for both open and posted even though only one would be applicable.

Your menu would look something like this:


In this example, I am using a simple query in our SSRS report that unions the oeordhdr_sql and oehdrhst_sql tables together: 

select 'OPEN' as status, ord_type, ord_no, inv_no, cus_no, bill_to_name, tot_sls_amt, ord_dt, inv_dt

from oeordhdr_sql with(NOLOCK)


select 'HISTORY' as status, case when orig_ord_type is NULL then ord_type else orig_ord_type end as ord_type, ord_no, inv_no, cus_no, bill_to_name, tot_sls_amt, ord_dt, inv_dt

from oehdrhst_sql with(NOLOCK)

I’ve made a very simple Tablix using the data from the above query:


On the ord_no field I am using the following for the URL Action to call our workspace menu:

=UFssrsCA.CAFunctions.ContextualMenu("OETest_OrdNoLink",Variables!ReportID.Value,Parameters!Division.Value, Parameters!Division.Value, Fields!ord_type.Value, Fields!ord_no.Value, Fields!inv_no.Value, Fields!status.Value, Fields!ISHistory.Value)

Using this method, I will have to display both the View and the Edit links on the menu. However, I can use an if statement to switch the menu based on certain criteria.

In this example, I will check out “Status” field and display a different menu based on if it is a History or Open order:

=iif(trim(Fields!status.Value)="OPEN",UFssrsCA.CAFunctions.ContextualMenu("OETest_OrdNoLink",Variables!ReportID.Value,Parameters!Division.Value, Parameters!Division.Value, Fields!ord_type.Value, Fields!ord_no.Value, Fields!inv_no.Value, Fields!status.Value, Fields!ISHistory.Value),UFssrsCA.CAFunctions.ContextualMenu("OETest_OrdNoLink_History",Variables!ReportID.Value,Parameters!Division.Value, Parameters!Division.Value, Fields!ord_type.Value, Fields!ord_no.Value, Fields!inv_no.Value, Fields!status.Value, Fields!ISHistory.Value))

Based on the value in the status field, I am able to show a completely different menu within the same field in the table.

Now if I upload my workspace I still see the menu I already configured on open orders:


But if I go to a History order I have a blank menu ready to be configured:


There are many other scenarios where this can be a useful trick. For instance you may want to show a link for releasing orders on credit hold but you wouldn’t want to show that menu item on orders not on credit hold. You could also add nested if statements and have more than 2 menus available on a single data point.

Keep following the Exact Macola blog for more technical tips!


The ERP Blog covers the impact of Enterprise Resource Planning (ERP), business software and accounting solutions on organizations in the manufacturing and distribution industries.

On Demand Webinar

5 Reasons To Use Automated Workflows in Your ERP Solution

Watch the Webinar


Subscribe to Email Updates

Are you evaluating  ERP software?

Recent Posts