Newbie! - Month and YTD in same report with user selecting month for report - From Excel Data

[billyackers]

Hello All

Please be gentle! I am a complete newbie to Crystal Reports but am fairly PC literate. I am sure the issue may have been covered elsewhere but half of my problem is not knowing what phrase or term to use when searching! so, here goes!...

I have an excel spreadsheet on a sharepoint site. I have made the connection and can see all fields etc.

I managed to fumble my way through creating a basic report that allows me to use Date Parameters to provide me with the suitable filtering for a reporting period. I also managed to have a look at a Cross tab to produce a table and all is looking very promising.

What I really need to do, in the same report, is to show Year To Date and month (based on a parameter choice - is that the right term?) on the same report.

For example, I have data for 2012 (jan - Dec) which includes categories and values and also the date of entry. I would like to be able to choose (at any point in time and may be retrospective) say March 2012 and it will bring back the information for the Month of March and the YTD info.

Ideally the YTD data will be for the period of 1 Jan - 31 Mar but if that is not possible I am happy to take just the Month of March data and the whole YTD (ie if I ran the report now there would be more values entered as the whole year has passed compared to the amount that would have been there at the time in March) Hope that makes sense.

All help gratefully received - in advance!

Regards

 

[IanWaterman]

Assuming your dataset is only 2012, you only need one paramter. To keep it simple I suggest using a date parameter and users should enter the last date of the month as required.

Create two formula

@YTD
If {yourdatefield} <= {?dateparameter] then {valuefiled} else 0

@MTD
If {yourdatefield} >= dateadd("m", -1, {?dateparameter]) +1 and {yourdatefield} <= {?dateparameter] then {valuefiled} else 0

You can then add sum summarries to these two formula.

NB
This formula dateadd("m", -1, {?dateparameter]) +1 subtracts one month from your paramter date to get date of last day of previous month and then adds a day to get 1st of month selected

Ian

 

[billyackers]

Ian

Thank you for your prompt response. I am having an error returned for the @YTD formula though.

My field names are as follows:
{yourdatefield} is {'2012_'.Date Raised by NMS}
{?dateparameter} is {?ReportPeriod}
{valuefield} is {'2012_'.Order value}

What I did notice was, in your post above, you put {dateparameter], was that a typo ?

Thanks

 

[IanWaterman]

What are the date types for these

{'2012_'.Date Raised by NMS}
{?ReportPeriod}

They must be the same ie date or datetime

If {'2012_'.Date Raised by NMS} is datetime and parameter is date change formula

@YTD
If date({'2012_'.Date Raised by NMS}) <= {?ReportPeriod} then {'2012_'.Order value} else 0

Ian

 

[billyackers]

Thank you again Ian

I dont receive an error now but I am trying to work out where this goes in my report so I can have the following:-

Jan Feb Mar Apr May etc
Month YTD Month YTD Month YTD Month YTD Month YTD
Category 1
Category 2
Category 3

As above but with values in.... will keep trying!

 

[IanWaterman]

Thats not quite how I interpreted your description ;-)

You could do the Monthly columns with a normal cross tab. However, I don't think you can do the Monthly YTD figures with a normal cross tab

You will need to build a manual cross tab with formulae for each month and MonthYTD. The MthYTD formula with will similar to my previous YTD buth using
Month(datefield) to define months, Month() returns the month number.

YOu data parameter will then be used to suppress your column headers and summaries

In suppression formula for each colum you would use something like this.

For the April column

Month(?ReportDate) < 4

Ian

 

[IanWaterman]

Once you have created formula, place in details and suppress. Group bt Category Filed and add sum summaries to all formula in category group fotter.

Ian