Convert string into Camel Case - SSRS

Convert string into Camel Case - SSRS

You can simply use
 =StrConv("MY unpropER CASE LiNe", vbProperCase)

Reference for strconv ...
http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.strings.strconv(v=VS.80).aspx

SSRS code - colour

http://blogs.msdn.com/bwelcker/archive/2006/09/26/End-of-Amnesia-_2800_Avoiding-Divide-By-Zero-Errors_2900_.aspx

http://blogs.msdn.com/davidlean/archive/2009/02/17/sql-reporting-how-to-conditional-color-1-4-the-basics-report-expressions-custom-code.aspx

Public Function ColorRYG_CTAS123(ByVal Value As Decimal, ByVal MaxPositive As Decimal

, ByVal MaxNegative As Decimal, ByVal Neutral As Decimal) As String

'Example: =code.ColorBack(expression, Max(expression), Min(expression), 0)
'=code.colorback( Fields!Sales.Value,max( Fields!Sales.Value),min( Fields!Sales.Value),0)
'Find Largest Range

Dim decRange As Decimal

Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)

Dim decNegRange As Decimal = Math.Abs(MaxNegative - Neutral)

decRange = IIf(decPosRange > decNegRange, decPosRange, decNegRange)

'Force color into Max-Min Range. Important if you want to Clip the color display to a subset of the data range.

Value = Switch((Value > MaxPositive), MaxPositive, Value <>

'Find Delta required to change color by 1/255th of a shade

Dim decColorInc As Decimal = 255 / decRange

'Find appropriate color shade

Dim iColor As Integer = CInt(Math.Round((Value - Neutral) * decColorInc))

'Return Appropriate +ve or -ve color

Dim strColor As String

If iColor >= 0 Then

'Green

iColor = 255 - iColor 'Thus 0 = White & 255 = Green

strColor = "#" & iColor.ToString("X2") & "FF00"

Else

'Red

iColor = iColor + 255 'NB iColour is -ve; -1 - -255

strColor = "#FF" & Math.Abs(iColor).ToString("X2") & "00"

End If

Return strColor

End Function

Public Function ColorRYG_CTAS45(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal, ByVal Neutral As Decimal) As String

'Example: =code.ColorBack(expression, Max(expression), Min(expression), 0)

'=code.colorback( Fields!Sales.Value,max( Fields!Sales.Value),min( Fields!Sales.Value),0)

'Find Largest Range

Dim decRange As Decimal

Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)

Dim decNegRange As Decimal = Math.Abs(MaxNegative - Neutral)

decRange = IIf(decPosRange > decNegRange, decPosRange, decNegRange)

'Force color into Max-Min Range. Important if you want to Clip the color display to a subset of the data range.

Value = Switch((Value > MaxPositive), MaxPositive, Value <>

'Find Delta required to change color by 1/255th of a shade

Dim decColorInc As Decimal = 255 / decRange

'Find appropriate color shade

Dim iColor As Integer = CInt(Math.Round((Value - Neutral) * decColorInc))

'Return Appropriate +ve or -ve color

Dim strColor As String

If iColor >= 0 Then

'Green

iColor = 255 - iColor 'Thus 0 = White & 255 = Green

strColor = "#" & iColor.ToString("X2") & "FF00"

Else

'Red

iColor = iColor + 255 'NB iColour is -ve; -1 - -255

strColor = "#FF" & Math.Abs(iColor).ToString("X2") & "00"

End If

Return strColor

End Function

Public Function ColorRYG_Admissions(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal, ByVal Neutral As Decimal) As String

'Example: =code.ColorBack(expression, Max(expression), Min(expression), 0)

'=code.colorback( Fields!Sales.Value,max( Fields!Sales.Value),min( Fields!Sales.Value),0)

'Find Largest Range

Dim decRange As Decimal

Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)

Dim decNegRange As Decimal = Math.Abs(MaxNegative - Neutral)

decRange = IIf(decPosRange > decNegRange, decPosRange, decNegRange)

'Force color into Max-Min Range. Important if you want to Clip the color display to a subset of the data range.

Value = Switch((Value > MaxPositive), MaxPositive, Value <>

'Find Delta required to change color by 1/255th of a shade

Dim decColorInc As Decimal = 255 / decRange

'Find appropriate color shade

Dim iColor As Integer = CInt(Math.Round((Value - Neutral) * decColorInc))

'Return Appropriate +ve or -ve color

Dim strColor As String

If iColor >= 0 Then

'Green

iColor = 255 - iColor 'Thus 0 = White & 255 = Green

strColor = "#" & iColor.ToString("X2") & "FF00"

Else

'Red

iColor = iColor + 255 'NB iColour is -ve; -1 - -255

strColor = "#FF" & Math.Abs(iColor).ToString("X2") & "00"

End If

Return strColor

End Function

SSRS Dates Fun

SSRS 2005

If you want to set the parameter / textbox with Date to format ... You can try this.

=Format(Today(), "yyyyMMdd")

If you want to subtract (or add), you can try the below syntax

=Format(DateAdd("d", -1, Today()), "yyyyMMdd")

=Format(DateAdd(DateInterval.Day, -1, Today()), "yyyyMMdd")

MSFT did not provide full details ... but this one will helpful

Dashboard Creation

Dashboard Creation

Below notes is my quick overview helps in creating the dashboard (reports) (mostly for SQL 2005, SSRS 2005) and in particular to decision making.

Dashboards

• To deliver information for (day-to-day) decision making
• Real time business intelligence. Snapshots of KPIs, Trend Graphs, Ad-hoc Reports and more ...
• With drill down capability, summary levels to detail levels for better results.

Below Steps can be used for quickly building the dashboard

Step 1: Identify and choose the data to be displayed.

• Data sources, connection strings
• Source Tables, Columns, its data types
• Prepare the list of calculations, grouping, where conditions or other statements like case
• Any other important SQL information

Step 2: Choose Layout

• Design on paper first or atleast the rough sketch
• For charts, consider titles, legends, colors, heading, details, type - atleast select bar or pie to start with, later we can change
• Grids - headings, rows, column groupings, aggregations - totals / sums, % variances, % calculations, Period / dates and more ... purely derive from BA and Data. Create a mockup in Excel, easy and then we can go get the same from SQL.

Step 3: Data Conversion

• Pick the data sources. If only one, it is simple and no need to worry about linking.
• Prepare the data for report so that it can be used. For ex: which goes to x-axis, y-axis, series ...
• Configuration / parameter details
• Integration w/ other data sets

Step 4: Paper design of layout

Paper design of layout - Grid / Chart / both

Step 5: Getting together

Put data & layout together --> w/ report data, datasets & parameters. Resources --> embedded images. Write Unit test.

"Intermediate result and presentation is ready"

Step 6: Conceptualize plan of interactivity

For better readability, usability with different modules.

Step 7: Data for Interactive Dashboard

Test with integration by developer.

Step 8: Interactive Dashboard

with Business Users. Run through the requirements, colors, look & feel, interactivity.

Enjoy the dashboard!!!

Dashboard

• Performance
• Executive
• Metrics
• Analytics (BI)

Design considerations

• Zoomable or go to details of data
• #1. Put the user first
• #2. Right medium: Graph / Chart ... for better communication & to what level
• #3. Color selection
• #4. Layout optimization
• #5. Keep dashboards to one page (for executives)