February 14, 2017 at 2:02 am
Hello 🙂
I have a problem to integrate a condition in an expression in order to generate a report:
For exemple :
I want to sum the revenues of the agency of "Paris" only among so many agencies:
Agency | Recipes |
=============
TOULOUSE | 500 Euro |
LYON | 800 Euro |
MARSEILLE| 300 Euro |
PARIS | 750 Euro |
PARIS | 150 Euro |
REIMS | 250 Euro |
METZ | 185 Euro |
===============
The expression that i used is as follows:
= Sum (iff (Fields! Agency.Value = "PARIS", Fields! Recipes.Value, 0))
The result should be 900 Euro, BUT the system displays an #Error !!
What is the problem?
Can we use the (WHERE) in an SSRS expression?
I'm waiting for feedback from you. Thank you 🙂
February 14, 2017 at 2:28 am
"750 Euro" and "150 Euro" are text values, so you can't add them up. You'll need to separate the numbers from the words before you can do a sum.
John
February 14, 2017 at 2:39 am
John Mitchell-245523 - Tuesday, February 14, 2017 2:28 AM"750 Euro" and "150 Euro" are text values, so you can't add them up. You'll need to separate the numbers from the words before you can do a sum.John
Hi John and thank you for your help but i just found the solution browsing this forum 😀 i should add "CDBl" in the expression like
= Sum (iff (Fields! Agency.Value = "PARIS", CDbl(Fields! Recipes.Value),CDbl (0)))
Thank you 🙂
February 14, 2017 at 2:51 am
Personally, my suggestion here would be to fix your data, not fix the expression for SSRS. Effectively a value of "900 Euros" is telling you two things: 1. The currency, 2. The monetary value. A Column should really only tell you one thing, so my suggestion would be to split your column Recipes into two columns (for example RecipeValue and RecipeCurrency). This preserves the datatype of your Monetary Value, meaning you can do simple tasks like sums, averages, get values between certain ranges, etc, etc, and if you need to do logic on the currency you simple have to question the Currency column, rather than trying to strip it out or use a LIKE '%Euro' Statement.
Doing it the way you have above is going to cause you a lot of headaches in the future, and make simple tasks difficult.
Edit: If your data is always in the format of [Value]{whitespace}[Currency], something like this should work:Use DevTestDB;
GO
CREATE TABLE Revenue (ID INT IDENTITY(1,1),
Agency VARCHAR(50),
Recipe VARCHAR(30));
GO
INSERT INTO Revenue (Agency, Recipe)
VALUES ('TOULOUSE','500 Euro'),
('LYON','800 Euro'),
('MARSEILLE','300 Euro'),
('PARIS','750 Euro'),
('PARIS','150 Euro'),
('REIMS','250 Euro'),
('METZ','185 Euro'),
('LONDON','150 Sterling'),
('MANCHESTER','90 Sterling'),
('NEW YORK','500 Dollar');
GO
SELECT *
FROM Revenue;
GO
ALTER TABLE Revenue ADD RecipeValue DECIMAL(12,2);
ALTER TABLE Revenue ADD RecipeCurrency VARCHAR(20);
GO
UPDATE Revenue
SET RecipeValue = LEFT(Recipe, CHARINDEX(' ', Recipe)),
RecipeCurrency = RIGHT(Recipe, LEN(Recipe) - CHARINDEX(' ',Recipe));
GO
SELECT *
FROM Revenue;
GO
--ALTER TABLE Revenue DROP COLUMN Recipe; --Drop the original column ONLY if you are happy with the results
--and it doesn't effect anything else.
--If other items will be effected, you will need to amend those first,
--and then rerun the UPDATE statement.
GO
SELECT *
FROM Revenue;
GO
DROP TABLE Revenue;
GO
An alternative would be to use calculated columns, however, I'm less keen on that solution as it doesn't solve the data problem at it's core (plus your table could fall over if a value like '100Euro' is entered).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 14, 2017 at 8:03 am
Thank you Thom A for your help this is a real course for me, it's not just a solution to my problem 🙂
in fact i have only Euro in the table so i have basically one row with the monetary value and i concatenate the currency to the monetary value 🙂
it was helpful thank you 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply