MSSQL1 9Fb1C+8JS4VGW+3cwyWgXtE++yvGaKvZYhXozOlsYQ/V0OEJyUtDleF7Jdx3dOA/jWzRiU5x0+1pSe3JYHSim9xW+evIBcOk7Hulpenk4HvCyFODQ1lymW9a8ZzDdG03xHqbr2FWeoF/a1Hod2ykd9/EwetFXQ== MSSQL1 DataSource1 Category,System.String Rank,System.String First_x0020_Name,System.String Surname,System.String Initials,System.String Gender,System.String Country,System.String DOB,System.String Total,System.Int32 30 DataSource1 MSSQL1 DECLARE @Org_Key AS int; SET @Org_Key = 2; DECLARE @Language_Code AS nvarchar(30); SET @Language_Code = 'ENG'; /***************************************/ DECLARE @Translation_User_1 AS nvarchar(200); DECLARE @Translation_User_2 AS nvarchar(200); DECLARE @Translation_User_3 AS nvarchar(200); DECLARE @Translation_User_4 AS nvarchar(200); DECLARE @Translation_User_5 AS nvarchar(200); DECLARE @Translation_User_6 AS nvarchar(200); SET @Translation_User_1 = (SELECT Translation FROM Translation WHERE Mnemonic = 'USER_DISPLAY_FIELD_1' AND Language_Code = @Language_Code AND Organization_Key = @Org_Key); SET @Translation_User_2 = (SELECT Translation FROM Translation WHERE Mnemonic = 'USER_DISPLAY_FIELD_2' AND Language_Code = @Language_Code AND Organization_Key = @Org_Key); SET @Translation_User_3 = (SELECT Translation FROM Translation WHERE Mnemonic = 'USER_DISPLAY_FIELD_3' AND Language_Code = @Language_Code AND Organization_Key = @Org_Key); SET @Translation_User_4 = (SELECT Translation FROM Translation WHERE Mnemonic = 'USER_DISPLAY_FIELD_4' AND Language_Code = @Language_Code AND Organization_Key = @Org_Key); SET @Translation_User_5 = (SELECT Translation FROM Translation WHERE Mnemonic = 'USER_DISPLAY_FIELD_5' AND Language_Code = @Language_Code AND Organization_Key = @Org_Key); SET @Translation_User_6 = (SELECT Translation FROM Translation WHERE Mnemonic = 'USER_DISPLAY_FIELD_6' AND Language_Code = @Language_Code AND Organization_Key = @Org_Key); DECLARE @sql nvarchar(max); SET @sql = 'SELECT cat.Category_Name AS [Category], p.Rank_String AS [Rank], CASE SUBSTRING(Rank_String,1,1) WHEN ''E'' THEN ''EQUAL'' WHEN ''B'' THEN ''BLANK'' WHEN ''C'' THEN ''CLOSE'' ELSE ''NOT EQUAL'' END AS ''First Name'', CASE SUBSTRING(Rank_String,2,1) WHEN ''E'' THEN ''EQUAL'' WHEN ''B'' THEN ''BLANK'' WHEN ''C'' THEN ''CLOSE'' ELSE ''NOT EQUAL'' END AS ''Surname'', CASE SUBSTRING(Rank_String,3,1) WHEN ''E'' THEN ''EQUAL'' WHEN ''B'' THEN ''BLANK'' WHEN ''C'' THEN ''CLOSE'' ELSE ''NOT EQUAL'' END AS ''Initials'', CASE SUBSTRING(Rank_String,4,1) WHEN ''E'' THEN ''EQUAL'' WHEN ''B'' THEN ''BLANK'' WHEN ''C'' THEN ''CLOSE'' ELSE ''NOT EQUAL'' END AS ''Gender'''; IF @Translation_User_1 <> '' BEGIN SET @sql = @sql + ', CASE SUBSTRING(Rank_String,5,1) WHEN ''E'' THEN ''EQUAL'' WHEN ''B'' THEN ''BLANK'' WHEN ''C'' THEN ''CLOSE'' ELSE ''NOT EQUAL'' END AS ''' + @Translation_User_1 + '''' END IF @Translation_User_2 <> '' BEGIN SET @sql = @sql + ', CASE SUBSTRING(Rank_String,6,1) WHEN ''E'' THEN ''EQUAL'' WHEN ''B'' THEN ''BLANK'' WHEN ''C'' THEN ''CLOSE'' ELSE ''NOT EQUAL'' END AS ''' + @Translation_User_2 + '''' END IF @Translation_User_3 <> '' BEGIN SET @sql = @sql + ', CASE SUBSTRING(Rank_String,7,1) WHEN ''E'' THEN ''EQUAL'' WHEN ''B'' THEN ''BLANK'' WHEN ''C'' THEN ''CLOSE'' ELSE ''NOT EQUAL'' END AS ''' + @Translation_User_3 + '''' END IF @Translation_User_4 <> '' BEGIN SET @sql = @sql + ', CASE SUBSTRING(Rank_String,8,1) WHEN ''E'' THEN ''EQUAL'' WHEN ''B'' THEN ''BLANK'' WHEN ''C'' THEN ''CLOSE'' ELSE ''NOT EQUAL'' END AS ''' + @Translation_User_4 + '''' END IF @Translation_User_5 <> '' BEGIN SET @sql = @sql + ', CASE SUBSTRING(Rank_String,9,1) WHEN ''E'' THEN ''EQUAL'' WHEN ''B'' THEN ''BLANK'' WHEN ''C'' THEN ''CLOSE'' ELSE ''NOT EQUAL'' END AS ''' + @Translation_User_5 + '''' END IF @Translation_User_6 <> '' BEGIN SET @sql = @sql + ', CASE SUBSTRING(Rank_String,10,1) WHEN ''E'' THEN ''EQUAL'' WHEN ''B'' THEN ''BLANK'' WHEN ''C'' THEN ''CLOSE'' ELSE ''NOT EQUAL'' END AS ''' + @Translation_User_6 + '''' END SET @sql = @sql + ', COUNT(*) AS Total FROM Org cat with (nolock), Table2 p with (nolock) left outer join Profile ctop with (nolock) on p.List_Profile_Key = ctop.List_Profile_Key WHERE p.Organization_Key = ' + CAST(@Org_Key AS nvarchar(100)) + ' AND (ctop.Category_Key = cat.Category_Key OR ctop.Category_key is null) AND p.Organization_Key = cat.Organization_Key GROUP BY p.RANK_STRING, cat.Category_Name ORDER BY cat.Category_Name, Total DESC' exec (@sql) UserFields Rank,System.String Country,System.String Rank UserFields DataSource1 Country Country Name Rank Name Relation Rank EngineV2 None;Black;2;Solid;False;4;Black Transparent Transparent 0,0.2,7.72,0.3 Transparent 0,0,5,0.3 Transparent True 0,0,1.5,0.3 Arial,8 0,0,0,0 DataDataSource1_Category {DataSource1.Category} Black ,,,,WordWrap=True,A=0 Center Transparent True 1.5,0,0.9,0.3 Arial,8 0,0,0,0 DataDataSource1_Rank {DataSource1.Rank} Black ,,,,WordWrap=True,A=0 Center Transparent True 2.4,0,0.7,0.3 Arial,8 0,0,0,0 DataDataSource1_First_Name {DataSource1.First_Name} Black ,,,,WordWrap=True,A=0 Center Transparent True 3.1,0,0.6,0.3 Arial,8 0,0,0,0 DataDataSource1_Surname {DataSource1.Surname} Black ,,,,WordWrap=True,A=0 Center Transparent True 3.7,0,0.6,0.3 Arial,8 0,0,0,0 DataDataSource1_Initials {DataSource1.Initials} Black ,,,,WordWrap=True,A=0 Center Transparent True 4.3,0,0.7,0.3 Arial,8 0,0,0,0 DataDataSource1_Gender {DataSource1.Gender} Black ,,,,WordWrap=True,A=0 Center CrossHeaderBand1 Transparent 5,0,0.6,0.3 Transparent 0,0,0.6,0.3 Arial,8,Bold 0,0,0,0 Text1 {UserFields.Country} Black Center Relation UserFields CrossDataBand1 DataSource1 DataDataSource1 0277922ce51a476caeb53a956055ac16 0.39,0.39,0.39,0.39 Page1 11 8.5 Letter Arial,100 [50:0:0:0] 268435455 System.Dll System.Drawing.Dll System.Windows.Forms.Dll System.Data.Dll System.Xml.Dll Stimulsoft.Controls.Dll Stimulsoft.Base.Dll Stimulsoft.Report.Dll Report 5/23/2016 12:52:39 PM 5/23/2016 11:23:51 AM E:\2.mrt fa3fde04b4544225bda5ad688d53c3b4 Report Inches 2016.1.11 CSharp