Cross Tabs - Anyway to merge 2 into 1?

[smmedeiros]

Can anyone offer ideas for a solution.

I have 4 variables.
ServerName
Decommission Date
Create Date
Company Name

I currently have 2 independent cross tabs that represent the # of servers added by quarter using company as the row, create date (formatted by Mon-YY) as the column and count of create date as the summarized field.

My 2nd report is the same, using the decommission date.

These work fine. However, my request from my customer is to build 1 table (or something that looks like a table) that would contain the same information as above but merged into 1 view.

Ie. row would still be company, summarized field would be count of decom date & count of create date, column would be each quarter end date strung across.. ie. 1st2008, 2nd2008, 3rd2008, 4th 2008, 1st2009, 2nd2009, etc.… If I don’t put anything in this field, it displays overall totals. If I place both the decom date and the create date in this field, it does display the values, however, it will split the headings into 2 sections.

How can I evaluate each entry to determine which quarter of the year the create date and also decom date falls into. Please note. Some of my values can contain both a create date value and a decommission date value. Also, my data spans 3 years. So, essentially, I need some type of counter that can be groups by calendar quarter end date.


Thanks in advance for any type of ideas.

 

[Madawc]

With an SQL database you could do an SQL Command (Stored Procedure). Select the same records more than once and have a sequencing field so that the Crystal shows them correctly.

Or you could do a mock-crosstab. This is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back.


Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[smmedeiros]

Madawc.
Thanks for your feedback.

Specific to "With an SQL database you could do an SQL Command (Stored Procedure). Select the same records more than once and have a sequencing field so that the Crystal shows them correctly." Do you have any insight as to how to accomplish this? I have limited SQL knowledge, so, any examples would be greatly appreciated.

 

[Madawc]

What I do is write records into temporary tables, and sometimes group and delete. You could try looking on the SQL forum for more help or examples.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP