Custom HQ "PO" Report - "Copy as Table" function

(0) Share

Report

Posted on by Community Member Microsoft Employee

We currently have a customised report running on HQ that gives us a detailed breakdown of all PO's across our stores. The breakdown shows us both the PO Date and the PO Required Date and is in format dd/mm/yyyy. However when the report values are exported into excel using the "Copy as Table" function, both the PO date and PO required date outputs show on occasion as a date&time output in excel i.e. dd/mm/yyyy hr:mm. Is there anyway we can restrict the RMS report values to only output to excel in dd/mm/yyyy format?

Both entries for the PO Date & PO Required Date are shown below:

Begin Column
   FieldName = "PurchaseOrder.DateCreated"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "PO Date"
   VBDataType = vbDate
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 990
   GroupMethod = groupmethodNone
   ColFormat =
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "PurchaseOrder.RequiredDate"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Req'd Date"
   VBDataType = vbDate
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1050
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Regards

Bruce Straughan

*This post is locked for comments

All responses (3)

Answers (1)

Suggested answer

Antonijo Todorovik 4,025 on at

Like (0)

Report

RE: Custom HQ "PO" Report - "Copy as Table" function

Hi again Bruce.

Great and thanks for sharing this with the forum. As I saed, I wasn't sure of the formula, as it depends on the SQL version...

Best regards, A.
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Custom HQ "PO" Report - "Copy as Table" function

Hi A,

Thank you for your reply,

I tried your three examples but unfortunately none of them worked for me, this maybe because we are currenty still using SQL 2005. I did manage to achieve what I needed though by using the script below.

"convert(varchar, PurchaseOrder.DateCreated, 103)"

Regards

Bruce
Antonijo Todorovik 4,025 on at

Like (0)

Report

RE: Custom HQ "PO" Report - "Copy as Table" function

Hi Bruce.

Try adding a convert tSQL function in the FORMULA field. Depending of the SQL Server version you use, the Formula can be:

- Formula = "CONVERT(date, PurchaseOrder.RequiredDate)"

- Formula = "DATEADD(dd, 0, DATEDIFF(dd, 0, PurchaseOrder.RequiredDate))"

- Formula = "CAST(PurchaseOrder.RequiredDate AS date)

etc...

Google out a tSQL conversion possibilitites and try with them.

Don't have time to test this, just trying to give you some ideas...

Kind regards, A.