1 Reply Latest reply on Sep 19, 2017 3:04 PM by AlasdairRobertson

    SSRS Report with mutiple parameters

    SSuda Rookie

      I have created a report that can run multiple parameters using SSRS.   This report is unable to uploaded to Ivanti because of the logic.  Ivanti support states that I can uploaded into a tsql query instead of parameters. Does anyone know how to do this? 

       

      The report gives the option of preset data parameters or to enter in the date range.  Thanks

       

       

      Picture of report and code listed below.

      Global - Changes Implemented On TimeReport.png

      IF @DateRange ='Preset'
      (
      SELECT        TOP (1000) ChangeNumber AS [Change #], Status, OwnerFullName AS Coordinator, Priority, Subject, CreatedDateTime AS [Date Created], ScheduledEndDate AS [Scheduled Completion], ClosedDateTime,

        CASE When ClosedDateTime  < ScheduledEndDate THEN 'On Time'
        WHEN  ClosedDateTime >  ScheduledEndDate  THEN 'Late' 
        WHEN  ClosedDateTime is NULL THEN 'In Progress'
        ELSE 'NA'
      END as [Implemenated Status]
      FROM      Change
      WHERE Status <> 'Cancelled' and  CreatedDateTime >=  '2017-06-01 21:00:00.000' and ScheduledEndDate Between @PresetStartDate and @PresetEndDate)

      ELSE IF @DateRange ='Custom'
      (
      SELECT        TOP (1000) ChangeNumber AS [Change #], Status, OwnerFullName AS Coordinator, Priority, Subject, CreatedDateTime AS [Date Created], ScheduledEndDate AS [Scheduled Completion], ClosedDateTime,

        CASE When ClosedDateTime  < ScheduledEndDate THEN 'On Time'
        WHEN  ClosedDateTime >  ScheduledEndDate  THEN 'Late' 
        WHEN  ClosedDateTime is NULL THEN 'In Progress'
        ELSE 'NA'
      END as [Implemenated Status]
      FROM      Change
      WHERE Status <> 'Cancelled' and  CreatedDateTime >=  '2017-06-01 21:00:00.000' and ScheduledEndDate Between @CustomStartDate and @CustomEndDate)
      ORDER BY [Implemenated Status]

        • 1. Re: SSRS Report with mutiple parameters
          AlasdairRobertson ITSMMVPGroup

          I often use direct date fields in my reports, in the following SQL the start and end dates are SSRS parameters which feed the report.  the parameters are given values in ISM and when the report runs the user can update the date values and refresh the report.

           

          Here is an example which works:

          • SQL Select statement

           

          Select 
          Service as Service,
          RecId as GroupCount 
          from incident 
          where Createddatetime >= @StartDate and createddatetime < @EndDate
          

           

          • Dataset Paramter Properties

          • @StartDate and @EndDate are the parameters, they are configured in SSRS and pulled in to my report as needed. 

          • When report is uploaded to ISM the configure the parameters:

          • Report runs

           

          There is a report template in ISM you can download which has some date functions already configured in it, these also take in to account the browser language and region settings so corrects date off sets for localisation. to get the template follow the instructions below:

          1. Open ISM
          2. Access the Report Manager Role
          3. Select the Templates workspace
          4. New Report

           

          OOTB parameters, to figure these out download an existing report and take it a part to see how it is pulled together:

           

           

          One thing to note about SSRS reports is that the SQL version for the report server must match the SQL Report Builder 3 or BIDS version of the platform in use, it is not backwards compatible so SSRS 2012 reports may not run on an SSRS 2008R2 server.  As far as I can tell they are forward compatible.

           

          I hope that helps.