Display 12 columns each for a month but month values dependent on User selected Month

Question

0.00/5 (No votes)

See more:

VS2010

, +

Using Crystal Reports for VS2010.

I have the following requirement for a standalone CR.:

From the indicated start date, retrieve all sales records from the table for the next 12 months. StartDate is the parameter that report uses and user selects.

Columns are as under: Month1 is the month of the StartDate followed by consecutive months.

Column1 Column2 Month1 Month2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12
For eg: If StartDate is 01/05/2013 (May 2013) then columns should be:

Column1 Column2 May13 Jun13 Jul13 Aug13 Sep13 Oct13 Nov13 Dec13 Jan14 Feb14 Mar14 Apr14

Would really appreciate if someone could point me to links or give some directions on how to achieve this.

UPDATE:

I have managed to achieve the Dynamic column headers using formula

ToText(DateAdd('m', 1, {?StartDate}), 'MMM yy')
I am stuck figuring out a formula to get the data value in the Detail section. If field name is {Sales.Extension} how do I calculate the Sum of Extension for that Month only.

Posted 12-Jan-14 11:45am

ShaikhM

Updated 12-Jan-14 13:05pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-01-12T21:13:00

Solution 1

The main idea is to use Cross-Tab as is described here[^].

Have a look here: Crystal report with Dynamic Column[^]

Posted 12-Jan-14 21:13pm

Maciej Los

ShaikhM · Answer 2 · 2014-01-13T15:21:00

Instead of using Crosstab (so much complicated and I hate it), I used the std formula way.

Created 12 formulae one each month header

@m1: ToText({?StartDate}, 'MMM yy') 
@m2: ToText(DateAdd('m', 1, {?StartDate}), 'MMM yy')

And so on upto 12.

This gives a dynamic header listing the Month names in the Column Headers.

For the data part,

@ext1:

C#

if ToText({SALES_TX_HDR.sale_date}, 'MMM yy') = {@m1} Then
        {SALES_TX_LINES.extension}

@ext2:

C#

if ToText({SALES_TX_HDR.sale_date}, 'MMM yy') = {@m2} Then
        {SALES_TX_LINES.extension}

Ans so on.

Works as I want listing the Sum of sales for an item for that month.

Thanks Guys appreciate it.