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
3 Answers, 1 is accepted
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:
Regards,
Todor
Progress Telerik
Five days of Blazor, Angular, React, and Xamarin experts live-coding on twitch.tv/CodeItLive, special prizes, and more, for FREE?! Register now for DevReach 2.0(20).

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)
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
Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.