CrossTab in CR based on a Date

[Former Member]

Hi,

I've been searchin in several forums for the solution of this in CR but no success.

I use SAP Crystal Report V.14 and SS2012, my task is to build a report/pivot with CR for the last 12 months.

artnr	customer	custno	status	LastResponse
10007	cust1	2544	St1	20150824
33139	cust2	5173	St1	20150801
33139	cust3	667	St3	20150717
11764	cust4	1746	St4	20150901

Output this crosstab in CR:

	09	10	11	12	01	02	03	04	05	06	07	08
St1												2
St3											1

rows: status
columns: the LAST 12 months (today = 2015-09-03), derived from column LastResponse
pivot: count

As I'm new to CR has anyone a hint for me? thx

[abhilash_kumar]

Hi Stefan,

If the 'Last Response' is a 'Date' type column, you can try this:

1) Insert a Crosstab. Use Status as the 'Row' and use Last Response as the column. For the Column, click 'Group Options' > Where it says 'Print this date', select 'For each month' from the drop-down.

2) Use any field as the summary and set it to 'Count'.

-Abhilash

[Former Member]

Hi Abhilash,

unfortunately the column LastResponse is string and so the dropdown can't present any month data. And I always want to present the LAST 12 months - means a fixed number of columns for the crosstab.

What's the trick? Is it possible at all in CR or better to do a pivot sql on the DB?

thx

[abhilash_kumar]

The LastResponse field can be converted to date using the formula below:

cDate(Picture({LastResponse}, 'xxxx/xx/xx'))

You should now be able to use this formula field as the column and set it to print 'For Each Month'.

If there are 'missing months' in the column, you would need to build a 'Look-up' table consiting of all dates and then replace all references to the date field from the 'old' table with the date field from the 'new look-up table'.

E.g:

-Abhilash

[Former Member]

hi,

the table column "LastResponse" now has only valid dates like "20150119" (YYYYMMDD)

cDate(Picture({LastResponse}, 'xxxx/xx/xx'))

I get an error like

"erronous string for date format" and the report leaves blank.

Is the picture not ok?

>>>>>>>>>>>>>>

it was a CR-bug; close and reopen the report and it worked

>>>>>>>>>>>>>>

The look-up-table solution is the most feasable way I think. I try tomorrow...

[Former Member]

Hi Abhilash,

thx for your suggestion with the lookup-Table. In T-SQL I could create one and it works like a charm

The result table is used for a CR-subreport and it works very well.

When trying to use the subreport in a mainreport I have one issue: displaying the sub in the main it only shows columns with an entry (not null), in the above example column (= month) 07 and 08 for status St1 and St3, and the status field is the linking field main report to sub.

Any hints?