Current Lanaguage in connection?

Hi.

I have a SQL Server Analysis Services Connection in Reporting Services. But my problem is that I need to allways have the same language on dataset values. Since I have multiple languages in my olap it all depends on the language setting on the invidual computer what language the new dataset values will become. Since the developers have diffrent language settings the datasets can get really ugly. I would prefere to have english on dataset values under development. But only on the dataset becouse our reports support more then one language.

I found a Current Language property under the connection setting that might be what I'm looking for? But I get error message becouse from format no mather what format I try. I have tryed stuff like, en, en-US, english, en-english and many more but havnt managed to find one that works yet.

Do anyone know what format the Current Language expect? And do anyone know if it would solve my proble, or if there are som other way to solve my problem?

Thanks!

I'm not familiar with Current Language, but the Locale Identifier property of the AS connection string expects a LCID. I used this a while back for dynamic languages (see http://blogs.msdn.com/bwelcker/archive/2005/07/03/435130.aspx) If this doesn't work, you could try posting over at the SSAS forum.|||

Thanks Brian.

The Locale Identifier was exactly what I was looking for! The only reason I asked about the Current Language was becouse it was the only advanced parameter I found that might have somthing to do with language. But when adding Locale Identifier directly to the string it worked perfectly!

Thanks again.

Currency in Analysis Services

Hi,

All the reports with comes out as $ (ex.) ( $123,437,00) I am working for the requirement that the same report has to show in multiple currency (ex.) ($,Yen,Pound,AUS$ ).As i am in Analysis Side can anyone give good Suggestion to get the output. Do any one faced the same?

Take a look at the following entry on Mosha's Blogsite:

http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx

HTH,

Steve

Currency Formatting

Is there a way to format currency for check print using the reporting
services software? For example, instead of displaying $32,469.68, I could
display "thirty-two thousand four hundred sixty-nine and 68/100 dollars".
--
K. ThomasI would use a UDF on the SQL side to return this as a string
"K. Thomas" wrote:
> Is there a way to format currency for check print using the reporting
> services software? For example, instead of displaying $32,469.68, I could
> display "thirty-two thousand four hundred sixty-nine and 68/100 dollars".
> --
> K. Thomas|||Or you could write code and put in a custom assembly. In RS Books Online
look for "assembly references" in the index.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
news:3199963D-FEDA-4E92-B278-F50E38DEC8F6@.microsoft.com...
>I would use a UDF on the SQL side to return this as a string
> "K. Thomas" wrote:
>> Is there a way to format currency for check print using the reporting
>> services software? For example, instead of displaying $32,469.68, I
>> could
>> display "thirty-two thousand four hundred sixty-nine and 68/100 dollars".
>> --
>> K. Thomas|||Jeff,
In terms of efficiency, I stand well corrected,
Thanks,
Tony
"Jeff A. Stucker" wrote:
> Or you could write code and put in a custom assembly. In RS Books Online
> look for "assembly references" in the index.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
> news:3199963D-FEDA-4E92-B278-F50E38DEC8F6@.microsoft.com...
> >I would use a UDF on the SQL side to return this as a string
> >
> > "K. Thomas" wrote:
> >
> >> Is there a way to format currency for check print using the reporting
> >> services software? For example, instead of displaying $32,469.68, I
> >> could
> >> display "thirty-two thousand four hundred sixty-nine and 68/100 dollars".
> >> --
> >> K. Thomas
>
>|||I have no idea which would be more efficient, I just like knowing there are
multiple options... ;-)
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
news:2C1B882D-A985-4CD0-B791-46A4BF12F8B3@.microsoft.com...
> Jeff,
> In terms of efficiency, I stand well corrected,
> Thanks,
> Tony
> "Jeff A. Stucker" wrote:
>> Or you could write code and put in a custom assembly. In RS Books Online
>> look for "assembly references" in the index.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
>> news:3199963D-FEDA-4E92-B278-F50E38DEC8F6@.microsoft.com...
>> >I would use a UDF on the SQL side to return this as a string
>> >
>> > "K. Thomas" wrote:
>> >
>> >> Is there a way to format currency for check print using the reporting
>> >> services software? For example, instead of displaying $32,469.68, I
>> >> could
>> >> display "thirty-two thousand four hundred sixty-nine and 68/100
>> >> dollars".
>> >> --
>> >> K. Thomas
>>

Currency Formats

Is there any dynamic way through Reporting Services where you can use
different currency formats on reports? For example, we want to be able to
display in US dollars and /or Euros.Change the language property for the desired text box to English - Irish (or
whatever) and change the format to currency. btw the character code for Euro
is ALT-0128.
"nscotia17" wrote:
> Is there any dynamic way through Reporting Services where you can use
> different currency formats on reports? For example, we want to be able to
> display in US dollars and /or Euros.|||You can also hide and display fields, rows, groups based upon a visiblitiy
expression to match whatever idetifies your currency as â?¬ or $ in your data.
"nscotia17" wrote:
> Is there any dynamic way through Reporting Services where you can use
> different currency formats on reports? For example, we want to be able to
> display in US dollars and /or Euros.

Currency format when exporting to excel

Hi,

I have a problem with the number format when i export my reports to excel with Reporting Services. I set numbers as currency with the command FormatCurrency() in visual studio, but when i export the report to excel, the numbers are considered as text.

Does anyone have a solution?
Thanks in advance.

Use the Format property on the cell/textbox, for currency the format string should be c<number decimal places>, i.e. c0 prints the currency figure with no decimal places. Once you have done this, remove the FormatCurrency() function from the expression as it overrides anything you have in the Format property. The reason the FormatCurrency() does not work when exporting to Excel is because its return value is a string, so Excel is confused - is it a currency or is it a string?

|||Thanks a lot, it works perfectly!!!! I'm so releaved

Currency format in reporting services

I have what is likely a simple question but I have had no luck in tracking
down an answer so far. I have a report that I've created in RS that is for an
English company and I want to display the output in pounds as opposed to $.
When I go to a given text-box and click the format/currency option all I get
is $. I realize that RS tries to default to the currency setting on the
location the report is run but since the report will be hosted in the states
that's not going to help. All I need to do is format a text box as pounds!
Anyone have any ideas?
Thanks in advance.I noticed that if you click on the text box and look at the properties for it
that there is a language property. Change it from default to English (United
Kingdom) and that will change the currency symbol to the other symbol. I had
something formatted as currency and tried this and it seemed to work.
"littleren" wrote:
> I have what is likely a simple question but I have had no luck in tracking
> down an answer so far. I have a report that I've created in RS that is for an
> English company and I want to display the output in pounds as opposed to $.
> When I go to a given text-box and click the format/currency option all I get
> is $. I realize that RS tries to default to the currency setting on the
> location the report is run but since the report will be hosted in the states
> that's not going to help. All I need to do is format a text box as pounds!
> Anyone have any ideas?
> Thanks in advance.|||Thank You Kim! It worked perfectly.
"KimB" wrote:
> I noticed that if you click on the text box and look at the properties for it
> that there is a language property. Change it from default to English (United
> Kingdom) and that will change the currency symbol to the other symbol. I had
> something formatted as currency and tried this and it seemed to work.
> "littleren" wrote:
> > I have what is likely a simple question but I have had no luck in tracking
> > down an answer so far. I have a report that I've created in RS that is for an
> > English company and I want to display the output in pounds as opposed to $.
> > When I go to a given text-box and click the format/currency option all I get
> > is $. I realize that RS tries to default to the currency setting on the
> > location the report is run but since the report will be hosted in the states
> > that's not going to help. All I need to do is format a text box as pounds!
> >
> > Anyone have any ideas?
> >
> > Thanks in advance.|||I'm glad it helped!
"littleren" wrote:
> Thank You Kim! It worked perfectly.
>
> "KimB" wrote:
> > I noticed that if you click on the text box and look at the properties for it
> > that there is a language property. Change it from default to English (United
> > Kingdom) and that will change the currency symbol to the other symbol. I had
> > something formatted as currency and tried this and it seemed to work.
> >
> > "littleren" wrote:
> >
> > > I have what is likely a simple question but I have had no luck in tracking
> > > down an answer so far. I have a report that I've created in RS that is for an
> > > English company and I want to display the output in pounds as opposed to $.
> > > When I go to a given text-box and click the format/currency option all I get
> > > is $. I realize that RS tries to default to the currency setting on the
> > > location the report is run but since the report will be hosted in the states
> > > that's not going to help. All I need to do is format a text box as pounds!
> > >
> > > Anyone have any ideas?
> > >
> > > Thanks in advance.|||Hi,
Is it possible to apply your machine's Regional Settings to the currency
symbol Meaning, when I change my Windows' Regional Settings to English/UK,
currency will display with a pound symbol, if South Africa, with an R, or if
US. with a $?
Regards,
PM
"KimB" wrote:
> I'm glad it helped!
> "littleren" wrote:
> > Thank You Kim! It worked perfectly.
> >
> >
> > "KimB" wrote:
> >
> > > I noticed that if you click on the text box and look at the properties for it
> > > that there is a language property. Change it from default to English (United
> > > Kingdom) and that will change the currency symbol to the other symbol. I had
> > > something formatted as currency and tried this and it seemed to work.
> > >
> > > "littleren" wrote:
> > >
> > > > I have what is likely a simple question but I have had no luck in tracking
> > > > down an answer so far. I have a report that I've created in RS that is for an
> > > > English company and I want to display the output in pounds as opposed to $.
> > > > When I go to a given text-box and click the format/currency option all I get
> > > > is $. I realize that RS tries to default to the currency setting on the
> > > > location the report is run but since the report will be hosted in the states
> > > > that's not going to help. All I need to do is format a text box as pounds!
> > > >
> > > > Anyone have any ideas?
> > > >
> > > > Thanks in advance.

Culture Settings Problem

Hi everyone,
My name is Ed Allison. I am very new to reporting services (about 2 weeks,
in between working on a lot of other stuff), and have had no luck attempting
to track down a particular problem.
I am running Visual Studio 2005 and Reporting Services 2005 on Windows XP
(fully service packed etc.), but the data source for my reports is SQL
Server 2000. I only use VS2005 for reports, as we are still on vs2003 for
the rest of our products. It looks as if, somewhere along the line, a
culture setting has been set wrong. This problem is occurring at the design
stage, i.e. I have not loaded the reports onto the report server.
I am working in the UK. I have double checked that my regional and language
settings under Control Panel and they are set to English(UK).
Under Tools>Options>Environment>International Settings, Language is set to
'Same as Microsoft Windows'.
Whenever I format to currency (using "C" as the formatstring, generally),
the amount is returned in $, not £. Whenever I have date parameters on a
stored procedure for a report dataset, the dates are initially registered in
UK format (dd/mm/yyyy), but as soon as I click the 'view report button', the
dates are set in the parameter windows to mm/dd/yyyy format. This causes an
error when such a conversion yields an invalid date, e.g. '30/01/2006' to
'01/30/2006'.
I have searched in vain around visual studio 2005 for anything relating to a
culture setting specific to it without success. I have also tried MSDN and
Books Online, but no luck. I have checked every user account on my local
machine to see if one was entered with US culture settings, but no luck.
Report Services Configuration also does not appear to mention culture.
If anyone could help, I would be most grateful.
Ed AllisonI have found the solution to this, and am thoroughly embarrassed to have
missed it.
For the sake of anyone new like me who has a similar problem with their
culture settings apparently being ignored - there is a Language property on
the Report that defaults to English(United States). If this is set to the
global User.Language, formatting will correspond to culture settings on the
report client. This property is also present in individual textboxes.
"Ed Allison" <ed@.optix.co.uk> wrote in message
news:ODg7mZbJGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi everyone,
> My name is Ed Allison. I am very new to reporting services (about 2
> weeks, in between working on a lot of other stuff), and have had no luck
> attempting to track down a particular problem.
> I am running Visual Studio 2005 and Reporting Services 2005 on Windows XP
> (fully service packed etc.), but the data source for my reports is SQL
> Server 2000. I only use VS2005 for reports, as we are still on vs2003 for
> the rest of our products. It looks as if, somewhere along the line, a
> culture setting has been set wrong. This problem is occurring at the
> design stage, i.e. I have not loaded the reports onto the report server.
> I am working in the UK. I have double checked that my regional and
> language settings under Control Panel and they are set to English(UK).
> Under Tools>Options>Environment>International Settings, Language is set to
> 'Same as Microsoft Windows'.
> Whenever I format to currency (using "C" as the formatstring, generally),
> the amount is returned in $, not £. Whenever I have date parameters on a
> stored procedure for a report dataset, the dates are initially registered
> in UK format (dd/mm/yyyy), but as soon as I click the 'view report
> button', the dates are set in the parameter windows to mm/dd/yyyy format.
> This causes an error when such a conversion yields an invalid date, e.g.
> '30/01/2006' to '01/30/2006'.
> I have searched in vain around visual studio 2005 for anything relating to
> a culture setting specific to it without success. I have also tried MSDN
> and Books Online, but no luck. I have checked every user account on my
> local machine to see if one was entered with US culture settings, but no
> luck. Report Services Configuration also does not appear to mention
> culture.
> If anyone could help, I would be most grateful.
> Ed Allison
>

Cubes in Report manager?

Can OLAP cubes be stored on report manager aswel as reporting services reports?
Many thanks
GregNot sure what you mean. Cubes can be data sources for reporting services.
You can have a shared data connection stored in reporting services, and that
data source can be analysis services. Is that what you're asking?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:B38B7CC1-7390-4807-8A76-E928B2046044@.microsoft.com...
> Can OLAP cubes be stored on report manager aswel as reporting services
> reports?
> Many thanks
>
> Greg|||He might be asking about local cubes. You could store the .cub file in RS
but the server wouldn't know anything special about it. I don't think the AS
engine would be able to load it either.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:u5rQ3%23F5EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Not sure what you mean. Cubes can be data sources for reporting services.
> You can have a shared data connection stored in reporting services, and
> that data source can be analysis services. Is that what you're asking?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:B38B7CC1-7390-4807-8A76-E928B2046044@.microsoft.com...
>> Can OLAP cubes be stored on report manager aswel as reporting services
>> reports?
>> Many thanks
>>
>> Greg
>

Cube processed time always shows current time.

I have an issue with the "processed" time that shows on the analysis
manager. Every time I open Analysis Services and click on my database, one
of my cubes shows the current time for being "processed" under metadata.
Not all of my cubes shows this time. My database and cube have the same
name (don't know if this means anything, but my other cubes have different
names.
Any help?
Eric
Message posted via http://www.webservertalk.comYou might get better help over in microsoft.public.sqlserver.olap or
microsoft.public.sqlserver.datawarehouse
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Eric Wiltrout via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:55c55310d5784032b6089b6f75c89123@.SQ
webservertalk.com...
> I have an issue with the "processed" time that shows on the analysis
> manager. Every time I open Analysis Services and click on my database,
one
> of my cubes shows the current time for being "processed" under metadata.
> Not all of my cubes shows this time. My database and cube have the same
> name (don't know if this means anything, but my other cubes have different
> names.
> Any help?
> Eric
> --
> Message posted via http://www.webservertalk.com

Cube Partition

How do you slice a Cube Partition, if you want it to slice by "less than" instead of "equal to"? Analysis Services does not give you a choice.
Example: Time.FiscalYear AllTime.FY2005

Unfortunately, it looks like it can't be done without explicitly naming all the members included in the "less than" slice. For instance, if you wanted "less than" 2003, generally in MDX you'd just say {null:[Date].[Calendar Year].&[2003]}. But, for reasons I'll explain below, it doesn't appear that SSAS allows such an expression as the slice for a partition. Instead, you have to manually list all the members: {[Date].[Calendar Year].&[2001],[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]}

If I understand this correctly, the "slice" for a partition has to be a string which resolves to a set. The way SSAS resolves it to a set is by running it through StrToSet("your slice",CONSTRAINED). The CONSTRAINED parameter tells it to only accept a simple set of explicitly named members. So StrToSet("{null:[Date].[Calendar Year].&[2003]}",CONSTRAINED) blows up during processing with the following error: "Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated."

Though that's not a very good error message, I think that's what's happening because the following MDX query blows up:

select StrToSet("{null:[Date].[Calendar Year].&[2003]}",CONSTRAINED) on 0
from [Adventure Works]

One problem is that when you do a syntax check in Visual Studio when you're constructing the partition slice, it says it checks out fine... I've reported this as a bug:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=155391

Just as reference on where I got my info...

Info on what the Slice property of a partition allows (i.e. only something that works with StrToSet and CONSTRAINED):
http://msdn2.microsoft.com/en-us/library/microsoft.analysisservices.partition.slice(d=ide).aspx

Info on how the CONSTRAINED parameter works:
http://cwebbbi.spaces.msn.com/blog/cns!7B84B0F2C239489A!488.entry

Cube Partition

How do you slice a Cube Partition, if you want it to slice by "less than" instead of "equal to"? Analysis Services does not give you a choice.
Example: Time.FiscalYear AllTime.FY2005

Unfortunately, it looks like it can't be done without explicitly naming all the members included in the "less than" slice. For instance, if you wanted "less than" 2003, generally in MDX you'd just say {null:[Date].[Calendar Year].&[2003]}. But, for reasons I'll explain below, it doesn't appear that SSAS allows such an expression as the slice for a partition. Instead, you have to manually list all the members: {[Date].[Calendar Year].&[2001],[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]}

If I understand this correctly, the "slice" for a partition has to be a string which resolves to a set. The way SSAS resolves it to a set is by running it through StrToSet("your slice",CONSTRAINED). The CONSTRAINED parameter tells it to only accept a simple set of explicitly named members. So StrToSet("{null:[Date].[Calendar Year].&[2003]}",CONSTRAINED) blows up during processing with the following error: "Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated."

Though that's not a very good error message, I think that's what's happening because the following MDX query blows up:

select StrToSet("{null:[Date].[Calendar Year].&[2003]}",CONSTRAINED) on 0
from [Adventure Works]

One problem is that when you do a syntax check in Visual Studio when you're constructing the partition slice, it says it checks out fine... I've reported this as a bug:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=155391

Just as reference on where I got my info...

Info on what the Slice property of a partition allows (i.e. only something that works with StrToSet and CONSTRAINED):
http://msdn2.microsoft.com/en-us/library/microsoft.analysisservices.partition.slice(d=ide).aspx

Info on how the CONSTRAINED parameter works:
http://cwebbbi.spaces.msn.com/blog/cns!7B84B0F2C239489A!488.entry

Cube not refreshing?

AS and RS 2005

My cube is built on views both for dimensions and the fact table.

I am using reporting services and the report i have designed works fine except it does not update when i change the data. I can see the change in a select from the view so it is definitly there. It does refresh if i reprocess the cube. This happens in rolap as well as molap.

Any Ideas?

Thanks,

Jules

Moving to Reporting Services forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

why have you moved this?

does that mean it is reporting services that is caching the data?

It looks more like a problem with analysis services to me given that the report displays the correct data if a reprocess the cube.

Whats you thinking here.

Please help

Thanks,

Jules

|||

Most chances it is Reporting services problem. You can try and browse your AS cube using cube browser built-in in SQL Management studio to see if new numbers are avaliable.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cube Model

After generating a Cube Model (model from an OLAP source) from either Reporting Services or SSMS - is there any way to modify the model and re-deploy? It appears there is not - as there are ways for OLTP data sources.
This guy has a solution:
http://codebetter.com/blogs/brendan.tompkins/archive/2007/02/28/SQL-2005-Report-Models-from-an-Analysis-Server-OLAP-Cube.aspx
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:61D3B225-6B31-4FE3-8C8F-D9330DB04EE9@.microsoft.com...
After generating a Cube Model (model from an OLAP source) from either Reporting Services or SSMS - is there any way to modify the model and re-deploy? It appears there is not - as there are ways for OLTP data sources.

Cube Model

After generating a Cube Model (model from an OLAP source) from either Report
ing Services or SSMS - is there any way to modify the model and re-deploy?
It appears there is not - as there are ways for OLTP data sources.This guy has a solution:
http://codebetter.com/blogs/brendan...-OLAP-Cube.aspx
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:61D3B225-6B31-4FE3-8
C8F-D9330DB04EE9@.microsoft.com...
After generating a Cube Model (model from an OLAP source) from either Report
ing Services or SSMS - is there any way to modify the model and re-deploy?
It appears there is not - as there are ways for OLTP data sources.

cube measures on rows in reporting services....

Is there anyway for reporting services to display measures on rows instead
of columns as in Excel?
I'm trying to format my data in rows but this doesn't seems possible in
reporting services... does anyone has any examples of how this can be done?Create a shared datasource to Foodmart 200 and try this RDL. HEre is a sample
report that i created with measures on rows.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33in</Height>
<CanGrow>true</CanGrow>
<Value>Report20</Value>
</Textbox>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.72in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!Measures_Unit_Sales.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.24in</Height>
</MatrixRow>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!Measures_Profit.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.24in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>FoodMart_2000</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_Customers_Country">
<GroupExpressions>
<GroupExpression>=Fields!Customers_Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!Customers_Country.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="Customers_Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<FontSize>12pt</FontSize>
<Color>DarkRed</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>Customers_Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Customers_Country.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.24in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>2in</Width>
<Top>0.33in</Top>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<StaticRows>
<StaticRow>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<FontSize>12pt</FontSize>
<Color>DarkRed</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Units</Value>
</Textbox>
</ReportItems>
</StaticRow>
<StaticRow>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<FontSize>12pt</FontSize>
<Color>DarkRed</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Profit</Value>
</Textbox>
</ReportItems>
</StaticRow>
</StaticRows>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>2.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="FoodMart 2000">
<DataSourceReference>FoodMart 2000</DataSourceReference>
</DataSource>
</DataSources>
<Width>5in</Width>
<DataSets>
<DataSet Name="FoodMart_2000">
<Fields>
<Field Name="Customers_Country">
<DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_Unit_Sales">
<DataField>[Measures].[Unit Sales]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_Profit">
<DataField>[Measures].[Profit]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>select
{{[Customers].[Country].Members}}on rows,
{ [Measures].[Unit Sales],[Measures].[Profit]} on columns
from Sales</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
"Nestor" wrote:
> Is there anyway for reporting services to display measures on rows instead
> of columns as in Excel?
> I'm trying to format my data in rows but this doesn't seems possible in
> reporting services... does anyone has any examples of how this can be done?
>
>

Cube Deploy Error

Hello, I'm trying to deploy a cube on Analysis Services and I'm getting the next error:

Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: DSAP PRODUCTO of Dimension: DSAP PRODUCTO from Database: SAPIN_BI, Cube: Sapin BI Dllo, Measure Group: DSAP COMBOS, Partition: DSAP COMBOS, Record: 1.

I'm sure the data is consistent and has integrity.... and I verified It ...

Could you please give me a clue ?

What do I have to post or which script do I have to send you ?

Thanks a lot !

jortiz wrote:

Hello, I'm trying to deploy a cube on Analysis Services and I'm getting the next error:

Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: DSAP PRODUCTO of Dimension: DSAP PRODUCTO from Database: SAPIN_BI, Cube: Sapin BI Dllo, Measure Group: DSAP COMBOS, Partition: DSAP COMBOS, Record: 1.

I'm sure the data is consistent and has integrity.... and I verified It ...

Could you please give me a clue ?

What do I have to post or which script do I have to send you ?

Thanks a lot !

Hello, Is there any service like a kind of remote desktop via Web, to show you the problem ?

|||

Assuming your cube is built on top of a SQL Server relational database, your best bet might be to use Profiler to capture the SQL queries being generated by SSAS as it processes your cube. Then look at the queries and try to determine what query is returning unexpected data. More than likely the problem lies in the design of the dimension usage within the cube. Perhaps one of the dimensions is related to one of the measure groups based on the wrong attribute or fact table column?

HTH,

Dave Fackler

|||

Dave Fackler wrote:

Assuming your cube is built on top of a SQL Server relational database, your best bet might be to use Profiler to capture the SQL queries being generated by SSAS as it processes your cube. Then look at the queries and try to determine what query is returning unexpected data. More than likely the problem lies in the design of the dimension usage within the cube. Perhaps one of the dimensions is related to one of the measure groups based on the wrong attribute or fact table column?

HTH,

Dave Fackler

Hi Dave, It doesn't matter which dimensions and fact table I use, I get a similar error like this:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_HSAP_REDENCIONES, Column: NMPRODUCTO_KEY, Value: 1. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: DSAP PRODUCTO of Dimension: DSAP PRODUCTO from Database: SAPIN_BI, Cube: Sapin BI Dllo, Measure Group: HSAP REDENCIONES, Partition: HSAP REDENCIONES, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'HSAP REDENCIONES' partition of the 'HSAP REDENCIONES' measure group for the 'Sapin BI Dllo' cube from the SAPIN_BI database.

|||

Based on the errror message, it would appear that your fact table has a column named NMPRODUCTO_KEY and a record was read from the fact table that has a value of 1 in that column. In addition, the measure group HSAP REDENCIONES would appear to be related to the DSAP PRODUCTO dimension using the NMPRODUCTO_KEY column to match the DSAP PRODUCTO attribute (which appears to be serving as the key attribute in the dimension). The question is: do any of the members in the DSAP PRODUCTO dimension have a key value of 1 for the DSAP PRODUCTO attribute? What column in the underlying dimension table is the DSAP PRODUCTO attribute based on?

Dave F.

|||

Sorry if I interfere. Have you added a new column to your fact table lately that was not in the processed cube initially?

If you use query binding for the partition(s) this new column will not appear automatically. You will have to add it yourself.

This looks like a problem that I had a few days ago.

Regards

Thomas Ivarsson

|||

Thomas Ivarsson wrote:

Sorry if I interfere. Have you added a new column to your fact table lately that was not in the processed cube initially?

If you use query binding for the partition(s) this new column will not appear automatically. You will have to add it yourself.

This looks like a problem that I had a few days ago.

Regards

Thomas Ivarsson

Thank you so much for your answers !!

|||

What solution did work?

Regards

Thomas Ivarsson

Cube DDL reverse engineer ?

Hi guys

I have built several cubes. However I would need to create local cubes from the built cubes in Analysis Services. As a result I need to work out the DDL for my existing cubes. However the cube contains many mdx calculated members. I was wondering if it would be easier to simply generate the DDL from my existing cube in Analysis Services.

Does anyone know any third party tools that I can use to reverse engineer the DDL from the built cubes in Analysis Services ?

Thanks

Tom

Why not just load it in Server Manager and tell it to Script Database as Create. That should give you the full DDL I believe.|||I want to script the cube not the sql server database.

If there is a way could you please give a more detailed description of the process.

I really appreciate it ! thankyou !

Tom|||The cube is contained in an Analysis Services database, not a SQL Server database. Or are you saying that your AS database contains multiple cubes and you only want to script out one?

Start SQL Server Management Studio
When asked to connect to server drop the Server type down and pick Analysis Services
Expand the databases node.
Right click on a database
Select Script Database as Create To

Alternatively if you want just one cube expadn the cubes node
Right click on the cube you are interested in
Select Script Cube as Create To|||Start SQL Server Management Studio
When asked to connect to server drop the Server type down and pick Analysis Services
Expand the databases node.
Right click on a database
Select Script Database as Create To
__

I've started sql server enterprise manager. When I tried to make a new registration under a sql server group it DOES NOT pick up analysis services. I know they're running on different ports why would sql server manager pickup analysis services ?

can you confirm the process please. I really can't see it working. As there's no possible way for sql server manager to connect to analysis services.

Thankyou
Tom

|||Tom,

I think I see the problem. I'm betting you are using Analysis Services 2000, not Analysis Services 2005...

With SQL Server 2005, the new management tool (called SQL Server Management Studio) replaces Enteprise Manager in SQL Server 2000. SQL Server Management Studio does indeed have the ability to connect to Analysis Services as well as SQL Server. And it has the ability to script the DDL for an entire Analysis Services 2005 database as well as any portion thereof.

However, since you are likely using Analysis Services 2000, this won't work for you. If you need to generate the DDL to create a cube, you'll have to look to some third-party tool options for this as Analysis Manager does not have the ability to generate DDL. Take a look at the short list of options mentioned on Mosha's website at http://www.mosha.com/msolap/util.htm#Metadata. This should at least give you some ideas and options...

Dave Fackler
|||thanks for that at least now I have a good idea what the other person's talking about

Thanks
Tom

Cube Calculations and Report Model Problems

Hello all,

I understand this is an Analysis Services board, but due to the topic, I thought it would be as good of a place as any. I have also cross-posted to the reporting services group.

I've been working with SSRS for a year and a half now, but I am brand new to SSAS. I've gone through tutorials and did as much reading as I could.

The underlying data I am using is similar to the following:

Shift_Start Shift_End Machine_ID Breakdown_Min ... Quality_Pct
1/1/2005 7am 1/1/2005 5pm 535 120 .55

As you can tell, we have one record for each shift. This stores breakdown minutes (as well as many other additive measures). As it stands now, we've also stored percentages in the table (although this could be removed and calculated within the cube).

Because the percent won't add up across dimensions, I removed that measure and created a calculation. This calculation does the work necessary to come up with the percentages. I have no problems using the calculations in the cube browser.

This is where SSRS comes into play. I created a data source to point to the cube and generated a data model. I then used the Report Builder to create a report based on this model.

My problem is that I cannot see the calculations I defined. As I see it, mabye the report model isn't completely integrated with analysis services cubes yet? Has anyone else noticed this behavior and/or has anyone found a solution? I hope it is as simple as me missing a setting...

Thank you for taking the time...

Regards,

Dan

I hate to break it to you Dan but I think you will need to figure out a different way of doing it... here is some discussion about using Report Designer but I think the same would apply to Report Builder:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=64253&SiteID=1

The gist, I believe, is that you can use Calculated Measures but not Calculated Members. The other limitations on the Query, ie only measures on the columns is a real pain too. Oh well maybe it will be fixed in a service pack one of these years...

Sorry 'bout that =/

Cube Calculations and Report Model Problems

Hello all,

I understand this is an Analysis Services board, but due to the topic, I thought it would be as good of a place as any. I have also cross-posted to the reporting services group.

I've been working with SSRS for a year and a half now, but I am brand new to SSAS. I've gone through tutorials and did as much reading as I could.

The underlying data I am using is similar to the following:

Shift_Start Shift_End Machine_ID Breakdown_Min ... Quality_Pct
1/1/2005 7am 1/1/2005 5pm 535 120 .55

As you can tell, we have one record for each shift. This stores breakdown minutes (as well as many other additive measures). As it stands now, we've also stored percentages in the table (although this could be removed and calculated within the cube).

Because the percent won't add up across dimensions, I removed that measure and created a calculation. This calculation does the work necessary to come up with the percentages. I have no problems using the calculations in the cube browser.

This is where SSRS comes into play. I created a data source to point to the cube and generated a data model. I then used the Report Builder to create a report based on this model.

My problem is that I cannot see the calculations I defined. As I see it, mabye the report model isn't completely integrated with analysis services cubes yet? Has anyone else noticed this behavior and/or has anyone found a solution? I hope it is as simple as me missing a setting...

Thank you for taking the time...

Regards,

Dan

I hate to break it to you Dan but I think you will need to figure out a different way of doing it... here is some discussion about using Report Designer but I think the same would apply to Report Builder:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=64253&SiteID=1

The gist, I believe, is that you can use Calculated Measures but not Calculated Members. The other limitations on the Query, ie only measures on the columns is a real pain too. Oh well maybe it will be fixed in a service pack one of these years...

Sorry 'bout that =/

Cube calculation error

Upon building a cube in analysis services, i go to the calculations tab and i get an an error on the page... "Unexpected error occurred: Error in the application".!!!!

Thus, i can't do any calculated measures. Anyone, know what this is ?

Thanks LP

Check out http://support.microsoft.com/default.aspx/kb/926421.|||There are 2 DLLs msmdlocal.dll and msmgdsrv.dll in these folders:
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
C:\Program Files\Common Files\System\Ole DB

The ones in the PrivateAssemblies folder are older versions so you copy the ones from the OLE DB folder to the PrivateAssemblies folder and the problem goes away.