3 Replies Latest reply on Jun 12, 2018 11:47 PM by Juergen Voelkening

    SSRS formatting ServiceReqParam date values

    vee Apprentice

      Hi all, I only started learning reporting last Friday so hopefully this is something easy to answer!

       

      My scenario is this: I have a report that outputs all the ParameterName & ParameterValue information from ServiceReqParam where the ParentLink_RecID is the RecId of the Service Request the report was generated from. In other words, print out all the fields from a particular service request form.

       

      The output looks something like this:

      Of course some forms have date/time fields so as they come out in the report I want to be able to format them. Ideally I can do timezone offset, but even just formatting them as dd/mm/yyyy would be great. Because I don't know what a ParameterValue value will be, I tried the following expression:

       

      =IIF(IsDate(Fields!ParameterValue.Value),(FormatDateTime(Fields!ParameterValue.Value,1)),Fields!ParameterValue.Value)

       

      This works great, but only for any date/time values, it gives an error for anything else:

      When I try something simple such as:

       

      =IIF(IsDate(Fields!ParameterValue.Value),"This is a date",Fields!ParameterValue.Value) it's happy:

      But the moment I try to do any formatting on the date fields in the "true" section of the IIF, I get errors.

       

      Any suggestions? Thanks heaps!

        • 1. Re: SSRS formatting ServiceReqParam date values
          Juergen Voelkening Apprentice

          Hi Vee,

          we are using the DisplayType to convert the datetime fields.

           

          example:

           

          case when ServiceReqParam.DisplayType ='datetime'

          then substring(ServiceReqParam.ParameterValue,9,2) +'.'+substring(ServiceReqParam.ParameterValue,6,2)+'.'+substring(ServiceReqParam.ParameterValue,1,4)

          else ServiceReqParam.ParameterValue

          end

          • 2. Re: SSRS formatting ServiceReqParam date values
            vee Apprentice

            Hi Juergen,

             

            Thanks heaps for your input, unfortunately I'm not quite sure how to use that sample you provided. The syntax is different from what I seem to be able to use in Report Builder, or perhaps I'm just not putting it in the right place?

            I've been putting my expression into the "Value" field:

            • 3. Re: SSRS formatting ServiceReqParam date values
              Juergen Voelkening Apprentice

              Hi Vee,

              actualy it is the SQL Statement in the Dataset.

               

              Example with Format, Translation and filter for "unwanted" Columns

               

              select ServiceReqTemplateParam.DisplayName,

              (case when ServiceReqParam.ParameterValue ='true' then 'Ja'

                    when ServiceReqParam.ParameterValue ='false' then 'Nein'

              Else

              case when ServiceReqParam.DisplayType ='datetime' then substring(ServiceReqParam.ParameterValue,9,2) +'.'+substring(ServiceReqParam.ParameterValue,6,2)+'.'+substring(ServiceReqParam.ParameterValue,1,4)

              else ServiceReqParam.ParameterValue

              end

              end) ParameterValue,

               

              ServiceReqTemplateParam.SequenceNum  from ServiceReqParam, ServiceReqTemplateParam  where  ServiceReqParam.ParameterName not in('Besteller',

              'Bestelllabel',

              'RequestorLoginID',

              'text_1',

              'Empfaenger',

              'Empfaenger_Abteilung')

              and ServiceReqParam.SvcReqTmplParamLink_RecID =ServiceReqTemplateParam.recid

              and ServiceReqParam.ParentLink_RecID  = (@RecID)