Formulas and Variables

Formulas are widely used in RT Report Manager. They give you the flexibility to calculate the value of the field during the processing time taking in consideration the values the other parameters and variables. Formula can be used for:

Create a file name based on date and time, customer name, sales person name, report type, state etc. like:

• <Date> Sales - <Employee> : Nov 2010 Sales - Carol; Dec 2010 Sales - Carol; Dec 2010 Sales – John

•Loss ratio Top <Value>% customers: Loss ratio Top 10% customers; Loss ratio Top 20% customers

Set default value for a parameter. Most of the reports have specific requirements for parameter values.

•Sales report most likely will have “Date From” and “Date To” parameters. Depending on your reporting period soon you will find that each time you want to run the report you are selecting the beginning and the end of the previous week, month quarter etc.

•You may have a report, which accepts a parameter from a list and you want the list in specific order. For example: List of the customers ordered by the percentage of the sales drop. List of the items ordered by the number of returns.

Email addresses could be assigned directly from job data. 2 job types provide internal values: Data driven job or Bursting job. In Data driven job, the values returned from data query are available and the email can be assigned directly

You can create a task to export report to HTML or Image and access these file as references to ambed them in Email’s body

If you think about the difference between 2 runs of one report you will see that everything is related to only one variable: Time (Date). If you have a report “Sales” with parameters “Customer” and “As Of Date” and you are running it for the end of each month you will insert always the same customer name (or ID) for the first parameter, but the value for the second parameter (As of Date) will be different. You can automate this report if you find a way to tell the reporting engine to use always the end of the current month as a value for the second parameter.

Relative dates are dates for which you know just the relation to the current date. For example, previous month end will be different depending of the current date

Current Date	Previous Month End
June 5^th 2022	May 31^st 2022
June 23^rd 2022	May 31^st 2022
March 5^th 2023	February 28^th 2023
March 5^th 2024	February 29^th 2024

No matter on which date in June 2022 you will run the “Sales” report “parameter “As of date” will be set to 5/31/2022, when you run it next month the parameter value will be 6/30/2022. If you run the report in March 2023, the parameter value will be Feb 28^th 2023, but in March 2024 it will be Feb 29^th 2024

In order to make possible to use relative dates RT Report Manager is extending the regular date control. A button “Relative” is added. This button has 2 states:

Select a date like any other date control. The value will be a fixed date as 1/1/2011 , 6/23/2011 etc.

You will be able to choose the offset from the current date instead of a fixed date.

The value will look like this: Day 5 days ago, End of previous week, End of February previous year, Beginning of the next month etc.

You can choose the date directly from the calendar, but the program will save the difference to the current date. Let say today is June 3rd 2022. By selecting June 1st 2022 you will tell the program to always use a date 2 days earlier than the current one. If you run it on August 1st 2022 the calculated relative date will be June 30th 2022 (2 days before August 1st 2022 ).

This type of offset can be useful if you have a report which shows the data for a period and you want to set the period to dates compared to current date: last 5 days, next 10 days etc.

Monday you can use week offset. Field “Week offset” will set the offset to the current week. If it is 0 current week will be used. – 1 for the previous week, +1 for the next week etc.

This type of offset can be useful if you have a report which shows the data for the previous week, next week, last 2 weeks etc.

In case you want to set a date to previous month end you can use month offset. Field “Month offset” will set the offset to the current month. If it is 0 current month will be used. – 12 for the same month 1 year ago and +1 for the next month.

This type of offset can be useful if you have a report which shows the data for the previous month and especially if you need the month end date.

In case you want to set a date to previous quarter end you can use quarter offset. Field “Quarter offset” will set the offset to the current quarter. If it is 0 current quarter will be used. – 4 for the same quarter 1 year ago and +1 for the next quarter.

This type of offset can be useful if you have a report which shows the data for the previous quarter and especially if you need the quarter end date.

In case you want to set a date to specific date previous year you can use year offset. Field “Year offset” will set the offset to the current year. If it is 0 current year will be used. – 2 for 2 years ago and +1 for the next year.

This type of offset can be useful if you have a report which shows the data for a specific date like end of February previous year or Last of the second quarter 2 years ago.

Relative dates will give you the flexibility to set values for dates related to current date. This is critical for the scheduled reports where you don’t know the date when the report will be run.

Relative dates are a powerful way to set date values without any programming experience. However there are things that require some further complexity. If you want to save the “Sales” report from the previous example to a file “Sales report as of <date of the report>.pdf” you need a way to specify the file name with some variables. This is the place where you will use expressions.

This screen has similar functionality as the Crystal report’s or Excel’s interface used to prepare expressions . The selection in the first list will determine the items shown in the second one. Double click on the item in the second list to add it to the expression. As screenshots above show selecting “Columns” will load report parameters and fields in the second list. The example above is using one of the built-in functions “DateToString”, to convert the date value to a string in order to use it in the file name. Without this conversion there will be an error

“DateToString” and other functions are available from “Functions” node. You can see some instructions how to use the selected function in the right panel.

In addition, you can choose the function from the intelisense drop down while typing

Some expressions can be pretty complex and will require some testing. Click on button “Test” to do that. Another dialog screen will be shown where you can set the variable values and check the result.

Expressions will give you additional flexibility to get the desired values. You need to have basic knowledge about the functions. If you are using functions in Excel, you will be able to use expressions editor without any problem.

Created variables will be available in expressions screen. Application variable can be used in various scenarios to avoid hardcoding fixed values. For example, you can define variable for default output folder and change the variable when you need to start exporting files in a different folder without each job.

This is the most powerful way to calculate dynamic values. You will need to have at least basic programming knowledge. RT Report Manager provides fully functional C# editor with intellisence and error reporting.

In case you try to save code with errors, you will see the error description and the exact position of the error.

There are some shared objects between the main application and your code. For example, in reports and jobs parameters collection will be shared through the object rtReportParameters and rtJobsParameters. You can get any of the parameters including additional data assigned by RT Report Manager.

You can check the read only section on the top of the namespace to see which objects are shared.

Sharing the internal application data can be extremely powerful since you will be able to rich not just information related to the report, but also to the interface. For example, you may have a report, which accepts parameter Employee ID from RT Report Manager interface and to add combobox, which will give the user easier way to insert the value.

If you check parameter value it will be 1, since Employee ID is 1, but you may want to export the report to a file which name will contain the actual employee’s name (Anne Dodsworth). The name will be available through the Parameter Additional Data property which will return the DataRow associated with the value in the list.

if (prmEmployeeID.AdditionalData != null && prmEmployeeID.AdditionalData is System.Data.DataRow) {