Report parameters which are not required

[Dávid]

Hi,

I have a report, where the user wants to be able to filter the data based on 2 parameters. Is it possible to make those parameters optional? The point is to be able to filter the results, but to be able to see them all as well.

Example: Let's say I have parameters Location and SalesmanId. I want to be able to filter by Location to show all the Salesmen and also to be able to set Location and SalesmanId to see only sales of the particular salesman in the location.
If I use in the SqlDataSource query condition WHERE, both parameters will be required, otherwise the query won't be valid. How to make the parameters optional? - that the query will show all salesmen in all locations, if none of the parameters is set.

Thank you in advance for your help.

Radovan

[Todor]

Hello Radovan,

You may use Nullable parameters and apply an approach for simulating optional parameters in SQL. For example, as explained in the following blogs:

• Queries with optional parameters 
• Implementing Optional Parameters in T-SQL Stored Procedures

Regards,
Todor
Progress Telerik

[Dávid]

Thanks for tip Todor. It really seemed like a solution for my problem in theory, however when I have tried to apply it in the report it didn't work as expected. I have tried it with 2 parameters and at first it seems like it works fine. The report is loaded and there are 2 filters on the side. There are 2 problems though:
1. The initial load time for the report is very long, as it tries to load all the data. Is it possible to somehow prevent the initial load and wait for user's action?
2. The filters do not work. When I have tried to select an option from the dropdown list, the data would reload, but they were not filtered. I'm posting the sql for data source below and also print screens of the parameter settings in the report. Can you please look at them if you see any problem?

SQL Query:

SELECT
    [TransportRecords].[Id] AS TransportRecordId,
    [AspNetUsers].[UserName] AS DriverName,
    [Provider].[Name] AS ProviderName,
    [ProviderWarehouse].[Name] AS ProviderWarehouseName,
    [SourceWarehouse].[Name] AS SourceWarehouseName,
    [Purchaser].[Name] AS PurchaserName,
    [PurchaserWarehouse].[Name] AS PurchaserWarehouseName,
    [TargetWarehouse].[Name] AS TargetWarehouseName,
    [TransportRecords].[KmStateAfter] - [TransportRecords].[KmStateBefore] AS Distance,
    [TransportRecords].[TransportedMaterialVolume],
    [TransportRecords].[DateUnloaded],
    [TransportTargetLocations].[Name] AS SourceTransportLocationName,
    [Products].[Name] AS ProductName,
    [CustomEnumValues].[Name] as UnitName,
    [TransportRecords].[SourceType],
    [TransportRecords].[TargetType],
    [TransportRecords].[VehicleId],
    [TransportRecords].[ProviderId]
FROM (((((((((([TransportRecords]
 LEFT JOIN [BusinessPartners] Provider
 ON [TransportRecords].[ProviderId] = [Provider].[Id])
 LEFT JOIN [BusinessPartners] Purchaser
 ON [TransportRecords].[PurchaserId] = [Purchaser].[Id])
 LEFT JOIN [Warehouses] ProviderWarehouse
 ON [TransportRecords].[ProviderWarehouseId] = [ProviderWarehouse].[Id])
 LEFT JOIN [Warehouses] PurchaserWarehouse
 ON [TransportRecords].[TargetWarehouseId] = [PurchaserWarehouse].[Id])
 LEFT JOIN [Warehouses] SourceWarehouse
 ON [TransportRecords].[SourceWarehouseId] = [SourceWarehouse].[Id])
 LEFT JOIN [Warehouses] TargetWarehouse
 ON [TransportRecords].[TargetWarehouseId] = [TargetWarehouse].[Id])
 LEFT JOIN [TransportTargetLocations]
 ON [TransportRecords].[SourceTransportTargetLocationId] = [TransportTargetLocations].[Id])
 LEFT JOIN [AspNetUsers]
 ON [TransportRecords].[DriverId] = [AspNetUsers].[Id])
 LEFT JOIN [Products]
 ON [TransportRecords].[ProductId] = [Products].[Id])
 LEFT JOIN [CustomEnumValues]
 ON [TransportRecords].[UnitId] = [CustomEnumValues].[Id])
WHERE (@MachineId IS NULL OR [TransportRecords].[VehicleId] = @MachineId)
AND (@BusinessPartnerId IS NULL OR [TransportRecords].[ProviderId] = @BusinessPartnerId)

[Todor]

Hi Radovan,

1. The initial load time for the report is very long, as it tries to load all the data. Is it possible to somehow prevent the initial load and wait for user's action?

The AvailableValues for the Report Parameters, e.g. BusinessPartners and Machine will be downloaded entirely so that the users to be able to select among all values.

The initial load cannot be avoided and will be made with the default value for the parameters. I noticed that the default Value for both parameters is empty/NULL, which will result in fetching all data. To avoid this, you may set a particular value to be used for the parameters, for example, you may set the Value to be '=Fields.Id', which will result in using the First value from the AvailableValues.

2. The filters do not work. When I have tried to select an option from the dropdown list, the data would reload, but they were not filtered.

I don't see anything suspicious in the query.

Have you linked the Report Parameters with the SqlDataSource parameters? You may see the article Using Parameters with the SqlDataSource component for more details.

Regards,
Todor
Progress Telerik