SSRS: Accessing Report Builder 3

This blog post covers accessing and installing the "Report Builder" tool that comes with Microsoft SQL Server Reporting Services (SSRS) 2008R2.


All access to Report Builder is obtained via Internet Explorer. In order to get access to the system you will require specific permission – without it the instructions below will not work (the “Report Builder” button will not be visible).

To start Open “Internet Explorer” and go to the following URL:

https:///Reports/

Where is the name of the SQL Server on which Reporting Services has been installed.

After a few seconds pause Internet Explorer will display something similar to:

The available options on your screen will depend on the permissions the administrator of the system has defined for you.

This is the "main" page for SQL Server Reporting Services. This version is slightly updated for the latest 2008R2 version of SQL Server.

Looking at the "buttons" on the menu bar click on the one labelled "Report Builder":

This will bring up a dialog:

If this is the first time you've clicked "Report Builder" then the software will download and install on your machine. The installed software is user-specific so no-one else using your computer will have access to it and it does not require elevated permissions to install (i.e. it will not trigger UAC on Vista or Windows 7).

It's about 70MB so the download could take a few minutes. Once it's completed you will see a splash screen as Report Builder starts up:

This splash screen is displayed while the application starts, as soon as the application is setup you will see a selection screen:

Select the type of report you want to proceed (i.e. Table or Matrix Wizard).

Read More

SSRS: Adding Parameters in Query Designer (When Using a DataModel as a Data Source)

This blog post gives an example of how to add a parameter to a report using Microsoft SQL Server 2008R2 and the Report Builder 3 tool.


After running Report Builder and opening your report you will see something like this;

Expand the "Datasets" node in the treeview on the left;

If you right-click the active Dataset (DataSet1 - highlighted above) and then select "Dataset Properties";

This screen is showing you a lot of technical details about the Query you have put together. Click "Query Designer ..." to get to something more meaningful. To use the "Dataset Properties" screen you will need  training.

The "Query Designer" screen appears and, if you used the wizard to generate your reports,  you'll remember it from them:

Click on the "Filter" icon in the bar at the top. This will display the "Filter Data" screen:

Scroll down the Field list (bottom left) until you find the field you're interested in adding as a parameter. For the sake of this example I'm going to add the "INVOICE DATE" field. When you find the field you're after you can either double-click it or drag/drop it into the "xxxx INVOICESs with:" area):

If you hover over the "equals" (next to INVOICE DATE) it will turn into a click-able link. Click it:

Select "On or After" in the list and then hover over the "INVOICE DATE" (which will also turn into a click-able link) and right-click it:

Select "Prompt" (this means the user will get a prompt).

Click "OK" to save the changes and close the dialog.

We are now back to the "Query Designer" main page and it looks like nothing has changed. Click "Run" button:

A dialog will now appear asking for a value for the "INVOICE DATE" parameter.

Click "OK" and the report will run and give you values for the specified date.

Click "OK" to save the changes to the Query and then click "OK" to save changes to the Dataset and return to the main editor.

Click "Run" at the top left:

This will switch to the report run viewer and, as there is a report, will prompt you at the top of the screen for a value:

Enter "20-APR-2010" and either hit return or click "View Report". The report will now run with the specified parameter.

Read More

SSRS: Formatting a Parameter as Date/Time

This Knol covers how to convert an existing text-based parameter in Microsoft SQL Server Reporting Services to a Date/Time providing the users with a Date/Time picker to select a value rather than forcing them to enter text (i.e. 10-JAN-2010) without making a typo.


Assuming you have your report already open in Report Designer and you've just run it. The report we're going to deal with has a single parameter "INVOICE_DATE". Click "Design" at the top left:

Expand the Parameters item in the "Report Data" treeview (on the left):

Highlight "INVOICE_DATE", right-click and then select "Parameter Properties". The "Report Parameter Properties" dialog will now appear:Highlight "INVOICE_DATE", right-click and then select "Parameter Properties". The "Report Parameter Properties" dialog will now appear:

On the General tab change the Prompt to "Invoice Date: " and the Data type to "Date":On the General tab change the Prompt to "Invoice Date: " and the Data type to "Date/Time":

Click "OK".

Click "Run" the top left:

You can not see that instead of having a simple text-edit you've got a little button to click which will bring up the date picker.

Read More

SSRS: Specifying a Default Value for a Date/Time Parameter

This blog post covers how to specify a default parameter value for a Date/Time Parameter in a report written using Microsoft SQL Server Reporting Services (SSRS). You can easily modify this to specify default values for other types of parameter.


Assuming you have your report already open in Report Designer and you've just run it. The report we're going to deal with has a single parameter "INVOICE_DATE". Click "Design" at the top left:

Expand the Parameters item in the "Report Data" treeview (on the left):

Highlight "INVOICE_DATE", right-click and then select "Parameter Properties". The "Report Parameter Properties" dialog will now appear:Highlight "INVOICE_DATE", right-click and then select "Parameter Properties". The "Report Parameter Properties" dialog will now appear:

We're interested in changing the "Default Value" so select that tab:

Select the "Specify values" radio group:

Click "Add":

Then click the function button (highlighted) on the right of the new line:

Enter the expression:

=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))

(You're probably wondering where I got this from - type "reporting services first day of the month" into Google - it's the first result).

This expression will give you the first day of the PREVIOUS month. Click "OK". Click "OK". Click "OK" (you should be back to the main designer now with no additional dialogs open).

Click "Run", your report Parameter will now have a default value (and if it's the only parameter your report will now run automatically).

Read More

Noetix: Identifying Un-used Columns For Removal

This blog post covers a description of an application (source code provided) that identifies columns which are not required (i.e. all values of the column in the view are the same) and generates a script that somone who has completed the NoetixViews Customization Certification course can include in the Generation Scripts.


The first thing to note is how this works; it opens every view doing a MIN/MAX on every column. It has been tested on a 16GB database and works fine - of course the main requirement of an application like this is to have enough TEMP tablespace in order to work. This is the reason the columns are dealt with individually rather than just opening each view and doing a MIN/MAX on every column in the view in a single hit - this was blowing out the 5GB of TEMP tablespace we had available on the test system.

The application is a simple console application:

All it does is process the views in the database using the SQL:

SELECT DISTINCT UPPER(v.VIEW_NAME) VIEWNAME
  FROM n_views v
 WHERE EXISTS
 (SELECT 'x'
          FROM all_views av
         WHERE av.OWNER = USER
           AND av.VIEW_NAME = UPPER(v.VIEW_NAME))
   AND NVL(v.OMIT_FLAG, 'N') = 'N'
   AND NVL(v.Special_Process_Code, 'X') NOT IN ('LOV', 'BASEVIEW')
   AND (UPPER(v.VIEW_NAME) = '%VIEWNAME%' OR '%VIEWNAME%' = 'ALL')
ORDER BY UPPER(v.VIEW_NAME)

This gives a list of all the views in the database. The %VIEWNAME% is replaced with the first argument on the command line (optional). If nothing is specified then it is replaced with ALL.

Views which are omitted by default are automatically excluded as are all views which are of type LOV (list of values) or BASEVIEW.

Once the list of views has been obtained then the list of columns in each view is retrieved using the SQL:

 SELECT COLUMN_NAME, DATA_TYPE, COLUMN_ID
  FROM ALL_TAB_COLUMNS ATC
 WHERE ATC.OWNER = USER
   AND ATC.TABLE_NAME = '%VIEWNAME%'
   AND ATC.COLUMN_NAME NOT LIKE 'A$%'
   AND ATC.COLUMN_NAME NOT LIKE 'Z$%'
   AND ATC.DATA_TYPE NOT IN ('ROWID', 'LONG')
   AND INSTR(ATC.COLUMN_NAME, '$') = 0

The list of  columns excludes the A$ and Z$ columns that are managed by Noetix and columns which contain a "$" symbol (flexfields) as if you want to remove these you can disable them in the e-Business Suite itself.

Once the appliction has the list of columns it looks through them building the SQL:

SELECT 1 AS IGNORE
   ,TO_CHAR(MIN()) AS MIN
   ,TO_CHAR(MAX()) AS MAX
FROM

The MIN and MAX values are then compared, if they are the same when an SQL file is created for the suppression of the field (for future reference the MIN and MAX values returned are stored in the file).

As you can imagine when accessing the GL views this query can take quite a while - for our 16GB database processing all the views in our relatively simple configuration will take about a week (running multiple processes has gotten this down to 2-3 days).

This isn't quick - but it is effective - we are removing around 3,000 columns from the views. That's 3,000 less columns in the help and for our end users to "accidentally" pick the wrong one from!

Source Code: here (Visual Studio 2010, 20kb Zip Archive)
Build Environment: VS2010 + Oracle Client

Read More

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