Solved

Set Date Format on SSRS Report

Posted on 2010-11-23
19
858 Views
Last Modified: 2012-05-10
Hi All,

I've created a report using MS SSRS 2005 that has a DateStart and a DateEnd parameter on a stored procedure. When I go to preview and select a date it produces the date as MMDDYYYY. How do I change the report so all dates are in DDMMYYYY format please?

Many Thanks
Lee
0
Comment
Question by:ljhodgett
  • 7
  • 7
  • 2
  • +2
19 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34196331
Hi,

did you tried this.

select replace(convert(varchar(10),getdate(),103),'/','')
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34196345
=Format(Fields!myDate.Value, “MM/dd/yyyy”)
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34196379
Format(Fields!SSS.Value),"DDMMYYYY")
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34196395
Hi,

if you want to use convert then you need to change in query.

select replace(convert(varchar(10),getdate(),103),'/','')datecolumn,othercolumn
from tablename
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34196396
yup..Brichsoft is right, I misread the format you wanted.
0
 

Author Comment

by:ljhodgett
ID: 34196870
The stored procedure I'm using is setup for dmy. I've added a screen shot of the error so hopefully that'll help as i'm not sure where i need to place the above codes as i'm quite new to reporting server but familiar with sql.

Best Regards
Lee
ss.JPG
0
 

Author Comment

by:ljhodgett
ID: 34196885
If I select 01/01/10 and 02/01/10 it seems to work almost looking like something is processing the date format incorrectly.

Best Regards
Lee
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34197075
Hi,

can you show your procdure???
0
 

Author Comment

by:ljhodgett
ID: 34197213
The Procedure is:

ALTER PROCEDURE [dbo].[Report_ProcessPerformance]
      @DateStart datetime,
      @DateEnd datetime,
      @SelectionOptions tinyint=0,
      @IncludeIncircuitTest bit = 0,
      @GroupByTime tinyint = 0,
      @StageID uniqueidentifier = NULL,
      @AssemblyProductType varchar(50) = NULL,
      @Variants bit = 0

 AS
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/*
       The procedure designed to pull out and calculate data from Testdata and SPEA database.
Report will be compiled according to the parameters supplied. The report allows selection of specific
product or line (multiple products) filtering.
The Stored Procedue is based on Report_PCBModulesPerformance_v3.
The Report calculate true process yield/FTP i.e. what products passed cleanly through all stages without
having a False failure on the equipment or a repair action.

Expects parameters:
      @DateStart - start date and time
      @DateEnd - end date and time
      @SelectionOptions - what to include into the report , 1 - PCB, 2 - Drive , 0 - both, PCB and DRIVE
      @IncludeIncircuitTest - specifies whether data from SPEA database will be combined with the report data,
                             if stage 510 is selected and @IncludeIncircuitTest = 0 then data will only be selected from SPEA database
      @GroupByTime - grouping flag, as follows      
                           0 No group   0 No group
                           1 Day           1 Stage -REMOVED
                           2 Week        2 Product
                           3 Month        3 Stage & Product -REMOVED
                  grouping flag will be combination of both;
                        0 - no grouping
                        1 - Product only
                        10 - Day
                        11 - Day & Product
                        20 - Week
                        21 - Week & Product                  
                        30 - Month
                        31 - Month & Product
      @StageID - stage ID number (default NULL), if NULL all stage will be included in the report
      @AssemblyProductType - assembly or product type (search using LIKE, therefore wildcards are allowed)
      @Variants - specifies whether to include variants in the report, logical value. 1 - Include, 0 - not included

Returns recordset:
      
      UnitName - product or assembly type, if not groupped by, records contain NULL      
      StageID - stage ID
      DateTest - date or week number or month number as
                  dd/mm/yyyy       for dates
                  ww - yy        for weeks
                  mm - yy            for month
             if not groupped by, record contains 0
      Stage - Stage name and number as number - stage name , if not groupped by, records contain NULL      
      FTP_Percents -       first time pass percentage (as float)
      FTF_Percents - first time failure percentage (as float) 100 -  FTP_Percents
      PCBYield         - yeild deficit percentage (as float)
      TestDef           - test deficit percentage (as float)
      FTP                - number of first time pass (as int)
      Retest                - number of retest pass (as int)
      Repaired         - number of repaired pass (as int)
      Failed                - number of failures (currently waiting repair or retest) ( as int)
      TotalCount      - total units tested

USAGE:
      Test Engineering Intranet Site.

Created By: R.Meigh
Date:            02/07/2009

Modified by:      R.Meigh      
Date: 08/09/2009
      -      Modified to use dbo.Test_UnitRegister exclusively.
      -      TestDeficit total also recalculated

20/10/09      -      RM      - Use of function IsWithinTimeslot removed as it caused query to hang after date of 1st Oct 2009???

19/11/09    -   RM  - Updated query to include 'Test_UnitRegister.FTPStatus = 7' for SCRAPPED Product.
*/

ALTER PROCEDURE [dbo].[Report_ProcessPerformance]
      @DateStart datetime,
      @DateEnd datetime,
      @SelectionOptions tinyint=0,
      @IncludeIncircuitTest bit = 0,
      @GroupByTime tinyint = 0,
      @StageID uniqueidentifier = NULL,
      @AssemblyProductType varchar(50) = NULL,
      @Variants bit = 0

 AS

-- initial settings
SET NOCOUNT ON;
SET DATEFIRST 1;
SET DATEFORMAT dmy;

      --Added to log usage of stored procedure LH - 15/04/09
      DECLARE @ProcedureName nvarchar(60)
      SET @ProcedureName = (select name from sysobjects  where id= @@procid)
      EXEC AddSPusage @ProcedureName = @ProcedureName

-- declarations
DECLARE @DynamicPart1 as nvarchar(max);                  -- testdata processor

DECLARE @VariantCut int;                        -- cut variant 8, full assembly and variant 12

/* RM 20/10/09
-- if time is supplied, other than from 00:00:00 to 23:59:59 then indicate time search

DECLARE @TimeSlotCheck bit;                  -- time slot check, whether we need to check time when searching
DECLARE @TimeSlotStart datetime, @TimeSlotEnd datetime;

IF  CONVERT(char(8),@DateStart,108)<>'00:00:00' OR  CONVERT(char(8),@DateEnd,108) <> '23:59:59'
BEGIN
      SET @TimeSlotCheck = 1;
      SELECT       @TimeSlotStart = @DateStart,@TimeSlotEnd = @DateEnd;

      SET @DateStart = convert(datetime,CONVERT( char(10),@DateStart, 103 ) + ' 00:00:00');
      SET @DateEnd = convert(datetime,CONVERT( char(10), @DateEnd, 103 ) + ' 23:59:59');
END
ELSE
      SET @TimeSlotCheck = 0;
*/
-- declare switches
DECLARE @ProductSelectTable bit;

-- how we cut the variant
SELECT @VariantCut = CASE @Variants WHEN 0 THEN 8 ELSE 12 END;

-- if default groupping applied then group by Product
IF @GroupByTime = 0
      SET @GroupByTime =1;

-- sort out product selection
CREATE TABLE #ProductSelector (Product varchar(24));

SET @AssemblyProductType = REPLACE(@AssemblyProductType,'%','');

SET @ProductSelectTable= 0;
IF UPPER(SUBSTRING(@AssemblyProductType,1,5))='LINE:'
BEGIN
      INSERT INTO #ProductSelector
      SELECT ProductType FROM Admin_LineProducts WHERE UPPER(LineName) = UPPER(REPLACE(@AssemblyProductType,'LINE:',''));
      SET @ProductSelectTable = 1;

END

-- grouping declarations
DECLARE @groupStageSelect_T nvarchar(250),
      @groupStageGroup_T nvarchar(250),
      @groupProductSelect_T nvarchar(1000),
      @groupProductGroup_T nvarchar(1000),
      @groupDateSelect_T nvarchar(250),
      @groupDateGroup_T nvarchar(250);

DECLARE @groupStageSelect_Q nvarchar(250),
      @groupStageGroup_Q nvarchar(250),
      @groupProductSelect_Q nvarchar(1000),
      @groupProductGroup_Q nvarchar(1000),
      @groupDateSelect_Q nvarchar(250),
      @groupDateGroup_Q nvarchar(250);



-- default groupping parameters
SET @groupStageSelect_T = 'NULL AS StageID, '
SET @groupStageGroup_T = '';      
SET @groupProductSelect_T = ' NULL AS UnitName, '
SET @groupProductGroup_T = ''
SET @groupDateSelect_T = 'NULL as DayTest,';
SET @groupDateGroup_T = '';

SET @groupStageSelect_Q = 'NULL AS StageID, '
SET @groupStageGroup_Q = '';      
SET @groupProductSelect_Q = ' NULL AS UnitName, '
SET @groupProductGroup_Q = ''
SET @groupDateSelect_Q = 'NULL as DayTest,';
SET @groupDateGroup_Q = '';

IF RIGHT(CAST(@GroupByTime as varchar),1)='1' --OR RIGHT(CAST(@GroupByTime as varchar),1) = '3'            -- by product
BEGIN
      SET @groupProductSelect_T = 'CASE TestData.dbo.Test_UnitRegister.TransactionNo WHEN 1 THEN CASE WHEN TestData.dbo.Test_UnitRegister.ProductType IS NULL
                                    THEN ''''Product Not Known'''' ELSE TestData.dbo.Test_UnitRegister.ProductType END ELSE CASE WHEN TestData.dbo.Test_UnitRegister.[Assembly] IS NULL
                                    THEN ''''PCB Not Known'''' ELSE SUBSTRING(testdata.dbo.Test_UnitRegister.[Assembly], 1, @VariantCut) END END
                                    AS UnitName, ';

      SET @groupProductGroup_T = 'CASE TestData.dbo.Test_UnitRegister.TransactionNo WHEN 1 THEN CASE WHEN TestData.dbo.Test_UnitRegister.ProductType IS NULL
                                    THEN ''''Product Not Known'''' ELSE TestData.dbo.Test_UnitRegister.ProductType END ELSE CASE WHEN TestData.dbo.Test_UnitRegister.[Assembly] IS NULL
                                    THEN ''''PCB Not Known'''' ELSE SUBSTRING(testdata.dbo.Test_UnitRegister.[Assembly], 1, @VariantCut) END END, ';


END


-- assign groupping by dates
IF LEFT(CAST(@GroupByTime as varchar),1)='1' AND @GroupByTime>=10            -- by DAY
BEGIN      
      -- ass backwards conversion in order to allow for correct ORDER BY and keep the output as string
      SET @groupDateSelect_T = 'convert(datetime, CONVERT( char(10), Test_UnitRegister.RouteCompDate, 103 )) AS DayTest,';
      SET @groupDateGroup_T = 'convert(datetime, CONVERT( char(10), Test_UnitRegister.RouteCompDate, 103 )),';

      SET @groupDateSelect_Q = 'convert(datetime, CONVERT( char(10), Test_UnitRegister.RouteCompDate, 103 )) AS DayTest,';
      SET @groupDateGroup_Q = 'convert(datetime, CONVERT( char(10), Test_UnitRegister.RouteCompDate, 103 )),';

END

IF LEFT(CAST(@GroupByTime as varchar),1)='2' AND @GroupByTime>=10            -- by WEEK
BEGIN
      SET @groupDateSelect_T = 'RIGHT(CAST(YEAR(Test_UnitRegister.RouteCompDate) as varchar(4)),2) + '''' - ''''+ RIGHT(''''0''''+CAST(DATEPART(wk,Test_UnitRegister.RouteCompDate) as varchar(2)),2) AS DayTest,';
      SET @groupDateGroup_T = 'RIGHT(CAST(YEAR(Test_UnitRegister.RouteCompDate) as varchar(4)),2) + '''' - ''''+ RIGHT(''''0''''+CAST(DATEPART(wk,Test_UnitRegister.RouteCompDate) as varchar(2)),2),';

      SET @groupDateSelect_Q = 'RIGHT(CAST(YEAR(Test_UnitRegister.RouteCompDate) as varchar(4)),2) + '''' - ''''+ RIGHT(''''0''''+CAST(DATEPART(wk,Test_UnitRegister.RouteCompDate) as varchar(2)),2) AS DayTest,';
      SET @groupDateGroup_Q = 'RIGHT(CAST(YEAR(Test_UnitRegister.RouteCompDate) as varchar(4)),2) + '''' - ''''+ RIGHT(''''0''''+CAST(DATEPART(wk,Test_UnitRegister.RouteCompDate) as varchar(2)),2),';
END

IF LEFT(CAST(@GroupByTime as varchar),1)='3' AND @GroupByTime>=10            -- by YEAR
BEGIN
      SET @groupDateSelect_T = 'RIGHT(CAST(YEAR(Test_UnitRegister.RouteCompDate) as varchar(4)),2) + '''' - ''''+ RIGHT(''''0''''+CAST(MONTH(Test_UnitRegister.RouteCompDate) as varchar(2)),2) AS DayTest,';
      SET @groupDateGroup_T = 'RIGHT(CAST(YEAR(Test_UnitRegister.RouteCompDate) as varchar(4)),2) + '''' - ''''+ RIGHT(''''0''''+CAST(MONTH(Test_UnitRegister.RouteCompDate) as varchar(2)),2),';

      SET @groupDateSelect_Q = 'RIGHT(CAST(YEAR(Test_UnitRegister.RouteCompDate) as varchar(4)),2) + '''' - ''''+ RIGHT(''''0''''+CAST(MONTH(Test_UnitRegister.RouteCompDate) as varchar(2)),2) AS DayTest,';
      SET @groupDateGroup_Q = 'RIGHT(CAST(YEAR(Test_UnitRegister.RouteCompDate) as varchar(4)),2) + '''' - ''''+ RIGHT(''''0''''+CAST(MONTH(Test_UnitRegister.RouteCompDate) as varchar(2)),2),';
END

/* Status can be:
     255 - unknown status, incorrect feed from user
     0 - Passed
     1 - Retested, Passed
     2 - Repaired awaiting retest
     3 - Failed
     4 - Aborted
     5 - Repaired Passed
     6 - Failed then next retest aborted
       7 - SCRAPPED*/

/*QSOFT Status
      10      - first tested, passed
      20      - retested, passed
      30      - repaired, passed
      40      - rejected
      50      - failed, awaiting repair (can be a Retest Pass if in ProLog.LproResult is -1 )
      60    - repaired, awaiting retest


*/
--If Spea Test Results are to be included then...
      IF @SelectionOptions <>2
            BEGIN
--RM 08/09/09 START
                  SET @DynamicPart1 = ' SELECT
                              ' + @groupDateSelect_T + @groupStageSelect_T +@groupProductSelect_T + '
                                CAST(SUM(CASE WHEN (testdata.dbo.Test_UnitRegister.QsoftFTP = 1 OR
                                testdata.dbo.Test_UnitRegister.QsoftFTP IS NULL OR testdata.dbo.Test_UnitRegister.QsoftStatus = 10) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 1 THEN 1 ELSE 0 END) AS float)
                              AS FTP,
                                CAST(SUM((CASE CASE WHEN (testdata.dbo.Test_UnitRegister.QsoftFTP = 1 OR
                                testdata.dbo.Test_UnitRegister.QsoftFTP IS NULL) AND
                                testdata.dbo.Test_UnitRegister.FirstTimePass = 1 THEN 1 ELSE 0 END WHEN 1 THEN 0 ELSE CASE WHEN (testdata.dbo.Test_UnitRegister.QsoftStatus = 30 AND
                                testdata.dbo.Test_UnitRegister.QsoftFTP = 0) OR
                                ((testdata.dbo.Test_UnitRegister.FTPStatus = 5 OR testdata.dbo.Test_UnitRegister.FTPStatus = 7 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 2) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0)
                                THEN 0 ELSE CASE WHEN (testdata.dbo.Test_UnitRegister.QsoftStatus = 50 AND testdata.dbo.Test_UnitRegister.QsoftFTP = 0) OR
                                ((testdata.dbo.Test_UnitRegister.FTPStatus = 3 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 4 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 6) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0)
                                THEN 0 ELSE CASE WHEN (testdata.dbo.Test_UnitRegister.FTPStatus = 1 AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0) OR
                                (testdata.dbo.Test_UnitRegister.QsoftStatus = 20 AND testdata.dbo.Test_UnitRegister.QsoftFTP = 0) THEN 1 ELSE 0 END END END END)) AS float)
                              AS Retest,
                                CAST(SUM((CASE CASE WHEN (testdata.dbo.Test_UnitRegister.QsoftFTP = 1 OR
                                testdata.dbo.Test_UnitRegister.QsoftFTP IS NULL) AND
                                testdata.dbo.Test_UnitRegister.FirstTimePass = 1 THEN 1 ELSE 0 END WHEN 1 THEN 0 ELSE CASE WHEN ((testdata.dbo.Test_UnitRegister.QsoftStatus = 30 OR
                                testdata.dbo.Test_UnitRegister.QsoftStatus = 60) AND testdata.dbo.Test_UnitRegister.QsoftFTP = 0) OR
                                ((testdata.dbo.Test_UnitRegister.FTPStatus = 5 OR testdata.dbo.Test_UnitRegister.FTPStatus = 7 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 2) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0) THEN 1 ELSE 0 END END)) AS float)
                              AS Repaired,
                                CAST(SUM((CASE CASE WHEN (testdata.dbo.Test_UnitRegister.QsoftFTP = 1 OR
                                testdata.dbo.Test_UnitRegister.QsoftFTP IS NULL) AND
                                testdata.dbo.Test_UnitRegister.FirstTimePass = 1 THEN 1 ELSE 0 END WHEN 1 THEN 0 ELSE CASE WHEN (testdata.dbo.Test_UnitRegister.QsoftStatus = 30 AND
                                testdata.dbo.Test_UnitRegister.QsoftFTP = 0) OR
                                ((testdata.dbo.Test_UnitRegister.FTPStatus = 5 OR testdata.dbo.Test_UnitRegister.FTPStatus = 7 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 2) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0)
                                THEN 0 ELSE CASE WHEN (testdata.dbo.Test_UnitRegister.QsoftStatus = 50 AND testdata.dbo.Test_UnitRegister.QsoftFTP = 0) OR
                                ((testdata.dbo.Test_UnitRegister.FTPStatus = 3 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 4 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 6) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0) THEN 1 ELSE 0 END END END)) AS float)
                              AS Failed,
                                CAST(SUM((CASE WHEN (testdata.dbo.Test_UnitRegister.QsoftStatus > 60 AND testdata.dbo.Test_UnitRegister.QsoftFTP = 0) OR
                                testdata.dbo.Test_UnitRegister.FTPStatus > 7 THEN 1 ELSE 0 END)) AS float)
                            AS OtherFaults,
                                CAST(SUM(1) AS float) AS TotalCount
                  FROM    testdata.dbo.Test_UnitRegister ';
--RM 08/09/09 END
/*                  SET @DynamicPart1 = ' SELECT
                              ' + @groupDateSelect_T + @groupStageSelect_T +@groupProductSelect_T + '
                                CAST(SUM(CASE WHEN (qsoft.dbo.UniLog.LUniFTP = 1 OR
                                qsoft.dbo.UniLog.LUniFTP IS NULL) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 1 THEN 1 ELSE 0 END) AS float)
                              AS FTP,
                                CAST(SUM((CASE CASE WHEN (qsoft.dbo.UniLog.LUniFTP = 1 OR
                                qsoft.dbo.UniLog.LUniFTP IS NULL) AND
                                testdata.dbo.Test_UnitRegister.FirstTimePass = 1 THEN 1 ELSE 0 END WHEN 1 THEN 0 ELSE CASE WHEN (qsoft.dbo.UniLog.LUniStatus = 30 AND
                                qsoft.dbo.UniLog.LUniFTP = 0) OR
                                ((testdata.dbo.Test_UnitRegister.FTPStatus = 5 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 2) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0)
                                THEN 0 ELSE CASE WHEN (qsoft.dbo.UniLog.LUniStatus = 50 AND qsoft.dbo.UniLog.LUniFTP = 0) OR
                                ((testdata.dbo.Test_UnitRegister.FTPStatus = 3 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 4 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 6) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0)
                                THEN 0 ELSE CASE WHEN (testdata.dbo.Test_UnitRegister.FTPStatus = 1 AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0) OR
                                (qsoft.dbo.UniLog.LUniStatus = 20 AND qsoft.dbo.UniLog.LUniFTP = 0) THEN 1 ELSE 0 END END END END)) AS float)
                              AS Retest,
                                CAST(SUM((CASE CASE WHEN (qsoft.dbo.UniLog.LUniFTP = 1 OR
                                qsoft.dbo.UniLog.LUniFTP IS NULL) AND
                                testdata.dbo.Test_UnitRegister.FirstTimePass = 1 THEN 1 ELSE 0 END WHEN 1 THEN 0 ELSE CASE WHEN ((qsoft.dbo.UniLog.LUniStatus = 30 OR
                                qsoft.dbo.UniLog.LUniStatus = 60) AND qsoft.dbo.UniLog.LUniFTP = 0) OR
                                ((testdata.dbo.Test_UnitRegister.FTPStatus = 5 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 2) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0) THEN 1 ELSE 0 END END)) AS float)
                              AS Repaired,
                                CAST(SUM((CASE CASE WHEN (qsoft.dbo.UniLog.LUniFTP = 1 OR
                                qsoft.dbo.UniLog.LUniFTP IS NULL) AND
                                testdata.dbo.Test_UnitRegister.FirstTimePass = 1 THEN 1 ELSE 0 END WHEN 1 THEN 0 ELSE CASE WHEN (qsoft.dbo.UniLog.LUniStatus = 30 AND
                                qsoft.dbo.UniLog.LUniFTP = 0) OR
                                ((testdata.dbo.Test_UnitRegister.FTPStatus = 5 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 2) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0)
                                THEN 0 ELSE CASE WHEN (qsoft.dbo.UniLog.LUniStatus = 50 AND qsoft.dbo.UniLog.LUniFTP = 0) OR
                                ((testdata.dbo.Test_UnitRegister.FTPStatus = 3 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 4 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 6) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0) THEN 1 ELSE 0 END END END)) AS float)
                              AS Failed,
                                CAST(SUM((CASE WHEN (qsoft.dbo.UniLog.LUniStatus > 60 AND qsoft.dbo.UniLog.LUniFTP = 0) OR
                                testdata.dbo.Test_UnitRegister.FTPStatus > 6 THEN 1 ELSE 0 END)) AS float)
                            AS OtherFaults,
                                CAST(SUM(1) AS float) AS TotalCount
                  FROM         qsoft.dbo.UniLog RIGHT OUTER JOIN
                                testdata.dbo.Test_UnitRegister ON qsoft.dbo.UniLog.LUniSN COLLATE database_default = testdata.dbo.Test_UnitRegister.SerialNumber';
*/
            END
      ELSE --We Don't need to Include QSOFT DB Results since we are only Reporting on MODULES
            BEGIN
                  SET @DynamicPart1 = ' SELECT
                              ' + @groupDateSelect_T + @groupStageSelect_T +@groupProductSelect_T + '
                                CAST(SUM(CASE WHEN testdata.dbo.Test_UnitRegister.FirstTimePass = 1 THEN 1 ELSE 0 END) AS float)
                              AS FTP,
                                CAST(SUM((CASE CASE WHEN testdata.dbo.Test_UnitRegister.FirstTimePass = 1 THEN 1 ELSE 0 END WHEN 1 THEN 0 ELSE CASE WHEN ((testdata.dbo.Test_UnitRegister.FTPStatus = 5 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 7 OR testdata.dbo.Test_UnitRegister.FTPStatus = 2) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0)
                                THEN 0 ELSE CASE WHEN ((testdata.dbo.Test_UnitRegister.FTPStatus = 3 OR testdata.dbo.Test_UnitRegister.FTPStatus = 4 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 6) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0)
                                THEN 0 ELSE CASE WHEN (testdata.dbo.Test_UnitRegister.FTPStatus = 1 AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0) THEN 1 ELSE 0 END END END END)) AS float)
                              AS Retest,
                                CAST(SUM((CASE CASE WHEN testdata.dbo.Test_UnitRegister.FirstTimePass = 1 THEN 1 ELSE 0 END WHEN 1 THEN 0 ELSE CASE WHEN ((testdata.dbo.Test_UnitRegister.FTPStatus = 5 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 7 OR testdata.dbo.Test_UnitRegister.FTPStatus = 2) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0) THEN 1 ELSE 0 END END)) AS float)
                              AS Repaired,
                                CAST(SUM((CASE CASE WHEN testdata.dbo.Test_UnitRegister.FirstTimePass = 1 THEN 1 ELSE 0 END WHEN 1 THEN 0 ELSE CASE WHEN
                                ((testdata.dbo.Test_UnitRegister.FTPStatus = 5 OR testdata.dbo.Test_UnitRegister.FTPStatus = 7 OR testdata.dbo.Test_UnitRegister.FTPStatus = 2) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0)
                                THEN 0 ELSE CASE WHEN  ((testdata.dbo.Test_UnitRegister.FTPStatus = 3 OR testdata.dbo.Test_UnitRegister.FTPStatus = 4 OR
                                testdata.dbo.Test_UnitRegister.FTPStatus = 6) AND testdata.dbo.Test_UnitRegister.FirstTimePass = 0) THEN 1 ELSE 0 END END END)) AS float)
                              AS Failed,
                                CAST(SUM((CASE WHEN testdata.dbo.Test_UnitRegister.FTPStatus > 6 THEN 1 ELSE 0 END)) AS float)
                            AS OtherFaults,
                                CAST(SUM(1) AS float) AS TotalCount
                  FROM         testdata.dbo.Test_UnitRegister ';
      
            END

      IF @ProductSelectTable = 1            -- if product selector required then inner join with product selector      
                  SET @DynamicPart1=@DynamicPart1+N' INNER JOIN #ProductSelector p1 ON (LEFT(Test_UnitRegister.Assembly,LEN(p1.Product)) = p1.Product or Test_UnitRegister.ProductType = p1.Product)';

      SET @DynamicPart1=@DynamicPart1+N'
            WHERE     (testdata.dbo.Test_UnitRegister.RouteCompDate BETWEEN @DateStart AND @DateEnd) ';
/* RM 20/10/09
      -- if time slot check is necessary then add condition to check the time slot by using function IsWithinTimeSlot
      IF @TimeSlotCheck=1
            SET @DynamicPart1=@DynamicPart1+N'
            AND (testdata.dbo.IsWithinTimeSlot(@TimeSlotStart, @TimeSlotEnd, testdata.dbo.Test_UnitRegister.RouteCompDate) = 1) ';
*/
      -- Product or PCB and SEARCH parameter
      IF @SelectionOptions=0 AND @AssemblyProductType IS NOT NULL AND @ProductSelectTable =0 --BOTH
            SET @DynamicPart1=@DynamicPart1+N' AND (LEFT(Test_UnitRegister.Assembly,LEN(@AssemblyProductType)) = @AssemblyProductType OR LEFT(Test_UnitRegister.ProductType,LEN(@AssemblyProductType)) = @AssemblyProductType)';
      IF @SelectionOptions=1 AND @AssemblyProductType IS NOT NULL AND @ProductSelectTable =0 --PCB Only
            SET @DynamicPart1=@DynamicPart1+N' AND (LEFT(Test_UnitRegister.Assembly,LEN(@AssemblyProductType)) = @AssemblyProductType)';
      IF @SelectionOptions=2 AND @AssemblyProductType IS NOT NULL AND @ProductSelectTable =0 --Module Only
            SET @DynamicPart1=@DynamicPart1+N' AND (LEFT(Test_UnitRegister.ProductType,LEN(@AssemblyProductType)) = @AssemblyProductType)';

      -- LIMIT Data to PCB or MODULES
      IF @SelectionOptions=1 --PCB Only
            SET @DynamicPart1=@DynamicPart1+N' AND (Test_UnitRegister.TransactionNo = 2)';
      IF @SelectionOptions=2 --PRODUCT Only
            SET @DynamicPart1=@DynamicPart1+N' AND (Test_UnitRegister.TransactionNo = 1)';
      
      SET @DynamicPart1=@DynamicPart1+N'            
            GROUP BY  ' + @groupDateGroup_T + @groupStageGroup_T + @groupProductGroup_T;
            
      IF RIGHT(RTRIM(@DynamicPart1),1) = ','
            SET @DynamicPart1 = SUBSTRING(RTRIM(@DynamicPart1),1,LEN(RTRIM(@DynamicPart1))-1);


-- prepare for passing patameters as string, a lot quicker than PREPARE
DECLARE @PassAssemblyProductType nvarchar(100);

IF @AssemblyProductType IS NOT NULL
      SET @PassAssemblyProductType = ',@AssemblyProductType = ''' + @AssemblyProductType+ '''' ELSE SET @PassAssemblyProductType = ',@AssemblyProductType = NULL';

DECLARE @SqlPart1 nvarchar(max),
      @SqlPart2 nvarchar(max),
      @SqlPart3 nvarchar(max),
      @SqlPart4 nvarchar(max),
      @SqlPart5 nvarchar(max),
      @SqlPart6 nvarchar(max);

-- or simply perform one search
IF @DynamicPart1 IS NOT NULL
      SET @SqlPart2 = @DynamicPart1 ELSE SET @SqlPart2 = '';

IF @DynamicPart1 IS NULL RETURN;

-- Produce calculation

SET @SqlPart1=N'SELECT UnitName AS UnitName,StageID AS StageID,DayTest AS DateTest, ';


      SET @SqlPart1 = @SqlPart1 + 'NULL as Stage,'

SET @SqlPart1 = @SqlPart1+ '
CASE WHEN (Sum(TotalCount))>0 THEN ROUND( (Sum(FTP)/(Sum(TotalCount)) )*100,2) ELSE 0 END AS FTP_Percents,
CASE WHEN (Sum(TotalCount))>0 THEN ROUND( (sum(TotalCount)-sum(FTP))/sum(TotalCount)*100,2) ELSE 0 END AS FTF_Percents,
CASE WHEN (Sum(TotalCount))>0 THEN 100 - ROUND( (sum(FTP)+sum(Retest)+sum(OtherFaults)) / sum(TotalCount) * 100,2) ELSE 0 END As PCBYield,
CASE WHEN (Sum(TotalCount))>0 THEN       CASE ((sum(FTP)+sum(Retest))/sum(TotalCount)) WHEN 0 THEN 0 ELSE ROUND((sum(Retest)/sum(TotalCount)) *100,2) END ELSE 0 END as TestDef,
CAST(sum(FTP) as int) AS FTP,
CAST(sum(Retest) as int) AS Retest,
CAST(sum(Repaired) as int) AS Repaired,
CAST(sum(Failed) as int) AS Failed,
CAST(sum(OtherFaults) as int) AS OtherFaults,
CASE WHEN (Sum(TotalCount))>0 THEN ROUND( (Sum(OtherFaults)/(Sum(TotalCount)) )*100,2) ELSE 0 END AS Other_Percents,
sum(TotalCount) AS TotalCount
            FROM ( ' ;

--CASE WHEN (Sum(TotalCount))>0 THEN CASE ((sum(FTP)+sum(Retest))/sum(TotalCount)) WHEN 0 THEN 0 ELSE      100-ROUND(((sum(FTP)/sum(TotalCount)/((sum(FTP)+sum(Retest)+sum(OtherFaults))/sum(TotalCount)))) *100,2) END ELSE 0 END as TestDef,
--RM 08/09/09 above redefined


SET @SqlPart5  = ' ) as ResultSearch  '

SET @SqlPart5  = @SqlPart5 + ' GROUP BY  UnitName,StageID,DayTest';

SET @SqlPart6 = ' ORDER BY DayTest,UnitName';

/*print @SqlPart1
print @SqlPart2
print @SqlPart3
print @SqlPart4
print @SqlPart5
print @SqlPart6
print'--'
print @variantcut
print @DateStart
print @Dateend
print @AssemblyProductType
*/

print @DynamicPart1
print @groupDateGroup_T
print @groupStageGroup_T
print @groupProductGroup_T
/* RM 20/10/09
EXEC ('sp_executesql N''' +@SqlPart1+@SqlPart2+@SqlPart3+@SqlPart4+@SqlPart5+ @SqlPart6 + ''',N''@VariantCut int,@DateStart datetime, @DateEnd Datetime,@AssemblyProductType varchar(24), @TimeSlotStart datetime, @TimeSlotEnd datetime'',
      @VariantCut = '+ @VariantCut + ',
      @DateStart = '''+ @DateStart + ''',
      @DateEnd = '''+ @DateEnd  + ''',
      @TimeSlotStart = '''+ @TimeSlotStart  + ''',
      @TimeSlotEnd = '''+ @TimeSlotEnd  + '''' +
      @PassAssemblyProductType
*/
EXEC ('sp_executesql N''' +@SqlPart1+@SqlPart2+@SqlPart3+@SqlPart4+@SqlPart5+ @SqlPart6 + ''',N''@VariantCut int,@DateStart datetime, @DateEnd Datetime,@AssemblyProductType varchar(24)'',
      @VariantCut = '+ @VariantCut + ',
      @DateStart = '''+ @DateStart + ''',
      @DateEnd = '''+ @DateEnd  + '''' +
      @PassAssemblyProductType
);

DROP TABLE #ProductSelector;

0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:ljhodgett
ID: 34197224
Bit of a long stored procedure lol. I've added another screen shot as when I run the query to a dataset it returns the data correctly when I put the parameter in the screen shot.

Best Regards
Lee
ss2.JPG
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34197806
I will get back to u shortly
0
 
LVL 3

Accepted Solution

by:
expert_dharam earned 500 total points
ID: 34201889

Hi ljhodgett,

'@DateStart' and '@DateEnd' input parameters in your stored procedures are defined as 'datetime' data-type.
Which is by default US standard i.e. 'mm/dd/yyyy'.

If you want to pass the date in 'dd/mm/yyyy' format, you need to modify your stored procedures.

Steps for modification:

[1] Rename '@DateStart' and '@DateEnd' with '@DateStartParam' and '@DateEndParam' respectively only in the opening section of the procedure.

E.g.
ALTER PROCEDURE [dbo].[Report_ProcessPerformance]
      @DateStartParam varchar(10),
      @DateEndParam varchar(10),



[2] Declare two more variables after the 'AS' statement as mentioned below:
      Declare @DateStart DateTime
      Declare @DateEnd DateTime

E.g.
ALTER PROCEDURE [dbo].[Report_ProcessPerformance]
      @DateStartParam varchar(10),
      @DateEndParam varchar(10),
      .
      .
      .
      .
      AS
      Declare @DateStart DateTime
      Declare @DateEnd DateTime

[3] Create a logic which will convert 'dd/mm/yyyy' value in '@DateStartParam' and '@DateStartParam' into 'mm/dd/yyyy' format.


[4] SET the transformed 'mm/dd/yyyy' value to '@DateStart' and '@DateEnd' respectively.
0
 
LVL 1

Expert Comment

by:amarsale
ID: 34202641
1) Go to Dataset tab
2) click on edit selected Dataset (button next to the dataset name with 3 dots)
3) go to parameters tab
4) for both Parameters DateEnd and DateStart place following expression in the value field resp.
=Dateserial(mid(Parameters!DateStart.Value,7,4),mid(Parameters!DateStart.Value,4,2),mid(Parameters!DateStart.Value,1,2))
=Dateserial(mid(Parameters!DateEnd.Value,7,4),mid(Parameters!DateEnd.Value,4,2),mid(Parameters!DateEnd.Value,1,2))
5) click ok.
0
 

Author Comment

by:ljhodgett
ID: 34203407
Hi Expert_dharam, Amarsale

Unfortunately I cant really change the stored procedure as this is a production machine and would involve changing a lot of code on the production machines im afraid. I am a little confused though as if this was the case surely the query in the dataset as shown in SS2.jpg above would fail as that is in british format?

I've also tried to but the dateserial commands in and no joy either i'm afraid as it comes up with the same error.

Best Regards
Lee
0
 
LVL 3

Expert Comment

by:expert_dharam
ID: 34204112
To be on a safe side, create a copy of the stored procedure and test it.
Generate 'Create To' script, rename the SP name and create.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34204179
Hi,
 
you wanted to to show as DDMMYYYY or you wanted to pass.

if you wanted to pass then dharam's method is right.

To set the date format in SSRS you have to set the language of the report, chnage the language to 'English (United Kingdom)'.

This blog may help:
http://bobp1339.blogspot.com/2007/10/multi-language-tips-in-ssrs.html
0
 

Author Comment

by:ljhodgett
ID: 34204866
Hi,

I'm just in the process of trying some of the above but how do I change the language of the report please? Is it a case of changing one of the property values etc?

One thing I have notice is the server is infact installed as English (United States) but my pc is set to British. When I click the calender next to the parameter on the preview window and select 24/11/10 it displays it like that and tries to pass it which errors, If I manually enter 11/24/10 it works perfectly.

Best Regards
Lee
0
 

Author Comment

by:ljhodgett
ID: 34205024
I've just changed my operating system to United States from British and it works be it when I go to the preview window of the SSRS and click the calender it shows the date in American formatting. Is there a way of showing the date on the calender as British formatting but parse the parameter as United States formatting please?

Many Thanks and sorry for the long winded question.

Best Regards
Lee
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34221987
Hi Lee,

I'm trying to find the answer,meanwhile please look at this link.

http://geekswithblogs.net/naijacoder/archive/2008/06/26/123422.aspx
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now