Validate Start date and End date parameter in SSRS

You need to add a function to validate the parameters.

Go to the Report Properties under the Report menu and place this code to the Code Tab.

Public Function CheckDate(SDate as Date, EDate as Date) as Integer

Dim msg as Stringmsg = “”

If (SDate > EDate) Then

msg=”Start Date should not be later than End Date”

End If

If msg <> “” Then

MsgBox(msg, 16, “Parameter Validation Error”)

Err.Raise(6,Report) ‘Raise an overflow

End If

End Function

Then go to the Report Parameters and Add named it XXX with the datatype as string.

Checked the Hidden and Allow blank value ckeckboxes.

In Available Values choose Non-Queried and from Default Values choose Non-Queried in right side of textbox then press the FX button then paste this code.

=CODE.CheckDate( .Value)

Then press OK.

Find the complete solution at:-http://www.sqlservercentral.com/Forums/Topic537763-145-1.aspx

 

Get distinct values of a column of a SharePoint list using SQL Server Reporting Services

This issue is very common when you are pulling data from SharePoint list to load parameter values. In this post let us discuss the solution for the same.

Step 1 : Create a report and Go to the Report Tab at the top.

Step 2 : Go to the Report properties and then code and write the following code in it.

Public Function RemoveDuplicates(parameter As string) As String

Dim value as String

Dim items As Object() = Split(parameter,”~”)

System.Array.Sort(items)

Dim k As Integer = 0

For i As Integer = 0 To items.Length – 1

If i > 0 AndAlso items(i).Equals(items(i – 1)) Then

Continue For

End If

If items(i) <> “”

if i =0 then

value = items(i)

else

value = value+”,”+items(i)

End If

End If

Next

Return value

End Function

Step 3 : Create a Dataset which contain all the values that need to be display in the parameter including duplicates. Let’s name it Color Dataset.

Step 4 : Now create a parameter that should take values from the Color dataset. Make it a hidden parameter. Let’s name it Hidden_Param.

Step 5 : Create another parameter that will use to show only unique values in the report parameter. Let’s name it Main_Param, also select multiple values option.

Use following Expression in the Available Value under it specific values on both the labels Add as well as Value .

=Split(Code.RemoveDuplicates(join(Parameters!Hidden_Param.Value,”~”)),”,”)

Step 6 : Use this Main parameter in your main dataset , Dataset properties’parameter to map it.

Step 7 : See the preview and its done

Please check the below link that can help you get distinct values of a column of a SharePoint list using SQL Server Reporting Services:

1) http://blogs.msdn.com/b/sqlforum/archive/2011/04/28/walkthrough-how-to-get-distinct-values-of-a-column-of-a-sharepoint-list-using-sql-server-reporting-services.aspx

2) http://www.msbiguide.com/2013/12/distinct-values-for-ssrs-parameter-when-query-returns-duplicates/