Referring to report items

Hi,
Is there any way to directly access a value contained in, for example, a
textbox?
If I were to create a textbox ('textbox1') with a default 'value' attribute
of "Hello World!", how would I make 'textbox2' get the value of 'textbox1'
and set its own 'value' to equal it?
I thought maybe it would be something like an expression in textbox2 along
the lines of: "=Controls!textbox1.Value"
Of course, I can see problems if controls aren't rendered etc, but I thought
I'd ask.
Thanks,
CraigTry =ReportItems!textbox1.Value
Regards,
Davy Ramirez
Winsight - Paris
http://www.winsight.fr
"CraigyBoop" <CraigyBoop@.discussions.microsoft.com> wrote in message
news:34FA58AE-092E-42F7-B371-CAC59F9E0568@.microsoft.com...
> Hi,
> Is there any way to directly access a value contained in, for example, a
> textbox?
> If I were to create a textbox ('textbox1') with a default 'value'
attribute
> of "Hello World!", how would I make 'textbox2' get the value of 'textbox1'
> and set its own 'value' to equal it?
> I thought maybe it would be something like an expression in textbox2 along
> the lines of: "=Controls!textbox1.Value"
> Of course, I can see problems if controls aren't rendered etc, but I
thought
> I'd ask.
> Thanks,
> Craig|||Please see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_7ilv.asp
for more information about ReportItems and Globals in general.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davy Ramirez" <davy.ramirez.removethis@.winsight.fr> wrote in message
news:%23gr%23UsssEHA.820@.TK2MSFTNGP12.phx.gbl...
> Try =ReportItems!textbox1.Value
> Regards,
> Davy Ramirez
> Winsight - Paris
> http://www.winsight.fr
> "CraigyBoop" <CraigyBoop@.discussions.microsoft.com> wrote in message
> news:34FA58AE-092E-42F7-B371-CAC59F9E0568@.microsoft.com...
>> Hi,
>> Is there any way to directly access a value contained in, for example, a
>> textbox?
>> If I were to create a textbox ('textbox1') with a default 'value'
> attribute
>> of "Hello World!", how would I make 'textbox2' get the value of
>> 'textbox1'
>> and set its own 'value' to equal it?
>> I thought maybe it would be something like an expression in textbox2
>> along
>> the lines of: "=Controls!textbox1.Value"
>> Of course, I can see problems if controls aren't rendered etc, but I
> thought
>> I'd ask.
>> Thanks,
>> Craig
>

Reference SQL Parser

I need to parse SQL statements directly and extract each segment
individually. Is there a way reference the Microsoft SQL Parser
directly from VB.Net?

Thanks!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"mCompany" <mail@.mvestcapital.com> wrote in message
news:40419b99$0$193$75868355@.news.frii.net...
> I need to parse SQL statements directly and extract each segment
> individually. Is there a way reference the Microsoft SQL Parser
> directly from VB.Net?
> Thanks!
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

If you're referring to the MSSQLParser COM object, I belive it's MS
internal, and isn't documented or supported. But if you can explain why you
need to do this, someone may have a better answer.

Simon|||mCompany (mail@.mvestcapital.com) writes:
> I need to parse SQL statements directly and extract each segment
> individually. Is there a way reference the Microsoft SQL Parser
> directly from VB.Net?

As Simon said, if you are more specific what you are up to, you can
get more precise advice.

The simple way is to send the statements to SQL Server, and first send
SET PARSEONLY ON.

If you want find where there statement boundaries, there is a really
obscure variant which requires you to use DB-Library. Which probably
precludes Visual Basic, because I doubt there is a DB-lib interface
for VB these days. You first send SET OFFSETS ON to SQL Server and then
you retrieve the information with dboffsets. I should hasten to add that
I played with this at some occasion. I got different results back from
SQL 6.5 and SQL 7, but none of them appeared correct to me.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||All we really need to do is split out the various SQL segments (SELECT,
FROM, WHERE, ORDER BY). It's not that difficult to write, but if
there's a quick way to do, we'll use it. Any ideas?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"mCompany" <mail@.mvestcapital.com> wrote in message
news:404202bd$0$194$75868355@.news.frii.net...
> All we really need to do is split out the various SQL segments (SELECT,
> FROM, WHERE, ORDER BY). It's not that difficult to write, but if
> there's a quick way to do, we'll use it. Any ideas?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

I don't know of any supported tool, but roughly parsing a very simple SELECT
into its parts should be straightforward with most string/regex tools.
However, you need to think about the limitations of that solution - SQL
queries can be written in numerous ways, and with subqueries, derived
tables, unions etc, you may have problems.

You still haven't really explained your goal, so may want to clarify that to
help someone give you a better answer. For example, are you implementing
custom syntax highlighting? Or are you accepting arbitrary SQL strings from
a client, and you want to ensure they are syntactically correct before
execution? Or do you want to validate SQL strings as being SQL92/99
compliant?

Simon|||The SET OFFSETS should work. If it does not, we'll write our own basic
parser.

Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||mCompany (mail@.mvestcapital.com) writes:
> All we really need to do is split out the various SQL segments (SELECT,
> FROM, WHERE, ORDER BY). It's not that difficult to write, but if
> there's a quick way to do, we'll use it. Any ideas?

Not that difficult? Boy, you are in for a real treat.

Believe me, I have written Perl code that parses bits of T-SQL, and this
is no simple exercise if you want it to work perfectly. If you can cut
down on the ambitions and miss out on some constructs, the work you need
to put in it, is of moderate size.

The reason why this is difficult is that T-SQL has a wretched ad-hoc
syntax onto which things have been added through the years, and lot of
old syntax retained for compatibility. For instance, do you think these
two statements parse the same?

SELECT 5ee
SELECT 5dd

They don't.

What I needed to was to find all references to table in the code. To this
end I sifted through the code and woke up on keywords that can be followed
by a table name: INSERT, UPDATE, DELETE, FROM and JOIN. FROM is most
tricky, because there may come a list of tables. And in the middle of
there may be a rowset fucntion like OPENQUERY or OPENXML. This is perfectly
legal T-SQL:

SELECT *
FROM a, b, OPENQUERY(REMOTESRV, 'SELECT * FROM a') AS c, d
JOIN e ON a.col = e.col, f
WHERE a.col2 = b.col2
AND a.col3 = c.col3
AND a.col4 = d.col4
AND e.col5 = f.col5

But the code I wrote will not find d and f. It would be crazy to write
code like that.

A tip is that start by doing a pass that eliminates all constants, and
replaces all string literals with simple tokens. String literals here
includes all that is enclosed in '', "" and [].

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson