Hi everyone,

DB--SQL server

SSRS-2008

I am creating matrix report with grouping on WEEK and Fiscalyearweek,

I need to calculate of difference between FY14W01,FY15W01 ande  percentage of those..

how to calculate in ssrs level.

You can achieve this by adding a dummy parent group based on required weeks and then adding a row inside it wih expression like

=First(Fields!YourField.Value,"Dummy Group")-Last(Fields!YourField.Value,"Dummy Group")/IIf(Last(Fields!YourField.Value,"Dummy Group")=0,Nothing,Last(Fields!YourField.Value,"Dummy Group"))

Hi Visakh,

I can't understand can you please explan more how to...

I nned it like below screenshot.

Hi veerapaneni,

According to your description, you want to calculated the increment percentage and difference between two fiscal year week within each week. Right?

In this scenario, since we need to do calculation based on values between dynamically generated cells, we can't simply use expression to achieve this goal. In this scenario, we need to use custom code to record the value for fiscal 14 and fiscal 15, then we can calculate the difference and percentage within the column group. We have tested your case in our local environment, please refer to the steps and screenshots below:

1. Add the custom code below into the report (you may need to modify the data type based on your scenario:

Public Shared Value1 as Integer

Public Shared Value2 as Integer

Public Shared previous as string

Public Shared previousweek as string

  Public Shared Function GetValue(Value as Integer,product as String,Week as String) as Integer

If product =previous and Week =previousweek  Then

     Value2=Value

Else

previous=product

previousweek=Week

Value1=Value

End If

     return Value

  End Function

  Public Shared Function GetPct()

     return (Value2-Value1)/Value1

  End Function

Public Shared Function GetDiff()

     return Value2-Value1

  End Function

2. Design the matrix like below:

3. The result look like below:

If you have any question, please feel free to ask.

Best Regards,
Simon Hou

Hi veerapaneni,

According to your description, you want to calculated the increment percentage and difference between two fiscal year week within each week. Right?

In this scenario, since we need to do calculation based on values between dynamically generated cells, we can't simply use expression to achieve this goal. In this scenario, we need to use custom code to record the value for fiscal 14 and fiscal 15, then we can calculate the difference and percentage within the column group. We have tested your case in our local environment, please refer to the steps and screenshots below:

1. Add the custom code below into the report (you may need to modify the data type based on your scenario:

Public Shared Value1 as Integer

Public Shared Value2 as Integer

Public Shared previous as string

Public Shared previousweek as string

  Public Shared Function GetValue(Value as Integer,product as String,Week as String) as Integer

If product =previous and Week =previousweek  Then

     Value2=Value

Else

previous=product

previousweek=Week

Value1=Value

End If

     return Value

  End Function

  Public Shared Function GetPct()

     return (Value2-Value1)/Value1

  End Function

Public Shared Function GetDiff()

     return Value2-Value1

  End Function

2. Design the matrix like below:

3. The result look like below:

If you have any question, please feel free to ask.

Best Regards,
Simon Hou

• Marked as answer by Simon_HouMicrosoft contingent staff, Moderator Wednesday, December 17, 2014 1:55 AM
• Unmarked as answer by veerapaneni manasa Wednesday, December 17, 2014 10:27 AM
• Marked as answer by veerapaneni manasa Wednesday, December 17, 2014 10:27 AM

Hi Simon,

I added the custoome code in report properties .

After that when i try to call the functions it's showing unknow member it's showing.

when I click on preview nthg it's showing.

Please go through the below screen shot.

Thanks,

M

Hi veerapaneni,

It doesn't matter when calling function it has red underline.

Regards,
Simon Hou

• Marked as answer by veerapaneni manasa Wednesday, December 17, 2014 10:27 AM

Hi Simon,

If One value is 0 .It's behaving differently.

Can you please help on this.urgent.

Thanks,

Manasa.

Hi Simon,

Same custom code  is working fine .\

the issuse is with 0.

example: if tabltes FY12w01 =0 and Fy13W01=282

Difference=282-0=282 we need but this code is showing some garbage value.

can you please help me it's so important.

Hi veerapaneni,

In SSRS, it generates cells from left to right. So in this scenario, it executes Code.GetPct() function first. Since your second value is divided by a 0 value, it's already a math error. We can't have 0 on denominator. Try to avoid 0 value if possible. If you have to keep the 0 value. Modify the custom code a little bit.

Public Shared Function GetPct()

     If Value1=0 Then

     return 0

     Else

     return (Value2-Value1)/Value1

End Function

Now it will return 0 on Pct if the first value is 0.

Hi Simon,

DB level No data for FY12M01.in that case it's failing.

We need to handle Empty if month is not there in DB.

I tried with your solution It's not Working

Thanks,

Manasa

Hi Simon,

If value is empty No need to calculate Percentage and Diffrence.

Can you please help me on this.

I am using the custom code 

Public Shared Value1 as String
Public Shared Value2 as String
Public Shared previous as string
Public Shared previousweek as string
Public Shared Function GetValue(Value as String,partner as String,Week as String) as String
If partner =previous and Week =previousweek Then
Value2=Value
Else
previous=partner
previousweek=Week
Value1=Value
End If
return Value
End Function
Public Shared Function GetDiff()
return Value2-Value1
End Function
Public Shared Function GetPct()
return (Value2-Value1)/Value1
End Function

How to handle this.

Thanks,

M

Hi Manasa,

I am experiencing a similar problem. Were you able to find a solution for the same? If you can share the solution it would be really helpful.

Thanks,

Gayatri