Reports

The following article is about Reports in ICE.

Local Mode vs Server Mode

There are two ways of setting up reports within ICE: Local mode or Server mode. By viewing the URL, you can determine the mode in which current reports are set up. In ICE Admin Sitemap setup,

  1. Local mode: SSRS reports are Uploaded to ICE.
    1. URL looks like: ~/ICE/Analysis/Analysis.aspx?rcode=.
    2. Report processing occurs on client's PC (eases the load off your sql server since all the processing is done on the local machine; however, performance will vary by machine).
    3. SSRS reports are saved on a server that is accessible by ICE. All SSRS reports have to be uploaded to ICE.
    4. Reports are rendered with Telerik control (with limited SSRS functionality).
    5. The following functionality is not available with local mode:
      1. Multi-value parameters (checkbox lists).
      2. Default date parameters (must select via calendar control).
      3. Linked / sub reports.
    6. Layout of parameters will be different from Server mode.
  2. Server mode: A link will be created to the SSRS report on your Reporting Server. (Available in version 4.5 and higher)
    1. URL looks like: ~/ICE/Analysis/ReportViewer.aspx?rdl=
    2. Report processing occurs on your reporting server (increased speed compared to local mode)
    3. SSRS reports are uploaded to the reporting server.
    4. Allows you to run the reports in SSRS or ICE.
    5. Allows you to leverage all SSRS options including the following:
      1. Ability to use report caching or snapshots to speed up report performance.
      2. Subscriptions can be set up to distribute reports on a schedule.
      3. Reports can also be managed via Report Manager.
      4. Much easier/faster to deploy reports directly from BIDS to the Report Server and to locate and manage reports since all reports can be in one location on your server.
      5. (If client runs SQL Enterprise) Data-driven Subscriptions (i.e., mail merge with SSRS)
    6. Layout of parameters will be different from Local mode.
    7. Reports are rendered with Microsoft Report Viewer.

Parameters

Both modes (Local and Server) must have a @contactid parameter in order to display chapter-specific data. If this report is not chapter-specific, the parameter is not required. If this report is intended to display chapter-specific data based on a user's login, a parameter named @contactid must be added to the report. ICE will pass the chapter's iMIS ID to filter the report. You must have Microsoft Visual Studio to create, edit, and/or upload your SSRS reports. This example is from Microsoft Visual Studio 2005 and Microsoft Visual Studio 2008. Please remember SSRS variables are case-sensitive and should have no spaces or special characters other than an underscore. SSRS will automatically create the parameter for you when you enter it into the dataset.

Example Query
Copy 
SELECT a.PRODUCT_CODE
, CASE WHEN a.PAY_METHOD = 'WEB%' THEN 'WEB'
WHEN a.PAY_METHOD = 'CC' THEN 'CC'
ELSE 'NON-WEB' END AS WEB 
, n.ID
, CASE WHEN n.FULL_NAME <> '' THEN n.FULL_NAME
ELSE n.COMPANY END AS Full_Name_Company
, na.FULL_ADDRESS
, na.CITY
, na.ZIP
, n.EMAIL
, a.EFFECTIVE_DATE
, a.AMOUNT
, a.NOTE
, a.DESCRIPTION
FROM (Activity a
INNER JOIN Name n ON a.ID=n.ID)
INNER JOIN Name_Address na ON n.MAIL_ADDRESS_NUM=na.ADDRESS_NUM
INNER JOIN Name nc ON nc.ID = n.CO_ID
WHERE 
a.PRODUCT_CODE = 'BUYSTUFF'
AND (a.ACTIVITY_TYPE='GIFT' OR a.ACTIVITY_TYPE='SALES') 
AND a.Transaction_Date between @Begin_Date and @End_Date 
AND nc.ID in (@contactid) 
ORDER BY a.DESCRIPTION, a.PRODUCT_CODE

In this case SSRS automatically created three parameters.

  1. Begin_Date
  2. End_Date
  3. contactid

The following three parameters in SSRS are 3 different parameters, even if they are spelled the same. SSRS treats them as three different parameters because they are all have different upper and lower cases.

  1. ContactId
  2. contactID
  3. contactid

SQL 2005 parameter

Do the following to edit the parameter in Microsoft Visual Studio 2005:

  1. Click on Data or Layout tab first.
  2. Go to Report > Report Parameters.

  3. Provide the contactid with a default value of 0.
  4. Click on the contactid parameter once you selected the Report Parameters, .
  5. In the Default values area, select Non-queried and enter in the number 0.
  6. The parameter should be a data type of string.
  7. Click OK.

SQL 2008 parameter

Do the following to edit the parameter in Microsoft Visual Studio 2008 – current versions:

  1. Right click on the parameter contactid.
  2. Select Parameter Properties.

  3. The parameter should be a data type of Text.

  4. Provide the contactid with a default value of 0.
  5. Select Default Values.
  6. Click Specify values.
  7. Click Add.
  8. Enter the number 0.
  9. Click OK.

Uploading Local Mode Reports

Do the following to upload a new or revised report:

  1. Click Upload from the Reports menu.
  2. Click Select, choose the main report you want to upload from the file browser

    3. Note: The first file uploaded here must be the main report. The file extension for the main report is always *.RDL. Any sub-reports will need to be uploaded after this report with the extension of *.RDLC. Text entered in the Title field will populate in the header area of the report you are uploading.

  3. Click Open.
  4. The description is used for internal documentation and will populate in the Description column of Manage Reports on the ICE Admin page. 
  5. Once the report has been uploaded, you will need to add it to the Sitemap to make it available in a menu. 
  6. Each time a local mode report is edited, it must be re-uploaded into ICE.

Configuration for Server Mode Reports

Report Server Connection to ICE

If you have the ability to link ICE directly to your SSRS reporting environment, it is encouraged to improve performance. This section is for creating the connection from ICE to the SSRS report server. For Server Mode to function, ICE must be able to connect to the SSRS Report Server URL.

  1. On the web server hosting ICE, open a web browser.
  2. Go to the Report Server URL. (e.g. http://servername/reportserver)

      Note: The Report Server URL is different than the Report Manager URL (e.g. http://servername/reports)

    • If you do not know your Report Server URL, please do the following:
      1. Log into your SQL Server.
      2. Click Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools > Reporting Services Configuration Manager.
      3. Select Web Service URL from left menu.

      • Note: If the Report Server URL is not accessible, please contact a network administrator. There are likely firewall or permission settings which need to be addressed.

  3. Click Reports > Settings > Configuration.

  4. Click Add New Server Information to create a connection to your SSRS server.

  5. Enter your information to setup the connection to your SSRS server.
    • Name: This is used in ICE only to identify the server configuration. We recommend this be generic such as "ICEReports".
    • Server URL: URL in Reporting Services Configuration Manager.
    • User Name: Login with access to your Report Server. (not an iMIS login)
    • Password: Password for the login with access to your Report Server. (not an iMIS login)
    • Domain: Network domain.
    • Major Version: see the Report Viewer Version Information.

  6. In ICE Admin, click Settings > System Configuration. 

    1. Click the edit icon to update the value of the following attribute: Ice.ReportViewer.HttpHandlerVersion.
    2. Ensure this is the same value as entered in step five above.
    3. Scroll to bottom of page under Application Pool and click Recycle.

Report Manager Settings

Note: These steps must be done in Microsoft Visual Studio. All reports will have to be uploaded to your SSRS – Report Manager website before you can link the report from ICE.

  1. Open the project within SSRS. The project will have either a .rptproj or. sln file extension. Either of these files can be opened with Microsoft Visual Studio.
  2. Right click on the main solution name – in this case the project is called 'SSRS Reports'.
  3. Click Properties.

    4. For this project, the reports will be uploaded to a folder named CSI Reports.

  4. When you create a report or update a report, you need to deploy the report for updates to appear in ICE.
    1. Locate your report in Microsoft Visual Studio.
    2. Right click on the report.
    3. Click Deploy.

    4. This is the result you should see in your Output window. If you do not see this, please troubleshoot your report until it is deployed successfully.

    5. Once your report has been deployed, go to your SSRS report manager site to find the report – http://servername/Reports/.
    6. If you do not know your Report Server URL, please do the following:
      1. Log into your SQL Server.
      2. Click Start – All Programs – Microsoft SQL Server 2008 – Configuration Tools – Reporting Services Configuration Manager.
      3. Select Report Manager URL from left menu.
      4. See URL.

Adding reports to sitemaps in ICE

Both Local and Server mode reports must be added to the sitemap in ICE to be visible to users.

Do the following to add reports to sitemaps:

  1. Go to Sitemaps > Actions > Edit.
  2. Select a sitemap to Edit.
  3. Select the menu option or add a new node for the desired location of the report.
  4. Edit the properties on the right side of the Editing Tool window:
  5. The URL determines if this will be a Local or Server mode.

    6. Example: File name is Membership_Pending_Transactions.rdl

    The URL should be either.

    • Local mode: ~/ICE/Analysis/Analysis.aspx?rcode=Membership_Pending_Transactions.
    • Server mode: ~/ICE/Analysis/ReportViewer.aspx?rdl=MembershipPendingTransactions.

    The URL should ALWAYS be unique for both Local and Server mode. With Server mode, the URL does not have to match the SSRS file name but in Local mode it has to match the file name without the extension.

URL for Server Mode reports

  1. Copy the link of the SSRS report in Report Manager.
    1. If this was the link to your report: http://sql2012/Reports/Pages/Report.aspx?ItemPath=%2fICE+Reports%2fStuff_Report_Log_ED.
    2. Copy the part of the URL after "=" and paste that portion into the parameter tab of the ICE sitemap: %2fICE+Reports%2fStuff_Report_Log_ED.
  2. If you are using SQL 2016 or newer version, the URL in Report Manager will look different.
    1. If this was the link to your report: http://sql2016/Reports/report/ICEReports/UnclaimedNewMembers.
    2. Copy the part of the URL after "report" and paste that portion into the parameter tab of the ICE sitemap: /ICEReports/CollegiateOfficerRoster.

  3. Return to ICE Admin > Edit Sitemap > Edit menu item.
    1. Click on the Parameters tab.
    2. Select the Report Server in the dropdown box you created in the section on Server mode Configuration.
    3. Paste the URL of the SSRS report that you copied into the Report Path.
    4. Click Save.