Rank 1
Hi,
We have few reports where there is a crosstab which shows product details with segments and values for the segment. There is a column grouping on segmentname column. Data shown by the report will be as follows
___________________________________________________________________________________________
ID Code Description Barcode Active? Sellable? Redeemable? Size Color Class Department
__________________________________________________________________________________________
1 ABC123 Pink Teddy ABC123 'Y' 'N' 'Y' 'S' 'Pink' B1 N1
2 XYZ456 Blue Teddy XYZ456 'Y' 'N' 'Y' 'S' 'Blue' B1 N1
3 CBV256 Chota Bheem CBV256 'Y' 'N' 'Y' 'S' 'Brown' B2 N1
3 NIV256 Ninja Sword NIV256 'Y' 'N' 'Y' 'M' 'Brown' B2 N2
4 BBN267 Big Basket Ball BBN267 'Y' 'Y' 'Y' 'B' 'Orange' B3 N3
___________________________________________________________________________________________
Columns up to 'Reemable?' are static columns. All columns after 'Reedeemable?' are segments. Segments are defined in a different table and are dynamic. Column grouping on segmentname is used to get all segment details for a product in a single row. There are filters on crosstable to see if only active products should be shown or only redeemable or sellable products should be shown.
We have clients who have a big list of products. They would want to look for details with different combinations of segmentname and segment values. For example the filter could be as follows
((segmentname = 'Color' and value = 'Brown') or (segmentname = 'Class' and value = 'B3'))
Since the segments are dynamic and there could be different combinations, adding segments as a parameter to the report will not help. We did not find anyway to add the filters to a string variable and append the string variable to the crosstab's datasource commandtext. Is there anyway in telerik reports for the user to open a window from the report where he could create such combinations and the filter string thus formed in addition to filters in the report could be passed to the crosstab? Please suggest what could be done.
We are using Report designer and creating reports for windows forms applications. The same reports are being used by Asp.net webforms.
9 Answers, 1 is accepted
The only option for the end user to apply the filtering to the report by some criteria is via the report parameters.
If the requirement is to filter the records upon data retrieval, you can use data source parameters that are mapped to report parameters - Using Parameters with the SqlDataSource component.
Please share the report definition demonstrating the current structure of the crosstab and how exactly the data is grouped so we can provide you more accurate suggestions.
Regards,
Katia
Telerik by Progress
Rank 1
Hi Katia,
Thank you for replying.
Using Parameters with SqlDataSource will not serve our purpose. I attach screenshots of report design and reports for your reference. ProductDetails.trdx has no segment filter. ProductDetailsSegmentFilter.trdx has segment filter. Different clients could have different segments defined. I can have only one segment and value selected at a time now. Also, I need more operators in the filter. In filters screen I have no option to select 'is null' and 'is not null'. Creating filters at design time will be wrong. As mentioned earlier, user should be able to form segment filters by selecting segment, operator and value when report is run. He could apply more than one filter on segment using and/or clause. segment filter formed should be one single string which will be applied in combination with other filters. For example user would want to select sellable products with 'DIVISION' 03 and 'DEPARTMENT' 100. In this case, filter should be as follows
IsSellable = 'Y'
and ((segmentname = 'DIVISION' and valuechar = '03') and (segmentname = 'DEPARTMENT' and valuechar = '100'))
Is there a way to do this? Also, when I apply a segment filter, all segment columns should appear in the report. If you see ReportviewerSegmentFilter.png, CLASS segment filter was applied and I see only CLASS column in the report.
When no segment filter is applied, there is a blank column before segment columns start.
Please suggest what could be done to fix the above concerns.
Rank 1
Hi Katia,
Thank you for replying.
Using Parameters with SqlDataSource will not serve our purpose. I attach screenshots of report design and reports for your reference. ProductDetails.trdx has no segment filter. ProductDetailsSegmentFilter.trdx has segment filter. Different clients could have different segements defined. I can have only one segment and value selected at a time now. Also, I need more operators in the filter. In filters screen I have no option to select 'is null' and 'is not null'. Adding segment parameters to SqlDatasource or adding segment filters at design time will be wrong. As I mentioned earlier, user should be able to form segment filters by selecting segment, operator and value when report is run. He could apply more than one filter on segment using and/or clause. segment filter formed should be one single string which will be applied in combination with other filters. For example user would want to select sellable products with 'DIVISION' 03 and 'DEPARTMENT' 100. In this case, filter should be as follows
IsSellable = 'Y'
and ((segmentname = 'DIVISION' and valuechar = '03') and (segmentname = 'DEPARTMENT' and valuechar = '100'))
Is there a way to do this? Also, when I apply a segment filter, all segment columns should appear in the report. If you see ReportviewerSegmentFilter.png, CLASS segment filter was applied and I see only CLASS column in the report.
When no segment filter is applied, there is a blank column before segment columns start.
Please suggest what could be done to fix the above concerns.
The end user can choose the value for the parameter you define for the report, however choosing the operator and creating a custom filter by the end user is not supported.
Filters need to be applied to the report at design-time by specifying the expression, operator and value for each filter - How to: Add filtering to Report.
You can use built-in functions or user functions in the expression or the value part.
Instead of allowing the end user to choose the value for the Segment parameter, you can add report parameters for each segment for example: "Department", "Division", "Class" parameters. If needed set AvailableValues property for the parameter so the end user can choose from the range of predefined values - check Report Parameter Properties.
The parameter can also accept multiple values - Using Multivalue Parameters. You can also add 'IsNull' parameter of type boolean and use its value when filtering the data results.
I hope this will help.
Regards,
Katia
Telerik by Progress
Rank 1
Hi Katia,
We could add each segment as a parameter. But, the issue is that the list of segments is obtained at the runtime. Not all customers will have the same list of segments. Is there anyway to create a list of parameters dynamically?
Rank 1
Hi Katia,
There is another concern in addition to one that is raised in the earlier posts. Each segment could be of different types. Say a text/datetime/list. Based on the type of segment, will it be possible to show the parameter for segment value as a textbox/ datetime/ muti valued parameter? If it is a list, there should be way to get values for the list from a datasource to which segmentID will be passed.
To add report parameters at run-time check How to: Add Report Parameters(Add a Report Parameter programmatically) help article providing an example.
If you are using a Reporting REST or WCF service, modifications can be done in a custom resolver plugged in the service.
You can also check Modifying or a report at Run-Time kb article.
Regards,
Katia
Telerik by Progress
Rank 1
Hi Katia,
Thank you. I could add parameters programmatically and see them in the report. I have the following concerns.
- How do I programmatically add filters to cross table if any value is selected or entered for the parameter that is programatically added? Only if a value is entered or selected, a filter should be applied.
- I added cross table in the report because, segment names should be pivot columns.How do I add a filter say to show all products which has segment 'DIVISION' set to '003'? The report should show all the segments even if filter is applied only on one or two segments.
- In case I have a multi valued parameter in the report, is there any way to determine if all values are selected? In case all values are selected, we would like to pass value -1 for the parameter to the datasource.
In regards to your questions:
1. When you need to modify the report programmatically the general recommendation is first to test your scenario in the Designer and then to refer to automatically generated code in Report.Designer.cs(vb) file. This might give you an idea how the report is created in code-behind, you can use this code as a sample and modify according to your requirements.
You can also check Modifying or a report at Run-Time kb article providing more information on this topic.
Here you can check an example of adding the filters to Table/CrossTab programmatically.
2. Check How to: Add filtering to Table item and Crosstab item help article describing the steps.
3. Unfortunately, there is no setting to detect if the user has selected all values. More details about multivalue parameters can be found in this help article.
Regards,
Katia
Telerik by Progress