Creating an SSRS Report to Display Data or a Custom Error Message for an Interactive MIS System

 

Creating an SSRS Report to Display Data or a Custom Error Message for an Interactive MIS System

In Management Information Systems (MIS), reports are crucial tools for decision-making. However, sometimes reports do not have data due to various reasons, such as a lack of transactions during a specific period. In such scenarios, it’s beneficial to provide feedback in the report, such as a custom error message instead of showing a blank report, enhancing the user experience and making the reports more interactive and intuitive.

In this article, we’ll explore how to create an SSRS (SQL Server Reporting Services) report that dynamically displays either data or a custom message (such as “No Records Found”) based on whether the query returns data. This functionality is useful in interactive MIS systems, where users rely on accurate and relevant information in real-time.

---

1. Overview of the Requirement

In many reporting systems, if a query returns no data, the report still generates, often showing a blank table or empty dataset, which can be confusing to users. To enhance the interactivity and clarity of reports in an MIS system, it’s better to display a custom error message informing users that no records were found for the report's criteria.

2. Key Features of the Report

• Data-Driven Output: The report should show data if the query returns records.
• Custom Error Message: If no data is found, a message like "No Records Found" or any other relevant information should be displayed.
• Interactive Feedback: Provides users with immediate feedback, reducing confusion when no data is available.

---

3. Step-by-Step Guide to Creating the Report

Step 1: Create the SSRS Report

1. Open SQL Server Data Tools (SSDT) or Report Builder.
2. Create a new report.
3. Define your data source (usually a SQL Server database).

Step 2: Design the Query

To determine if the report has data, design a SQL query that returns data based on your report’s needs. For instance, if you are building a sales report, your query might look like this:

sql
SELECT SaleID, CustomerName, SaleAmount, SaleDate 
FROM Sales 
WHERE SaleDate = CAST(GETDATE() AS DATE)

This query retrieves sales data for the current date. The key here is to handle cases where the query returns zero rows.

Step 3: Add a Dataset to the Report

1. Right-click Datasets in the Report Data pane, and select Add Dataset.
2. Select the data source and paste your query into the query editor.
3. Click OK to create the dataset.

Step 4: Design the Report Layout

1. Insert a Table or Matrix onto the report design surface to display the data from the dataset. Map the fields from the dataset to the table.

2. Test the Report with some sample data to ensure it shows the results as expected when there is data.

---

4. Handling No Data Scenario with a Custom Error Message

Now, we’ll add logic to handle the situation where the report returns no data.

Step 5: Add a Hidden Parameter for Row Count

To check if the dataset has data, we can use a hidden parameter that holds the count of rows returned by the dataset.

1. Right-click Parameters in the Report Data pane and select Add Parameter.
2. Set the Name as RowCount and set Data type to Integer.
3. In the Default Values section, select Get values from a query.
4. Choose the dataset and set the query to:

sql
SELECT COUNT(*) FROM Sales WHERE SaleDate = CAST(GETDATE() AS DATE)

This query will return the number of records for the current date. If the count is zero, we’ll use this parameter to show the custom message.

Step 6: Add Conditional Visibility Logic

Next, we’ll set conditions to either display the data table or show the custom message when no records are found.

1. Select the table in your report.
2. In the Properties window, find the Visibility section and click the fx button next to the Hidden property.
3. In the expression editor, enter the following expression:

vb
=IIF(Parameters!RowCount.Value = 0, True, False)

This means the table will be hidden if the RowCount parameter is 0 (no data), and visible otherwise.

Step 7: Add a Textbox for the Custom Error Message

Now, add a textbox to display the custom message when there is no data.

1. Drag a Textbox onto the report surface where the table would be (or below it).
2. Type the custom message in the textbox, for example: "No Records Found for the selected date."
3. Set the font size, color, and alignment as desired to make the message stand out.
4. Set the visibility for the textbox using the same fx expression, but reverse the logic:

vb
=IIF(Parameters!RowCount.Value = 0, False, True)

This makes the message visible when RowCount = 0 (no data) and hides it otherwise.

---

5. Additional Enhancements

You can make further enhancements to this approach to improve the interactivity of your reports in the MIS system:

A. Display Custom Messages for Different Scenarios

You can add multiple conditional messages for different scenarios, such as:

• No data for a particular product or department.
• A warning if a query takes too long to execute, suggesting users refine their search criteria.

B. Use Report Parameters for Interactivity

• Add date range parameters or filters to allow users to refine the data they are looking for.
• Show different messages based on user input, such as "No sales recorded for this department today."

C. Customize Styling for the Error Message

To improve the user experience, consider adding some styling to the error message:

• Set the font color to red to make it stand out.
• Add icons or borders to highlight the message area.

---

6. Testing and Deployment

After designing the report, test it thoroughly:

• Run the report for different scenarios: one where data is available, and one where no data is returned.
• Ensure the correct content (data or custom message) is displayed in each case.

Once the report is ready, deploy it to your SSRS server. Users of the MIS system will now have access to a more interactive and informative report that provides clear feedback even when no data is available.

---

7. Summary and Benefits

By following this approach, you enhance the user experience in your MIS system by:

• Reducing confusion: Users don’t encounter empty tables; instead, they receive clear feedback when no data is found.
• Improving interactivity: Users receive relevant and timely information without guessing about the report's status.
• Customizing for business needs: Custom messages allow for detailed feedback, such as showing "No sales for this department" instead of a generic message.

This approach adds clarity and professionalism to your reports, ensuring that stakeholders and decision-makers receive actionable feedback even when data is missing. It also enhances the interactivity of the MIS system by providing a seamless reporting experience.

By implementing these strategies, your SSRS reports will become powerful tools that handle both data display and user-friendly messaging, giving your MIS system the reliability and interactivity it needs for effective decision-making.