Archive for October, 2011

Frequently used Functions and Formula

Frequently used Functions and Formula

To remove the commas and currency symbols in the measure values,

=FormatNumber([Sales revenue];"#")

To get the measure values in decimal notations,

=FormatNumber([Sales revenue];"#,##.00")

To get the beginning and ending [year] values from a list of year values inside the reports

=Min([Year])+" to " +Max([Year]) In Report

=First([Year])+" to " +Last([Year]) In Report

To get the prompt names from the query,

=UserResponse(“Prompt Names:”)

To show "ALL" values in label , when selecting no values in Simple Filter. For example take year as an Simple report filter and there are values like 2002,2004,2006 … and "All Year". To bring these values in label , we can use two functions,



Both the functions works the same way when selecting ALL Year from List of Values shown in Simple Filter

IT will show like 2002;2004;2006 in the label, which becomes complex when more and more year values added in the tables. So we can show just "All Year" as label.

To show "All Year" as label, we can modify the functions as shown below,

=If DrillFilters([Year])<>"" Then DrillFilters([Year]) Else "All Year"

To get the report refreshed time and date in a cell, use the formula below.

="Last Refresh Date: "+FormatDate(LastExecutionDate();"dd/MM/yyyy") +" , "+LastExecutionTime()

%d bloggers like this: