Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Set Date Format on SSRS Report

Posted on 2010-11-23
19
Medium Priority
?
872 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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
 

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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