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
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