Convert Crystal Reports Group Selection Formula to SSRS
Crystal Reports Group Selection Formula
When you group summarize data, all the groups are included in the report by default. There may be times, however, when you do not want to include all the groups, but only some groups whose summarized values meets a certain condition. You may want to see ony the groups with the highest summary values or the lowest in the report. This is where you can use Group Selection Formula in SSRS. In short, Crystal Group Selection formula provides an easy way to filter report data based on group summary values.
Convert Group Selection Formula to SSRS
In SSRS, there is no feature like Group Selection Formula. Because of the complexity of the problem, there is no way to convert Group Selection Formula automatically. It can be done in SSRS, but you need know the report to implement a workaround. Usually if there is a consistent workaround without the understanding of the report, it can be done automatically, otherwise, manual fix is required.
To implement the Group Selection formula, the first thing you may think is to use the filter in the dataset level in SSRS. Unfortunately this will not work because you need summary values to filter out unnecessary group, but in SSRS, you cannot use aggregation summary in the dataset or matrix level. It is not supported in SSRS.
There are two ways to implement Group Selection Formula in SSRS.
- Change the SQL query in SSRS
- Conditionally hide those groups not needed in the report based on summary values.
We will show an example on how to use both approaches to convert Crystal Group Selection Formula to SSRS.
Sample Conversion - Group Selection.rpt
In this report, there are two report parameters, the first one is for operation Over or Under, and the second value is for summary. What the report does is that if the group summary is over or under a certain value, the groupo will be filtered out.
Here is the Group Selection Formula used in the report.
if {?Over/Under} = "Over" then
Sum ({Customer.Last Year's Sales}, {Customer.Country}) >= {?Value}
else if {?Over/Under} = "Under" then
Sum ({Customer.Last Year's Sales}, {Customer.Country}) <= {?Value}
Here is what the original report look like in preview.
After Advanced Automatic Conversionfrom Crystal Migration Services, everything converts well except for the group selection formula. The converted report is ready to run but the groups are not filtered according to group selection formula. By the way, since Access cannot support parameters in Query well, we use SQL Express DB as the datasource. The Xtreme2005 is the sample Xtreme 2005 access data uploaded to SQL Express.
Solution 1 - Change SQL Query in SSRS for Group Selection Formula
Here is the original SQL code in converted rdl file:
SELECT "Customer"."Country", "Customer"."Customer Name", "Customer"."Region", "Customer"."Postal Code", "Customer"."Last Year's Sales" FROM "Xtreme2005"."dbo"."Customer" "Customer"
ORDER BY "Customer"."Country"
We can use subquery to implement Group Selection in SSRS.
<QueryParameters>
<QueryParameter Name="@Over_Under"> <Value>=Parameters!Over_Under.Value</Value> </QueryParameter>
<QueryParameter Name="@Value"> <Value>=Parameters!Value.Value</Value> </QueryParameter>
</QueryParameters>
<CommandText>
SELECT "Customer"."Country", "Customer"."Customer Name", "Customer"."Region", "Customer"."Postal Code", "Customer"."Last Year's Sales" FROM "Xtreme2005"."dbo"."Customer" "Customer"
WHERE "Customer"."Country" in (Select "Customer"."Country" FROM "Customer" "Customer" Group By "Customer"."Country"
having (@Over_Under = 'Over' And Sum("Customer"."Last Year's Sales") >= @Value) OR (@Over_Under = 'Under' And Sum("Customer"."Last Year's Sales") <= @Value))
ORDER BY "Customer"."Country"
</CommandText>
The query marked in bold is the implementation of Group Selection Formula. You can see it is done by using subquery.
Here is the screenshot after we fix the group selection formula:
You can see after the fix, the conversion is 100% full conversion.
Solution 2 - Change Group Visibility to Implement Group Selection
Do not touch the query in the converted rdl, instead we can change the visibility of group to hide not used groups.
The record selection formula itself can be converted to VB code as follows.
=IIF ((Parameters!Over_Under.Value = "Over"), ((Sum(Cdbl(Fields!Last_Year_s_Sales.Value), "Group1")) >= Parameters!Value.Value) , ((Sum(Cdbl(Fields!Last_Year_s_Sales.Value), "Group1")) <= Parameters!Value.Value))
This conversion can be found in the commented code in the code section in the conversion. We include this to make the fix as easy as possible.
Here is how to change the visibility of the group in Report Builder.
- Right Click on "Group 1", and click on the "Group Properties..." menu item.
- Group Properties Dialog show up, and then click on the "Visibilty"
- After that, choose "Show or hide based on an expression" radio button.
- Click the formula icon button, and the "Expression" window will display, there you can add the condition to hide group.
Here is the screenshot.
One thing to remember, the group selection formula is to show the group when the condition is true. Here in SSRS, the expression is when the condition meets, the group will be hidden, so when you do the copy and paste, remember to add the NOT.
After the fix, the conversion is exactly the same as we have in the first solution, so we leave out the screenshot here.
Conclusion
The first solution will have better performance because the group filter is done in server side, but you need know SQL code to implement it. The second solution is easier to implement, but the visibility of groups are done in the report level, so performance is not as good as the first solution.
Both solution will work for you, and you can choose the one that better fits your requirements. When you try the rdls, please change database connection accordingly.