Screen 6: Reports

Overview

In OMNINxG all reports are developed with SQL Server Reporting Services (SSRS). In order to develop SSRS reports the steps involved are:

• 1. Develop & Test Report in SSDT on local desktop

  2. Publish Report to DEV Server

  3. QA Report on DEV Server

  4. Commit Report to SVN

  5. Release Report to Staging Server (As part of deployment process)

Detailed Development Process Flow

• • Reports are developed on a local desktop using SQL Server Data Tools (SSDT)

    • SSDT for Visual Studio 2012 or SSDT for Visual Studio 2010 should be installed

    • SSDT for Visual Studio 2012 is available at: http://msdn.microsoft.com/en-us/jj650015

  • For each module i.e. CRM, Projects, Finance, HR etc. there is a separate SSDT Solution.

    • For instance for CRM the solution name is: CRMReports

    • These solutions are all in SVN and can be CHECKED out from SVN using the URL:

      • https://svn17.devzing.com/aspirtek/omninxganalytics

      • Use TortoiseSVN to CHECK out all projects under this URL.

    • Once Reports are developed in an EXISTING solution or a new SOLUTION developed they must be CHECKED IN to the URL above. - this way all developers will obtain a version of all ANALYTICS reports and can work on them separately.

    • Every Report created must use OMNINXGReportTemplate.rdl as a base (see attached file). In order to create a report follow these steps:

      • Download the file OMNINXGReportTemplate.rdl from this page

      • Rename the file according to the name of the report you want - but keep the extension. For instance if the report you want to make is TrialBalance rename the file TrialBalance.rdl

      • Using SSDT in the Solution Explorer under the Reports folder, choose Add Item and Add your new report TrialBalance.rdl to your solution.

        • Whenever UserID is required in the queries for the report:

          • Add a Hidden Parameter called UserID set a default value as 1

          • Refer to the Parameter as @UserID in any of the DataSet queries.

        • Once the Report is ready follow these steps to show the Executed By name at the bottom of the report:

          • Add a Parameter called @UserID - set it as a Hidden Parameter and a default value of 1

          • Add a DataSet called GetUserDisplayName, set the query to: select DisplayName from dnn_Users where UserID = @UserID

          • In the Field at the Executed By field change the value to: First(Fields!DisplayName.Value,"GetUserDisplayName")

      • Now follow the normal SSRS development process.

  • Once the Report is developed it is published to the DEV report Server

    • Dev Report Server URL: https://omninxg-dev/reportserver (only available inside ASPIRTEK Network)

  • In OMNINxG under Analytics there are submenus for each module i.e. CRM, Projects, HR etc.

    • For the module specific analytics page in OMNINxG the Module Masters SQL Server Reporting Services Module is installed in that page.

    • This Module is configured to display all reports in the SSRS Solution for that module.

      • For instance the Page Analytics -> CRM contains the SSRS Module that is connected to the solution CRMReports

      • When a new report is added to that solution and published it is automatically displayed in the page Analytics->CRM

  • After the Report is finalized its style etc. maybe changed.

Video (Please note that this video DOES NOT USE the OMNINXGReportTemplate.rdl file as a base - in order to make any report you MUST use a copy of this file to start your report)

Report Template

http://www.databasejournal.com/features/mssql/creating-a-custom-report-template-in-ssrs.html