chartscrystal-reportssage-crm

A chart from Sage CRM data in Crystal Reports 2011

I am working in Crystal 2011. I am extracting data from one table from the Sage CRM database (not sure if it is SQL). The group and sort should go as follows:

  1. by Date ( multiple dates per month, want to group by month and display only the last 12 months )
  2. by invoice type, have three types out of 7 that I am using

So far I have created two formulas:

NumberVar monthValue := (month({table.CreatedDate})); 
 if(DateDiff("m", {table.CreatedDate}, CurrentDate) < 13) then 
     cstr(year({table.CreatedDate}),0,"") + "-" + cstr(Monthname(month({table.createdDate})))  
     else 
         "no"

The no is used in the select expert to remove any records that is more than 12 months old

My problem is that the dates are not being grouped properly, instead of having the following list

I realise that the date is sorted like this because it takes it alphabetically but how can I sort and group by year and month and display year-Month in the chart?

chart of accounts

As you can see in the chart, the dates are not in order and are not grouped properly.

I have restarted this report so many times. I get so far and then it breaks.

Solution

  • Ok , that was a very long winded question but after reading chapter 11 of No Stress Tech Guide AGAIN, I have a better understanding of how the report takes in the data and manipulates it. The report is now grouped into month and sorted to only show records for the last 12 months.

    so what did i do ?

    1. First I created a blank report
    2. Create a chart and use the advanced tab to select the data
    3. on Change of ( created Date , Invoice Type) and show Values of (totalValue)
    4. Pressed the Order button on created Date and set this section will be printed for each month
    5. Select Expert and set created Date forumla to DateDiff("m", {table.CreatedDate}, CurrentDate) < 13) (so no external formula needed.
    6. Used Select Expert to only display the invoice Types I want

    So far , so good, just have to tweak it a bit now here is what the finished product looks like. enter image description here