Use SSRS Data Alert

 

SQL 2012 Reporting Services has data alerts capability.  It is a data driven alerting solution. You could use it to inform the users about reporting data that is important to them at a relevant time through email or SMS format.

Here are the steps to create a data alert:

• Open the report that you want to create the data alert.
• Click on the New Data Alert on the Actions drop down menu

• The Data Alert Designer window will open

• Edit the Alert Name
• Create the Alert Rules that are applicable/interested by the users by clicking on the (Add rules…) on the right side pane. 
• Select and set the data feed columns from the list that you will use to create the rule i.e. Low Water Count.Value

• Set the threshold i.e. is greater than 30

• You may change the option from Alert me if any data has:  to Alter me if no data has:

• Set the the time frequency that you want the alert to be sent at Schedule settings session.  Click on Advanced option to set the stop date or option for sending message only the alert result changed.

• Set the message recipients and other information in the Email settings session.

• Click on Save button to create the alert.

 

Manage the Data Alert

Once the data alert been created, you could manage/edit it using Data Alert Manager.

• To open the data alert manager, right click on the report and click on the Manage Data Alert menu.

• Right click on the alert name, you could select Edit or Delete the alert to modify it.
• You could also select Run option to execute the data alert.

Data Alert Manager also provides alert histories and status for review. When condition met, the system will send out the e-mail message and you will see the Sent Alerts Count increased.

Here is a sample data alert email.

It provides the actual data alert result and rules/report parameters used for the SSRS report.  In addition, there is a hyperlink in the message for use to review the report detail as needed.

Setup SMS Alert

If you like to send out the data alert to the mobile device with SMS format, you could use the SMS through email option. Different provider uses different SMS gateway.  Make sure you use the correct gateway address.  For example, if you have AT&T mobile number, you could set the PhoneNumber@mms.att.net or phoneNumber@txt.att.net in the recipients address.  When the data alert condition met, it will send a big text message to that phone number.

Depended on the provider you used, it is likely to be several of them. Here is a sample:

Requirements

To use the SSRS data alert, you must have the followings:

• SQL 2012 SSRS integrated mode.  It could be integrated with SharePoint 2010 or SharePoint 2013.
• Enable SQL Server Agent
• Enable SSRS email delivery option.
• Need Manage Alert SharePoint permission to setup/manage the data alert.
• Use stored credentials or no credentials for report data source.

If the New Data Alert option is grayed out, the report data source must be configured to use either integrated security credentials or prompt for credentials. To enable the option, you must set the data source to use stored credentials or no credentials.

Reference

http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx

http://technet.microsoft.com/en-us/library/gg492252.aspx

http://en.wikipedia.org/wiki/List_of_SMS_gateways

Use DAX Search Function to Retrieve Subset Data in SSRS Report

Most of you would be familiar with substring search in T-SQL. If you have a SSRS report using DAX, do you ever winder how to do the search using the pass in parameter?

Here is the sample DAX query used to retrieve the Order Count and Sales Amount for Customers from Internet Sales:

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales'    
        )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

Here is the sample result:

 

 

If you only want to return certain customer, you will need to add a filter by pass in the Customer name as the report parameter.  Here is the sample DAX query:

 

 

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales',  DimCustomer[CustomerName] = @CustomerName
        )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

Then you could get the result that has exact match to the customer name passed in.

 

If you need to return all the customers that has name like “Peter”, you could use DAX Search function to retrieve the list.  Here is the example:

 

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales',  
    IFERROR(Search(@CustomerName, DimCustomer[CustomerName]), -1) > 0
        )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

 

The above query equals to the following T-SQL:

SELECT 
[CustomerName] ,[Gender], [Order Count],[Sum of Sales Amount]
FROM [dbo].[FactInternetSales] F 
INNER JOIN [dbo].[DimCustomer] C
On C.CustomerKey = F.CustomerKey
WHERE C.[CustomerName] like @CustonerName

You can see the result as below.  All the customers with name containing “Peter” got returned.

 

 

Sometime you may also want to handle the situation when user passed in Space or Blank parameter.  Here is a technique you could use.  When Blank customer name entered, the report will return all the customer without any filter.

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales',  
    IF(@CustomerName = blank(), 1=1, IFERROR(Search(@CustomerName, DimCustomer[CustomerName]), -1) > 0 )
                    )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

 

Here is the result:

 

 

Reference

http://technet.microsoft.com/en-us/library/ee634235.aspx

Setup SSRS email Delivery Option Using Gmail Account

 

In order to use the SSRS email delivery subscription or Data alerts feature, you need to configure the email option. If you don’t have access to the company exchange server, you could configure the system to use a Gmail account instead.  Here are the steps:

Configure a virtual SMTP Server

• Install SMTP Server window option on the local box or a server.
• Open IIS 6.0 manager to configure the SMTP option:
• Click on the Access tab and select Anonymous access option in the Authentication option box

• Click on the Outbound Security option button from the Delivery tab, select the Basic authentication with your Gmail account and enable the TLS Encryption.

• Click on the Outbound connections option button, set the TCP Port number to 587

• Click on the Advance option button, set the Smart Host to use smtp.gmail.com

Now you need to configure the SSRS to use the email delivery method. Depend on the SSRS mode, you will do the following:

SSRS Integrated Mode

Open SharePoint 2013 Central Administration and select the Manage Service applications option

• Click on the SQL Server Reporting Services Service to open the configuration page
• Click on the E-mail Setting link

 

• Enable the Use SMTP server option and set the Outbound SMTP server name or IP address; Set the From address to your Gmail address same as you used in the setup IIS step.

• Click on OK button. You are good to go now.

SSRS Native Mode

Open Report Service Configuration manager.

• Click on the e-mail setting tab
• Type your Gmail address in the Sender Address on the SMTP Settings window
• Set the SMTP Server to the server name as previous step

• Click on Apply button. You are good to go now.

Send a SMS text message from email

If you want to send SMS text for the Data Alert. You could type in the 10 digit number with the mobile provider extension as the example below to send the SMNS text.

 

Reference

http://www.vsysad.com/2012/04/setup-and-configure-smtp-server-on-windows-server-2008-r2/

http://www.nytimes.com/2011/11/03/technology/personaltech/how-to-send-text-messages-by-e-mail.html?_r=0

Create SSRS Report using DAX

 

We could query BISM Tabular model using either MDX or DAX. People are very familiar with how to use MDX in SSRS.  Using DAX in SSRS is totally different from using MDX.  Here is an example to create SSRS report using DAX language:

List of Steps:

1. Open SQL Server Data Tools to create a SSRS report.

2. Add an existed BISM Tabular Model as the Shared data source.

3. Write the DAX in the SSMS or DAX Studio first. The DAX Studio is an Excel Add-Ins that you could download from the CodePlex.

EVALUATE

SUMMARIZE

    (    

        CALCULATETABLE( 'Internet Sales'

        ,'Product Category'[Product Category Name] = "Bikes"       

    ) ,

 'Date'[Calendar Year],

 'Product Category'[Product Category Name],               

  "Total Sales", 'Internet Sales'[Internet Total Sales],               

  "Total Tax Amount", 'Internet Sales'[Internet Total Tax Amt],              

  "Total Margin", 'Internet Sales'[Internet Total Margin]           

 )

 

4. Create SSRS dataset

• Add a new dataset and connect to the BISM Tabular Model
  
• Open Query Designer
  
• Click on the Command Type DMX button on the toolbar to switch to the DMX query designer.

• Click on the Query/Design Mode button to switch to the Query mode.

• Paste or type your DAX query into the Query window and then click on OK button.
  
• Click on the Fields tab to fix/rename the field name to make it more readable. The field name generated by the query designer will not lineup with the column name you have in the DAX query.

 

• Now, you could add those fields to the report.

5. Add parameter

• Go to dataset query, open the query designer.
  
• Change the DAX query by replacing the filer part of Query with parameter name and click on the Parameter Button to open the Query Parameters window

• Add Parameter and set the default value, then click OK to exit out the designer
  
• On the report data window, you will find a parameter been created with default value that you typed in.

6. Create parameter dataset

• There is no parameter query generated by the tool. You need to create it if you allow user to pick from the drop down list.
  
• Write a DAX query with VALUES() function using the same steps before to create a dataset that will contain the list of parameter values.

EVALUATE 
values('Product Category'[Product Category Name])
ORDER By 'Product Category'[Product Category Name]

• Associate this dataset to the report parameter

• Now your SSRS report should work with single value selection.

Report Parameter Allows Multiple Values

If you allow multiple values for the report parameter, you could not use filter with equal sign (“ = ”) in the DAX query.  You could use PATHCONTAINS() function in the filter part of query to filter the result set.

PATHCONTAINS(<path>, <item>)

Since the multi select parameter string pass in will contain extra special characters that could not be used for the Path() function, you need to use Substitute() function to replace them.

Here is the complete DAX query for the report.

EVALUATE

SUMMARIZE(

    CALCULATETABLE( 'Internet Sales'

            ,PATHCONTAINS(

                    substitute( 

                        substitute( 

                            substitute( 

                                    @CategoryName

                              , "{ ", "") 

                        , " }", "") 

                    , ",", "|") 

        ,'Product Category'[Product Category Name] ) 

),                

 'Date'[Calendar Year],

 'Product Category'[Product Category Name],               

  "Total Sales", 'Internet Sales'[Internet Total Sales],               

  "Total Tax Amount", 'Internet Sales'[Internet Total Tax Amt],              

  "Total Margin", 'Internet Sales'[Internet Total Margin]           

 )

 

After you update your query, the SSRS report will work for multiple selections from the user now.

 

 

Reference:

http://daxstudio.codeplex.com/

http://msdn.microsoft.com/en-us/library/gg492182.aspx

Alternate Row Color for SSRS Report With Column Group

There are several ways to alternate background color for rows. The most common way is using RowNumber() function to retrieve the order of the row then set the background color using expression as:

=IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", Nothing)

If there is a row group, using RowNumber() can lead to some issues. The another alternative is to use RunningValue() function as:

=IIf(RunningValue(Fields!Product.Value, CountDistinct, "Customer") Mod 2 > 0,"Silver", Nothing)

Dynamic Column Group Scenario

When there is a column group with dynamic data (i.e. Sales Reason) , you will not be able to use above solutions to alternate the row color.  Since the number of column (the Sales Reason in my example) may be varied depended on the row group data (the Date and Product in my example).  Here is what you may see if you use above approach only.

Solution 1

Use Customer Code and variable to determine the row color

• Create a report customer code to toggle the row status for each function call

Public RowStatus as Boolean = False

Public Function GetRowStatus as Boolean 

    RowStatus  = Not RowStatus 

    Return RowStatus 

End Function

• Add a variable RowColor to the detail row group that has value set to the expression as below to call the customer function:

          = Code.GetRowStatus()

• Select the whole detail row and set the background color based on the variable value.
  
• Now the alternated row color shows up correctly.

Solution 2

Store RunningValue() in a textbox and use it to set the row color

• Add a new column outside the Column group at end of the report

• Name the detail row textbox as RowNumber and set its value to the expression as

        =RunningValue(Fields!Product.Value, CountDistinct, "OrderDate" )

• Select the whole detail row and set the back ground color to use the expression as:

         =IIF(ReportItems!RowNumber.Value mod 2 > 0, "LightGrey", Nothing)

• 
  

  Now the row color displays correctly and we can hide the last column that contains the RowNumber text box from the end user.

Reference:

http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/6dbde4cf-2b3f-4aa6-b47c-5c37c47bfc82#72a83b7f-241c-4c80-b79a-7f6e4b4bc74a

http://msdn.microsoft.com/en-us/library/dd255285

Display the No Row Information in the SSRS Report Footer

Recently there is a reader asked about how to display row count information in page footer if no data returned.  Since RowNumber() function can only be used in the report body to retrieve the number of rows in the specified scope, we could not use it in either the Header or Footer area.

We could use other counting function such as CountRows() or Count() to retrieve the number of rows retuned for the dataset.

Here is what we can do:

• Add a textbox in the report footer

• Use CountRows() function passing in the dataset name in the expression for the textbox as:

Result

AS you could see, the report footer shows the number of row returned by the dataset now.

Reference:

http://msdn.microsoft.com/en-us/library/dd255237(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms157163(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms156330(v=sql.110).aspx