Using RDL expressions in Report Builder


While not a documented feature, you actually can enter an arbitrary RDL expression into a textbox in a Report Builder report. For example, you could add a textbox that shows the date the report was run by setting the value of the textbox to the following:


   =String.Format(“Date: {0:MM/dd/yyyy}”, DateTime.Today)


Another potential use of this undocumented feature is to display the values selected by the user for each report parameter. This is a helpful workaround for the fact that the auto-generated filter description does not insert the run-time values for each parameter. Here’s an example of an RDL expression that would provide this info:


   =String.Format(“Sales Year: {0}”, Parameters!OrderYear.Value)


Note that you will have to guess the name of the report parameter generated by Report Builder. Typically it will be the name of the field used in the prompted filter condition, with any spaces removed.


You cannot enter an RDL expression directly into a detail or subtotal cell in a table or matrix.

Comments (14)

  1. PhilNicholas says:

    This is so useful I can’t believe its not in the help for report builder!

  2. PhilNicholas says:

    Found an issue on a lookup parameter, returned the  parameter text for a lookup table and junk was returned. Works fine for text, dates etc. Any ideas.

  3. PhilNicholas says:

    I needed to use the .Label property as the value would be the id, a little confusing as the id was garbled up as AAC.AAA.

  4. Helen W says:

    Any updates on how you can do this with a look-up parameter?

    thx

    Helen

  5. saigalrohit4u@hotmail.com says:

    Can we decide the navigation for subtotal in some way ? I couldn’t do this so I added my textbox in the subtotal region but its contents are not displayed at all and default subtotal is not taking me to the correct drilldown

  6. eric.mikula@directit.ca says:

    I want to use a relative date filter and reference the value in the text box.  For example, I want to run the report based on data from last month.  So I create a filter "Issue Date in last 1 months", which gives me the data I’m looking for.

    However, when I try and reference the parameter in a text box I get an error ("…refers to a non-existing report parameter ‘IssueDate’") when I use: "=parameters!IssueDate.Value".

    To further confound the issue, I don’t want to see the actual parameter value (which would be "1").  Instead I want to see the month name that the parameter is referring to.

    Is this possible?

  7. LisaO says:

    Can you use expressions in the column and row headers in RB?  I am running a matrix to show hours by month, but the "Month" date variation is an integer value- I want to show the month name.  I had limited success creating a new field and using a nested if statement i.e;

    IF(Month = 1, "January", IF(Month = 2, "February"…

    but there seems to be a limit to the number of nestings.  And of course the then the months are sorted alphabetically by name— HELP!!!!

  8. jarretf72 says:

    you should try a switch statement. I just found it searching on another blog. It seems to be working really well….

    =Switch(Fields!score.Value>=Fields!evaluation.Value,"PaleGreen", Fields!score.Value>0,"Yellow")

  9. SNGATESON says:

    LisaO

    i had a similar problem with the dates in a chart.

    Use this formula worked fine for me:

    SWITCH(MONTH=1, "JANUARY", MONTH=2 ,"FEB",MONTH=3, "MAR")

    hope this helps

  10. SNGATESON says:

    Following this i still can’t find a way to sort the dates not by alphabetically

    HELP!?

  11. An easier way to display the Month Name would be to use the expression "=MonthName(#)" (# 1 through 12). Then sort the data by the number rather then the Name you could use expression "=Month(YourDate)".

  12. This "How to" guide provides code samples & ideas on using color to improve your users

  13. Pajer says:

    Hi

    Using this string

    =String.Format("Date: {0:MM/dd/yyyy}", DateTime.Today)

    Is it possible to do it so that it creates the previous days date?

  14. Jun says:

    how to check is the string a substring in other string

    like Contains(Parameters!ReportParameter1, "1").ToString()