Convert Crystal Expression to SSRS
Gurus, I am trying to convert this below crystal expression to SSRS expresssion... Local stringVar SortDesc := "Sort: "; if {?Sort By County} = "Y" then SortDesc := SortDesc & "County/" else SortDesc := SortDesc & "Dist/"; IF {?Sort By Fiscal Year} = "Y" THEN SortDesc := SortDesc & "FY/"; select {?Sort by Route or District Priority} case "ROUTE": SortDesc := SortDesc & "Route/Route Seq/" case "PRIORITY": SortDesc := SortDesc & "Priority/" case "BOTH": SortDesc := SortDesc & "Priority/Route/Route Seq/"; SortDesc & "PPS #" Help Please!!!
July 27th, 2010 3:27am

Perhaps you could tell us what you are trying to accomplish with said expression. Yes, it can be converted literally using proper SSRS syntax, but the expression is USED somewhere. Is it a simple Text Box?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
July 27th, 2010 5:36am

Hi kellog1, As Todd said, it will help if you could describe a bit more about the scenario(e.g. where the formula is used. What is the meaning of the formula). I am not familiar of Crystal Report, so I just suppose the formula means that: 1. There is a string value "SortDesc" 2. When "Sort by Country" equal to "Y", the "SortDesc" is "Sort: Country/". Otherwise, the value is "Sort: Dist/" 3. When "Sort By Fiscal Year" equal to "Y", the "SortDesc" is "Sort: Country/FY/" or "Sort: Dist/FY/". 4. When "Sort by Route or District Priority" equal to "ROUTE", the "SortDesc" is "Sort: Country/FY/Route/Route Seq/" or "Sort: Dist/FY/Route/Route Seq/" or "Sort: Country/Route/Route Seq/" or "Sort: Dist/Route/Route Seq/". 5. When "Sort by Route or District Priority" equal to "PRIORITY", the "SortDesc" is "Sort: Country/FY/Priority/" or "Sort: Dist/FY/Priority/" or "Sort: Country/Priority/" or "Sort: Dist/Priority/". 6. When "Sort by Route or District Priority" equal to "BOTH", the "SortDesc" is "Sort: Country/FY/Priority/Route/Route Seq/" or "Sort: Dist/FY/Priority/Route/Route Seq/" or "Sort: Country/Priority/Route/Route Seq/" or "Sort: Dist/Priority/Route/Route Seq/". 7. Finally, add "PPS #" to the end of the SortDesc. If I have misunderstood, please don't hesitate to let me know. In SQL Server Reporting Services(SSRS), we can use IIF instead of IF...THEN in Crystal Report. SELECT... CASE seems to be the same with SWITCH. However, I would suggest you using custom code to achieve the same logic. Below is the sample code snippet for your reference: Dim SortDesc As String Public Function GetSortString(ByVal SortByCountry As String, ByVal SortByFiscalYear As String, ByVal SortByRouteOrPriority As String) As String IF SortByCountry = "Y" THEN SortDesc = SortDesc & "County/" ELSE SortDesc = SortDesc & "Dist/" IF SortByFiscalYear = "Y" THEN SortDesc = SortDesc & "FY/" SELECT CASE SortByRouteOrPriority CASE "ROUTE" SortDesc = SortDesc & "Route/Route Seq/" CASE "PRIORITY" SortDesc = SortDesc & "Priority/" CASE "BOTH" SortDesc = SortDesc & "Priority/Route/Route Seq/" END SELECT SortDesc = SortDesc & "PPS #" Return SortDesc End Function Then, in a expression in SSRS, we can use the following expression to call the code: =CODE.GetSortString(value of SortByCountry, value of SortByFiscalYear, value of SortByRouteOrPriority) It is sure, we can use expression to construct the same logic, however, it is a little complex. Below is another sample for your reference: =IIF(Fields!SortByCountry="Y", IIF(Fields!SortByFiscalYear ="Y", SWITCH(Fields!SortByRouteOrPriority ="ROUTE", "Sort: Country/FY/Route/Route Seq/", Fields!SortByRouteOrPriority ="PRIORITY", "Sort: Country/FY/Priority/", Fields!SortByRouteOrPriority ="BOTH", "Sort: Country/FY/Priority/Route/Route Seq/"), SWITCH(Fields!SortByRouteOrPriority ="ROUTE", "Sort: Country/Route/Route Seq/", Fields!SortByRouteOrPriority ="PRIORITY", "Sort: Country/Priority/", Fields!SortByRouteOrPriority ="BOTH", "Sort: Country/Priority/Route/Route Seq/") ) , IIF(Fields!SortByFiscalYear ="Y", SWITCH(Fields!SortByRouteOrPriority ="ROUTE", "Sort: Dist/FY/Route/Route Seq/", Fields!SortByRouteOrPriority ="PRIORITY", "Sort: Dist/FY/Priority/", Fields!SortByRouteOrPriority ="BOTH", "Sort: Dist/FY/Priority/Route/Route Seq/"), SWITCH(Fields!SortByRouteOrPriority ="ROUTE", "Sort: Dist/Route/Route Seq/", Fields!SortByRouteOrPriority ="PRIORITY", "Sort: Dist/Priority/", Fields!SortByRouteOrPriority ="BOTH", "Sort: Dist/Priority/Route/Route Seq/") ) ) For more information about custom code, please see: http://msdn.microsoft.com/en-us/library/ms155798.aspx If you have any more questions, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT
July 28th, 2010 10:13am

Thanks guys.
Free Windows Admin Tool Kit Click here and download it now
July 29th, 2010 7:21pm

IF OnFirstRecord OR {@Sort PCL} <> PREVIOUS({@Sort PCL}) THEN PCLAmt := 0; i have the above scenario in crsytal reports can anyone help me out how to convert this in to ssrs.. please hel me out in this thanks in advance
January 10th, 2011 7:10pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics