SSRS: Function ExistsInArray (Custom Report Builder Code)

A simple function for embedding in SSRS Reports that allows you to check and see if an item exists in a array of items (in either a case sensitive or insensitive way).


Here is the function;

Function ExistsInArray(ByVal SearchTerm As String, ByVal MultiValueParam As Parameter, optional ByVal CaseInsensitive as Boolean = False) As Boolean
  For i As Integer = 0 To MultiValueParam.Count - 1
    If CaseInsensitive = True Then
     If CStr(LCase(MultiValueParam.Value(i))) = LCase(SearchTerm) Then Return True
      Else
        If CStr(MultiValueParam.Value(i)) = SearchTerm Then Return True
      End If
  Next
  Return False
End Function

You pass in the SearchTerm as a string and then the multi-value parameter you wish to search through. Optionally you can make the search case insensitive by specifying True for the third parameter. The function loops through the items and when it finds one that matches it returns True, otherwise if it finds nothing it returns False.

Read More

Oracle PL/SQL: Creating A Database Link

This Knol is a quick developers guide to creating a link between two Oracle databases using TNS Information but not editing the TNSNames.ora file on the server.


It is quite often the case that it is better to link two databases together and report from one of them rather than draw together information from multiple data sources - usually because the link can be re-used more easily and as you have a requirement to re-use it then it stands to reason other people might.

The problem is that DBA's don't generally like developers making changes to the TNS file on the server - and usually this TNS file will only include information for the current database - not all the databases in your company.

Fortunately the CREATE DATABASE LINK command lets you either name a specific TNS entry OR specify the details. To create a database link called DBLINK_TEST to another oracle database which *does not* exist in the TNS file you can use;

CREATE DATABASE LINK DBLINK_TEST
       CONNECT TO
       IDENTIFIED BY
       USING '
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = 
      (PROTOCOL = TCP)
      (HOST = )
      (PORT = ))
           )
    (CONNECT_DATA = (sid = )))'

There is something to be said for not using the TNS file (especially in an Oracle EBS environment) as if you keep creating multiple clones of multiple systems the complexities of keeping the file up to date (and yes I know there are ways of automating it) might not be worth the time.

Read More