Crosstab Report Date

[Former Member]

Hi,

I want output like this in Cross Tab Report,

Itemcode	Jan	Feb	Mar	Upto Apr-10	Total
a	1	5	8	97	111
b	10	5	2	5	22
c	5	6	2	2	15

User will Choose From date and To date ,The  Todate is end of month date it should display month name else it will display   which date they choose in prompt.

[abhilash_kumar · Accepted solution]

Hi Rajesh,

Here's what you need to do:

1) Go to the Crosstab Expert > Place the Date column from the database under 'Columns'

2) Select the field under 'Columns' > Group Options > Under 'The column will be printed:' choose 'For Each Month'

3) Then move over to the Options tab > Check the option 'Customize group name field' > Select 'Use formula as a group name' > Click the formula button and use this code:

if Month({Date_field}) = Month(Maximum({?My Parameter})) then

(

    if Maximum({?My Parameter}) = dateserial(year(Maximum({?My Parameter})),month(Maximum({?My Parameter}))+1,1-1) then

        "Upto "&Totext(Maximum({?My Parameter}), "MMM-yy")

    else

        "Upto "&Totext(Maximum({?My Parameter}), "MM/dd/yyyy")

)

else

Monthname(Month({Date_field}), True)

In the above code, replace {Date_field} with the field you've used in the Columns and replace {?My Parameter} with the Date Range prompt.

If you're using a separate From Date and To Date prompt, then use this code:

if Month({Date_field}) = Month({?To Date}) then

(

    if {?To Date} = dateserial(year({?To Date}),month({?To Date})+1,1-1) then

        "Upto "&Totext({?To Date}, "MMM-yy")

    else

        "Upto "&Totext({?To Date}, "MM/dd/yyyy")

)

else

Monthname(Month({Date_field}), True)

Hope this helps.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thanks For your reply.

1.I didn't get parameter field when i make group name formula.

2.I have attached the screen of my report formula part and also you can see my parameter field is "Date"

3.I'm using SAP Crystal reports 2008 and Version 14.04.4.738 RTM

Thanks

-Rajesh N

[abhilash_kumar · Accepted solution]

Is that a Date Range prompt that you created at the report level (from the Field Explorer)?

Is the crosstab in the Subreport while the prompt is in the Main Report?

-Abhilash

[Former Member · Accepted solution]

That Data Parameter from Field Explorer (Main Report )  only,

I didn't use any sub report.

Thanks

-Rajesh N

[abhilash_kumar · Accepted solution]

Although you can't see the prompt in the Formula Editor Window, could you try to paste the first code I suggested and replace {?My Parameter} with {?Date} and let me know if it works.

-Abhilash

[Former Member · Accepted solution]

Thanks for Reply ,

Yes,I changed the name as {?Date} instead of {?My Parameter}  and i didn't got any error.

But now   it will display the column name upto 'Apr-13' if the Apr-13 date available in database.
it wont display which date field value not available in database.

sometime it is displaying default value like 'Upto Apr-13' when i choose ' Apr-30'.

Got it ?

[Former Member · Accepted solution]

From 01-01-13 to 31-08-13

From 01-01-13 to 13-08-13

[abhilash_kumar · Accepted solution]

Does the database have any records from the 1st of Aug to 13th of Aug?

If no, then the Crosstab will not show the column.

-Abhilash

[Former Member · Accepted solution]

Yes,The database had only one value for Aug-29.

Then,Is it possible in normal report ?

Please suggest any solution  or give any example report if you have.

Thanks a Lot ......
-Rajesh N

[abhilash_kumar · Accepted solution]

To make sure each Month prints, you can create a look-up table that has all dates and then do a Left Join with the Fact table being used at the moment.

You then need to use the Date field from the Lookup table which ensures that each day/month prints on the report.

Another method is described here.

-Abhilash

[former_member205840]

Hi

Insert cross tab columns based on your table date field right click on the column headings and go in format field-- Common--Display string and write the following formula :

if left(Monthname(Month(CurrentFieldValue)),3) = left(Monthname(Month({?Todate})),3) Then

'Upto '&left(Monthname(Month({?Todate})),3)&'-'&Totext(day({?Todate}),"##") else left(Monthname(Month(CurrentFieldValue)),3)

-Sastry

[Former Member]

Hi Sastry,

Thanks for your reply.

This formula is showing error like "Date field is required" but parameter field is date only.

Thanks

-Rajesh N