Crystal Question

[Former Member]

Experts,

I am wondering if something can even be done in Crystal reports.... Here is the scenario:

I hate a start date and a end date. each in a table in a separate field. I want to be able to create something that will take the dates and list every date between the two as its own header column in Crystal. Then I can align the details based on the date to the header.

It is for some time keeping reports that I am trying to build for our HR dept. They want to see a report every Monday morning that shows last weeks hours underneath the dates for last week. I have the query working where it shows a total hours for the day with a date stamp in another field, but I can't find a way to put it in crystal to make it work.

Example: 1/1/11 - 1/7/11

My current Query/Crystal shows like this:

1/1/11

8

1/2/11

7

1/3/11

0

1/4/11

5

1/5/11

5

1/6/11

8

1/7/11

6

NEED it to look like this:

1/1/11 1/2/11 1/3/11 1/4/11 1/5/11 1/6/11 1/7/11

8 7 0 5 5 8 6

Please if anyone can help, it would be greatly appreciated.

[Former Member]

Hi

I thing for your result u canto use Cross-Tabs in Crystal Reports.

Thanks

Rajkumar

[former_member206488]

Hi,

Can you please post your query here?

Thanks,

Neetu

[Former Member]

Here is the query that I am using: (this is from the Project management Add-on for SAP) Not sure if this will help you too much....

SELECT     MPProjektBuchungskopf.Personalnummer AS [Employee ID], MPProjektBuchungskopf.Periode AS Period, 
                      MPProjektBuchungserfassung.Projektnummer AS [Project #], MPProjektstamm.Matchcode AS [Project Name], 
                      MPPersonenstamm_1.Matchcode AS [Employee Name], MPSetupGeschaeftsbereich.Kostenstelle AS [Project Business Group], 
                      MPProjektBuchungserfassung.Freigabe AS Approved, MPProjektBuchungserfassung.KW AS [Calendar Week], 
                      MPProjektBuchungserfassung.Menge AS Hours, MPProjektBuchungserfassung.LeistungsTag AS Date, 
                      MPPersonenstamm_2.Matchcode AS [Approver or PM], MPPerioden.Beginn AS [Period Begin Date], MPPerioden.Ende AS [Period End Date]
FROM         P4100_PM.dbo.MPProjektBuchungskopf INNER JOIN
                      P4100_PM.dbo.MPProjektBuchungserfassung ON MPProjektBuchungskopf.BuchungsKopfID = MPProjektBuchungserfassung.BuchungsKopf INNER JOIN
                      P4100_PM.dbo.MPPersonenstamm AS MPPersonenstamm_1 ON 
                      P4100_PM.dbo.MPProjektBuchungskopf.Personalnummer = MPPersonenstamm_1.Personalnummer LEFT OUTER JOIN
                      P4100_PM.dbo.MPPerioden INNER JOIN
                      P4100_PM.dbo.MPKalenderPeriode ON MPPerioden.PeriodeJahr = MPKalenderPeriode.Periode ON 
                      P4100_PM.dbo.MPProjektBuchungskopf.Periode = MPKalenderPeriode.Periode LEFT OUTER JOIN
                      P4100_PM.dbo.MPPersonenstamm AS MPPersonenstamm_2 INNER JOIN
                      P4100_PM.dbo.MPProjektstammProjektleiter ON MPPersonenstamm_2.Personalnummer = MPProjektstammProjektleiter.Personalnummer RIGHT OUTER JOIN
                      P4100_PM.dbo.MPSetupGeschaeftsbereich INNER JOIN
                      P4100_PM.dbo.MPProjektstamm ON MPSetupGeschaeftsbereich.GeschaeftsbereichID = MPProjektstamm.Geschaeftsbereich ON 
                      P4100_PM.dbo.MPProjektstammProjektleiter.Projektnummer = MPProjektstamm.Projektnummer ON 
                      P4100_PM.dbo.MPProjektBuchungserfassung.Projektnummer = MPProjektstamm.Projektnummer
WHERE     (MPProjektBuchungskopf.Personalnummer IN ('P0301412')) AND (MPProjektBuchungserfassung.Menge <> 0)  AND MPProjektBuchungserfassung.Freigabe = 0
GROUP BY MPProjektBuchungskopf.Personalnummer, MPProjektBuchungserfassung.Projektnummer, MPProjektBuchungskopf.Periode, 
                      MPProjektstamm.Geschaeftsbereich, MPProjektstamm.Matchcode, MPPersonenstamm_1.Matchcode, MPSetupGeschaeftsbereich.Kostenstelle, 
                      MPProjektBuchungserfassung.Freigabe, MPProjektBuchungserfassung.KW, MPProjektBuchungserfassung.Menge, 
                      MPProjektBuchungserfassung.LeistungsTag, MPPersonenstamm_2.Matchcode, MPPerioden.Beginn, MPPerioden.Ende
ORDER BY Date