Skip to main content
Skip table of contents

Reports

Local Mode vs Server Mode

There are 2 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
SQL 
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 3 parameters.

  1. Begin_Date

  2. End_Date

  3. contactid


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

  1. ContactId

  2. contactID

  3. contactid

SQL 2005 parameter

To edit the parameter in 2005:

  • Microsoft Visual Studio 2005

    • Click on Data or Layout tab first.

    • Go to Report > Report Parameters



Provide the contactid with a default value of 0.

  • Microsoft Visual Studio 2005

    • Once you selected the Report Parameters

    • Click on the contactid parameter.

    • In the Default values area

      • Select Non-queried

      • Enter in the number 0.

    • The parameter should be a data type of string.

    • Click OK

SQL 2008 parameter

To edit the parameter in 2008 – current versions:

  • Microsoft Visual Studio 2008 – 2012

    • Right click on the parameter contactid

    • Select Parameter Properties

    • The parameter should be a data type of Text.

  • Provide the contactid with a default value of 0.

    • Next select Default Values

    • Click on Specify values

    • Click Add

    • Enter the number 0.

    • Click OK

Uploading Local Mode Reports

To upload a new or revised report:

  1. Choose Upload from the Reports menu.

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

    1. 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 the Manage Reports section 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. Navigate 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

      • Log into your SQL Server

      • Click Start – All Programs – Microsoft SQL Server 2008 – Configuration Tools – Reporting Services Configuration Manager

      • Select Web Service URL from left menu

      • See Report Server Web Service URL

    • 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 on Reports > Settings > Configuration.

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

  5. Enter your information to setup the connection to your SSRS server.

    1. Name: This is used in ICE only to identify the server configuration. We recommend this be generic such as "ICEReports"

    2. Server URL: URL in Reporting Services Configuration Manager

    3. User Name: Login with access to your Report Server (not an iMIS login)

    4. Password: Password for the login with access to your Report Server (not an iMIS login)

    5. Domain: Network domain

    6. Major Version - see Report Viewer Version Information

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

    1. Choose edit (pencil icon) to update the value of the following attribute: Ice.ReportViewer.HttpHandlerVersion

    2. Ensure this is the same value as entered in step 5j 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

    1. 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/

      1. If you do not know your Report Server URL, please do the following

        • Log into your SQL Server

        • Click Start – All Programs – Microsoft SQL Server 2008 – Configuration Tools – Reporting Services Configuration Manager

        • Select Report Manager URL from left menu

        • See URL


Adding Reports to Sitemap in ICE

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

  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:

    1. The URL determines if this will be a Local or Server mode.

      1. Example: File name is Membership_Pending_Transactions.rdl

      2. The URL should be either

        1. Local mode: ~/ICE/Analysis/Analysis.aspx?rcode=Membership_Pending_Transactions

        2. Server mode: ~/ICE/Analysis/ReportViewer.aspx?rdl=MembershipPendingTransactions

    2. 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:

      1. 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:

      1. 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'.











JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close