20 responses

24 12 2014

Great article I have be going crazy trying to figure out how to combine list data for weeks now. I have one question though:

In the first example on this page both tables MUST be present on the page for this to work. If I hide the first table the Net Income field reads $0. Is there a way to show only the second table in the report and still have the calculation perform as expected?

2 11 2014

Hi Paul,
I have a matrix report with only one row group. I need to calculate percentage between two rows in the row group. I have write the following expression but it do not work. “iif(Fields!Col.Value = “Discount % on Revenue”,iif(Fields!Col.Value = “Discount”,Sum(Fields!value.Value),0)/iif(Fields!Col.Value = “Turnover”,Sum(Fields!value.Value),0),Sum(Fields!value.Value))”. Can you please help. Thank you

30 07 2014

I retyped the entire code and now I get this message
There is an error on line 8 of custom code: [BC30205] End of statement expected.

3 08 2014
Paul Turley

Hi, I’ve created an example of this report and added the RDL file after the last screen image. I copied and pasted the code right out of the post. Please take a look and let me know if you have any issues.

4 08 2014

Thank you Paul, your example rdl file helped. I now have the feature working. Can you tell me how I can reverse it so it goes from lowest value = Green and Highest value = Red?

5 08 2014
Paul Turley

Just reverse the assignment of the OutR and OutG variables in the function. I’ve updated the code and uploaded a new example to the original post. The new code has a Direction parameter. You should be able to use the values of these three variables to output any color you want using some logic to do the color mixing math.

Function HeatMapColor(ThisValue As Decimal, Direction As String) As String
Dim OutR As Byte, OutG As Byte, OutB As Byte
OutB = 0

If Direction = “RtoG” Then
OutR = 255 – ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) * 255)
OutG = 255 * ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) )
Else ‘GtoR
OutG = 255 – ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) * 255)
OutR = 255 * ( ( (ThisValue – LowVal) / (HighVal – LowVal) ) )
End If
Return “#” & Right(“0″ & Hex(OutR), 2) & Right(“0″ & Hex(OutG), 2) & Right(“0″ & Hex(OutB), 2)
End Function

5 08 2014

Paul I don’t see an attached example in the post now


5 08 2014
Paul Turley

Sorry. Something went awry with the page update. It’s there now.

29 07 2014

this is what I have

Private LowVal As Decimal, HighVal As Decimal
Function SetHeatMapRange(LowValue As Decimal, HighValue As Decimal) As Object
LowVal = LowValue
HighVal = HighValue
End Function
Function HeatMapColor(ThisValue As Decimal) As String
Dim OutR As Byte, OutG As Byte, OutB As Byte
OutB = 0
OutR = 255 – (((ThisValue – LowVal) – (HighVal – LowVal))* 255)))OutG = 255 *(((ThisValue – LowVal) / (HighVal – LowVal)))
Return “#” & Right(“0″ & Hex(OutR), 2) & Right(“0″ & Hex(OutG), 2) & Right(“0″ & Hex(OutB), 2)
End Function

29 07 2014
Paul Turley

It looks like the problem might be with the double quote characters. Text editors and word processors often replace quote characters with “smart quotes”. Try taking this into NotePad and retyping the quotes and then copy the code back into the SSRS code editor.

29 07 2014

I get The is an error on line 8 of custom code: [BC30037] Character is not value

2 04 2014
Dunc Smith

When implementing this I get error:
BC30289 Statement cannot appear within a method body. End of method assumed.

3 09 2013

I have an report that extends your Heat map sample a little. Say for example you have a grouiping above region, say Country. Now within the Region you want all four cells with the same background/fill of the smallest number. Is there a way to do that?

30 08 2013

How to block right click on SSRS Report by using javascript?

2 09 2013
Paul Turley

This isn’t something that Reporting Services was designed to support. Perhaps if you could explain what you are trying to accomplish, in greater detail, I can offer some suggestions.

20 02 2013

I found this Article very useful. I have similar to this scenario problem that i’m facing. I have an assignment in the report that i have to bring data from 2 different datasets and each dataset from different database to display in the same data regions. here is example of my query

Dataset UnitProd: Select ID, Description, permitTypeID, OverallQouta from Units where permitTypeID = @PermitTypeID // This from Prod database

Dataset UnitTest: Select ID, Description, permitTypeID, OverallQouta from Units where permitTypeID = @PermitTypeID // This from Test Database

Here is my report will look like

ID | Description | OverallQouta | | ID | Description | OverallQouta |
54| AA Unit | 10 | | 54 | AA Unit | 15

the requirement to compare between 2 database for OverallQouta if overallQouta is not the same will highlight that row.

for now i use Lookup to bind dataset to display data in the report but when i compare i use expression something like

=IIF(First(Fields!OverallQouta.Value, “UnitProd”) First(Fields!OverallQouta.Value, “UnitTest”),”Yellow”,”Transparent”)
but when it compare and check that overallqouta is not the same it will highlight very row , what i want i just want to highlight only row that different.

Do you have an advise for me to accomplish this requirement?

Thank you.

21 11 2012

Can custom code be used in the navigation property of an report element?? If so how?

11 12 2012
Paul Turley

Sure. Use an expression to build the target path for a URL action. Expressions can call into an embedded VB function in the report or a custom assembly.

9 08 2012
Liangying Wei

Very useful to me. Thanks a lot!

4 07 2012

Nice Article. Many Thanks.

For those who are novice to SSRS, make PersSal as Public Shared instead of just Public

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 307 other followers

%d bloggers like this: