Date Range Validation (Start Date and End Date) in SSRS

      DateRange Validation or in other words Start Date and End Date validation is not a inbuilt validation in SSRS, so we require to implement VB script here. Follow the below steps :


1) Lets say we have a reporting table with four columns whose filters have parameters @StartDate and @EndDate :

2) Now Go to Menu → Report → Report Properties → Code
    And put the function code as below :

Function Script :

Function CheckDateParameters(StartDate as DateTime , EndDate as DateTime) as Boolean 
Dim RetValue As Boolean 
RetValue = "False" 
if(StartDate < EndDate) then 
RetValue = "True" 
else 
RetValue = "False" 
end if 
Return RetValue 
End Function 

3) Add a parameter @ValidateDateRange of Boolean type, make it hidden then go to its Default Values section, check radio button Specify Values and open exepression and put the code as  =Code.CheckDateParameters(Parameters!StartDate.Value, Parameters!EndDate.Value)

4) Add a Text Box and write the error message as "Start Date cannot be greater than End Date !" in its text.

5) Now go to the Tablix Properties of the original table :

6) Go to Visibility Section, check last radio button "Show or Hide on .." and put the expression =IIF(Parameters!ValidateDateRange.Value,True,False) :

7) Similarly go to the Visibility Section of Error Message TextBox and put the expression as

8) So now if we run the report with Start Date greater than End Date then we get the below ouput :

and its done.



Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Paging in SSRS Reports

        Paging in SSRS Reports or Displaying Fixed Number of Rows per Page in SSRS Report does not have any inbuilt property, so it needs some work around.

To achieve  Paging in SSRS Reports, take an example we have a report output as :

which has over 1000 records and we wish to implement paging of 10 records per page.
Follow the below steps :

1)  See the table design for the above report as :

2) Right click and Add a parent Row Group.

3) Then go to the Parent Group Properties and Click on Group Expression.

4) Put the code =CEILING(RowNumber(Nothing)/10) in it and press ok.

5)  On "Page Breaks" options, Check the option "Between each instance of a group".

6) Now delete the Group column with selecting "Delete columns only" option.

7) Save & Run the report and we will get the below output, 10 records per page :

That's it.




Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Merge Cells Vertically in SSRS

          Rarely we need to use Merge Cells vertically in the SSRS reports. Most of the times number of rows remain fixed in such reports. So taking an example below.
         Lets say we wish to Merge Cells vertically to display the data for different banks with Transaction numbers and transaction amount on Daily basis. See below:

      To get the output, we will require to follow the below steps :

1) Drag & Drop a Matirx.







2) Delete Row group and related rows.

3) Delete Group Expression from ColumnGroup :

4) Insert Column Left, Outside Group :

5) Insert row above, inside the group :

6) Add the desired number of rows :

7) Add another column left, Outside Group :

8) Bind the Column Group cells with the desired fields, and put the static row header in adjacent left column :

9) Now in First Column, select the desired cells and right click, you will get  an option of "Merge Cells".

10) Merge the Cells and give the requested Names :

11) Run the report and you will get the below output :

Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Check Current Isolation Level

        To check the Current Isolation Level, execute the below script :

Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Transpose a Table in SSRS

        Some times we require to Transpose the table to see the data with more readability. Lets say we have an output as below :



         And we wish to see the data as below :




To achieve it, we need the following steps to do.

1) From the Tools Drag a Table.

2) Delete the Row group with related row :

3)  Delete Additional Columns.

4) Add a Column Group (checked with "Show Detail data").

5)  Insert the required number of rows(In our case its 3).

6) Bind Dataset Field Values to the Cells.

7) Add the Row Header in previous column.

8) Run the report and you will get the below output.







Reference: Govind Badkur(http://sqlserver20.blogspot.com)

List of SSRS Reports for a string Containing in its Name , Dataset, Path , DataSourceName

            On Production server, it is difficult to get the List of SSRS Report having certain DataSourceName, located at certain path or with certain Dataset or ReportName. Because when deploying the rdl files on production server through browser, it doesn't store exactly the same as we have at our local machine. In fact it stores in Catalog, Subscriptions and other tables in ReportServer database in XML format.

           So parsing the XML content from these tables can help to find the list of such reports.

Below is the script to pull the reports using various filters.

--;WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd, 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS cl, DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition')
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd, DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
,CTE
AS
(
SELECT Name AS ReportName, Path AS ReportPath ,
  CONVERT(XML, CONVERT(varbinary(MAX),Content)) Content
FROM Catalog WITH (NOLOCK)
WHERE Name LIKE '%MISReport%'  -- 1. Filter for Report Name
)
--SELECT * FROM CTE
SELECT ReportName
  ,ReportPath
  ,b.value('DataSources[1]/DataSource[1]/DataSourceReference[1]','varchar(100)') AS DataSourceName 
  ,d.value('@Name','varchar(100)') AS DatasetName 
  ,s.value('CommandText[1]','varchar(max)') AS CommandText 
FROM CTE
  CROSS APPLY Content.nodes('Report') AS a(b)
  CROSS APPLY b.nodes('DataSets/DataSet') AS c(d)
  CROSS APPLY d.nodes('Query') AS r(s)
--WHERE d.value('@Name','varchar(100)') LIKE '%Activation%'  -- 2. Filter for Dataset Name
--WHERE ReportPath LIKE '%/LiveReports/Common/Activations/%'  -- 3. Filter for Path
--WHERE b.value('DataSources[1]/DataSource[1]/DataSourceReference[1]','varchar(100)')  -- 4. Filter for DataSourceName

    
 

           In above script we have shown two XMLNAMESPACES, use any one whichever is applicable as per your machine configuration.
Similarly Uncomment any of the filter you desired listed as below :
1. Filter for Report Name
2. Filter for Dataset Name
3. Filter for Path
4. Filter for DataSourceName 

---------------------------------------------------------------------------------------------

 Below is the script to pull the List of Reports which are subscribed to the given recipient.

;WITH CTE
AS
(
SELECT C.ItemID, C.Path, C.Name, 
  CONVERT(XML,S.ExtensionSettings) AS ExtensionSettingsXML ,
  CONVERT(XML, CONVERT(varbinary(MAX),Content)) Content ,
  S.LastStatus ,
  S.LastRunTime
FROM CATALOG C 
  INNER JOIN Subscriptions S ON C.ItemID = S.Report_OID
WHERE C.Name LIKE '%MISReports%'
)
SELECT ItemID, Path, Name, Content
FROM CTE
  CROSS APPLY ExtensionSettingsXML.nodes('ParameterValues/ParameterValue') a(b) 
WHERE b.value('Name[1]','varchar(50)') IN ('TO','CC','BCC')
  AND b.value('Value[1]','varchar(1000)') LIKE '%govind.badkur@gmail.com%'
    
 

Reference: Govind Badkur(http://sqlserver20.blogspot.com)