I have a standalone report that gets all the data from the table. I added a bitmask filter to my SQL query, and I get the results I expect in SQL Studio.
SELECT
[Item],
STRING_AGG([RuleNo], ', ') WITHIN GROUP (ORDER BY [RuleSort]) AS GroupedRuleNos
FROM [SFB_Annex].[dbo].[tblAnnex01]
LEFT JOIN [SFB_Annex].[dbo].[tblProducts] ON [tblAnnex01].[ProductID] = [tblProducts].[ProductID]
WHERE ([tblProducts].[QuadrantBitMask] & 32) != 0
GROUP BY [Item]
ORDER BY [Item];
When I try to add the bit mask as a filter, I keep getting an error "Cannot perform '<>' operation on System.Boolean and System.String."
Here is the filter I am trying to use:
Telerik.Reporting.Filter filter = new Telerik.Reporting.Filter();
filter.Expression = "=([tblProducts].[QuadrantBitMask] AND " + QuadrantBitMask.ToString() + ")";
filter.Operator = Telerik.Reporting.FilterOperator.NotEqual;
filter.Value = "!=0";
report.Filters.Add(filter);
How would I add the bitmasked value into the filter?
Hello Ken,
I haven't used bitwise operations till now but this is an interesting case. I made a few tests and it look like the bitwise operations would return boolean values. I've attached a sample report with some conditional formatting based on bitwise operations.
You could try performing the bitwise operation in the expression. Then check if the result is true or false. Not completely sure if this would solve the case but I'll be happy to get some additional feedback and perform some additional tests on my side, too.
Regards,
Krasimir Baylov
Hi Krasimir,
I am trying to add a filter via code:
Telerik.Reporting.Filter filter = new Telerik.Reporting.Filter();
filter.Expression = "=([tblProducts].[QuadrantBitMask] AND " + QuadrantBitMask.ToString() + ")";
filter.Operator = Telerik.Reporting.FilterOperator.NotEqual;
filter.Value = "True";
report.Filters.Add(filter);
But the filter code is not filtering but the report shows all records.
Hi Ken,
In Telerik.Reporting.Filter rules you may use Expressions that accept Global Objects as arguments - see Filtering Rules. You may replace '[tblProducts].[QuadrantBitMask]' with the corresponding Field returned by the data source.
You need to consider also the Expression Scope.
If you would like to set the filter with code, I suggest using the Visual Studio Report Designer to generate it and check the autogenerated code behind in the InitializeComponent() method of the Report class. The method is in the ReportName.designer.cs file. The code is identical for .NET and .NET Framework.
Hi Todar,
The field I want to use to compare to the bitmask is [tblProducts].[QuadrantBitMask].
I am using the Report Designer but I am still not getting the filtered bit masked values.
I created a report with two text boxes
Textbox 1 = Fields.QuadrantBitMask and Textbox 2 =(CInt(Fields.QuadrantBitMask) And 32)
I was seeing false values but realized I had nulls in the QuadrantBitmask field.
I added IsNull to the values.
= IsNull(Fields.QuadrantBitMask,4096)
= (CInt(IsNull(Fields.QuadrantBitMask,4096)) And 32)
All the values in text box 2 are true there are no false values.
I started with a SQL statement that I am trying to create the report for:
SELECT
[Item],
STRING_AGG([RuleNo], ', ') WITHIN GROUP (ORDER BY [RuleSort]) AS GroupedRuleNos
FROM [SFB_Annex].[dbo].[tblAnnex01]
LEFT JOIN [SFB_Annex].[dbo].[tblProducts] ON [tblAnnex01].[ProductID] = [tblProducts].[ProductID]
WHERE ([tblProducts].[QuadrantBitMask] & 32) != 0
GROUP BY [Item]
ORDER BY [Item];
Hello Ken,
In the Filtering Rule, you may use only Global Objects. The first part of the Expression value, i.e., '=([tblProducts].[QuadrantBitMask] AND ...' is not valid as there is no object '[tblProducts].[QuadrantBitMask]' in the context of the report. The Expression value, as set in the report definition, is a string. The string gets resolved during the report processing stage to an actual value. That's why the objects used in the Expression should be valid in the report context.
Since your Report SqlDataSource query doesn't return a field representing '[tblProducts].[QuadrantBitMask]', you may set it as a value of an additional Report Parameter. This will let you use it also as an SqlDataSource parameter, for example, in the Where clause of the query. You may use DataSource to specify the AvailableValues for Report Parameters - Report Parameter Properties.
Hi Ken,
The Report Parameters cannot be optional.
You may make a Report Parameter Nullable (check the AllowNull property).
If the parameter uses DataSource, i.e., has AvailableValues, you may let it be MultiValue and let none or all of them be selected.
The above should let you load all the records as if there is no filtering.
I created a parameter for the bitmask field. When I run the report I get the below error. What am I not setting correctly?
I found how to update the SelectCommand. I am including the snippet of code. I have a different problem with the report now that I submitted a support ticket for.
I would still like to know if there is a way to do this with a filter.
public bool RenderReport_Annex01(string reportPathName, int QuadrantBitMask = 0){
bool success = false;
string FileNameSuffix = "";
//Telerik.Reporting.ReportPackager reportPackager = new Telerik.Reporting.ReportPackager();
using (var sourceStream = System.IO.File.OpenRead(reportPathName))
{
// unpackage the report
Telerik.Reporting.Report report = (Telerik.Reporting.Report)reportPackager.UnpackageDocument(sourceStream);
string SelectCommand = string.Empty;
string WhereClause = string.Empty;
string GroupByClause = string.Empty;
string OrderByClause = string.Empty;
// Check if the report's DataSource is a SqlDataSource
if (report.DataSource is SqlDataSource sqlDataSource)
{
// Access the SelectCommand property
SelectCommand = sqlDataSource.SelectCommand;
(SelectCommand, OrderByClause) = ExtractAndRemoveOrderBy(SelectCommand);
Console.WriteLine("OrderByClause: " + OrderByClause);
(SelectCommand, GroupByClause) = ExtractAndRemoveGroupBy(SelectCommand);
Console.WriteLine("GroupByClause: " + GroupByClause);
}
else
{
Console.WriteLine("The DataSource is not a SqlDataSource.");
}
// add the filter
if (QuadrantBitMask != 0)
{
WhereClause = " WHERE ([tblProducts].[QuadrantBitMask] & " + QuadrantBitMask.ToString() + ") != 0";
SelectCommand = SelectCommand + WhereClause + GroupByClause + OrderByClause;
report = UpdateSelectCommand(report, SelectCommand);
// Print out the SelectCommand
Console.WriteLine("SelectCommand: " + SelectCommand);
FileNameSuffix = "By_Quadrant";
}
Hello Ken,
For the problem in your first comment, you need to set the required ValueMember property of the AvailableValues. This would let you select the corresponding value before rendering the report and configuring your filter.
Regarding the SelectCommand, you may pass it with a Report Parameter as explained in the KB article Change Connection String dynamically through a report parameter - see Notes at the bottom.