Adding embedded codes to reports

With this recipe, let's see how to add a parameter in order to accept a value that can be converted to a date and use it for the dataset.

Getting ready

The solution related to this chapter is named as Chapter12.sln. We will be using this solution for working with recipes and the report we use for this recipe is DailySales. Now publish the solution. If you do not have the solution, you need to create a report with the given query that connects with the WideWorldImportersDW database. Following are the steps:

1. Open the Chapter12.sln solution and configure the data source DataSourceWideWorldImportersDW with settings related to your database server and configure the Reporting Services server settings.
2. If you are making your own report, make sure that the following query is used and the table data region is added as per the output shown in the Figure 12.01:

         SELECT s.[Invoice Date Key], c.Customer, ct.City + ' - ' + ct.
         State Province] City 
           , e.[Preferred Name], si.[Stock Item] 
           , Sum(s.Quantity) Quantity, Sum(s.[Total Including Tax]) Sales 
         FROM Fact.Sale s 
           INNER JOIN Dimension.Customer c 
             ON c.[Customer Key] = s.[Customer Key] 
           INNER JOIN Dimension.City ct 
             ON ct.[City Key] = s.[City Key] 
           INNER JOIN Dimension.Employee e 
             ON e.[Employee Key] = s.[Salesperson Key] 
           INNER JOIN Dimension.[Stock Item] si 
             ON si.[Stock Item Key] = s.[Stock Item Key] 
         WHERE s.[Invoice Date Key] = '2016-05-24' 
         GROUP BY s.[Invoice Date Key], c.Customer, ct.City + ' - ' + ct.
         State Province] 
           , e.[Preferred Name], si.[Stock Item] 
   

3. In addition to that, have a text box added to show the dateset with the following expression:

         ="Selected date: " & format( First(Fields!Invoice_Date_Key.Value,
         "DataSetSales"), "yyyy-MM-dd") 
   

4. Publish the report and view. You should see a report as shown in the following screenshot:
   

   Figure 12.01

As you see, the dataset is specifically set for 2016-05-24. Rather hardcoding it, let's add a parameter and make sure that user can input following values:

• Today
• Yesterday
• Values between -1000 and 0 (0 for Today and negative values for previous days)
• Date formatted as YYYY/MM/DD

We just cannot accept the value without validating and formatting. We have to validate and then convert the value to a date before passing it to the dataset.

How to do it...

The following are the steps for adding a parameter, accepting a value, and adding an embedded code for validating and formatting the value:

1. Open the Chapter12 solution and open the DailySales report.
2. Add a report parameter and name it as ReportParameterSalesDate (see Figure 12.02).
3. Set the Prompt as Sales Date and set the Data type as Text:
   

   Figure 12.02

4. Open the Default Values page and set the default value as Yesterday. Click OK to save the parameter:
   

   Figure 12.03

5. In order to add the code, right click on the reporting area (not the body) for getting the following context menu opened and select Report Properties...:
   

   Figure 12.04

   Note

   Note that Report Properties... can be opened using the Report main menu. If you click on the Report menu, the first item of it is Report Properties....

6. Click the Code in the left pane to open the Custom code section and set the following code:

         Function GetDate(ByVal dateString as String) As Date 
           Dim dateEntered = Convert.ToDateTime("1900-01-01") 
           Try 
                If dateString.ToString.ToLower().Trim() = "today" Then 
         dateEntered = Now() 
                ElseIf dateString.ToString.ToLower().Trim() = "yesterday"
           Then 
                     dateEntered = Now.AddDays(-1) 
                ElseIf (CInt(dateString) > -1001 And CInt(dateString) < 1)
                Then 
                     dateEntered = Now.AddDays(CInt(dateString)) 
                Else 
                     If (dateString.Length = 8) Then 
                          Dim year As Integer = CInt(Left(dateString, 4)) 
                          If year > Now.Year Or year < 2000 Then 
                             Throw New Exception 
                          End If 
                          Dim month As Integer =
                          CInt(dateString.Substring(4, 2)) 
                          Dim day As Integer = CInt(Right(dateString, 2))
                          dateEntered = New Date(year, month, day) 
                     End If 
                End If 
           Catch ex As Exception 
                dateEntered = Convert.ToDateTime("1900-01-01") 
             End Try 
             Return dateEntered  
         End Function 
   

   Look at the written code once. It initially checks values like today and yesterday and sets the relevant dates. Then it addresses numeric values entered and set it as a date. If user has entered as value with the YYYY/MM/DD format, then it extracts year, month and date separately, and format the date. If an exception occurs or an invalid value is found, it sets for the date as 1900-01-01.

7. Click OK to save the settings:
   

   Figure 12.05

8. Next, we need to change the dataset query. Open the dataset and change the WHERE clause of the query as shown here:

         WHERE (s.[Invoice Date Key] = Convert(date, @Date)) 
   

9. The preceding statement adds a parameter to the dataset. Open the Parameters page of Dataset properties and add the following expression for the @Date parameter:

         =Code.GetDate(Parameters!ReportParameterSalesDate.Value) 
   

10. Click OK to save the Dataset properties:
    

    Figure 12.06

11. It is always better to show the date selected in the report. Not only that, it is better to notify the user that the date entered is invalid. That can be easily achieved by changing text box added that shows selected date as shown here:
12. Right-click on the text box to get the context menu and click on Expression menu.
13. Change the expression as shown in the following code:

         ="Selected date: " &
         Iif(format(Code.GetDate(Parameters!ReportParameterSalesDate.Value)
         ,"yyyy-MM-dd") = "1900-01-01" 
         , "Invalid date entered",
         format(Code.GetDate(Parameters!ReportParameterSalesDate.Value),
         "yyyy-MM-dd")) 
    

14. Click OK to save the expression.
15. Deploy the report and view it. You should see the report showing data for yesterday values (if exist only).
16. If you enter a value like -200, it sets the relevant date for the query and executes it:
    

    Figure 12.07

17. If you enter an invalid value, you should see a message as we configured with the expression:
    

    Figure 12.08

How it works...

Reporting Services allows us to use the functions available with .NET framework System.Math and System.Convert namespaces and Visual Basic run-time library functions. You do not need to add any references to the report or the project if you have not used other functions related to other namespaces. If you need to use other functions, you need to add references as full-qualified references such as System.Text.StringBuilder.

When the report is deployed, the code added to the report is deployed. It does not get compiled during the deployment and Reporting Services executes the code when it is requested by a user.

There's more...

Adding various functions to each report may not be the best solution if functions have to be used with many more reports. If you have a set of functions that are required by many reports, it is always better to have all the function in a single .NET class, compile it into a single assembly and use it with all reports. That is what we are going to talk with our next recipe that is Referencing external .NET assemblies.