Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Concatenate Expression with If Statement (Report Builder)

Hello There!

I'm somewhat of a plebe when it comes to paginated reports so hoping to get some help. 

Using the period table in the column group filtered to yesterday, month to date, year to date I get all the measures in the columns I need. What I'm trying to do is add the dates affiliated with each period to match the report mockup. 

The dataset is already filtered so that it includes only the periods yesterday, month to date and year to date, the 3 values in the column group. 

 

I imagine the expression would look something like this:

=Fields!Period.Value & IF(Count(Fields!Fields!Period_Date.Value >1), Min(Fields!Period_Date.Value & "-" & Max(Fields!Period_Date.Value, Fields!Period_Date.Value))

 

But my syntax is causing an error and I'm not entirely sure why. 


In other words, if there is more than one date associatd with the period value then show the min and max date to the right of the period value, otherwise if there is only 1 value (yesterday) then display that date. 

I'm not really worried about the values in the rows. For this request I'm just looking for something that will dynamically display the period name (yesterday, current month to date, year to date) with the corresponding date(s) associated with the period in the column grouping, to match the mockup. 

Link to Supporting Doc 

Mockup

 ReportBuilderDesignerPageReport Builder Output Page

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hello,
    After some further trial and error was able to figure it out. Thanks so much for the help!

    =IF(Fields!Period.Value = "Yesterday",

    Fields!Period.Value & " " & Fields!Period_Date.Value,

    Fields!Period.Value & " " & Min(Fields!Period_Date.Value) & "-" & Max(Fields!Period_Date.Value)

    )

3 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support

    Hi, Anonymous 

     

    You seem to be missing a few parentheses. You can try this:

    =IF(Count(Fields!Period_Date.Value) >1, Min(Fields!Period_Date.Value) & "-" & Max(Fields!Period_Date.Value), Fields!Period_Date.Value)

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Janey! Thanks for recommendation, although it wasn't quite what I'm looking for. With that expression, I get the following:

      In Yesterday Column: 6/27/2021 - 6/27/2021
      MTD Column: 6/1/2021 - 6-28-2021
      YTD Column: 1/1/2021 - 6/28/2021

      What I am trying to get is the following
      Yesterday Column: Period Value "Yesterday" Period Date  Value"6/27/2021"
      MTD Column: Period Value "Month to Date" Min and Max Period Date Values "6/1/2021 - 6/28/2021"
      YTD Column: Period Value "Year to Date" Min and Max Period Date  Values "1/1/2021 - 6/28/2021"

      I'm sure its just a syntactical issue, but I can't quite seem to work out the kinks. Here is what I'm trying to use now. 

      =IF(Fields!Period.Value) <> "Yesterday",

      (Fields!Period.Value & " " & (Min(Fields!Period_Date.Value) & "-" & Max(Fields!Period_Date.Value))),

      (Fields!Period.Value & " " & Min(Fields!Period_Date.Value))

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hello,
        After some further trial and error was able to figure it out. Thanks so much for the help!

        =IF(Fields!Period.Value = "Yesterday",

        Fields!Period.Value & " " & Fields!Period_Date.Value,

        Fields!Period.Value & " " & Min(Fields!Period_Date.Value) & "-" & Max(Fields!Period_Date.Value)

        )