Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Crystal report formula not working

Posted on 2014-01-01
8
Medium Priority
?
1,313 Views
Last Modified: 2014-01-01
I have one report where the following formula is not working. I did not design this reports and as far as I know the report should run and look for last months sales history data. Also I'm pretty sure this report worked in 2013 so it may be that the formula does not work in 2014 because of it set to look at last months data.

UPPERCASE(MONTHNAME(MONTH(CurrentDate)-1,FALSE))+" "+

TOTEXT(IF MONTH(CurrentDate)=1 THEN YEAR(CurrentDate)-1 ELSE YEAR(CurrentDate),0,"")

Open in new window


Here is the error I get
formula error
0
Comment
Question by:Gerhardpet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 12

Expert Comment

by:Gregory Miller
ID: 39749942
It is actually the Month formula not the Year...
Replace line 1 with this and see what happens:
IF MONTH(CurrentDate)=1 THEN UPPERCASE(MONTHNAME(MONTH(CurrentDate),FALSE)) ELSE UPPERCASE(MONTHNAME(MONTH(CurrentDate)-1,FALSE)) + " " +

Open in new window

0
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 39749972
Above formula returns JANUARY in January but it should return DECEMBER. This should fix it:
UPPERCASE(MONTHNAME(IF MONTH(CurrentDate)=1 THEN 12 ELSE MONTH(CurrentDate)-1,FALSE))+" "+

Open in new window

0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39749974
IF MONTH(CurrentDate)=1 
THEN UPPERCASE(MONTHNAME(12),FALSE)) 
ELSE UPPERCASE(MONTHNAME(MONTH(CurrentDate)-1,FALSE)) + " " +

Open in new window


I think you want to make a minor adjustment to Technodweeb's recommendation.

The issue is that the previous month concept of subtracting 1 works great until the you hit January as 1 - 1 = 0.  I hard coded the value for December in the true part of the If statement.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 12

Expert Comment

by:Gregory Miller
ID: 39749977
Yes, you are correct...
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39749985
@ButlerTechnology

You formula does it only replace the first line or the whole formula I have?
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39749995
This is what I have now and I get this error
formula error
0
 
LVL 12

Expert Comment

by:Gregory Miller
ID: 39749996
Replaces only the first line...
as you have done

Remove the last ")" on the 3rd line
0
 
LVL 1

Author Closing Comment

by:Gerhardpet
ID: 39750099
@Technodweeb
That did not work.

@pcelba
your formula works. Thanks!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question