Formula for Record Calculation

I have created two commands in the database expert. And I want to do the calulation of these records.

My situation is:

In table A, I have fields "Country","Amount" and "Currency".
In table B, I have fields "Currency" and "Exchange".

I want get the sum of "Amount"*"Exchange" if table A "Currency" = table B "Currency" that group by "Country"

How to write this formula?

Thank you fro helping me.Have u linked these commands by currency?|||Please tell me how to linkand what I should do after linking?
Thanks very much|||I have linked the currency field of two commands.

My situation is tableB store the currency of all countries in the world. And some of the countries using same currency. If I use a sql statement to join two table, then the records will be duplicated and cannot get the correct exchange rate of each currency.
So that I want to write a formula that caluclate the tableA amount with correct currency. But I don't know how to get the exchange rate of tableB if tableA.currency=tableB.currency.

Do you have any idea to do that?
thanks alot~~~|||I am using mysql 4.0 which is not supported subquery.

If you have any idea other than my method. Please tell me. This problem spent me a week ago~

Really thanks alot~

Forms authentication security

I have followed exactly what the MSDN white paper asked to implement the Form
based security. I have created the useraccount database,registered few new
users in there and then logged in the RS Report Manager and gave the browser
rights to the new users on a folder and reports which all works fine.But when
I log in with the new registered user I can log in the Report manager but the
page does not display any reports or folders!! what am I doing wrong please
help!!!Hi shabab where u got that information i.e formbased security.
can pls refer that link?
Thanks!
simmi
"shahab" wrote:
> I have followed exactly what the MSDN white paper asked to implement the Form
> based security. I have created the useraccount database,registered few new
> users in there and then logged in the RS Report Manager and gave the browser
> rights to the new users on a folder and reports which all works fine.But when
> I log in with the new registered user I can log in the Report manager but the
> page does not display any reports or folders!! what am I doing wrong please
> help!!!|||for example, Forms Authentication). Here's
a white paper that can get you started
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
also there is one very nice article extended on this white paper which is
published on devex. go to google and search for form based authentication in
MS RS...its by Harden ..let me know if you can find otherwise I will dig into
my favourites and let you know.
good luck
"simmi" wrote:
> Hi shabab where u got that information i.e formbased security.
> can pls refer that link?
> Thanks!
> simmi
> "shahab" wrote:
> > I have followed exactly what the MSDN white paper asked to implement the Form
> > based security. I have created the useraccount database,registered few new
> > users in there and then logged in the RS Report Manager and gave the browser
> > rights to the new users on a folder and reports which all works fine.But when
> > I log in with the new registered user I can log in the Report manager but the
> > page does not display any reports or folders!! what am I doing wrong please
> > help!!!

Formatting Reports

I have created a report but I want to format it and put in headers and a bit of colour to it.
Does anyone know how I can do this.
Thanks,
macca

This is easy thing in reporting service read those tutorila to know how they are great ones on msdn

http://msdn2.microsoft.com/en-us/library/ms170623.aspx

http://msdn2.microsoft.com/en-us/library/ms170246.aspx

Formatting Formula

I am modifuying a report which was created by someone else,I 'm not sure what this formula is doing :

dim strInput
strInput = {Header.INSTRUCTIONS}
formula = replace(replace(replace(strinput, "<br>", chr(10) & chr(13)), "<b><u>",""), "</u></b>", " :") & chr(10) & chr(13) & " "

would someone guide whats going on as have not enough time for research !

Thanks.Hi, I replied to your post in the CR web site.
Modified formula slightly, to test, this is in basic syntax

dim strInput
strInput = "TEXT1<br>TEXT2<b><u>TEXT3</u></b>TEXT4"

formula = replace(replace(replace(strinput, "<br>", chr(10) & chr(13)),"<b><u>",""), "</u></b>", " :") & chr(10) & chr(13) & " "

chr(10) is a 'line feed' and chr(13) is a 'carriage return'

Output from my test looks like this.

TEXT1

TEXT2TEXT3 :TEXT4

So when the formula sees <br> it adds a 'line feed' and a 'carriage return'

when it sees <b><u> it does not add anything, when it sees

"</u></b>", " :") if should add the :|||thank you very much for clear and detail reply!It helps.|||If you only want to show the HTML or RTF text then you can use the Text Interpretation (RTF Text / HTML Text) This will show the text as it is shown in the html or rtf document you don't need to create custom functions.

Creat a formula containg the text or put the database field on the report. Right click -> Format Field -> Paragraph Tab -> Text Interpretation.

formatting columns in a view

I have a view that I created from 4 SQL tables in order to query data for a report. I can't change the format of columns in the original table but would like to format the columns in the view as a different data type.

The original table has the values formated as varchar, the info in the columns is numbers and I would like to have the values changed to decimal when the view is queried.

Is this even possible? Any help would be appreciated.

You should be able to cast them in the select clause of your view (Assuming ALL of the data in those columns is numeric)

CREATE VIEW v1

AS

SELECT CAST(charcol1 AS Decimal(18,2)) AS numcol1, ...

|||Perfect. Thank you!

Formatting columns for a flat file destination control

So i've created a flat file destination control and have mapped the columns. At what point can you control which column shows up first?

cheston wrote:

So i've created a flat file destination control and have mapped the columns. At what point can you control which column shows up first?

In the advanced section of the Flat File Connection Manager screen.|||I do not see an option to move the columns and dragging them does not work either.
|||

cheston wrote:

I do not see an option to move the columns and dragging them does not work either.

Nope. You have to define them in the desired order first.|||If that's too painful, it might worth a try to edit the .dtsx file itself and reorder the DTS:FlatFileColumn elements.

FORMAT_STRING "Currency" returns different currencies

hi!

i'm just getting to know SSAS. i created a standard measure and a calculated measure. the standard measure returns my values in euros, whereas the calculated measure displays them in swiss francs. the analysis server language (which one can set in sql server management studio) is German (Germany). my operating system language is German (Switzerland) though. seems as if calculated measures would read their standard value out of the system language, instead of the SSAS language - is that true? and how can it be fixed?

thank you,

Nico

Please check the following blog - http://www.sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx it should help you to set up the formatting of currencies the way you want it.

format issue

I've created a new calculate member in (SQL Server Business Intelligence Studio 2005) with the following format string: "#,#0.00". This measure is displayed like this: 22,250.22

Then I created a Measure in the Cube structure menue and used the same format string:"#,#0.00" and this measure is displayed like this:0,66 -->

Comma and decimal point are changed in the display of the measures!!!!

Can anyone help me with this? I just like every measure to use a decimal point for the decimal place or the other way round. But I want every measure to be displayed in the same format!

Thanks a lot!

We've had a problem like that too. As far as I can remember, it is a known bug where calculated members are formatted according to the server locale instead of the client locale.

Verify which is the account that your SSAS service is running under, and then verify the locale of that account.

By the way, we're also having problems when the calculated member is a division, and the result is close to zero, the format switches to scientific notation (like 3.45E-7) instead of showing 0.00.

|||But I get different results from the same SSAS installation using the same format ?!|||If I understood your question correctly you get the expected format for regular measures and the wrong format for calculated measures. The bug only applies to calculated measures.|||Have you solved the problem or do you know a workaround?|||I don't know a workaround. We are just lucky that all users across the world accept a US locale, so we set the account of SSAS to a US locale. This way regular measures and calculated measures show in the same format. I assume this will be fixed in SP2.|||

Here is a link to a BLOG that might solve your problem:http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx

I have seen the same problem because i work in a scandinavian country. If I have a column that indicates the currency I simply do like this (### ### ### ###.##).

Note that you will have to use space as thousand separator.

With my language settings SSAS2005 thinks that a (,) is a thousand separator and a (.) is always a decimal indicator.

This is quite stupid but it is the way it works presently in SSAS2005. It will also cascade as a problem in Excel because if you do not get it right all measures will be converted to strings in Excel, if you use language settings different from US-settings.

HTH

Thomas Ivarsson

|||(### ### ### ###.##) thanks for this hint but unfortunately it doesn't work either. I used this format for both measures but in one , appeared as the comma seperator and in the other the decimal point appeared.

Is there an official site where this bug is admited by Microsoft?

Thanks!
|||

OK. What is your regional/national settings for the O/S?

What thousand separator and what decimal sign are you using with these settings? look at regional settings in the control panel.

Does this problem appear in the BI-Dev Studion Browser or in a client, like Excel?

I have found it to work on clients with swedish settings( space as thousant separator and decimal(,) as decimal sign). In USA the dot is used as deciaml sign and decimal as thousand separator.

I recommend you to have a look at Moshas Blog entry of how to solve this in script.

Regards

Thomas Ivarsson

|||Regional Language settings are swedish.
Nummer is formatted like this: 123 456 789,00

It appears in the BI-Dev studio and in the Management studio.

I created a measure in the Cube structure with the following format: ### ### ###.## -->result: 22 387.31

and I created a measure in the calculation tab with the following format: "### ### ###.##" --> result: ,66

I have no idea how to solve this. Is there an official Microsoft page where this bug is recognized?

Any more suggestions?|||

Hello again. So we are both swedish.

I have tried the same scenario you are describing but I do not get the same result for the calculated member.

Both "### ### ###.##" in a measure and in a calculated measure returns 000 000 000.00, with my swedish regional settings in the O/S. Check the sign for thousand separator and decimal in regional settings.

This is not correct because we use (,) as decimal sign but it will secure that client applications will fomat values in the correct way. Else, values can be converted to text in Excel.

I have heard som "talk" that the currency format in SSAS2005 will automatically turn in to the currency settings in your O/S. This have not happened yet om my machine.

My assumption is that, standard format settings, have to do with the NET-framework on your machine.

Have you tried the script, for formatting, in Moshas Blog that I have pointed at?

Regards

Thomas Ivarsson

|||

To reproduce the problem, you need to have the client in one locale, and the account under which the SSAS service is running in another locale. In case the service is running under a non domain user, you need to check the "Apply all settings to the current user account and to the default profile" box in Regional and Language Options, Advanced tab.

|||

Hello,

I had this problem because the collation of my AS2005 server was diferent from the collation of my cube.

Can you confirm if both settings are the same?

Best Regards

|||Hi,

where can I look the collation up?

Thanks!|||

Right click on SSAS2005-server in management studio.

Select properties.

Select languages/collation.

HTH

Thomas

format issue

I've created a new calculate member in (SQL Server Business Intelligence Studio 2005) with the following format string: "#,#0.00". This measure is displayed like this: 22,250.22

Then I created a Measure in the Cube structure menue and used the same format string:"#,#0.00" and this measure is displayed like this:0,66 -->

Comma and decimal point are changed in the display of the measures!!!!

Can anyone help me with this? I just like every measure to use a decimal point for the decimal place or the other way round. But I want every measure to be displayed in the same format!

Thanks a lot!

We've had a problem like that too. As far as I can remember, it is a known bug where calculated members are formatted according to the server locale instead of the client locale.

Verify which is the account that your SSAS service is running under, and then verify the locale of that account.

By the way, we're also having problems when the calculated member is a division, and the result is close to zero, the format switches to scientific notation (like 3.45E-7) instead of showing 0.00.

|||But I get different results from the same SSAS installation using the same format ?!|||If I understood your question correctly you get the expected format for regular measures and the wrong format for calculated measures. The bug only applies to calculated measures.|||Have you solved the problem or do you know a workaround?|||I don't know a workaround. We are just lucky that all users across the world accept a US locale, so we set the account of SSAS to a US locale. This way regular measures and calculated measures show in the same format. I assume this will be fixed in SP2.|||

Here is a link to a BLOG that might solve your problem:http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx

I have seen the same problem because i work in a scandinavian country. If I have a column that indicates the currency I simply do like this (### ### ### ###.##).

Note that you will have to use space as thousand separator.

With my language settings SSAS2005 thinks that a (,) is a thousand separator and a (.) is always a decimal indicator.

This is quite stupid but it is the way it works presently in SSAS2005. It will also cascade as a problem in Excel because if you do not get it right all measures will be converted to strings in Excel, if you use language settings different from US-settings.

HTH

Thomas Ivarsson

|||(### ### ### ###.##) thanks for this hint but unfortunately it doesn't work either. I used this format for both measures but in one , appeared as the comma seperator and in the other the decimal point appeared.

Is there an official site where this bug is admited by Microsoft?

Thanks!
|||

OK. What is your regional/national settings for the O/S?

What thousand separator and what decimal sign are you using with these settings? look at regional settings in the control panel.

Does this problem appear in the BI-Dev Studion Browser or in a client, like Excel?

I have found it to work on clients with swedish settings( space as thousant separator and decimal(,) as decimal sign). In USA the dot is used as deciaml sign and decimal as thousand separator.

I recommend you to have a look at Moshas Blog entry of how to solve this in script.

Regards

Thomas Ivarsson

|||Regional Language settings are swedish.
Nummer is formatted like this: 123 456 789,00

It appears in the BI-Dev studio and in the Management studio.

I created a measure in the Cube structure with the following format: ### ### ###.## -->result: 22 387.31

and I created a measure in the calculation tab with the following format: "### ### ###.##" --> result: ,66

I have no idea how to solve this. Is there an official Microsoft page where this bug is recognized?

Any more suggestions?|||

Hello again. So we are both swedish.

I have tried the same scenario you are describing but I do not get the same result for the calculated member.

Both "### ### ###.##" in a measure and in a calculated measure returns 000 000 000.00, with my swedish regional settings in the O/S. Check the sign for thousand separator and decimal in regional settings.

This is not correct because we use (,) as decimal sign but it will secure that client applications will fomat values in the correct way. Else, values can be converted to text in Excel.

I have heard som "talk" that the currency format in SSAS2005 will automatically turn in to the currency settings in your O/S. This have not happened yet om my machine.

My assumption is that, standard format settings, have to do with the NET-framework on your machine.

Have you tried the script, for formatting, in Moshas Blog that I have pointed at?

Regards

Thomas Ivarsson

|||

To reproduce the problem, you need to have the client in one locale, and the account under which the SSAS service is running in another locale. In case the service is running under a non domain user, you need to check the "Apply all settings to the current user account and to the default profile" box in Regional and Language Options, Advanced tab.

|||

Hello,

I had this problem because the collation of my AS2005 server was diferent from the collation of my cube.

Can you confirm if both settings are the same?

Best Regards

|||Hi,

where can I look the collation up?

Thanks!|||

Right click on SSAS2005-server in management studio.

Select properties.

Select languages/collation.

HTH

Thomas