how to apply an inflation factor depending on the

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

how to apply an inflation factor depending on the

Author

Topic

isaac.bs
Starting Member

3 Posts

Posted - 2007-10-05 : 12:56:10

Anybody knows how to apply an inflation factor depending on the date to a measure

The Inflation Factor is (Current Month Rate / X Month Rate)

So lets say for current year the rates are

Jan 121.64

Feb 121.98

March 122.244

April 122.171

For example

If I want to calculate for March I would show a column for January February and March, the column for January would be multiplied by (122.244/121.64) the column for February would be multiplied by (122.244/121.98) and march by one (122.244/122.244)

But in April the factors would be different I would have 4 columns, January February March and April, January amounts would be multiplied by (122.171/121.64) February by (122.171/121.98) March by (122.171/122.244) and April by one (122.171/122.171)

Im using SSAS 2005

Thanks in advance

Isaac

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-10-12 : 17:55:06

If I understand correctly, I would approach this as a Data problem, not a presentation problem. Thus the solution would lie in your SQL query/procedure and not SSRS. You could solve this in SSRS with an IIF() block for each month, but given that we are probably talking about 12+ Months you are REALLY making life hard this way. For example, 2 months would look (something) like this nightmare:

=iif(Fields!MonthName.Value.ToString = "January", Fields!Rate.Value * 122.244/121.64, iif(Fields!MonthName.Value.ToString = "February", Fields!Rate.Value * 122.244/121.98, ..<logic for other 12 months..)

A better way to handle this is directly in your SQL query. Add the inflation value for each individual month. Add a field, probably in a CASE block or subquery, that would get the inflation value for the current month. Then make an Adjusted rates field that equals CurrentInflation / MonthlyInflation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources