Q&A: Can we use SilverSession in Excel Export Queries?

Question

In our Portal we have Sales Orders from our back-office ERP system. And our own Employees can do an Excel Export of these Sales Orders. We also give access to our Customers to that Portal, but we have the export secured so only our own Employees have this option. We want to give that option to our Customers too. The problem is that Customers should only be allowed to export their own Sales Orders. In other words, they should only be able to export Sales Orders where the DebtorNumber in that Sales Order matches the DebtorNumber of the current logged on Customer.

In the SilverSession we have the DebtorNumber of the current user. But how can we use that in the Optimized Query behind the Excel Export?

Answer

The information from the current logged on user is indeed stored in the SilverSession. And that information is available throughout the Configuration. For example, you can have an input field pre-filled with the DebtorNumber from the current user by assigning SilverSession.DebtorNumber as the Source of that field.

In exact the same way you can use SilverSession in the Optimized Queries. Using the syntax SilverSession.<PropertyName> in your query. So the query for the Customer SalesOrders export might look somthing like this:

Now if you are writing more complex queries using you SQL Management Studio, the query as stated above will obviously return an error because SilverSession.DebtorNumber is nowhere specified. Now you could use fixed values in your query and replace them afterwards. But especially in bigger queries with multiple dynamic values, there is a big chance that you forget one of these fixed values later on. 

So here is the trick I use:

  • Declare the Properties as variables
    Declare the Properties you want to use from the SilverSession as variables on top of your query
  • Assign a test value
    Assign a test value to each of the variables you have declared
  • Use the variables in your query
    Use the variables where you need them in your SQL statement and test the query
  • Do a Search & Replace
    Using Ctrl+H (Search & Replace) replace the "@" with "SilverSession.".

Of course, when copying the SQL statement to your OptimizedQuery, leave out the declare and set statements. These were used for testing purposes only.