Post reply

How to use a substring in SSRS

  • February 23, 2017 at 1:51 am

    #340969

    Hi 🙂 

    i have a field with a date , and i want to make a substring of this field as follow : 01/09/2016 i want to get 09/2016 without "01" 

    Thank you 🙂

  • February 23, 2017 at 2:01 am

    #1929893

    woooow i get it so fast !! 
    i just have to use this 
    RIGHT(CStr(Fields!datedep_ebv.Value), 7)

    i hope that help others 🙂

  • February 23, 2017 at 5:32 am

    #1929911

    What was your actual aim here, was it for grouping, display purposes? Changing the data type of a field (in this case DATE to STRING) isn't always the best idea. You might have been better off just changing the cell formatting, or applying a calculation to convert the date to the first day of the month.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • February 23, 2017 at 8:01 am

    #1929954

    Hi Thom
    I have two tables! the first which have the currency conversion, and the other have the list of products already sold, the first table "currency" we put the conversion every beginning of month ("01/02/2016", "01/03/2016 "Etc ....) and in the table of sold products we have the date of sale,  I want to make a report between two date and I want to display the exchange rate that was in that interval.

    Table "currency"
    ------------------------------
    Date            | exchange rate
    ------------------------------
    01/01/2016  |    0.58
    01/02/2016  |   0.59
    01/03/2016  |   0.62
    -------------------------------------

    Table "sale"

    -----------------------------------
    Date |  Product |  Price
    ----------------------------------
    25/01/2016 |  Product1  |  250
    28/01/2016 |  Product2  |  525
    02/02/2016 |  Product3  |  380
    ----------------------------------------------

    So because i don't have the exchange rate for the first recording of the table "Sale" (25/01/2016 |  Product1  |  250) I was obliged to make a substring of the fields date in the table "Currency" and after that to contacinate with "01" wich a day and when i have my date i make a lookup with tha table currency to get the field excanhge rate ! 

    and it works for me 🙂 
    =Lookup(FormatDateTime("01/" & RIGHT(CStr(Fields!date_sale.Value), 7),DateFormat.ShortDate),
    FormatDateTime(Fields!date_currency.Value,DateFormat.ShortDate), Fields!exchange_rate.Value, "currency")

    Thanks 🙂 

    ps : sorry for my bad english 🙁 

  • March 1, 2017 at 9:38 pm

    #1931095

    Why don't you just join the two tables in SQL on the year and month of the two dates? This assumes you will never have more than one date per month in the currency table.


    SELECT *
    FROM currency c
    INNER JOIN sale s
     ON YEAR(c.Date) = YEAR(s.Date)
      AND MONTH(c.date) = MONTH(s.Date)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply