//-------------ASSEMBLY---------------
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.AnalysisServices.AdomdClient;
using System.IO;
using System.Security.Permissions;
using System.Data.SqlClient;
using System.Diagnostics;
namespace ARACustomReportFunctions
{
public class CustomFormulaFunctions
{
public static string GetPreviousQuarter(string currentQuarter, int previousCount)
{
int counter = 1;
int quarterNumber = Convert.ToInt32(currentQuarter.Substring(1, 1));
int quarterYear = Convert.ToInt32(currentQuarter.Substring(currentQuarter.Length - 4, 4));
do
{
switch (quarterNumber)
{
case 1:
quarterNumber = 4;
quarterYear--;
break;
case 2:
case 3:
case 4:
quarterNumber--;
break;
}
counter++;
} while (counter <= previousCount);
return "Q" + quarterNumber + " " + quarterYear;
}
//[FileIOPermissionAttribute(SecurityAction.Assert, Read = "WarehouseConnection.txt")]
//[FileIOPermissionAttribute(SecurityAction.Assert, Write = "ARAErrors.txt")]
//[FileIOPermissionAttribute(SecurityAction.Assert, All = "c:\\ARA\\WarehouseConnection.txt")]
//[FileIOPermissionAttribute(SecurityAction.Assert, All = "c:\\ARA\\Errors.txt")]
//[FileIOPermissionAttribute(SecurityAction.Assert, Unrestricted = true)]
//[FileIOPermissionAttribute(SecurityAction.Assert, Unrestricted = true)]
[FileIOPermission(SecurityAction.Assert, All = @"c:\ARA\WarehouseConnection.txt")]
[FileIOPermission(SecurityAction.Assert, All = @"c:\ARA\Errors.txt")]
public static string QuarterlyReturn(string vehicleId, string formulaName, int yearlyFactor, string reportQuarter)
{
//SqlClientPermission sqlPermission = new SqlClientPermission(PermissionState.Unrestricted);
//FileIOPermission filePermission = new FileIOPermission(FileIOPermissionAccess.AllAccess, "c:\\ARA\\WarehouseConnection.txt");
//FileIOPermission fileWritePermission = new FileIOPermission(FileIOPermissionAccess.AllAccess, "c:\\ARA\\Errors.txt");
double quarterlyReturn = 0;
try
{
//throw new Exception("Testing exception throwing");
//build query string (selects all returns for the vehicle)
StringBuilder query = new StringBuilder();
query.Append("SELECT NON EMPTY { [Measures].[IV Eval Value] } ON COLUMNS, NON EMPTY { ([Investment Vehicle].[Investment Vehicle Alternate Key].[Investment Vehicle Alternate Key].ALLMEMBERS * [Formula].[Dim Formula].[Dim Formula].ALLMEMBERS * [Period].[Period Quarter].[Period Quarter].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Formula].[Dim Formula].[");
query.Append(formulaName);
query.Append("] ) ON COLUMNS FROM ( SELECT ([Investment Vehicle].[Investment Vehicle Alternate Key].&[");
query.Append(vehicleId);
query.Append("] ) ON COLUMNS FROM [IMPACT DW Formula])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS");
//open connection to OLAP
//sqlPermission.Assert();
//filePermission.Assert();
AdomdConnection adomdConn = new AdomdConnection();
//retrieve connection string from text file
string connectionStr = "";
using (StreamReader reader = new StreamReader("c:\\ARA\\WarehouseConnection.txt"))
//using (StreamReader reader = new StreamReader("WarehouseConnection.txt"))
{
connectionStr = reader.ReadToEnd();
}
adomdConn.ConnectionString = connectionStr; //"Data Source=http://SD031/olap/msmdpump.dll;Initial Catalog=ARA Data Warehouse";
//adomdConn.ConnectionString = "Data Source=http://SD031/olap/msmdpump.dll;Initial Catalog=ARA Data Warehouse";
adomdConn.Open();
//execute query
AdomdCommand myCmd = adomdConn.CreateCommand();
myCmd.CommandText = query.ToString();
AdomdDataReader myReader = myCmd.ExecuteReader();
double geometricallyLinkedReturn = 1;
//int counter = 1;
//int periodsSinceInception = 0;
string quarterName = "";
double quarterCounter = yearlyFactor * 4; //total # of quarters for the calculation
while (myReader.Read())
{
//multiply all 1 plus returns together for the quarters included in the calc
if (!myReader.IsDBNull(0))
{
//Retrieve quarter name from record
quarterName = myReader.GetString(4);
//if yearly factor is 0, just return current quarter's return
if (yearlyFactor == 0)
{
if (quarterName.ToUpper() == reportQuarter.ToUpper())
{
geometricallyLinkedReturn = Convert.ToDouble(myReader.GetString(6));
}
}
else
{
for (int quarters = 1; quarters < quarterCounter; quarters++)
{
//if quarter name matches current quarter or any previous quarter included in the calculation, link returns
if (quarterName.ToUpper() == reportQuarter.ToUpper() ||
quarterName.ToUpper() == GetPreviousQuarter(reportQuarter, quarters).ToUpper())
{
//retrieve evalvalue, add 1, and multiply by current geometricallyLinkedReturn
geometricallyLinkedReturn = geometricallyLinkedReturn * (Convert.ToDouble(myReader.GetString(6)) + 1);
break;
}
}
}
}
}
//calculate annualization factor
double annualizationFactor = 4 / quarterCounter;
//calculate return: (geometrically linked returns ^ annualization factor) - 1
if (yearlyFactor != 0)
{
quarterlyReturn = (Math.Pow(geometricallyLinkedReturn, annualizationFactor) - 1) * 100;
}
else
{
quarterlyReturn = geometricallyLinkedReturn * 100;
}
myReader.Close();
adomdConn.Close();
adomdConn.Dispose();
}
catch (Exception ex)
{
//throw ex;
//EventLog.WriteEntry("SSRS_ARA", ex.Message);
//System.Diagnostics.EventLog appLog =
// new System.Diagnostics.EventLog();
//appLog.Source = "ARACustomReportFunctions";
//appLog.WriteEntry(ex.Message);
//fileWritePermission.Assert();
using (StreamWriter writer = new StreamWriter("c:\\ARA\\Errors.txt"))
//using (StreamWriter writer = new StreamWriter("ARAErrors.txt"))
{
writer.Write(ex.Message);
}
}
return quarterlyReturn.ToString("N2") + "%";
}
}
}
//-------------AssemblyInfo.cs---------------
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Runtime.InteropServices;
using System.Security;
using System.Security.Permissions;
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly: AssemblyTitle("ARACustomReportFunctions")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("SMART")]
[assembly: AssemblyProduct("ARACustomReportFunctions")]
[assembly: AssemblyCopyright("Copyright © SMART 2009")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
// Setting ComVisible to false makes the types in this assembly not visible
// to COM components. If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(false)]
// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid("d9de2e07-b23e-4981-84ec-c4c2d3671fc0")]
// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]
[assembly: AllowPartiallyTrustedCallers]
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.