New to SSRS - Percentage Growth - please help

• SSRSnewbie

  SSC Veteran

  Points: 227

  More actions

  April 12, 2012 at 10:36 am

  #267312

  Hi,

  I'm looking to calculate the % increase of our databases within a selected date range.

  For example, the user runs the report for date ranges 01/01/2012 to 30/01/2012, and we can identify that the database has grown by X % in that period.

  The data is stored like this:

  Database Name / DateTimestamp / Used Space

  Any help would be great...

  Thanks.

• Jared

  SSC Guru

  Points: 61793

  More actions

  April 12, 2012 at 10:57 am

  #1472770

  lloyd.meyer (4/12/2012)

  ---

  Hi,

  I'm looking to calculate the % increase of our databases within a selected date range.

  For example, the user runs the report for date ranges 01/01/2012 to 30/01/2012, and we can identify that the database has grown by X % in that period.

  The data is stored like this:

  Database Name / DateTimestamp / Used Space

  Any help would be great...

  Thanks.

  (New Amount - Old Amount)/Old Amount*100. So if the space was 50gb and is now 100gb... (100-50)/50*100 = 100

  Or you can not multiply by 100 and express the column as percentage will will change 1.0 to 100% in the formatting.

  Jared
  CE - Microsoft

• SSRSnewbie

  SSC Veteran

  Points: 227

  More actions

  April 12, 2012 at 11:03 am

  #1472773

  Hi Jared,

  Thanks for the response.

  The problem I have is, the Used Space is stored in 1 column, for example:

  Database 1 01/01/2012 50gb

  Database 1 02/01/2012 100gb

  How does the variable/query know which is the new amount? If the new amount was on another column, it would be easy, but I'm racking my brain (not much in there left!!)

  Thanks again.

• Jared

  SSC Guru

  Points: 61793

  More actions

  April 12, 2012 at 11:10 am

  #1472783

  So add a new column in your query:

  DECLARE @datefrom datetime

  DECLARE @dateto datetime

  SET @datefrom = '2012-01-01'

  SET @dateto = '2012-02-01'

  SELECT a.databasename, a.date, a.spaceused, b.date, b.spaceused, (b.spaceused - a.spaceused)/a.spaceused AS pct_inc_dec

  FROM table1 a

  INNER JOIN table1 b

  ON a.databasename = b.databasename

  AND a.date = @datefrom

  AND b.date = @dateto

  Without knowing your table structures or what parameters the report will take, I cannot offer much more.

  Jared
  CE - Microsoft

• SSRSnewbie

  SSC Veteran

  Points: 227

  More actions

  April 13, 2012 at 3:07 am

  #1473100

  Jared,

  Thank you mate, looks like some progress. I'm really happy... thank you so much for the help.

  I've been using SQL for about 10 years, but hidden behind a multitude of reporting applications doing the hard work for me! 🙂

  I can barely type SELECT at the moment! 😉

  Thanks again, have a good weekend.

• Jared

  SSC Guru

  Points: 61793

  More actions

  April 13, 2012 at 6:25 am

  #1473184

  SSRSnewbie (4/13/2012)

  ---

  Jared,

  Thank you mate, looks like some progress. I'm really happy... thank you so much for the help.

  I've been using SQL for about 10 years, but hidden behind a multitude of reporting applications doing the hard work for me! 🙂

  I can barely type SELECT at the moment! 😉

  Thanks again, have a good weekend.

  Glad to help! I developed SO MANY complicated reports off of a bad database design, so give me a shout whenever.

  Jared
  CE - Microsoft