Data Report

I am creating a report using the Data Report in VB. The data is taken from two tables with a certain date criterea, that is, between two
dates. The date is formatted as MMMM yyyy. My problem is that the layout of the report does not match the data from the table. To make
my problem clear, the report layout is as follows:

Month1 Month2 Month3
Name W1 W2 W3 W4 W5 W1 W2 W3 W4 W5 W1 W2 W3 W4 W5

where W1, W2 etc are the weeks of the month.

Now, let me give you the structure of my table. It is as follows:

Name Month W1 W2 W3 W4 W5

The Query is as follows:

"SELECT DISTINCT EMPNAME, AH1, AH2, AH3, AH4, AH5 FROM STAFFMASTER, STAFFHRS WHERE " & _
"STAFFMASTER.EMPCODE=STAFFHRS.EMPCODE AND STAFFHRS.[PROJECT]='" & cbo1.Text & _
"' AND STAFFHRS.[MONTH & YEAR]>=#" & Format(DTP1.Value, "MMMM yyyy") & _
"# AND STAFFHRS.[MONTH & YEAR]<=#" & Format(DTP2.Value, "MMMM yyyy") & "#"

Now, how will I distribute the data in my report? One important thing, I am building dynamic data report. Please help.

ThanksLooks like it's more of a SQL issue and not Crystal.

Converting rows into columns is a complicated process and needs either a stored procedure or more than one sql statement.. it may need a temp table or dummy table to collect data first and then group it accordingly.

The fast forward solution is this :

.
.
.
Select space(30) "my1", space(30) "my2" , space(30) "my3", h.monthyear "my4", space(30)"my5", space(30) "my6", space(30) "my7",
space(30) "my7", space(30) "my8", space(30) "my9", space(30) "my10", space(30) "my11", space(30) "my12",
sum(h.ah1) april_Ah1, sum(h.ah2) april_Ah2, sum(h.ah3) april_Ah3, sum(h.ah4) april_Ah4, sum(h.ah5) april_Ah5,
0 may_Ah1, 0 may_Ah2, 0 may_Ah3, 0 may_Ah4, 0 may_Ah5, m.empcode
From staffmaster m, staffhrs h
where m.empcode = h.empcode
group by m.empcode, h.monthyear
having h.monthyear = "April 2004" and between (h.monthyear, start_date, end_date)
union all
Select space(30) "my1", space(30) "my2" , space(30) "my3", space(30) "my4", h.monthyear "my5", space(30) "my6", space(30) "my7",
space(30) "my7", space(30) "my8", space(30) "my9", space(30) "my10", space(30) "my11", space(30) "my12",
sum(h.ah1) april_Ah1, sum(h.ah2) april_Ah2, sum(h.ah3) april_Ah3, sum(h.ah4) april_Ah4, sum(h.ah5) april_Ah5,
sum(h.ah1) may_Ah1, sum(h.ah2) may_Ah2, sum(h.ah3) may_Ah3, sum(h.ah4) may_Ah4, sum(h.ah5) may_Ah5,m.empcode
From staffmaster m, staffhrs h
where m.empcode = h.empcode
group by m.empcode, h.monthyear
having h.monthyear = "May 2004" and between (h.monthyear, start_date, end_date)
.
.
.
for each month write a separate sql and just do union all that's going to give you data in column format : like this

my1 my2 my3 my4 my5 my6 my7 my7 my8 my9 my10 my11 my12 april_Ah1 april_Ah2 april_Ah3 april_Ah4 april_Ah5 may_Ah1 may_Ah2 may_Ah3 may_Ah4 may_Ah5 empcode
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -------
April 2004 40 80 120 160 200 0 0 0 0 0 001
May 2004 40 80 120 160 200 40 80 120 160 200 001
May 2004 40 80 120 160 200 40 80 120 160 200 002
May 2004 40 80 120 160 200 40 80 120 160 200 003

Finally in crystal report you just have to group it based on employee code so that per empoyee there would be only one record in your report.

Thanks
Guru
:wave:|||Ah! This is exactly what I wanted. But what is 'space' in the SQL. And also my1, my2, etc. Also, my table structure is as follows:

Table1:

Code
Month
AH1
AH2
AH3
AH4
AH5

Table2:

Code
Name

The retrieved recordset will be as follows:

Name
Month
AH1
AH2
AH3
AH4
AH5

The report I wish to display is as follows:

Name Month1 AH1 AH2 AH3 AH4 AH5 Month2 AH2 AH3 AH4 AH5

I hope the SQL that you mentioned will display as the above.

Thanks

Thanks|||Hi,

I would like to now what database are you using. I have given you sample of MSSql server ?

Also what do u store in month field ? Just give an actual example because you want to retrieve it based on dates ? Also are you trying to retrieve only one yrs. data or multiple yrs ?

Thanks
Guru
:confused:|||Just wondering how can you display 72 fields in one line. For each month you need to display this info in one line is that true ?

Are you trying to print list of all employees and man hrs worked ?

Thanks

:(|||Originally posted by dilemma
Hi,

I would like to now what database are you using. I have given you sample of MSSql server ?

Also what do u store in month field ? Just give an actual example because you want to retrieve it based on dates ? Also are you trying to retrieve only one yrs. data or multiple yrs ?

Thanks
Guru
:confused:

I use MS Access.

In month field I store the date formatted as "MMMM yyyy".

Example:

Name Month_Year W1 W2 W3 W4 W5
John May 2004 40 40 40 40 24

The user wants to display in the record, the data of the month he picks from a DateTimePicker control and two months ahead of that month. So, for example, if he picks December 2004, then the data of January 2005 and February 2005 should also be displayed.

Thanks|||Originally posted by dilemma
Just wondering how can you display 72 fields in one line. For each month you need to display this info in one line is that true ?

Are you trying to print list of all employees and man hrs worked ?

Thanks

:(

Not 72 fields. There will be only 19 fields, that is, the Name field is displayed only once.

That's right. All employees working on a particular project and the man hours assigned to them. I am attaching a picture of the report how it should be displayed.

Thanks|||Looking at the complexity of your problem If I were you I would do this. Assuming you are planning to create report in Crystal, create report with 3 sub reports for each month. 1 for Current month. 2 for II'nd month. 3 for III'rd month. Add these all 3 reports in one report as a subreport. They each will have different where criteria . eg the current month report would have criteria to fetch only current months data and similarly II'nd one retrieves only II'nd months data and III'rd guy retrieves III'rd months data.

Thanks
:wave:|||That sounds interesting. I am totally new to Crystal Reports. Can you help me on how to create the crystal subreport?

Thanks|||It's pretty easy. Create 3 reports to retrieve records for each month based on appropriate criteria. ONce done create 4'th report and add these 3 reports as subreports using crystal's subreporting features. Input parameter of these reports would be date.

Creation of reports in Crystal is also fairly easy. Just go through help. It has pretty good information.

Thanks|||Thanks for your reply.

Mine is a multiuser system. The database is going to be in the network. So I perfer using the .Dsr as report and not the .rpt. Also, should I use the wizard or should I go for runtime reports?

Thanks.|||I am sorry. I do not know what .Dsr is ? You could use wizard to create crystal report if you are a beginner. I didn't get last part runtime reports ? Doesn't matter even if you have multiuser environment. There is a parameter to open report in exclusive mode or temp copy mode. In your case you would be opening your report in temporary mode. That's it.

Thanks
Sanjay|||.Drs is a report created using the RDC. I have read some articles on Crystal Reporting that the .rpt files increases the size of the compiled program.

Is the parameter to open the report in exclusive mode set inside the crystal report application or is set within the vb code?

Thanks|||There is no doubt that .rpt files increase the size of the package. To open report in temp mode or exclusive mode you need to add something called OpenReport. The syntax is :

Function OpenReport (pFileName As String, [OpenMethod]) As Report

Where,
The OpenMethod parameter can take the values "0" (open by default) or "1" (open temporary copy).

If you do not provide this parameter the report is opened exclusively and it cannot be opened a second time

Thanks|||Ah! That is a very good information. So you suggest that I use .rpt? Thanks for the info.

Thankssql