Open Forum

  • 1.  Excel Refreshable Dates

    GOLD CONTRIBUTOR
    Posted Sep 20, 2021 11:12 AM
    I have a MO Receipts summary table where it appears the dates are coming in as text fields.  Is there anyway to easily change this so we can apply date filters?

    ------------------------------
    Teresa Waters
    Controller
    La Crosse Milling Company
    Cochrane WI
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Excel Refreshable Dates

    TOP CONTRIBUTOR
    Posted Sep 21, 2021 09:08 AM
    Hi Teresa,

    Can you share the query that's being run?

    Thanks

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Excel Refreshable Dates

    GOLD CONTRIBUTOR
    Posted Sep 21, 2021 11:36 AM

    Hi John,

    Here is what is in the spreadsheet:

     select [MOP Receipt Number],[Manufacture Order],[Item Number],[Item Description],[Date Received],[Serial/Lot QTY],[Serial/Lot Number],[Expiration Date] from [LCM].dbo.erbGPMMOReceiptsSummaryv2




    ------------------------------
    Teresa Waters
    Controller
    La Crosse Milling Company
    Cochrane WI
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Excel Refreshable Dates

    TOP CONTRIBUTOR
    Posted Sep 21, 2021 12:05 PM
    Ah, the fun begins... The question now is, what's erbGPMMOReceiptsSummaryv2?  Is it a table or a view (I'm guessing it's a view)?

    Do you have access to the SQL behind view?


    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Excel Refreshable Dates

    GOLD CONTRIBUTOR
    Posted Sep 21, 2021 01:40 PM
    Edited by John Kirsch Sep 21, 2021 01:41 PM
    Hi Teresa,

    If you are looking to convert the field in a TSQL command, you can use the cast or convert functions.  Here are some examples using a text string which you can run in SQL.  When you find the format you like, you can replace the text string with the field name.    I prefer the last one as it will return the date in the format of 1/5/21 for that example.

    Select '1-5-21'
    Select Cast('1-5-21' as date)
    Select Cast('1-5-21' as datetime)
    Select Format(Cast('1-5-21' as datetime),'M/d/yy')

    From the name of the object - it appears that it may have been built in Excel Report Builder so you may want to make changes in that tool rather than in TSQL.

    ------------------------------
    John Kirsch
    GP Product Lead
    Dynamic Consulting
    Green Bay WI
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Excel Refreshable Dates

    TOP CONTRIBUTOR
    Posted Sep 21, 2021 02:49 PM
    Hi John,

    To start with, you have a great first name!  ;)

    In my experience, I highly recommend "not" casting the date to the format you want to appear on a report or in excel.  Reporting tools and Excel let you format the date to how you want it displayed.  If you format it in the query, the data being sent to the report or excel can end up being a string (VARCHAR) format instead of a date.  It may display as a date but you can't do any of the cool date things (group by date in pivot table for example) unless it's a date.

    And to be totally correct, excel likes SQL DATETIME data types.  It doesn't always like SQL DATE datatypes.

    Thanks

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Excel Refreshable Dates

    TOP CONTRIBUTOR
    Posted Sep 21, 2021 01:55 PM
    Hi Teresa,

    The query you sent was very helpful!  My suspicions were confirmed.

    The query had:  RTRIM(CAST(DATEPART(mm, MOP1100.DATERECD) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(dd, MOP1100.DATERECD) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(yy, MOP1100.DATERECD) AS VARCHAR(4))) AS [Date Received],

    to make the Date Received look pretty.  The only problem, it converts the data type from DateTime to a VARCHAR (string).  Excel wants the date time - it can then display it or filter on it as you'd like.  You can fix this by just returning the DATERECD field.  Note: The same problem exists with the Expiration Date field (EXPNDATE).

    If Excel is the only place using this query, there should be no problems updating the query.  If it's used by a report somewhere, it may be problem there.

    This query should work for you:  (The -- at the beginning of the two lines comments out that line)

    SELECT MOP1100.MOPRCTNM AS [MOP Receipt Number],
    MOP1100.MANUFACTUREORDER_I AS [Manufacture Order],
    WO010032.ITEMNMBR AS [Item Number],
    IV00101.ITEMDESC AS [Item Description],
    MOP1100.DATERECD AS [Date Received],
    --RTRIM(CAST(DATEPART(mm, MOP1100.DATERECD) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(dd, MOP1100.DATERECD) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(yy, MOP1100.DATERECD) AS VARCHAR(4))) AS [Date Received],
    MOP1040.SERLTQTY AS [Serial/Lot QTY],
    MOP1040.SERLTNUM AS [Serial/Lot Number],
    MOP1040.EXPNDATE AS [Expiration Date]--,
    --RTRIM(CAST(DATEPART(mm, MOP1040.EXPNDATE) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(dd, MOP1040.EXPNDATE) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(yy, MOP1040.EXPNDATE) AS VARCHAR(4))) AS [Expiration Date]
    FROM dbo.MOP1100 WITH (NOLOCK)
    INNER JOIN dbo.WO010032 WITH (NOLOCK)
    ON WO010032.MANUFACTUREORDER_I = MOP1100.MANUFACTUREORDER_I
    INNER JOIN dbo.IV00101 WITH (NOLOCK)
    ON IV00101.ITEMNMBR = WO010032.ITEMNMBR
    INNER JOIN dbo.MOP1040 WITH (NOLOCK)
    ON MOP1040.DOCNUMBR = MOP1100.MOPRCTNM

    Good Luck!




    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Excel Refreshable Dates

    TOP CONTRIBUTOR
    Posted Sep 21, 2021 02:52 PM

    Hopefully, this is more readable...

    SELECT	MOP1100.MOPRCTNM AS [MOP Receipt Number],
    		MOP1100.MANUFACTUREORDER_I AS [Manufacture Order],
    		WO010032.ITEMNMBR AS [Item Number],
    		IV00101.ITEMDESC AS [Item Description],
    		MOP1100.DATERECD AS [Date Received],
    		--RTRIM(CAST(DATEPART(mm, MOP1100.DATERECD) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(dd, MOP1100.DATERECD) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(yy, MOP1100.DATERECD) AS VARCHAR(4))) AS [Date Received],
    		MOP1040.SERLTQTY AS [Serial/Lot QTY],
    		MOP1040.SERLTNUM AS [Serial/Lot Number],
    		MOP1040.EXPNDATE AS [Expiration Date]--,
    		--RTRIM(CAST(DATEPART(mm, MOP1040.EXPNDATE) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(dd, MOP1040.EXPNDATE) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(yy, MOP1040.EXPNDATE) AS VARCHAR(4))) AS [Expiration Date]
    FROM	dbo.MOP1100 WITH (NOLOCK)
    JOIN	dbo.WO010032 WITH (NOLOCK)
    	ON	WO010032.MANUFACTUREORDER_I = MOP1100.MANUFACTUREORDER_I
    JOIN	dbo.IV00101 WITH (NOLOCK)
    	ON	IV00101.ITEMNMBR = WO010032.ITEMNMBR
    JOIN	dbo.MOP1040 WITH (NOLOCK)
    	ON	MOP1040.DOCNUMBR = MOP1100.MOPRCTNM​


    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Excel Refreshable Dates

    Posted Sep 22, 2021 08:29 AM
    If you don't have luck with the back-end SQL date correction steps you can try this low-tech method:
    In your Excel file, just add a new column to hold the date in the correct format and add a simple formula such as "=B2+0".  The table will convert it to read something like "=[@[TRX DATE]]+0" and it will automatically copy down for all rows of your Excel Refreshable Report.  Then just format data in that column in a date format.  Use this new field in your pivot tables instead of the original "text" data.

    jph


    ------------------------------
    Jeff Hassenboehler
    Definition6
    Atlanta GA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: Excel Refreshable Dates

    GOLD CONTRIBUTOR
    Posted Sep 27, 2021 04:00 PM

    Thanks,
    I looked at the SQL fix and chickened out, but the adding a calculated column in the excel report worked great and I was able to add a pivot table and restrictions there.
    Thanks everyone for your help!!!!



    ------------------------------
    Teresa Waters
    Controller
    La Crosse Milling Company
    Cochrane WI
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role