Determining Data placement in columns on dates

[markajem]

I am attempting to create a report where as the data to be placed in each particular month column is determine by the date in the data field. I believe this should be done by a Cross Tab but lost on the date calculations. Fields in the data table are specific in the way of a separate field for month and a separate field for year. My problem is considering the logic to have the data place properly in each column designated for that particular month. Running Jan thru Dec as each column heading.

If someone can give me some guidance would appreciate it.

Thanks
Mark
PS to Don Gilsdorf - I have been attempting to contact you via email for past week and all emails coming back to me from both your email accounts. Mark
Email: markanas333@hotmail.com

 

[synapsevampire]

I would concatenate the separate fields into a real date column by using either a SQL Expression (best) or a Crystal Formula.

Now you can create the crosstab and state that you want the data to be grouped on this date expression/formula by Month (it's an option while creating the crosstab), and Crystal will handle that for you.

One potential problem is that if you do NOT have data for a specific month, you will NOT get that column.

The way I suggest reporting environments avoid this is by creating a Periods table which has (at least) a date for every year, now you can join your data to that table and you'll always get results for every day, week, month, etc.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[markajem]

Thanks for your advice. I would assume though I can't create the Period table with Crystal, would have to be some other program but would be able to report from the table once created. Mark
Email: markanas333@hotmail.com

 

[synapsevampire]

That's correct.

By date for every year, I mean that it has a date for every day of every year.

Then you select against the Periods table and join your table to the Periods table.

There are code intensive solutions within Crystal, and you can use Stored Procedures, but both are high maintenance approaches. If you're able to create an SP, you're probably able to create a table and remove this sort of nuisance for future reporting.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[markajem]

I guess I will have to find another approach then because I can't do SP's.
Any other ideas?

Thanks
Mark
Email: markanas333@hotmail.com