How to Use Date in Cross Tab (Sorting by Jan Feb March etc . . .)

mahmood66
mahmood66 used Ask the Experts™
on
dear, my report is correct only my problem is i want month Name(Jan, Feb, March . . . )  instead of Month#(1,2,3 . . .).
for reference your can see my screen short.
CrossTab.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Dear Experts
In case of i use MonthName(doc_Date) function then April and August are coming first because they are started with A alphabet. so my issue is i want to display Months wise like Jan, Feb, Mar etc.
Top Expert 2010

Commented:
Go to Cross Tab export, click Group Options on your column, then the Options tab, then check Customize Group name, use a formula as Group Name, and I'll think you'll figure it out from there.
GanapathiFacets Developer

Commented:
hi
try this. you have not mentioned your exact query. pls add this to your query and try running it.

SELECT
            MONTHNAME(DATE) as 'Month Name'
FROM
           TABLE_NAME
ORDER BY
           DATEPART(month, DATE)

Assume that the DATE field is present in the table.

Gana
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

Commented:
dear I have told that it is in Crystal report. any who i will mention the query.

 SELECT DISTINCT "Doc_Master"."Doc_date", "Doc_Master"."Doc_shpn", "Doc_Detail_Cont"."Doc_contno", "Doc_Master"."Doc_pol"
 FROM   "Sharif Metals Intl"."dbo"."Doc_Master" "Doc_Master" INNER JOIN "Sharif Metals Intl"."dbo"."Doc_Detail_Cont" "Doc_Detail_Cont" ON "Doc_Master"."Doc_invoice"="Doc_Detail_Cont"."Doc_invoiceno"
 WHERE  "Doc_Master"."Doc_date">={ts '2010-01-01 00:00:00'}

Author

Commented:
If you suggest me to amend the query, so i don't know how to amend the query. i know only how to add tables and making links.
kindly give me some sort of solution.
Top Expert 2010

Commented:
The exact query doesn't matter.  Go to the Crystal Cross Tab Expert (not export, that was a typo), and see my previous answer.

Author

Commented:
Dear JayConverse,
sorry to say i am not getting thing from there.
Top Expert 2010

Commented:
What does "i am not getting thing from there" mean?  Do you not have the Cross Tab Expert in your version of Crystal?

Author

Commented:
your can do one thing,
i have attached one excel file of my data, if your try to make same cross table report.
your will understand my problem.


cont.xls
Top Expert 2010

Commented:
I don't care about the Excel file, we're talking about Crystal Reports.

Author

Commented:
Dear JayConverse,
sorry, i did not write in detail, i have gone through your given options but still not get success.
see my screen short.

don't misunderstand me, i have given your just data, to import in as SQL Table and in few minutes your will get the problem which i am facing. but its ok, if your don't want to check like that.
report.JPG
Top Expert 2010

Commented:
Select "Use a Formula as Group Name"

Commented:
Add a Group Name Formula

MonthName(Month({Doc_Master.Doc_Date}),True)
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Using your data and the suggestion above I created this report

I had to use a formula to convert the date in the Excel sheet since Crystal thought it was a string

mlmcc
Q-26559827.rpt
GanapathiFacets Developer

Commented:
Friend, Here is the query you asked for

SELECT       DISTINCT
             DATENAME(month,"Doc_Master"."Doc_date"),
            "Doc_Master"."Doc_shpn",
            "Doc_Detail_Cont"."Doc_contno",
            "Doc_Master"."Doc_pol"
 FROM  
            "Sharif Metals Intl"."dbo"."Doc_Master" "Doc_Master"
             INNER JOIN "Sharif Metals Intl"."dbo"."Doc_Detail_Cont" "Doc_Detail_Cont"
                              ON "Doc_Master"."Doc_invoice"="Doc_Detail_Cont"."Doc_invoiceno"
 WHERE  
            "Doc_Master"."Doc_date">={ts '2010-01-01 00:00:00'}
            ORDER BY DATEPART(month,"Doc_Master"."Doc_date")

I hope "Doc_Master"."Doc_date" this field you wanted as month.
still you could check this.

Gana

Author

Commented:
Dear Qanapathis:
i appreciate your support.
I need month like January, February, March, April . . .. .etc.
see my two screen short for more clarification.
CrosstabDesign.JPG
CrossTab-Output.JPG
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
The problem is once you convert in the database then the columns will sort in alphabetical order rather than date order.  One thing you can try is to set a sort order for the group

mlmcc

Author

Commented:
Partially  solved

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial