Trying To Understand Currency Conversion
May 16, 2008
I am trying to understand the currency conversion but facing some problems in the same.The more I am tring to understand that the more confused am i getting.
My requirement is specific. I am trying to write sql queries for the measures which come under the scope of currency conversion.
I will query the Cube to get the measure value :
SELECT [Measures].[Sales Amount] on 0 from [Adventure Works DW]
And then query the SQL database as:
Use AdentureWorksDW
SELECT SUM(SalesAmount) FROM FactInternetSales
My understanding is this ( Please correct me if i am wrong) :
If the Currency conversion is:
1. Many to One : Data will be different (in SQL and MDX) as currency conversion is applied which will convert that to a particular (Corporate) currency.
2. One To many : Data will not be different (in SQL and MDX)
2. Many to many : Data will not be different (in SQL and MDX)
I did not get much information on the same from anywhere. I want to undersatnd the script of the Currency conversion. (The script generated by Currency conversion wizard) and then depending on the script (I will extract the Type of conversion) write the SQL for that.
Regards..
Girija Shankar
View 2 Replies
ADVERTISEMENT
Apr 10, 2008
Hi i need to apply a currency conversion in my view now i don't have any table which holds the currency value at the moment its hard codes. I can build a tale with the currecnty but am not sure how i would apply this in my view.. without nothing to references it.
View 1 Replies
View Related
May 30, 2008
I have a situation where I have three tables in the source i.e.
1. Currency
2. Currency ExchangeRate
3. ExchangeRateType
I understand the conversion of 1:M, M:1 and M:M currency conversion in the Analysis Services by modeling Currency as a Dim and Exchange Rate as a Fact Table with Time Key for semi-additive aggregation. Same as the way Exchange Rate works in the Adventure Works.
Now the problem we have, there is no one Exchange Rate, we have different Exchange Rate Types. So different Exchange Rate will be applied to different Currency conversion for Calculations.
How to materlized these 3 sources table into a dimensional model in a right way, so it answers all the scanerio.
Thank You
View 1 Replies
View Related
Mar 13, 2004
Hello I am a newbe to sql and i am shure my problem is simple but hope somone can help.
My Total comes out like $20.00 and i want it to come out like 20.00€
Simple but frustraiting
Any help apprieciated
Colin
View 4 Replies
View Related
Jan 24, 2007
I need to show my SUM of the 2 columns added in the query below formatted as currency. Is this possible?
SELECT SUM(QVSTDN + QVNONC) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
I tried:
SELECT CONVERT(varchar(12), SUM(QVSTDN + QVNONC) , 1) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
But this does not format it as currency. Any input would be helpful.
View 6 Replies
View Related
Sep 8, 2004
I have a currency that needs to go into the Database. The column in the database is of type money.
When i try ctype or convert into integer, desimal or even sqlmoney type i get an error. Cannot convert data type nvarchar to data type money.
Can anyone help me with this Please.
Here is the code...
sqlcom = New SqlCommand("insert MeasureQuantities(MeasureQuantityName,MeasureQuantityDescrip,MeasureQuantityPrice) values(@MeasureQuantityName,@MeasureQuantityPrice,@MeasureQuantityDescrip)", ocnn)
ocnn.Open()
Dim decPrice As System.Data.SqlTypes.SqlMoney
decPrice.ToDecimal()
decPrice = CType(MeasureQuantityPriceAdd.Text, System.Data.SqlTypes.SqlMoney)
sqlcom.Parameters.Add("@MeasureQuantityName", MeasureQuantityNameAdd.Text)
sqlcom.Parameters.Add("@MeasureQuantityPrice", decPrice) 'MeasureQuantityPriceAdd.Text, System.Data.SqlTypes.SqlMoney))
sqlcom.Parameters.Add("@MeasureQuantityDescrip", MeasureQuantityDescripAdd.Text)
sqlcom.ExecuteNonQuery()
View 2 Replies
View Related
May 28, 2008
How do I get data that looks like 200,000 returned as a currency format?
Here is what I have in my select statement it doesn't work and can't seem to find an example that works or maybe I just have it set up wrong?
'$' + convert(Sum(OrderHeader.SubTotal)as money) as 'Total Sales'
Ultimately I have a field that is returned and it exists on a reporting services report. I can't find a way just to format the field as currency through the properties either would that be the better way to do it if there is a way?
View 2 Replies
View Related
Mar 25, 2007
Hi all. Is there a way in SQL to convert the integer to currency format? just for example....
4000 convert to 4,000
1312500 convert to 1,312,500
30000 convert to 30,000
Most of you will say "Do it in your front end"... but the problem is I don't know how to do it in my Report(Business Intelligence Project). If anyone of you knows, tell me please... Thanks. :)
-Ron-
View 12 Replies
View Related
Sep 28, 2007
Hello forum,
I am quering a datebase table from an asp page, comparing values that the user input in a form, value is a string 1000000 and the field in the database that i am compparing from is a currency type, i am getting an error saying that i need to convert to currency, type mistmatch. i am using the following to convert but it does not work, any suggestions.
here is my query:
"SELECT STotalAllocation FROM tabletoquery where STotalAllocation >='" & CCur(Session("plusMillion")) & "' "
'Session("plusMillion") is equal to 1000000
this is the error:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Disallowed implicit conversion from data type varchar to data type money, table 'SiteDetail7CMaster', column 'STotalAllocation'. Use the CONVERT function to run this query.
search_report.asp, line 136
View 4 Replies
View Related
Feb 21, 2008
Hi!
I have money value of 56.0000.I want it to only give me the last two decimal places,56.00.I used this one to come up to that result:cast(round(sum(total),2)as int)but my result is this 56.Am i on the right track?
Funnyfrog
View 13 Replies
View Related
Oct 20, 2006
Starting in my control flow, I execute a data flow that populates a recordset via SQL 2005 Stored Proc. One of the columns in source table is a currency type.
Back to the control flow, I have a for each container that includes an execute sql task that updates or inserts records into another table. I get precision or data type issues since I can not assign the package variable to a currency data type. The only way I can get this to work is if I convert the currency column in my data flow to a string and then cast the variable in my update/insert sql task. Any suggestions?
Thanks....Gary
View 4 Replies
View Related
Dec 14, 2006
The $ must appears only on certain total levels and has to be in the same position in the column regardless of the value for financial reporting. In other words, I don't want the $ to float next the most significant digit in the value. See the example below from Excel. Using an IIF statement I can concatenate the $ to the string when it is a certain group level. However, I have tried many various formatting strings but can't find one that results in a fixed number of characters so that the $ will always appears in the same position in the column.
Anyone have a way to do this?
General Government
222
3.45%
$ 405,590,654
1.98%
Public Buildings
194
3.01%
375,453,314
1.83%
Other Facilities
22
0.34%
21,324,140
0.1%
Property Acquisition
6
0.09%
8,813,200
0.04%
Grand Total
6,439
100.0%
$ 20,507,977,298
100.0%
I did get this to work by adding a column to the grouping. That somehow causes the heading size to increase (??) and takes much more time than coding a format code in an expression (I have many reports to do).
View 4 Replies
View Related
Jun 19, 2006
It seems that the "decimal" type allows more flexibility but also uses more storage than "money". On the other hand, does "money" have any special t-sql functions that understand its unquie nature? (e.g. adding two money values together with no cents round-off error).
For example, would the number 12345.12 be declared as a decimal(7,2) or currency?
Barkingdog
View 1 Replies
View Related
Dec 27, 2004
i have this situations:
the aspx file with source code work good.
when i try run project which created from VS i take
"SQL Server does not exist or access denied."
connection strings same:
"Data Source=**.**.**.**,1433;Network Library=DBMSSOCN;Initial Catalog=slider;User ID=username;Password=password;"
i think may be it`s web.config in project wrong?
Edited by SomeNewKid
Removed username and password from connection string.
View 1 Replies
View Related
Aug 27, 2006
I am having a problem reinstall SQL Express 2005. I had it installed, but then I unistalled it. I have tried installing it again, but everytime I do it says that the product is already installed. There is no instance of it in my Add/Remove Programs. I have read the threads that relate to this topic, but they haven't helped. I have looked at my logs of the installs, but they don't make sense neither. I just want the program installed again, or a way to get it so that I can use it.
When I try to manually uninstall using msiexec.exe /x <processid> I get an error saying that the package could not be opened. This happens with all the process ids I use that I get from the log files (I have 11 logs).
am using Visual Basic 2005 and trying to create a database, and it will not let me. That is how I know that it is not installed, plus I can't find any instance of it one my computer. Please help. Thanks.
View 8 Replies
View Related
Nov 19, 2006
How di I understand what SQL Server Integration Services mean? Can some one help me?
View 3 Replies
View Related
Dec 22, 2004
I have a fact table with amounts, all in a single currncy. I would like to be able to process the cube where I can select the currency from a dimension.
The format of the currency exchange dimension must include date, currency code, and exchange rate, where the native exchange rate (the currency that the amounts are in) is defined as 1.000.
View 7 Replies
View Related
Aug 9, 2002
How can I get the money datatype to return only two decimals instead of four?
View 2 Replies
View Related
May 13, 2005
Hi,
I've just created an ecommerce website using ASP and SQL Server 2000 - on my development machine (WinXP Pro, IIS, SQL Server 2000) the SQL Server datatype for the price field is Money - however, the datatype on my hosts SQL Server is Currency (there is no Money datatype available??)
Now, the problem is this: on my local machine I enter a price of say 99.99 - this shows up on the front-end & in the admin area as 99.99, no problem. Now, on the live server when I enter 99.99 it is somehow converted to 9999 - if I enter 99,99 (with a comma) it shows correctly as 99.99 - however, when I go to edit the price it reverts to 9999...does anyone know what is going on here? Why is it converting 99.99 to 9999??
Please let me know if you need more clarification of the above.
Many thanks,
Peter
__________________
Paliz Design
http://www.palizdesign.com/
View 4 Replies
View Related
Feb 23, 2007
I have a column - datatype 'money' and my price variable is (for instance)
8450
how do I put this value into the money column without getting this error?
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'db196009544.dbo196009544.vehicles', column 'price'. Use the CONVERT function to run this query.
ASP VBScript
I am using this syntax:
price = Upload.Form("price") '---this gives 8450---
SQL="UPDATE vehicles SET price='" & price & "' WHERE id=" & request.queryString("linkID") & ""
Thanks
mark
View 4 Replies
View Related
May 3, 2004
I'm looking to display the results of the following query as currency. Any help with this would be greatly appreciated...
SELECT SUM (sysTotalCharge) - SUM (sysTotalCredit) AS TotalNet FROM Bookings WHERE (sysRecordDate BETWEEN '01/01/::Year::' AND '01/31/::Year::')
View 2 Replies
View Related
Jun 1, 2004
I've written an Sql statement to extract the Currency Amount and the currency rate and I want to calculate the Amount in Local Currency.
If the currency amount is in the local currency the Currency rate value returned is 0. I have used a case statement to convert the 0 to 1. (is there
easier way)
But I now can't calculate my Local amount value because of the way I have converted the currency rate.
Select IH.Amount, 'Currency Factor' = CASE
when
IH.[Currency Factor] = 0 then 1
else IH.[Currency Factor]
end,
what way should I have written this?
View 4 Replies
View Related
Feb 12, 2008
Hi,
The field below I need to convert to a dollar amount. Can someone help me please?
clm_tchg (decimal(10,2), null)
Thanks
View 5 Replies
View Related
Feb 1, 2007
I'm looking more into UDAs and wondering if someone can help me out. I get how I use them and route to them. What about their freshness -- the data? If I'm aggregating data via some methods...when is that underlying data aggregates. When the UDA is called at runtime (coulnd't that hurt performance). I guess I"m likening these things to indexed views with benefits. Am I lost here -- someone help..thanks.
View 1 Replies
View Related
Nov 28, 2007
It's probably right underneath my nose, but I cannot seem to be able to set the currency format.
My Stored procedure returns an integer that represents a cash value in pennies (like cents).
I then divide this value by 100 and convert it to a Double, so its value is represented in Pounds.
This all works fine, my problem is that when I wrap the above logic in a FormatCurrency, I get a dollar ($) sign before it. How can I change this so that it gives a pound sign (£)?
View 8 Replies
View Related
Feb 23, 2008
I'm trying to put together a very simple page where you can click the up and down button to move the item up or down in the list and save the new list into the database. I've run the stored procedure... and it works so Its not a procedure problem. Here is the list in my databaseOrder_Id Item Position1 Sebring 12 GrandPrix 23 Accord 44 Miati 3 When I go to click the button I get a object reference not set to an instance of an object... This error haunts me many times. Heres the code to my project:BLL /// <summary>
/// Gets Items from the Item table.
/// </summary>
/// <returns>A list of items.</returns>
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
public OrderItemDataSet.OrderItemDataTable GetItemDataByPosition()
{
return OrderItemAdapter.GetItemDataByPosition();
}
/// <summary>
/// Gets the order id based on the position specified.
/// </summary>
/// <returns>The order id.</returns>
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
public int SelectOrderIdByPos(int pos)
{
return SelectOrderIdByPos(pos);
}
/// <summary>
/// Update Order list.
/// </summary>
/// <param name="newPos">Position to change to.</param>
/// <param name="originalPos">Original position.</param>
/// <param name="orderId">Original id.</param>
/// <param name="otherOrderId">Position to change id.</param>
/// <returns></returns>
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
public void ReorderItemByIdAndPosition(int newPos, int originalPos, int orderId, int otherOrderId)
{
OrderItemAdapter.ReorderItemByIdAndPosition(newPos, originalPos, orderId, otherOrderId);
} Codebehind 1 using ReorderListWebApplication.BLL;
2 using System;
3 using System.Collections;
4 using System.Configuration;
5 using System.Data;
6 using System.Linq;
7 using System.Web;
8 using System.Web.Security;
9 using System.Web.UI;
10 using System.Web.UI.HtmlControls;
11 using System.Web.UI.WebControls;
12 using System.Web.UI.WebControls.WebParts;
13 using System.Xml.Linq;
14
15 namespace ReorderListWebApplication
16 {
17 public partial class _Default : System.Web.UI.Page
18 {
19 protected void Page_Load(object sender, EventArgs e)
20 {
21
22 }
23
24 protected void UpButton_OnClick(object sender, EventArgs e)
25 {
26 Label posLabel = (Label)ReorderItemDataList.FindControl("PosLabel");
27 Label idLabel = (Label)ReorderItemDataList.FindControl("IdLabel");
28
29 using (ReorderListWebApplication.BLL.OrderItem item = new ReorderListWebApplication.BLL.OrderItem())
30 {
31 int pos = 0;
32
33 pos = item.SelectOrderIdByPos(Convert.ToInt32(idLabel.Text) - 1);
34
35 // Grab other orderId!!
36 item.ReorderItemByIdAndPosition((Convert.ToInt32(posLabel.Text) - 1), Convert.ToInt32(posLabel.Text), 3, pos);
37 }
38 }
39
40 protected void DownButton_OnClick(object sender, EventArgs e)
41 {
42 using (ReorderListWebApplication.BLL.OrderItem item = new ReorderListWebApplication.BLL.OrderItem())
43 {
44
45 }
46 }
47 }
48 }
49
View 8 Replies
View Related
May 2, 2008
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: You have specified that your update command compares all values on SqlDataSource 'SqlDataSource5', but the dictionary passed in for oldValues is empty. Pass in a valid dictionary for update or change your mode to OverwriteChanges.
can somebody tell me what this is?
Angiemarie
View 3 Replies
View Related
Oct 18, 2005
Hello, I could use some help with this error message:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
This if statement is the problem
IF (select bill_freq from #header1) = '1' update #header1 Set [Monthend] = aa.pername from sv00564 zz inner join sv00532 aa on zz.wennsoft_period_id = aa.wennsoft_period_id Where zz.rmdnumwk like #header1.Invoice_Number
View 1 Replies
View Related
May 4, 2006
This is failing:// Trying to update DynamicPageContent.Html where DynamicPageContent.PageID='121'//With// Select Html from DynamicPageHistory where HistoryID='831'//Update DynamicPageContentSET Html=(SELECT Html FROM DynamicPageHistory WHERE HistoryID='831')WHERE PageID='121'With the following error:Server: Msg 279, Level 16, State 3, Line 1The text, ntext, and image data types are invalid in this subquery or aggregate expression.DynamicPageContent.Html is Ntext size 16 and DynamicPageHistory.Html is Ntext size 16. PageID is and int and HistoryID is an int. It fails with single quotes around 831 and 121 and it fails without single quotes. The error message is the same both ways.Ideas?Thanks,Rex
View 4 Replies
View Related
Jun 29, 2004
I feel extremly happy today and let me share with this wonderful forum why.
SQL is awesome and I'm really learning that theres alot of resources out there and coding is not that hard once you understand what your doing. Just took awhile for things to sink in. I still have a long ways to go but I'm really enjoying using sql. I practically live in our SQL Server 2000 at work and I'm learning more and more about it everyday.
Thank YOu so much guys I really appreciate all the help you've given me
Now I'm gonna study for the sql cert
:)
View 3 Replies
View Related
Mar 8, 2005
I have a table Test_GUID, with fields id as int PK IDENTITY, GUID as varchar(50), and Version as tinyint. I put data in it such as
id GUID Version
------------------
1 abc 1
2 abc 2
3 def 1
Then I tried to get the following result:
id GUID Version
-------------------
1 abc 2
3 def 1
By using a query:
SELECT GUID, MAX(Version) AS MaxVersion, id
FROM dbo.Test_GUID
GROUP BY GUID
But then I get an error because id is not being aggregated.
This works
SELECT GUID, MAX(Version) AS MaxVersion
FROM dbo.Test_GUID
GROUP BY GUID
but then how do I get the id of each record?
View 2 Replies
View Related
Jul 18, 2006
Set @mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= 'Exec(@mSQL + '''' + @mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
View 13 Replies
View Related
Oct 18, 2006
I'm doing code with VB.Net.
The error was:
System.FormatException: Input string was not in a correct format.
The line this is happening on is:
Response.Write("<TD><p>" + rdData("ClientRepID") + "</p></td>")
I don't understand, it is giving that error particularly when I want to get ClientRepID
Some more lines from the code:
Response.Write("<TD><p>" + rdData("Username") + "</p></TD>")
Response.Write("<TD><p>" + rdData("ClientRepID") + "</p></td>")
The first line the compiler had no problem but when it gets to the second line with ClientRepID it gives me this error. (ClientRepID exists in the database)
Can someone please help me? Thanks
View 4 Replies
View Related
