SSRS tutorials and examples

Listed below are specific examples about using reports in iMIS. For these examples, we are using a tool called BIDS (supported for SQL 2008 and 2008 R2). For more information, see Choosing a SQL reporting tool.

  1. If needed, install the Business Intelligence Development Studio (BIDS) feature.

  2. Open your report writer and create a new solution.
    • Open BIDS: go to All Programs > Microsoft SQL Server 2008 R2 > SQL Server Business Intelligence Development Studio.
    • Select File > New > Project.
    • Select Report Server Project.
    • (Project name) Name – SSRS Training Project
    • (Default location) Location – C:\...\Visual Studio 2008\Projects
    • Solution Name – SSRS Training Solution
    • Click OK.
  3. Add Shared Data Source called iMIS.
    • Right-click the Shared Data Sources folder inside the SSRS Training Project within the Solution Explorer and select Add New Data Source.

    • Name the Data Source IMIS so that it will work with the RDL that you will export from iMIS.
    • Click Edit and add the connection information for your local database.
    • Select Test Connection to verify that it works.
    • Note the new connection within the Shared Data Sources folder.

  4. Create a data source and default report template (RDL) using IQA.
    • Select RiSE > Intelligent Query Architect.
    • Browse to $/Samples/ReportingServices.
    • Create a new IQA that grabs the data you need for your report.

      • Sources – CsContact and CsMemberType business objects

      Filters – CsContact.Is a Company

      Display – Defaults & CsMemberType.Description

    • Go to the Report tab and click Save Report Definition to Workstation to export the default RDL file to your workstation.

    • Save the RDL to your project folder.
  5. Add the exported template to your reporting project.
    • Go back to BIDS and add the RDL to the Reports folder within the SSRS Training Project.
    • Right-click Reports and select Add > Existing Item.

    • Find the RDL you exported from IQA (note that name defaults to the name of the query).
    • Double-click on the name of the report to open it for editing. You’ll see the default template that IQA uses for all IQA report definitions which includes a header, a table to display the results set, and a footer.

  6. Change the report template into a pie chart that shows the percentage of members within a given member type.
    • Right-click on the footer and select Remove Page Footer.

    • Select the table, click the top left corner, and press the Delete button on your keyboard.
    • Drag the bottom of the report to give yourself more room to add the chart.
    • From the toolbox, drag and drop the Chart report item onto the body of the report.
    • The Select Chart Type window opens.
    • Select the exploded pie chart and click OK and expand the pie chart to fill in the area as needed.

    • Right-click on the chart itself to show its properties, which open in the Chart Data pane to the right of the chart.
      • In the Value section, select ID.
      • Change the Aggregate type from Sum to Count.

      • In the Category Groups section, select Description (which is the Member Type Description).

      • Change the title of the report by clicking the area that says Chart Title and typing.
      • Add labels that show the counts-per-member type by right-clicking on the chart and selecting Show Data Labels.

      • Click Preview to see the new report (there is a default parameter for the report title – click the View Report button on the top right to preview the report).
    • Change the chart to show percentages instead of counts.
      • Right-click on any of the labels in Design mode.
      • Select Series Label Properties.

      • Add #PERCENT to the Label data field so that the labels show the percent instead of the count.
      • When prompted, This property will not have effect unless UseValueAsLabel is set to False. Do you want to set the UseValueAsLabel to False?, click Yes.
      • Click OK.
  7. Combine all member types that have less than 5% into one pie slice called Other.
    • Go back to the Design view by clicking the Design tab.
    • If you do not see the chart properties, select View > Properties Window.
    • Click a pie slice to select the chart area (you will see the Chart Data pane at right).
    • On the Properties menu to combine all of the less than 5% member types:
    • Find the General sub menu, double-click the CustomAttributes section, and find and set two properties:
      • CollectedStyle = SingleSlice
      CollectedThreshhold = 5

  8. Preview, adjust as needed, and Save.

  9. Add your template to your report in iMIS.
    • On your Member site, open the Document System: select Manage > Document System.
    • Browse to the location of the original template : $/Samples/ReportingServices.
    • Bring your template into iMIS as a new object: select New > Reporting Services Template.

    • Upload your RDL file: Click Select and browse to the file that you turned into a chart template:  ..\Visual Studio 2008\Projects\SSRS Training Solution\SSRS Training Project.
    • Create a report runtime to use your template with your query: select New > Reporting Services Report.
    • On the Sources tab, use the browse buttons to select your template and your query.

    • Click Run to preview the report.
    • When you Save the new report, verify the folder to which you are saving: $/Samples/ReportingServices.
  10. Publish your report using the SSRS Report content item.
    • Edit a new or existing content record.
    • Select add content and select the SSRS Report, in the Utility gallery.

    • Select Hide the toolbar, since pagination isn’t needed.
    • Save & Publish, and view your report.

  1. Create an SSRS report RDL file. You can use the SQL Server Business Intelligence Studio (BIDS) feature to generate a Data Source, one or more Data Sets, and a report.

  2. Open the iMIS Document System and navigate to the folder where you want to save the report template.
  3. Import the SSRS RDL file into iMIS by clicking New > Reporting Services Template.

  4. The New Document window opens. Select the RDL file you want to import and click Upload.
  5. The report displays in the Reporting Services Templates folder.

  6. Open the content record you want to add the report to, or create a new content record if needed.
    • Click Add content to open the Content Gallery, and then open the Utility folder.
    • Select the Report Display content item.
    • Click OK.
  7. When the configuration file opens, locate the Document system path to the SSRS Report template field and click Select. Find the RDL file you added to the Document System.
  8. Review the configured content item and click Save & Publish.
  9. Navigate to the content record where you added the Report Display content item to review your new report.
  1. From the Staff site, go to RiSE > Document System.
  2. Choose a location that contains the query that the report uses, such as the Event queries located in $/Events/DefaultSystem/Reports/Queries. Edit the query, then click the Report tab.

  3. After you run it as a report, click Save Report Definition to Workstation. The report will automatically save in your Downloads folder.
  1. For this example, we changed the fonts, some alignment, and added a logo in the header.
    Before:

    After:
  2. Save your .rdl file with a new name. The .rdl file is basically the template, and will work together with the query to produce the actual report you see in iMIS. Since this is the template, you will need to navigate to the Template folder $/Events/DefaultSystem/Reports/Templates and bring it into iMIS at that location.
  3. Click New, then scroll down until you see Reporting Services Template and select that item. Choose your item, and give it a description.
  4. Upload the file.
  5. Now you need to create the Report file that will tie the template to the query. This can be done by going to the Reports folder first $/Events/DefaultSystem/Reports and go to New and choose Reporting Services Report. Give your report a name and description.
  6. Click the Sources tab. This is where you will connect the original query and to your template. Remember that the query was located in $/Events/DefaultSystem/Reports/Queries and the Template was located in $/Events/DefaultSystem/Reports/Templates.
  7. Now click Save As and save your report to $/Events/DefaultSystem/Reports. After closing this window, you should see your report in $/Events/DefaultSystem/Reports, which means you will also see it when you go to Finance > Reports > Events.
  8. Now, when I go to Finance > Reports > Events I see my new query and I am able to run it.

Note: Custom reports are not supported by ASI Technical Support. If you need assistance with creation or modification of a custom report, you need to consult with your AiSP. You may also request that a report be added to the product by adding it to the Ideas page.