Extended properties created by Visual Studio on CLR UDF''s

When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,

AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.

What I haven't found anywhere on Help or MSDN online is what these extended properties mean.

Could someone enlighten me?

1. What is a description of the above properties?

2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?

3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?

Thanks a lot.

Aneela_B wrote:

When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,

AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.

What I haven't found anywhere on Help or MSDN online is what these extended properties mean.

Could someone enlighten me?

1. What is a description of the above properties?

2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?

3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?

AFAIK, the above properties do not have any use at the moment. They have no impact on debugging.

If you change the .NET code you have to re-deploy your assembly (either CREATE or ALTER).

Niels

Extended properties created by Visual Studio on CLR UDF''s

When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,

AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.

What I haven't found anywhere on Help or MSDN online is what these extended properties mean.

Could someone enlighten me?

1. What is a description of the above properties?

2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?

3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?

Thanks a lot.

Aneela_B wrote:

When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,

AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.

What I haven't found anywhere on Help or MSDN online is what these extended properties mean.

Could someone enlighten me?

1. What is a description of the above properties?

2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?

3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?

AFAIK, the above properties do not have any use at the moment. They have no impact on debugging.

If you change the .NET code you have to re-deploy your assembly (either CREATE or ALTER).

Niels

Expression Problem Using a Multi-Select Parameter

I have a rectangle region in a report that contains a graph and a table. I want to display that list region only when the user selects a "Select All" from a multi-select report parameter. This rectangle region is used only to display summary data for All Agencies.

My report also contains a list region with graphs and tables, where I display data for each agency (my detail group), and page-break on each agency.

The problem I am experiencing occurs when using the Expression Builder for the Visibility property for my rectangle and list regions. Since a multi-select parameter is an array, I am forced to select an element in my paramater such as =Parameters!Agency.Value(0). When the user chooses "(Select All)", the first element is the first agency in the list. I don't want that.

How can I get Reporting Services to display a rectangle or list region when "Select All" is chosen, and to hide that rectangle or list region when one or more agencies are chosen from a multi-select parameter?

I have tried using Agency.Label and I've tried other expressions such as Parameters!Agency.Count = Count(Agency.Value), etc, without success.

If you're on SP0 or SP2 or later, the Select All option is always there. It's not really a checkbox you can detect. It's just a shortcut way for selecting/deselecting all options.

I've reported this as an enhancement. You should be able to tell whether they've selected all possible options:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124515

At that link I describe a couple of possible workarounds. Hope that helps.

|||

There is currently no built-in functionality, but here are some ideas to achieve what you are looking for:

* if the multi value parameter has a pre-defined (constant) list of valid values, you know how many values are available for selection. The report parameters in RS 2005 expose a new property called .Count which tells you the count of selected parameter values (e.g. =Parameters!P1.Count). Hence, you could compare the count of the selected values with the count of the total values.

* if the multi value parameter has a dataset-based valid values list, you could just use the same field in a CountDistinct aggregate function to determine how many valid values are available, e.g. =CountDistinct(Fields!A.Value) and compare it again with the Count of selected values (e.g. =Parameters!P1.Count).

-- Robert

expression contains a function that cannot operate on a set

Hello,

When using this expression

right(Measures.CurrentMember.Name,5) = "Units" or (instr(", CZ, DE", right([ProducingPlant].CurrentMember.Name,4))>0 or instr(", CZ, DE", right([SellingPlant].CurrentMember.Name,4))>0)

gives me the following error

The expression contains a function that cannot operate on a set with more than 4,2924,967,296 tuples

Any help would be greatly appreciated.

Here are two examples of how you might use parsing functions in an MDX statement. Hopefully this can help indicate the problem.

B.

Code Snippet

with member [Measures].[x] as

RIGHT([Product].[Category].CurrentMember.Name,5)

select

x on 0,

[Product].[Category].Members on 1

from [Adventure Works];

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

FILTER(

Product.Category.Members,

LEFT(Product.Category.CurrentMember.Name="C"

) on 1

from [Adventure Works];

|||

I doubt whether this expression itself is causing the error, since there's no set involved. Is this perhaps the condition within a Filter(), where the set to be filtered might be a crossjoined set exceeding 4 billion tuples?

|||

Expression works fine for limited number of hierarchies..I get this error, only when it exceeds certain number.

|||

Thanks for the code snippet..

I tried to split my expressions & tested by having only: right(Measures.CurrentMember.Name,5) = "Units"

When trying to add the 4th dimension with out any measure, started receiving the error.

The idea behind this statement is that we have 'n' number of measures with volume & price measures. We need to display all the volume measures & restrict price measures based on the values in the table. Since, we follow a similar naming convention, I am using the above statement to compare the last 5 characters.

Any suggestions to fix this error?

|||

Well, could you share what those hierarchies/dimensions and the overall MDX look like, beyond this expression?

|||

The overall MDX Expression is defined for a particular Role "Europe Users" under "Enable Read Permissions"

right(Measures.CurrentMember.Name,5) = "Units" or (instr(", CZ, DE", right([ProducingPlant].CurrentMember.Name,4))>0 or instr(", CZ, DE", right([SellingPlant].CurrentMember.Name,4))>0)

I created an Excel pivot table using "Administrator" Role

- 3 dimensions with 2 property fields each

- 5 measures for 2 years

- 5 Page Fields

- All dimensions expanded

- All all these total Rows in Excel is only 250 max.

Now, I login as one of the "Europe Users" & try to open the excel file & click "refresh data".. I get this error message. It seem that the expression is not able to handle this many number of tuples.. Interestingly, it was working fine in SQL2000, but when I migrated to SQL 2005, I get this error message.

Thanks for the continued support & looking for your response.

|||

Since this worked in AS 2000, I can only guess that the internal implementation of cell security has changed, such that the size of the query cell space becomes an issue. Maybe someone from Microsoft will be able to shed more light on this scenario - the only idea I can suggest (since this cube is migrated from AS 2000) is to look at whether some of the original AS 2000 dimensions can be combined. Since dimensions in AS 2000 were strictly hierarchical, additional dimensions were sometimes added to hold additional attributes - these could be accommodated in a single dimension in AS 2005. In that case, "auto-exists" will reduce the number of tuples generated when stacking attributes in a pivot table. Could you describe the applicable dimensions and attributes in the Excel pivot table?

|||

Based on the KB article, http://support.microsoft.com/default.aspx?scid=kb;EN-US;940019, it looks like the problem is inevitable for complex queries that is operating on cell measure. Again, it boils down to the fact that how would this work in SQL 2000.

To answer your question, dimensions are customer, engine, parts & its related attributes. measures are units (direct) & price (calculated)

Any suggestions would be greatly appreciated

Logesh

|||

"To answer your question, dimensions are customer, engine, parts & its related attributes." - In that case, it's not clear which dimension(s) correspond to ProducingPlant and SellingPlant in the expression. Which are the largest dimensions/attributes; and is there any relation between the dimensions (like between engine and part)?

|||

Most of these dimensions are directly linked to the fact table

Customer -> Fact Table

Engines -> Fact Table

Parts -> Fact Table

ProducingPlant -> Fact Table

SellingPlant - > Fact Table

Except, Engine & Customer are related. In the engine dimension, one of the attribute is customer name

fact table - 330,000 records

customer- 120, 3 attributes

Engines - 2000, 5 attributes

parts - 10000,2 attributes

producing plant / selling plant - 100 records

Please let me know,

|||

"Engine & Customer are related. In the engine dimension, one of the attribute is customer name" - in that case, would it be acceptable to add the 3 Customer attributes to the Engine dimension (you could use a Named Query to join the dimension tables for the source)? Not an elegant solution, but if the scenario with errors involves stacking Engine and Customer attributes, the row count would be reduced from 240,000 to 2,000.

|||

Thanks again for your time.

For testing purpose, I removed the customer attribute from the Engine dimension. All have independant dimension directly related to the fact table. But, still the same issue...

My requirement is to show all the measures with units data & restrict price measures. He should be able to see the price only for his region.

- Units measures are pre-fixed by "Units"

- All Price measures are pre-fixed by "USD" & "Price"

Do you suggest possible ways to handle this security? So that I can change the way it is implemented currently. Because, I get this issue, only when I include the cell level security.

|||

Did you try removing the Customer dimension itself - what I suggested was to incorporate Customer attributes in the Engine dimension, rather than removing the Customer attribute from Engine?

expression contains a function that cannot operate on a set

Hello,

When using this expression

right(Measures.CurrentMember.Name,5) = "Units" or (instr(", CZ, DE", right([ProducingPlant].CurrentMember.Name,4))>0 or instr(", CZ, DE", right([SellingPlant].CurrentMember.Name,4))>0)

gives me the following error

The expression contains a function that cannot operate on a set with more than 4,2924,967,296 tuples

Any help would be greatly appreciated.

Here are two examples of how you might use parsing functions in an MDX statement. Hopefully this can help indicate the problem.

B.

Code Snippet

with member [Measures].[x] as

RIGHT([Product].[Category].CurrentMember.Name,5)

select

x on 0,

[Product].[Category].Members on 1

from [Adventure Works];

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

FILTER(

Product.Category.Members,

LEFT(Product.Category.CurrentMember.Name="C"

) on 1

from [Adventure Works];

|||

I doubt whether this expression itself is causing the error, since there's no set involved. Is this perhaps the condition within a Filter(), where the set to be filtered might be a crossjoined set exceeding 4 billion tuples?

|||

Expression works fine for limited number of hierarchies..I get this error, only when it exceeds certain number.

|||

Thanks for the code snippet..

I tried to split my expressions & tested by having only: right(Measures.CurrentMember.Name,5) = "Units"

When trying to add the 4th dimension with out any measure, started receiving the error.

The idea behind this statement is that we have 'n' number of measures with volume & price measures. We need to display all the volume measures & restrict price measures based on the values in the table. Since, we follow a similar naming convention, I am using the above statement to compare the last 5 characters.

Any suggestions to fix this error?

|||

Well, could you share what those hierarchies/dimensions and the overall MDX look like, beyond this expression?

|||

The overall MDX Expression is defined for a particular Role "Europe Users" under "Enable Read Permissions"

right(Measures.CurrentMember.Name,5) = "Units" or (instr(", CZ, DE", right([ProducingPlant].CurrentMember.Name,4))>0 or instr(", CZ, DE", right([SellingPlant].CurrentMember.Name,4))>0)

I created an Excel pivot table using "Administrator" Role

- 3 dimensions with 2 property fields each

- 5 measures for 2 years

- 5 Page Fields

- All dimensions expanded

- All all these total Rows in Excel is only 250 max.

Now, I login as one of the "Europe Users" & try to open the excel file & click "refresh data".. I get this error message. It seem that the expression is not able to handle this many number of tuples.. Interestingly, it was working fine in SQL2000, but when I migrated to SQL 2005, I get this error message.

Thanks for the continued support & looking for your response.

|||

Since this worked in AS 2000, I can only guess that the internal implementation of cell security has changed, such that the size of the query cell space becomes an issue. Maybe someone from Microsoft will be able to shed more light on this scenario - the only idea I can suggest (since this cube is migrated from AS 2000) is to look at whether some of the original AS 2000 dimensions can be combined. Since dimensions in AS 2000 were strictly hierarchical, additional dimensions were sometimes added to hold additional attributes - these could be accommodated in a single dimension in AS 2005. In that case, "auto-exists" will reduce the number of tuples generated when stacking attributes in a pivot table. Could you describe the applicable dimensions and attributes in the Excel pivot table?

|||

Based on the KB article, http://support.microsoft.com/default.aspx?scid=kb;EN-US;940019, it looks like the problem is inevitable for complex queries that is operating on cell measure. Again, it boils down to the fact that how would this work in SQL 2000.

To answer your question, dimensions are customer, engine, parts & its related attributes. measures are units (direct) & price (calculated)

Any suggestions would be greatly appreciated

Logesh

|||

"To answer your question, dimensions are customer, engine, parts & its related attributes." - In that case, it's not clear which dimension(s) correspond to ProducingPlant and SellingPlant in the expression. Which are the largest dimensions/attributes; and is there any relation between the dimensions (like between engine and part)?

|||

Most of these dimensions are directly linked to the fact table

Customer -> Fact Table

Engines -> Fact Table

Parts -> Fact Table

ProducingPlant -> Fact Table

SellingPlant - > Fact Table

Except, Engine & Customer are related. In the engine dimension, one of the attribute is customer name

fact table - 330,000 records

customer- 120, 3 attributes

Engines - 2000, 5 attributes

parts - 10000,2 attributes

producing plant / selling plant - 100 records

Please let me know,

|||

"Engine & Customer are related. In the engine dimension, one of the attribute is customer name" - in that case, would it be acceptable to add the 3 Customer attributes to the Engine dimension (you could use a Named Query to join the dimension tables for the source)? Not an elegant solution, but if the scenario with errors involves stacking Engine and Customer attributes, the row count would be reduced from 240,000 to 2,000.

|||

Thanks again for your time.

For testing purpose, I removed the customer attribute from the Engine dimension. All have independant dimension directly related to the fact table. But, still the same issue...

My requirement is to show all the measures with units data & restrict price measures. He should be able to see the price only for his region.

- Units measures are pre-fixed by "Units"

- All Price measures are pre-fixed by "USD" & "Price"

Do you suggest possible ways to handle this security? So that I can change the way it is implemented currently. Because, I get this issue, only when I include the cell level security.

|||

Did you try removing the Customer dimension itself - what I suggested was to incorporate Customer attributes in the Engine dimension, rather than removing the Customer attribute from Engine?

expression contains a function that cannot operate on a set

Hello,

When using this expression

right(Measures.CurrentMember.Name,5) = "Units" or (instr(", CZ, DE", right([ProducingPlant].CurrentMember.Name,4))>0 or instr(", CZ, DE", right([SellingPlant].CurrentMember.Name,4))>0)

gives me the following error

The expression contains a function that cannot operate on a set with more than 4,2924,967,296 tuples

Any help would be greatly appreciated.

Here are two examples of how you might use parsing functions in an MDX statement. Hopefully this can help indicate the problem.

B.

Code Snippet

with member [Measures].[x] as

RIGHT([Product].[Category].CurrentMember.Name,5)

select

x on 0,

[Product].[Category].Members on 1

from [Adventure Works];

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

FILTER(

Product.Category.Members,

LEFT(Product.Category.CurrentMember.Name="C"

) on 1

from [Adventure Works];

|||

I doubt whether this expression itself is causing the error, since there's no set involved. Is this perhaps the condition within a Filter(), where the set to be filtered might be a crossjoined set exceeding 4 billion tuples?

|||

Expression works fine for limited number of hierarchies..I get this error, only when it exceeds certain number.

|||

Thanks for the code snippet..

I tried to split my expressions & tested by having only: right(Measures.CurrentMember.Name,5) = "Units"

When trying to add the 4th dimension with out any measure, started receiving the error.

The idea behind this statement is that we have 'n' number of measures with volume & price measures. We need to display all the volume measures & restrict price measures based on the values in the table. Since, we follow a similar naming convention, I am using the above statement to compare the last 5 characters.

Any suggestions to fix this error?

|||

Well, could you share what those hierarchies/dimensions and the overall MDX look like, beyond this expression?

|||

The overall MDX Expression is defined for a particular Role "Europe Users" under "Enable Read Permissions"

right(Measures.CurrentMember.Name,5) = "Units" or (instr(", CZ, DE", right([ProducingPlant].CurrentMember.Name,4))>0 or instr(", CZ, DE", right([SellingPlant].CurrentMember.Name,4))>0)

I created an Excel pivot table using "Administrator" Role

- 3 dimensions with 2 property fields each

- 5 measures for 2 years

- 5 Page Fields

- All dimensions expanded

- All all these total Rows in Excel is only 250 max.

Now, I login as one of the "Europe Users" & try to open the excel file & click "refresh data".. I get this error message. It seem that the expression is not able to handle this many number of tuples.. Interestingly, it was working fine in SQL2000, but when I migrated to SQL 2005, I get this error message.

Thanks for the continued support & looking for your response.

|||

Since this worked in AS 2000, I can only guess that the internal implementation of cell security has changed, such that the size of the query cell space becomes an issue. Maybe someone from Microsoft will be able to shed more light on this scenario - the only idea I can suggest (since this cube is migrated from AS 2000) is to look at whether some of the original AS 2000 dimensions can be combined. Since dimensions in AS 2000 were strictly hierarchical, additional dimensions were sometimes added to hold additional attributes - these could be accommodated in a single dimension in AS 2005. In that case, "auto-exists" will reduce the number of tuples generated when stacking attributes in a pivot table. Could you describe the applicable dimensions and attributes in the Excel pivot table?

|||

Based on the KB article, http://support.microsoft.com/default.aspx?scid=kb;EN-US;940019, it looks like the problem is inevitable for complex queries that is operating on cell measure. Again, it boils down to the fact that how would this work in SQL 2000.

To answer your question, dimensions are customer, engine, parts & its related attributes. measures are units (direct) & price (calculated)

Any suggestions would be greatly appreciated

Logesh

|||

"To answer your question, dimensions are customer, engine, parts & its related attributes." - In that case, it's not clear which dimension(s) correspond to ProducingPlant and SellingPlant in the expression. Which are the largest dimensions/attributes; and is there any relation between the dimensions (like between engine and part)?

|||

Most of these dimensions are directly linked to the fact table

Customer -> Fact Table

Engines -> Fact Table

Parts -> Fact Table

ProducingPlant -> Fact Table

SellingPlant - > Fact Table

Except, Engine & Customer are related. In the engine dimension, one of the attribute is customer name

fact table - 330,000 records

customer- 120, 3 attributes

Engines - 2000, 5 attributes

parts - 10000,2 attributes

producing plant / selling plant - 100 records

Please let me know,

|||

"Engine & Customer are related. In the engine dimension, one of the attribute is customer name" - in that case, would it be acceptable to add the 3 Customer attributes to the Engine dimension (you could use a Named Query to join the dimension tables for the source)? Not an elegant solution, but if the scenario with errors involves stacking Engine and Customer attributes, the row count would be reduced from 240,000 to 2,000.

|||

Thanks again for your time.

For testing purpose, I removed the customer attribute from the Engine dimension. All have independant dimension directly related to the fact table. But, still the same issue...

My requirement is to show all the measures with units data & restrict price measures. He should be able to see the price only for his region.

- Units measures are pre-fixed by "Units"

- All Price measures are pre-fixed by "USD" & "Price"

Do you suggest possible ways to handle this security? So that I can change the way it is implemented currently. Because, I get this issue, only when I include the cell level security.

|||

Did you try removing the Customer dimension itself - what I suggested was to incorporate Customer attributes in the Engine dimension, rather than removing the Customer attribute from Engine?

expression contains a function that cannot operate on a set

Hello,

When using this expression

right(Measures.CurrentMember.Name,5) = "Units" or (instr(", CZ, DE", right([ProducingPlant].CurrentMember.Name,4))>0 or instr(", CZ, DE", right([SellingPlant].CurrentMember.Name,4))>0)

gives me the following error

The expression contains a function that cannot operate on a set with more than 4,2924,967,296 tuples

Any help would be greatly appreciated.

Here are two examples of how you might use parsing functions in an MDX statement. Hopefully this can help indicate the problem.

B.

Code Snippet

with member [Measures].[x] as

RIGHT([Product].[Category].CurrentMember.Name,5)

select

x on 0,

[Product].[Category].Members on 1

from [Adventure Works];

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

FILTER(

Product.Category.Members,

LEFT(Product.Category.CurrentMember.Name="C"

) on 1

from [Adventure Works];

|||

I doubt whether this expression itself is causing the error, since there's no set involved. Is this perhaps the condition within a Filter(), where the set to be filtered might be a crossjoined set exceeding 4 billion tuples?

|||

Expression works fine for limited number of hierarchies..I get this error, only when it exceeds certain number.

|||

Thanks for the code snippet..

I tried to split my expressions & tested by having only: right(Measures.CurrentMember.Name,5) = "Units"

When trying to add the 4th dimension with out any measure, started receiving the error.

The idea behind this statement is that we have 'n' number of measures with volume & price measures. We need to display all the volume measures & restrict price measures based on the values in the table. Since, we follow a similar naming convention, I am using the above statement to compare the last 5 characters.

Any suggestions to fix this error?

|||

Well, could you share what those hierarchies/dimensions and the overall MDX look like, beyond this expression?

|||

The overall MDX Expression is defined for a particular Role "Europe Users" under "Enable Read Permissions"

right(Measures.CurrentMember.Name,5) = "Units" or (instr(", CZ, DE", right([ProducingPlant].CurrentMember.Name,4))>0 or instr(", CZ, DE", right([SellingPlant].CurrentMember.Name,4))>0)

I created an Excel pivot table using "Administrator" Role

- 3 dimensions with 2 property fields each

- 5 measures for 2 years

- 5 Page Fields

- All dimensions expanded

- All all these total Rows in Excel is only 250 max.

Now, I login as one of the "Europe Users" & try to open the excel file & click "refresh data".. I get this error message. It seem that the expression is not able to handle this many number of tuples.. Interestingly, it was working fine in SQL2000, but when I migrated to SQL 2005, I get this error message.

Thanks for the continued support & looking for your response.

|||

Since this worked in AS 2000, I can only guess that the internal implementation of cell security has changed, such that the size of the query cell space becomes an issue. Maybe someone from Microsoft will be able to shed more light on this scenario - the only idea I can suggest (since this cube is migrated from AS 2000) is to look at whether some of the original AS 2000 dimensions can be combined. Since dimensions in AS 2000 were strictly hierarchical, additional dimensions were sometimes added to hold additional attributes - these could be accommodated in a single dimension in AS 2005. In that case, "auto-exists" will reduce the number of tuples generated when stacking attributes in a pivot table. Could you describe the applicable dimensions and attributes in the Excel pivot table?

|||

Based on the KB article, http://support.microsoft.com/default.aspx?scid=kb;EN-US;940019, it looks like the problem is inevitable for complex queries that is operating on cell measure. Again, it boils down to the fact that how would this work in SQL 2000.

To answer your question, dimensions are customer, engine, parts & its related attributes. measures are units (direct) & price (calculated)

Any suggestions would be greatly appreciated

Logesh

|||

"To answer your question, dimensions are customer, engine, parts & its related attributes." - In that case, it's not clear which dimension(s) correspond to ProducingPlant and SellingPlant in the expression. Which are the largest dimensions/attributes; and is there any relation between the dimensions (like between engine and part)?

|||

Most of these dimensions are directly linked to the fact table

Customer -> Fact Table

Engines -> Fact Table

Parts -> Fact Table

ProducingPlant -> Fact Table

SellingPlant - > Fact Table

Except, Engine & Customer are related. In the engine dimension, one of the attribute is customer name

fact table - 330,000 records

customer- 120, 3 attributes

Engines - 2000, 5 attributes

parts - 10000,2 attributes

producing plant / selling plant - 100 records

Please let me know,

|||

"Engine & Customer are related. In the engine dimension, one of the attribute is customer name" - in that case, would it be acceptable to add the 3 Customer attributes to the Engine dimension (you could use a Named Query to join the dimension tables for the source)? Not an elegant solution, but if the scenario with errors involves stacking Engine and Customer attributes, the row count would be reduced from 240,000 to 2,000.

|||

Thanks again for your time.

For testing purpose, I removed the customer attribute from the Engine dimension. All have independant dimension directly related to the fact table. But, still the same issue...

My requirement is to show all the measures with units data & restrict price measures. He should be able to see the price only for his region.

- Units measures are pre-fixed by "Units"

- All Price measures are pre-fixed by "USD" & "Price"

Do you suggest possible ways to handle this security? So that I can change the way it is implemented currently. Because, I get this issue, only when I include the cell level security.

|||

Did you try removing the Customer dimension itself - what I suggested was to incorporate Customer attributes in the Engine dimension, rather than removing the Customer attribute from Engine?

expression

In RS, is it possible to code an expression so that if a field contains the
word "STAT" that whole row is highlighted in another color? If not, how about
a that field?
I can do it with numeric values, not text.
Thanks.On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> In RS, is it possible to code an expression so that if a field contains the
> word "STAT" that whole row is highlighted in another color? If not, how about
> a that field?
> I can do it with numeric values, not text.
> Thanks.
In Layout view, select the field that you want to change the
background color for and select F4 (for the Properties window). To the
right of Background Color, select <Expression...> and enter an
expression similar to the following:
=iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That worked. I ad the syntax wrong. Thanks!
"EMartinez" wrote:
> On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> > In RS, is it possible to code an expression so that if a field contains the
> > word "STAT" that whole row is highlighted in another color? If not, how about
> > a that field?
> >
> > I can do it with numeric values, not text.
> >
> > Thanks.
>
> In Layout view, select the field that you want to change the
> background color for and select F4 (for the Properties window). To the
> right of Background Color, select <Expression...> and enter an
> expression similar to the following:
> =iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On May 24, 9:12 pm, brian <b...@.discussions.microsoft.com> wrote:
> That worked. I ad the syntax wrong. Thanks!
> "EMartinez" wrote:
> > On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> > > In RS, is it possible to code an expression so that if a field contains the
> > > word "STAT" that whole row is highlighted in another color? If not, how about
> > > a that field?
> > > I can do it with numeric values, not text.
> > > Thanks.
> > In Layout view, select the field that you want to change the
> > background color for and select F4 (for the Properties window). To the
> > right of Background Color, select <Expression...> and enter an
> > expression similar to the following:
> > =iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Exporting to PDF through web service produses blank pages

Hi all,
I am exporting to PDF through web service a report that contains matrix.
I get almost every second page a blank page.
The same report when exported to PDF by the report manager does not contain
blank pages.
Is it a bug, Is there any work around
Thanks,
YuvalNobody is going to answer your question. I've been asking it for days. I
emailed all those Microsoft bloggers. I posted here. Nothing. I did
enough research that I am conviced Microsoft knows about the issue.
Note they claim you can find known bugs here:
http://msdn.microsoft.com/bugs/
But you can only find bugs they want you to find (ie ones they know how to
fix). If you seach for reporting services, you will find 0 known bugs.
The problem is that the List containing the matrix is growing so much that
the report won't fit on one page. So it prints two pages. You can see this
by changing the background colour of the List that contains the matrix.
When you run it, you will find the coloured list extends onto the 'blank'
page.
I haven't seen a fix yet. You can shrink your matrix so that the list won't
grow onto the next page. But that's not usually an acceptable fix, because
it means leaving about a third of the page blank.
Seems to me there's no acceptable way to print a matrix report. This issue
affects the tiff, and I found a different issue in Excel.
Scott
"Yuval Rabinovitz" <yvlrb@.netvision.net.il> wrote in message
news:e66FusBkEHA.3848@.tk2msftngp13.phx.gbl...
> Hi all,
> I am exporting to PDF through web service a report that contains matrix.
> I get almost every second page a blank page.
> The same report when exported to PDF by the report manager does not
contain
> blank pages.
> Is it a bug, Is there any work around
> Thanks,
> Yuval
>|||I did eventaully get a reponse from Microsoft. Everyone was quite friendly,
but it took a while to find the person who knew the answer.
If you are not using subtotals in your matrix, you might try "KB article
875518 is FIX: Width of the list that contains a matrix grows unexpectedly
when exported to PDF." This hotfix (only available by calling support)
didn't solve my problem.
If you are using subtotals in your matrix (like I was), this is the answer.
This response was from Sarah Parra:
"
The problem is that the list always extends a certain amount to the right
and bottom of the matrix, and the amount that it extends is based directly
on how wide (for column totals) or tall (for row totals) the total textbox
is. If you look at your report in the designer, in the bottom right corner,
under the totals boxes, there is a gray/brown empty space. That space is
effectively how much extra list you will see in the final output. We take
the size of the matrix to be the actual size that you see, minus the totals
boxes. That means that the list effectively looks like it is that much
bigger than the matrix (instead of being the exact same size). When the
rendering happens, it then adds that much padding to the right and below the
actual matrix.
This is a bug as well, it's just a different bug from the one described in
the article/hotfix. :) This one does have a fairly simple workaround. You
need to resize the list to be the size of the matrix, minus the size of the
totals columns/rows. The designer will not allow you to size the list
smaller than the matrix (which includes the totals), but you can do this
manually in the rdl. The following are the instructions I sent to another
customer on a sample report he had provided, so the sizes and line number
will not match up for you:
(1) Find the size of the column total box. You can just click on it and look
at the Width property. It's 0.875in.
(2) In the Solution Explorer, right click the .rdl file and select View
Code. In the code, find the Width definition for the List itself. You'll see
it on line 78, and it looks like this : <Width>5.875in</Width>. Change that
to that value, minus the size of the total column, .875. So just make it
5.0in, or you can make it a little bigger if you want some extra space
displayed to the right of the matrix itself. In the designer at this point,
it will look like the Total box is gone, but it's still there. You can do
the same thing for the row totals if you want. You will probably want to do
this, because if you have a situation where the matrix should end at the
very bottom of the page, you may still get an extra page if the extended
list height would force it.
(3) Preview and render the report. You should no longer see any blank pages.
"
"Scott Stonehouse" <scott@.mphec.ca> wrote in message
news:%23N3MVjFkEHA.2812@.tk2msftngp13.phx.gbl...
> Nobody is going to answer your question. I've been asking it for days. I
> emailed all those Microsoft bloggers. I posted here. Nothing. I did
> enough research that I am conviced Microsoft knows about the issue.
> Note they claim you can find known bugs here:
> http://msdn.microsoft.com/bugs/
> But you can only find bugs they want you to find (ie ones they know how to
> fix). If you seach for reporting services, you will find 0 known bugs.
> The problem is that the List containing the matrix is growing so much that
> the report won't fit on one page. So it prints two pages. You can see
this
> by changing the background colour of the List that contains the matrix.
> When you run it, you will find the coloured list extends onto the 'blank'
> page.
> I haven't seen a fix yet. You can shrink your matrix so that the list
won't
> grow onto the next page. But that's not usually an acceptable fix,
because
> it means leaving about a third of the page blank.
> Seems to me there's no acceptable way to print a matrix report. This
issue
> affects the tiff, and I found a different issue in Excel.
> Scott
>
> "Yuval Rabinovitz" <yvlrb@.netvision.net.il> wrote in message
> news:e66FusBkEHA.3848@.tk2msftngp13.phx.gbl...
> > Hi all,
> >
> > I am exporting to PDF through web service a report that contains matrix.
> > I get almost every second page a blank page.
> > The same report when exported to PDF by the report manager does not
> contain
> > blank pages.
> >
> > Is it a bug, Is there any work around
> >
> > Thanks,
> > Yuval
> >
> >
>

Exporting to Excel

Hi guys,
I have set up a report that exports perfectly into PDF,
but not into Excel.
The report contains a table with:
- a standard table header
- a table row: with alternating logos & a textbox directly
next to it
- a details area
- a table footer
(By alternating logos, I am referring to having different
images placed into a rectangle and different logos will
appear depending on certain conditions).
The problem: When exported into Excel, the logo/image does
not appear on the same row as the textbox that is directly
next to it. And the rest of the details from the table
gets pushed a few rows down.
Any info would be appreciated.
Regards,
LisaNo takers from Microsoft on this one?
Regards,
Lisa
"Lisa Liputra" wrote:
> Hi guys,
> I have set up a report that exports perfectly into PDF,
> but not into Excel.
> The report contains a table with:
> - a standard table header
> - a table row: with alternating logos & a textbox directly
> next to it
> - a details area
> - a table footer
> (By alternating logos, I am referring to having different
> images placed into a rectangle and different logos will
> appear depending on certain conditions).
> The problem: When exported into Excel, the logo/image does
> not appear on the same row as the textbox that is directly
> next to it. And the rest of the details from the table
> gets pushed a few rows down.
> Any info would be appreciated.
> Regards,
> Lisa
>
>
>

Exporting SQL Server 2005 data into formatted XML file

Hello,

I currently have a stored procedure in my SQL Server 2005 database that contains a simple SELECT FOR XML statement. I would like to call this stored procedure in C# and use C# to write an XML file such that when I open it in Visual Studio for editing it looks properly nested (rather than all on one line). Is there a quick way to do this?

I would like the file to look something like the following when opened:

Code Snippet

<Element1>

<Element2>

<Element3>Text</Element3>

<Element4>Text</Element4>

</Element2>

</Element1>

Thanks.

Create a DataSet put your table in it and call the DataSet.ReadXML method, if you want formatting you can use a Repeater for Webform. Try the link below to get started.

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

|||Thanks for your reply! I will look into that.