Date format in data composition schema report

Common questions about 1C:Query language, Query builder tool and Data composition schema

Pages: 1

sarah carrey	#1 People who like this:0 Yes/0 No Jun 28, 2012 11:57 AM
Active user Rating: 4 Joined: Sep 16, 2011 Company: Oksley Shipping	Hi! I have a report built on data composition schema and need to group rows by month. When I group by date it is grouped by every day, while I need months. Also I need to format it from 6/27/2012 into June, 2012. Is there any ideas how can I do so?

Leksus	#2 People who like this:0 Yes/0 No Jun 28, 2012 12:01 PM
Active user Rating: 3 Joined: Nov 1, 2011 Company:	Show your query please

alexht

People who like this:0 Yes/0 No

Jun 28, 2012 12:40 PM

Active user

Rating: 4
Joined: Apr 18, 2012
Company:

Sample query:

Code
SEL ECT BEGINOFPERIOD(FirstDoc.Date, MONTH) AS Date, FirstDoc.Ref FR OM Document.FirstDoc AS FirstDoc

Format:
DF='MMMM yyyy'

sarah carrey

People who like this:0 Yes/0 No

Jun 28, 2012 01:02 PM

Active user

Rating: 4
Joined: Sep 16, 2011
Company: Oksley Shipping

Code

SEL ECT
   GeneralJournalBalance.ExtDimension1 AS Company,
   GeneralJournalBalance.ExtDimension1.Description AS CompanyID,
   GeneralJournalBalance.ExtDimension2 AS Document,
   GeneralJournalBalance.Currency,
   GeneralJournalBalance.AmountBalance * -1 AS TotalD_FCY,
   GeneralJournalBalance.AmountRCBalance * -1 AS TotalD,
   Companies.Description,
   ISNULL(ISNULL(PurchaseReturn.Date, PurchaseInvoice.Date), &EmptyDate) AS Date
FR OM
   AccountingRegister.GeneralJournal.Balance(
         &Period,
         ,
         ,
         ExtDimension2 REFS Document.PurchaseInvoice
            OR ExtDimension2 REFS Document.PurchaseReturn) AS GeneralJournalBalance
      LEFT JOIN Catalog.Companies AS Companies
      ON GeneralJournalBalance.ExtDimension1 = Companies.Ref
      LEFT JOIN Document.PurchaseInvoice AS PurchaseInvoice
      ON GeneralJournalBalance.ExtDimension2 = PurchaseInvoice.Ref
      LEFT JOIN Document.PurchaseReturn AS PurchaseReturn
      ON GeneralJournalBalance.ExtDimension2 = PurchaseReturn.Ref

sarah carrey	#5 People who like this:0 Yes/0 No Jun 28, 2012 01:15 PM
Active user Rating: 4 Joined: Sep 16, 2011 Company: Oksley Shipping	Thanks, alexht, I've got your idea, so I've added additional date field for grouping as you adviced. And where can I apply format you suggested in DCS?

alexht	#6 People who like this:0 Yes/0 No Jun 28, 2012 01:42 PM
Active user Rating: 4 Joined: Apr 18, 2012 Company:	In DCS on page "Data sets" column "Appearance"

Pages: 1

Users browsing this topic (guests: 1, registered: 0, hidden: 0)

Date format in data composition schema report

Download brochures