KPI Annotations property field

If you look through the the properties collection of a KPI in ADOMD, the last item is called "Annotations". Does anyone know how to set this property through BIDS?

We need to pass additional data to our client app for each KPI in order to make up for incomplete functionality and bugs in Analysis Services in regards to formatting, and we would like to use this field if possible. "Annotations" is not the same as "Description" by the way. We may have to prepend XML data to the description field if there is not an alternative.

Any help would be appreciated.

Thanks,

Terry

and bugs in Analysis Services in regards to formatting

Can you please provide more details about bugs in formatting of KPIs ? I beleive any formatting issue can be solved without going to annotations.

|||

Terry and I work together and I'm replying on his behalf...

The issue we have happens when the KPI value and goal are evaluated in the status or trend expression. For example, if both are formatted (using the Format function) as percent, the KPI status expression shows an error message of "type mismatch for the / operator." We've been getting around this by formatting them as "standard" in the KPI and formatting them in the client app by checking for values that don't start with a "$".

Recently, we came across another issue where the value returned was null. The KPI expression was checking for divide by zero errors but not for null numerators. The status expression blew up with the same error, presumably because the goal was still a formatted value. Removing the format from the Value and Goal or returning a formatted 0 instead of a null both resolve the problem.

It seems that the KPI framework is very sensitive to how values are formatted. This is why we've been thinking about moving the formatting to the client side by passing a format string in Current Time Member or by using Annotations. It seems it would be better to format the end result instead of worrying about how AS handles formats in Status and Trend. Following all the examples we've seen, we're using KPIValue and KPIGoal in the status expression instead of repeating the Value calculation multiple times.

Any advice would be greatly appreciated.

|||

Can you please explain a little more about "if both are formatted (using the Format function)". Do you mean that you use VBA's Format function in the expression of KPI ? If so - this is really bad practice, it has all kinds of problems associated with it. Why wouldn't you want to rely on standard MDX formatting capabilities - I cannot think of scenario where MDX won't be able to do what Format function can do.

It would be best if you will provide some examples of KPI expressions that you are using, and expected formatting.

Thanks,

Mosha (http://www.mosha.com/msolap)

|||

Yes, we are using VBA's Format in KPIs. We're using Format_String in calculations. It may be bad practice, but AdventureWorks and all of the books we have show examples of using it for KPIs. It would be great if there's a better way to do this. Here's sample code for one of the KPIs:

VALUE:

Case
When (Not IsEmpty( ([Measures].[Average Customers]) ))
And (Not IsEmpty( [Measures].[Annualized Contribution]))
Then Format(
([Measures].[Annualized Contribution])
/
([Measures].[Average Customers])
,"Currency")
Else
Format(0,"Currency")
End

If the Status is Null and the Goal is a formatted value this would have caused the type mismatch error. This is why we're returning a formatted 0 instead.

GOAL:

Format(300,"Currency") //Temporary goal

STATUS:

Case
When IsEmpty( KpiValue( "Average Customer Contribution" ) )
Then Null
When KpiValue( "Average Customer Contribution" ) /
KpiGoal ( "Average Customer Contribution" ) >= .90
Then 1
When KpiValue( "Average Customer Contribution" ) /
KpiGoal ( "Average Customer Contribution" ) < .85
And
KpiValue( "Average Customer Contribution" ) /
KpiGoal ( "Average Customer Contribution" ) >= .80
Then 0
Else -1
End

TREND:

Case
When [Date].[Calendar Year Hierarchy].CURRENTMEMBER.LEVEL Is
[Date].[Calendar Year Hierarchy].[(All)]
Then 0
When
VBA!ABS(
KpiValue( "Average Customer Contribution" ) -
(KpiValue ( "Average Customer Contribution" ),
[Date].[Month].CurrentMember.PrevMember)
/
(KpiValue ( "Average Customer Contribution" ),
[Date].[Month].CurrentMember.PrevMember)
) <=.05
Then 0
When
KpiValue( "Average Customer Contribution" ) -
(KpiValue ( "Average Customer Contribution" ),[Date].[Month].CurrentMember.PrevMember)
/
(KpiValue ( "Average Customer Contribution" ),[Date].[Month].CurrentMember.PrevMember)
>.05
Then 1
Else -1
End

We're currently developing on SP2 because of the LastNonEmpty performance problems in SP1. In several cases, our KPIs are just a calculated measures that have Format_String="Percent". To use them in the KPI and have the resulting KPI formatted as a percent, we have to multiple the calculated measure by 100 before the second format is applied. Analysis Services doesn't seem to know the underlying measure's format.

Thanks for your help.

|||

It may be bad practice, but AdventureWorks and all of the books we have show examples of using it for KPIs.

I went over all the KPIs in AdventureWorks cube, and I didn't see any one using Format function. Could you please point out which KPI uses it, and I will file a bug for it to be fixed in the next release of the sample. Or perhaps it is already fixed and we are using different versions of AdventureWorks ? I also would like to know which book shows such examples. You can contact me by mail if you don't want this to be published in the forum.

Now to your scenario. It seems like you want 'Currency' to be formatting of KPI's Value property. Using VBA!Format in the Value expression makes it return a string. Therefore all other properties which try to do arithmetic operations with KPIValue operate with strings instead. It is almost a miracle, but AS actually does support arithmetics on strings to some degree by converting them to numbers when possible. But this is a slippery road. For example, you will find that "300" - "100" = 200, but "300" + "100" = "300100", and not 400 !!! And there are, of course, all other problems when you work with strings that you have noticed already.

Here is how I would've done it. In the MDX Script you can add the following snippet (it also fixes couple of other minor problems with your expression))

CREATE HIDDEN MyKPIValue = IIF ( [Measures].[Average Customers] <> 0, [Measures].[Annualized Contribution]/[Measures].[Average Customers], NULL );

FORMAT_STRING(MyKPIValue) = 'Currency';

This calculated measure will be created as hidden, and then inside the Value expression of your KPI, you simply reference [Measures].[MyKPIValue].

HTH,

Mosha (http://www.mosha.com/msolap)

|||

I went through our current copy of AdventureWorks and didn't find it either. I may have seen it in an earlier version, or I may just be remembering incorrectly. I know I've seen it in other online articles. There's also a sample showing the use of VBA!ABS at DataBaseJournal.

Thanks for you explanation of strings in the KPIs. The type mismatch error makes sense now.

I think what we were missing here is that the value and goal really need to be created in the script and not in the KPI designer. We create a number of percentage ratios as KPIs where we may divide a currency amount by a count (just like ROA in AdventureWorks.) Using Format() seemed to be the only way to show this as a percent. We should be doing this calculation in the script and use Format_String. I just read another post of yours on solve order that suggests creating all the calculations in the script and only referring to them in the KPI designer is the right way to go.

It would be great to have documented best practices from Microsoft that keep us newbies away from problems like this. KPIs seem to be one of the least documented features.

Getting back to Terry's original question, is there a way of using annotations to pass additional info to the client for custom features? We plan on implementing spark lines and bullet graphs and it would be great if we could pass additional data to the client.

Thanks again for your help!

|||

> There's also a sample showing the use of VBA!ABS at DataBaseJournal.

VBA!ABS - is kosher to use, since it's a math function, and its return data type is number. And, BTW, the MDX articles in DataBaseJournal by William Pearson are usually very good - so this is a source I would trust.

> Getting back to Terry's original question, is there a way of using annotations to pass additional info to the client for custom features? We plan on implementing spark lines and bullet graphs and it would be great if we could pass additional data to the client.

Yes - it is certainly possible. Assuming you create your cubes and KPIs using AMO - here is the link to AMO documentation about how to add annotations to any AS object.

http://msdn2.microsoft.com/en-us/library/microsoft.analysisservices.modelcomponent.annotations.aspx

HTH,

Mosha (http://www.mosha.com/msolap)

|||

There's an open source project called BIDS Helper which is a Visual Studio Add-in. One feature lets you edit annotations on Analysis Services objects within BIDS:

http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Show%20Extra%20Properties&referringTitle=Home

|||I just looked at BIDs Helper. That is exactly what we were looking for. The other features will be very helpful as well. Thanks!!!

KPI Annotations property field

If you look through the the properties collection of a KPI in ADOMD, the last item is called "Annotations". Does anyone know how to set this property through BIDS?

We need to pass additional data to our client app for each KPI in order to make up for incomplete functionality and bugs in Analysis Services in regards to formatting, and we would like to use this field if possible. "Annotations" is not the same as "Description" by the way. We may have to prepend XML data to the description field if there is not an alternative.

Any help would be appreciated.

Thanks,

Terry

and bugs in Analysis Services in regards to formatting

Can you please provide more details about bugs in formatting of KPIs ? I beleive any formatting issue can be solved without going to annotations.

|||

Terry and I work together and I'm replying on his behalf...

The issue we have happens when the KPI value and goal are evaluated in the status or trend expression. For example, if both are formatted (using the Format function) as percent, the KPI status expression shows an error message of "type mismatch for the / operator." We've been getting around this by formatting them as "standard" in the KPI and formatting them in the client app by checking for values that don't start with a "$".

Recently, we came across another issue where the value returned was null. The KPI expression was checking for divide by zero errors but not for null numerators. The status expression blew up with the same error, presumably because the goal was still a formatted value. Removing the format from the Value and Goal or returning a formatted 0 instead of a null both resolve the problem.

It seems that the KPI framework is very sensitive to how values are formatted. This is why we've been thinking about moving the formatting to the client side by passing a format string in Current Time Member or by using Annotations. It seems it would be better to format the end result instead of worrying about how AS handles formats in Status and Trend. Following all the examples we've seen, we're using KPIValue and KPIGoal in the status expression instead of repeating the Value calculation multiple times.

Any advice would be greatly appreciated.

|||

Can you please explain a little more about "if both are formatted (using the Format function)". Do you mean that you use VBA's Format function in the expression of KPI ? If so - this is really bad practice, it has all kinds of problems associated with it. Why wouldn't you want to rely on standard MDX formatting capabilities - I cannot think of scenario where MDX won't be able to do what Format function can do.

It would be best if you will provide some examples of KPI expressions that you are using, and expected formatting.

Thanks,

Mosha (http://www.mosha.com/msolap)

|||

Yes, we are using VBA's Format in KPIs. We're using Format_String in calculations. It may be bad practice, but AdventureWorks and all of the books we have show examples of using it for KPIs. It would be great if there's a better way to do this. Here's sample code for one of the KPIs:

VALUE:

Case
When (Not IsEmpty( ([Measures].[Average Customers]) ))
And (Not IsEmpty( [Measures].[Annualized Contribution]))
Then Format(
([Measures].[Annualized Contribution])
/
([Measures].[Average Customers])
,"Currency")
Else
Format(0,"Currency")
End

If the Status is Null and the Goal is a formatted value this would have caused the type mismatch error. This is why we're returning a formatted 0 instead.

GOAL:

Format(300,"Currency") //Temporary goal

STATUS:

Case
When IsEmpty( KpiValue( "Average Customer Contribution" ) )
Then Null
When KpiValue( "Average Customer Contribution" ) /
KpiGoal ( "Average Customer Contribution" ) >= .90
Then 1
When KpiValue( "Average Customer Contribution" ) /
KpiGoal ( "Average Customer Contribution" ) < .85
And
KpiValue( "Average Customer Contribution" ) /
KpiGoal ( "Average Customer Contribution" ) >= .80
Then 0
Else -1
End

TREND:

Case
When [Date].[Calendar Year Hierarchy].CURRENTMEMBER.LEVEL Is
[Date].[Calendar Year Hierarchy].[(All)]
Then 0
When
VBA!ABS(
KpiValue( "Average Customer Contribution" ) -
(KpiValue ( "Average Customer Contribution" ),
[Date].[Month].CurrentMember.PrevMember)
/
(KpiValue ( "Average Customer Contribution" ),
[Date].[Month].CurrentMember.PrevMember)
) <=.05
Then 0
When
KpiValue( "Average Customer Contribution" ) -
(KpiValue ( "Average Customer Contribution" ),[Date].[Month].CurrentMember.PrevMember)
/
(KpiValue ( "Average Customer Contribution" ),[Date].[Month].CurrentMember.PrevMember)
>.05
Then 1
Else -1
End

We're currently developing on SP2 because of the LastNonEmpty performance problems in SP1. In several cases, our KPIs are just a calculated measures that have Format_String="Percent". To use them in the KPI and have the resulting KPI formatted as a percent, we have to multiple the calculated measure by 100 before the second format is applied. Analysis Services doesn't seem to know the underlying measure's format.

Thanks for your help.

|||

It may be bad practice, but AdventureWorks and all of the books we have show examples of using it for KPIs.

I went over all the KPIs in AdventureWorks cube, and I didn't see any one using Format function. Could you please point out which KPI uses it, and I will file a bug for it to be fixed in the next release of the sample. Or perhaps it is already fixed and we are using different versions of AdventureWorks ? I also would like to know which book shows such examples. You can contact me by mail if you don't want this to be published in the forum.

Now to your scenario. It seems like you want 'Currency' to be formatting of KPI's Value property. Using VBA!Format in the Value expression makes it return a string. Therefore all other properties which try to do arithmetic operations with KPIValue operate with strings instead. It is almost a miracle, but AS actually does support arithmetics on strings to some degree by converting them to numbers when possible. But this is a slippery road. For example, you will find that "300" - "100" = 200, but "300" + "100" = "300100", and not 400 !!! And there are, of course, all other problems when you work with strings that you have noticed already.

Here is how I would've done it. In the MDX Script you can add the following snippet (it also fixes couple of other minor problems with your expression))

CREATE HIDDEN MyKPIValue = IIF ( [Measures].[Average Customers] <> 0, [Measures].[Annualized Contribution]/[Measures].[Average Customers], NULL );

FORMAT_STRING(MyKPIValue) = 'Currency';

This calculated measure will be created as hidden, and then inside the Value expression of your KPI, you simply reference [Measures].[MyKPIValue].

HTH,

Mosha (http://www.mosha.com/msolap)

|||

I went through our current copy of AdventureWorks and didn't find it either. I may have seen it in an earlier version, or I may just be remembering incorrectly. I know I've seen it in other online articles. There's also a sample showing the use of VBA!ABS at DataBaseJournal.

Thanks for you explanation of strings in the KPIs. The type mismatch error makes sense now.

I think what we were missing here is that the value and goal really need to be created in the script and not in the KPI designer. We create a number of percentage ratios as KPIs where we may divide a currency amount by a count (just like ROA in AdventureWorks.) Using Format() seemed to be the only way to show this as a percent. We should be doing this calculation in the script and use Format_String. I just read another post of yours on solve order that suggests creating all the calculations in the script and only referring to them in the KPI designer is the right way to go.

It would be great to have documented best practices from Microsoft that keep us newbies away from problems like this. KPIs seem to be one of the least documented features.

Getting back to Terry's original question, is there a way of using annotations to pass additional info to the client for custom features? We plan on implementing spark lines and bullet graphs and it would be great if we could pass additional data to the client.

Thanks again for your help!

|||

> There's also a sample showing the use of VBA!ABS at DataBaseJournal.

VBA!ABS - is kosher to use, since it's a math function, and its return data type is number. And, BTW, the MDX articles in DataBaseJournal by William Pearson are usually very good - so this is a source I would trust.

> Getting back to Terry's original question, is there a way of using annotations to pass additional info to the client for custom features? We plan on implementing spark lines and bullet graphs and it would be great if we could pass additional data to the client.

Yes - it is certainly possible. Assuming you create your cubes and KPIs using AMO - here is the link to AMO documentation about how to add annotations to any AS object.

http://msdn2.microsoft.com/en-us/library/microsoft.analysisservices.modelcomponent.annotations.aspx

HTH,

Mosha (http://www.mosha.com/msolap)

|||

There's an open source project called BIDS Helper which is a Visual Studio Add-in. One feature lets you edit annotations on Analysis Services objects within BIDS:

http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Show%20Extra%20Properties&referringTitle=Home

|||I just looked at BIDs Helper. That is exactly what we were looking for. The other features will be very helpful as well. Thanks!!!

KeyColumns Collection Order

When a key collection is set for the Keycolumns property, the DataItem Collection Editor has up and down buttons to order the individual keys. For example, for month in a date hierarchy I have a key collection of MonthNumber then Year. Changing it to Year then Month number doesn't seem to affect anything other than the way the hiearchy is referenced in MDX.

Does the order of the keys in the collection matter to AS?

What would you expect the order keys in the DataItem to affect? Order of the members in the attribute?

For time dimension you can try and use the OrderBy or OrderByAttribute to order memebers.

See how Date dimension in AdventureWorks sample database is built.

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

|||

That's just it...I don't expect that it would change anything. The key collection, naming property, order, and value are all set appropriately. Everthing seems to be working fine. Moving one before the other seems to have no effect.

However, I've learned just enough about AS to know that not everything is documented nor are the effects of seemingly trivial settings like this. I just don't want this to come back and bite me later.

Thanks for your reply Edward.

Key columns in SQL Server Everywhere Edition

I have noticed that Microsoft SQL Server 2005 Everywhere Edition OLE DB Provider doesn't support DBPROP_UNIQUEROWS property from
DBPROPSET_ROWSET property set. Does it mean that I have to use the KEY_COLUMN_USAGE rowset to get list of key columns in table?

Moving to the "Transact-SQL" forum.|||Thank you for your help

key columns

I have noticed that Microsoft SQL Server 2005 Everywhere Edition OLE DB Provider doesn't support DBPROP_UNIQUEROWS property from
DBPROPSET_ROWSET property set. Does it mean that I have to use the KEY_COLUMN_USAGE rowset to get list of key columns in table?Yes, you should use DBSCHEMA_KEY_COLUMN_USAGE in this case. or you can get the same information with the following query: "select * from information_schema.key_column_usage;"

key columns

I have noticed that Microsoft SQL Server 2005 Everywhere Edition OLE DB Provider doesn't support DBPROP_UNIQUEROWS property from
DBPROPSET_ROWSET property set. Does it mean that I have to use the KEY_COLUMN_USAGE rowset to get list of key columns in table?Yes, you should use DBSCHEMA_KEY_COLUMN_USAGE in this case. or you can get the same information with the following query: "select * from information_schema.key_column_usage;"

Keep Together property not working on table

I'm using SSRS sp2 and have a report which has a table which spans the width
of the report. I set the 'KeepTogether' property on this table to True, but
I have detail rows from this table spanning 2 pages. I thought this prop
would keep them together and force a pagebreak before. There's only about
10 rows total and a height of 0.3 for each row.
Also, I assume this property (when it works) will only effect the detail
rows. what I really want to keep the entire table together including
header, detail and footer rows. is there a way to not-allow a pagebreak
anywhere in the table?
Thanks
--
moondaddy@.nospam.nospamHi moondaddy,
Would you please send me a sample page with datasource? I understand the
information may be sensitive to you, my direct email address is
v-mingqc@.online.microsoft.com, you may send the file to me directly and I
will keep secure.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi. I found a workaround to this problem which may help you. As far as I can
tell, the table "keep together" property only applies to individual detail
rows i.e. if you have multiple detail lines, they can still span pages.
If you can use a single detail row with multiple lines, the keep together
will work on those lines.
To create a detail row with multile lines, you can use the
"environment.newline()" function in expressions within the detail row. (This
function does not appear to be documented in an obvious location).
e.g. an expression like . . .
=Fields!line_1.value & environment.newline() & Fields!line_2.value
will result in two lines, but using a single detail row.
Hope this helps. It won't help with keeping your whole table on one page.
Reporting Services seems to be deficient in this area.
"moondaddy" wrote:
> I'm using SSRS sp2 and have a report which has a table which spans the width
> of the report. I set the 'KeepTogether' property on this table to True, but
> I have detail rows from this table spanning 2 pages. I thought this prop
> would keep them together and force a pagebreak before. There's only about
> 10 rows total and a height of 0.3 for each row.
> Also, I assume this property (when it works) will only effect the detail
> rows. what I really want to keep the entire table together including
> header, detail and footer rows. is there a way to not-allow a pagebreak
> anywhere in the table?
> Thanks
> --
> moondaddy@.nospam.nospam
>
>|||Thanks. These sound like good tips. I wish they were better documented.
(sorry for the late reply. just got back from a long leave of absence)
--
moondaddy@.nospam.nospam
"Glen" <Glen@.discussions.microsoft.com> wrote in message
news:BBCEBF2B-9146-410B-AB6D-2039A103C275@.microsoft.com...
> Hi. I found a workaround to this problem which may help you. As far as I
> can
> tell, the table "keep together" property only applies to individual detail
> rows i.e. if you have multiple detail lines, they can still span pages.
> If you can use a single detail row with multiple lines, the keep together
> will work on those lines.
> To create a detail row with multile lines, you can use the
> "environment.newline()" function in expressions within the detail row.
> (This
> function does not appear to be documented in an obvious location).
> e.g. an expression like . . .
> =Fields!line_1.value & environment.newline() & Fields!line_2.value
> will result in two lines, but using a single detail row.
> Hope this helps. It won't help with keeping your whole table on one page.
> Reporting Services seems to be deficient in this area.
> "moondaddy" wrote:
>> I'm using SSRS sp2 and have a report which has a table which spans the
>> width
>> of the report. I set the 'KeepTogether' property on this table to True,
>> but
>> I have detail rows from this table spanning 2 pages. I thought this prop
>> would keep them together and force a pagebreak before. There's only
>> about
>> 10 rows total and a height of 0.3 for each row.
>> Also, I assume this property (when it works) will only effect the detail
>> rows. what I really want to keep the entire table together including
>> header, detail and footer rows. is there a way to not-allow a pagebreak
>> anywhere in the table?
>> Thanks
>> --
>> moondaddy@.nospam.nospam
>>|||I'm having a similar problem with the KeepTogether option on a table. I have
a report with a simple 4 row table. The pages break fine in the HTML
rendering but when I export to PDF, the pages will break in between detail
rows of a single table at times.
Has anyone come up with a viable solution to this problem? I'm running
Reporting Services for SQL Server 2000 SP1.
Thanks.
PhilK
"moondaddy" wrote:
> Thanks. These sound like good tips. I wish they were better documented.
> (sorry for the late reply. just got back from a long leave of absence)
> --
> moondaddy@.nospam.nospam
> "Glen" <Glen@.discussions.microsoft.com> wrote in message
> news:BBCEBF2B-9146-410B-AB6D-2039A103C275@.microsoft.com...
> > Hi. I found a workaround to this problem which may help you. As far as I
> > can
> > tell, the table "keep together" property only applies to individual detail
> > rows i.e. if you have multiple detail lines, they can still span pages.
> >
> > If you can use a single detail row with multiple lines, the keep together
> > will work on those lines.
> >
> > To create a detail row with multile lines, you can use the
> > "environment.newline()" function in expressions within the detail row.
> > (This
> > function does not appear to be documented in an obvious location).
> >
> > e.g. an expression like . . .
> >
> > =Fields!line_1.value & environment.newline() & Fields!line_2.value
> >
> > will result in two lines, but using a single detail row.
> >
> > Hope this helps. It won't help with keeping your whole table on one page.
> > Reporting Services seems to be deficient in this area.
> >
> > "moondaddy" wrote:
> >
> >> I'm using SSRS sp2 and have a report which has a table which spans the
> >> width
> >> of the report. I set the 'KeepTogether' property on this table to True,
> >> but
> >> I have detail rows from this table spanning 2 pages. I thought this prop
> >> would keep them together and force a pagebreak before. There's only
> >> about
> >> 10 rows total and a height of 0.3 for each row.
> >>
> >> Also, I assume this property (when it works) will only effect the detail
> >> rows. what I really want to keep the entire table together including
> >> header, detail and footer rows. is there a way to not-allow a pagebreak
> >> anywhere in the table?
> >>
> >> Thanks
> >>
> >> --
> >> moondaddy@.nospam.nospam
> >>
> >>
> >>
>
>

Keep Identity

Hi,
Please let me know how to keep identity property retained while copying
empty tables(with identity property set) from one database to another.
One way that I know is Backup\Restore. Suggest some other options.
Thanks in advance...
You can try a simple SSIS package using the Transfer SQL Server Object Task;
look at the Book on line searching for the task and i think you'll be
satisfied.
Gilberto Zampatti
"manu" wrote:

> Hi,
> Please let me know how to keep identity property retained while copying
> empty tables(with identity property set) from one database to another.
> One way that I know is Backup\Restore. Suggest some other options.
> Thanks in advance...
|||Hi,
Can't you just script out the table and run the script on the other database?
You can script using EM in 2000 and Mgmt Studio in 2005.
Thank you.
Regards,
Karthik
"manu" wrote:

> Hi,
> Please let me know how to keep identity property retained while copying
> empty tables(with identity property set) from one database to another.
> One way that I know is Backup\Restore. Suggest some other options.
> Thanks in advance...
|||search web for kondredi and get his generate_inserts sproc. WONDERFUL
utility!
TheSQLGuru
President
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:2C6B421A-929F-4FDE-962B-F6000BE1450E@.microsoft.com...
> Hi,
> Please let me know how to keep identity property retained while copying
> empty tables(with identity property set) from one database to another.
> One way that I know is Backup\Restore. Suggest some other options.
> Thanks in advance...

Keep Identity

Hi,
Please let me know how to keep identity property retained while copying
empty tables(with identity property set) from one database to another.
One way that I know is Backup\Restore. Suggest some other options.
Thanks in advance...You can try a simple SSIS package using the Transfer SQL Server Object Task
;
look at the Book on line searching for the task and i think you'll be
satisfied.
Gilberto Zampatti
"manu" wrote:

> Hi,
> Please let me know how to keep identity property retained while copying
> empty tables(with identity property set) from one database to another.
> One way that I know is Backup\Restore. Suggest some other options.
> Thanks in advance...|||Hi,
Can't you just script out the table and run the script on the other database
?
You can script using EM in 2000 and Mgmt Studio in 2005.
Thank you.
Regards,
Karthik
"manu" wrote:

> Hi,
> Please let me know how to keep identity property retained while copying
> empty tables(with identity property set) from one database to another.
> One way that I know is Backup\Restore. Suggest some other options.
> Thanks in advance...|||search web for kondredi and get his generate_inserts sproc. WONDERFUL
utility!
TheSQLGuru
President
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:2C6B421A-929F-4FDE-962B-F6000BE1450E@.microsoft.com...
> Hi,
> Please let me know how to keep identity property retained while copying
> empty tables(with identity property set) from one database to another.
> One way that I know is Backup\Restore. Suggest some other options.
> Thanks in advance...

Keep Identity

Hi,
Please let me know how to keep identity property retained while copying
empty tables(with identity property set) from one database to another.
One way that I know is Backup\Restore. Suggest some other options.
Thanks in advance...You can try a simple SSIS package using the Transfer SQL Server Object Task;
look at the Book on line searching for the task and i think you'll be
satisfied.
Gilberto Zampatti
"manu" wrote:
> Hi,
> Please let me know how to keep identity property retained while copying
> empty tables(with identity property set) from one database to another.
> One way that I know is Backup\Restore. Suggest some other options.
> Thanks in advance...|||Hi,
Can't you just script out the table and run the script on the other database?
You can script using EM in 2000 and Mgmt Studio in 2005.
Thank you.
Regards,
Karthik
"manu" wrote:
> Hi,
> Please let me know how to keep identity property retained while copying
> empty tables(with identity property set) from one database to another.
> One way that I know is Backup\Restore. Suggest some other options.
> Thanks in advance...|||search web for kondredi and get his generate_inserts sproc. WONDERFUL
utility!
--
TheSQLGuru
President
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:2C6B421A-929F-4FDE-962B-F6000BE1450E@.microsoft.com...
> Hi,
> Please let me know how to keep identity property retained while copying
> empty tables(with identity property set) from one database to another.
> One way that I know is Backup\Restore. Suggest some other options.
> Thanks in advance...

Keep a Group Together

Is this property available as in MS Access? It should attempt to fit the
entire group (heading and detail) on the current page if it can, and issue a
page break if it can't.Which control are u using. U can set properties like Fit Matrix/Table
in Page if possible property of table or matrix.
If in table u can edit group to set properties for page break at
start/end.
Mahesh|||In this case, I'm using a table.
If I set a page break at the start of a new group, I'll be generating 100's
of pages! Groups could have from 1 to 30 or more detail items. If I have 10
groups with 1 detail item each, they should all fit on one page. If I have 10
with 20 items each, the first 3 (or so, depending on spacing) should fit on
one page and the 4th go to the next page because it wouldn't fit on the
current one.
That's the way Access works.
"Mahesh" wrote:
> Which control are u using. U can set properties like Fit Matrix/Table
> in Page if possible property of table or matrix.
> If in table u can edit group to set properties for page break at
> start/end.
> Mahesh
>|||Yes. This is actually a table setting though, not a group setting. It is
KeepTogether.
If you select the table, then select the little grey square at the very top
left, you can get to the table properties.
"Peter Manse" <PeterManse@.discussions.microsoft.com> wrote in message
news:C02669DF-3B74-4F5F-B8F8-F7C511A33482@.microsoft.com...
> In this case, I'm using a table.
> If I set a page break at the start of a new group, I'll be generating
> 100's
> of pages! Groups could have from 1 to 30 or more detail items. If I have
> 10
> groups with 1 detail item each, they should all fit on one page. If I have
> 10
> with 20 items each, the first 3 (or so, depending on spacing) should fit
> on
> one page and the 4th go to the next page because it wouldn't fit on the
> current one.
> That's the way Access works.
> "Mahesh" wrote:
>> Which control are u using. U can set properties like Fit Matrix/Table
>> in Page if possible property of table or matrix.
>> If in table u can edit group to set properties for page break at
>> start/end.
>> Mahesh
>>|||I was excited for minute, but it didn't last!
Here's what the pertinent properties for the table say:
KeepTogether: True
PageBreakAtEnd: True
PageBreakAtStart: False
Parent: Body
RepeatFooterOnNewPage: False
RepeatHeaderOnNewPage: True
The table is not kept together. Instead, the heading and as many detail
items as will fit on the page are printed. When there is no more room, a new
page is generated, the heading is reprinted, followed by the remaining detail
items.
This is true of the export (pdf in my case), too, although the export takes
up 20 pages where the html takes up only 11.
This is pretty basic formatting stuff, so I'm sure I must be doing something
wrong, but I just can't put my finger on it.
Any more ideas?
"goodman93" wrote:
> Yes. This is actually a table setting though, not a group setting. It is
> KeepTogether.
> If you select the table, then select the little grey square at the very top
> left, you can get to the table properties.
>
> "Peter Manse" <PeterManse@.discussions.microsoft.com> wrote in message
> news:C02669DF-3B74-4F5F-B8F8-F7C511A33482@.microsoft.com...
> > In this case, I'm using a table.
> >
> > If I set a page break at the start of a new group, I'll be generating
> > 100's
> > of pages! Groups could have from 1 to 30 or more detail items. If I have
> > 10
> > groups with 1 detail item each, they should all fit on one page. If I have
> > 10
> > with 20 items each, the first 3 (or so, depending on spacing) should fit
> > on
> > one page and the 4th go to the next page because it wouldn't fit on the
> > current one.
> >
> > That's the way Access works.
> >
> > "Mahesh" wrote:
> >
> >> Which control are u using. U can set properties like Fit Matrix/Table
> >> in Page if possible property of table or matrix.
> >>
> >> If in table u can edit group to set properties for page break at
> >> start/end.
> >>
> >> Mahesh
> >>
> >>
>
>|||Anybody on the MS SQL Reporting team have any ideas on this? I keep on trying
different combinations but just haven't hit on one that will work. It's as if
KeepTogether is being completely ignored.
"Peter Manse" wrote:
> I was excited for minute, but it didn't last!
> Here's what the pertinent properties for the table say:
> KeepTogether: True
> PageBreakAtEnd: True
> PageBreakAtStart: False
> Parent: Body
> RepeatFooterOnNewPage: False
> RepeatHeaderOnNewPage: True
> The table is not kept together. Instead, the heading and as many detail
> items as will fit on the page are printed. When there is no more room, a new
> page is generated, the heading is reprinted, followed by the remaining detail
> items.
> This is true of the export (pdf in my case), too, although the export takes
> up 20 pages where the html takes up only 11.
> This is pretty basic formatting stuff, so I'm sure I must be doing something
> wrong, but I just can't put my finger on it.
> Any more ideas?
> "goodman93" wrote:
> > Yes. This is actually a table setting though, not a group setting. It is
> > KeepTogether.
> >
> > If you select the table, then select the little grey square at the very top
> > left, you can get to the table properties.
> >
> >
> > "Peter Manse" <PeterManse@.discussions.microsoft.com> wrote in message
> > news:C02669DF-3B74-4F5F-B8F8-F7C511A33482@.microsoft.com...
> > > In this case, I'm using a table.
> > >
> > > If I set a page break at the start of a new group, I'll be generating
> > > 100's
> > > of pages! Groups could have from 1 to 30 or more detail items. If I have
> > > 10
> > > groups with 1 detail item each, they should all fit on one page. If I have
> > > 10
> > > with 20 items each, the first 3 (or so, depending on spacing) should fit
> > > on
> > > one page and the 4th go to the next page because it wouldn't fit on the
> > > current one.
> > >
> > > That's the way Access works.
> > >
> > > "Mahesh" wrote:
> > >
> > >> Which control are u using. U can set properties like Fit Matrix/Table
> > >> in Page if possible property of table or matrix.
> > >>
> > >> If in table u can edit group to set properties for page break at
> > >> start/end.
> > >>
> > >> Mahesh
> > >>
> > >>
> >
> >
> >|||Peter, I need to do the same thing. I have a table that is displaying
information in groups. I want the page to break before a group starts if in
order to start that group on page one it will finish on page two. Has
anyting come of this? Is there a way to do this?
"Peter Manse" wrote:
> Anybody on the MS SQL Reporting team have any ideas on this? I keep on trying
> different combinations but just haven't hit on one that will work. It's as if
> KeepTogether is being completely ignored.
> "Peter Manse" wrote:
> > I was excited for minute, but it didn't last!
> >
> > Here's what the pertinent properties for the table say:
> >
> > KeepTogether: True
> > PageBreakAtEnd: True
> > PageBreakAtStart: False
> > Parent: Body
> > RepeatFooterOnNewPage: False
> > RepeatHeaderOnNewPage: True
> >
> > The table is not kept together. Instead, the heading and as many detail
> > items as will fit on the page are printed. When there is no more room, a new
> > page is generated, the heading is reprinted, followed by the remaining detail
> > items.
> >
> > This is true of the export (pdf in my case), too, although the export takes
> > up 20 pages where the html takes up only 11.
> >
> > This is pretty basic formatting stuff, so I'm sure I must be doing something
> > wrong, but I just can't put my finger on it.
> >
> > Any more ideas?
> >
> > "goodman93" wrote:
> >
> > > Yes. This is actually a table setting though, not a group setting. It is
> > > KeepTogether.
> > >
> > > If you select the table, then select the little grey square at the very top
> > > left, you can get to the table properties.
> > >
> > >
> > > "Peter Manse" <PeterManse@.discussions.microsoft.com> wrote in message
> > > news:C02669DF-3B74-4F5F-B8F8-F7C511A33482@.microsoft.com...
> > > > In this case, I'm using a table.
> > > >
> > > > If I set a page break at the start of a new group, I'll be generating
> > > > 100's
> > > > of pages! Groups could have from 1 to 30 or more detail items. If I have
> > > > 10
> > > > groups with 1 detail item each, they should all fit on one page. If I have
> > > > 10
> > > > with 20 items each, the first 3 (or so, depending on spacing) should fit
> > > > on
> > > > one page and the 4th go to the next page because it wouldn't fit on the
> > > > current one.
> > > >
> > > > That's the way Access works.
> > > >
> > > > "Mahesh" wrote:
> > > >
> > > >> Which control are u using. U can set properties like Fit Matrix/Table
> > > >> in Page if possible property of table or matrix.
> > > >>
> > > >> If in table u can edit group to set properties for page break at
> > > >> start/end.
> > > >>
> > > >> Mahesh
> > > >>
> > > >>
> > >
> > >
> > >|||It looks like we're SOL, Sharin. Here's what Leo Tachev (author of "Microsoft
Reporting Services In Action") told me when I asked him the question:
"As it stands, RS 2000 and 2005 donâ't allow you to control the page break.
Data regions have Keep Together and Before and After page breaks. I am afraid
this is as far as you can control it at the moment."
This jives with what we're experiencing, but sounds like there's a bug in
the Keep Together function. Either that or the function is neither named nor
documented properly. My expectation with a function named Keep Together is
that it would work exactly the way you and I expect it to.
"SharinDenver" wrote:
> Peter, I need to do the same thing. I have a table that is displaying
> information in groups. I want the page to break before a group starts if in
> order to start that group on page one it will finish on page two. Has
> anyting come of this? Is there a way to do this?
>
> "Peter Manse" wrote:
> > Anybody on the MS SQL Reporting team have any ideas on this? I keep on trying
> > different combinations but just haven't hit on one that will work. It's as if
> > KeepTogether is being completely ignored.
> >
> > "Peter Manse" wrote:
> >
> > > I was excited for minute, but it didn't last!
> > >
> > > Here's what the pertinent properties for the table say:
> > >
> > > KeepTogether: True
> > > PageBreakAtEnd: True
> > > PageBreakAtStart: False
> > > Parent: Body
> > > RepeatFooterOnNewPage: False
> > > RepeatHeaderOnNewPage: True
> > >
> > > The table is not kept together. Instead, the heading and as many detail
> > > items as will fit on the page are printed. When there is no more room, a new
> > > page is generated, the heading is reprinted, followed by the remaining detail
> > > items.
> > >
> > > This is true of the export (pdf in my case), too, although the export takes
> > > up 20 pages where the html takes up only 11.
> > >
> > > This is pretty basic formatting stuff, so I'm sure I must be doing something
> > > wrong, but I just can't put my finger on it.
> > >
> > > Any more ideas?
> > >
> > > "goodman93" wrote:
> > >
> > > > Yes. This is actually a table setting though, not a group setting. It is
> > > > KeepTogether.
> > > >
> > > > If you select the table, then select the little grey square at the very top
> > > > left, you can get to the table properties.
> > > >
> > > >
> > > > "Peter Manse" <PeterManse@.discussions.microsoft.com> wrote in message
> > > > news:C02669DF-3B74-4F5F-B8F8-F7C511A33482@.microsoft.com...
> > > > > In this case, I'm using a table.
> > > > >
> > > > > If I set a page break at the start of a new group, I'll be generating
> > > > > 100's
> > > > > of pages! Groups could have from 1 to 30 or more detail items. If I have
> > > > > 10
> > > > > groups with 1 detail item each, they should all fit on one page. If I have
> > > > > 10
> > > > > with 20 items each, the first 3 (or so, depending on spacing) should fit
> > > > > on
> > > > > one page and the 4th go to the next page because it wouldn't fit on the
> > > > > current one.
> > > > >
> > > > > That's the way Access works.
> > > > >
> > > > > "Mahesh" wrote:
> > > > >
> > > > >> Which control are u using. U can set properties like Fit Matrix/Table
> > > > >> in Page if possible property of table or matrix.
> > > > >>
> > > > >> If in table u can edit group to set properties for page break at
> > > > >> start/end.
> > > > >>
> > > > >> Mahesh
> > > > >>
> > > > >>
> > > >
> > > >
> > > >|||I don't even see the KeepTogether function or how or where to use it. Did
Leo see a problem with this lack of functionality and does it sound like
something they will try and fix? Did you ask him if KeepTogether should do
what we want?
"Peter Manse" wrote:
> It looks like we're SOL, Sharin. Here's what Leo Tachev (author of "Microsoft
> Reporting Services In Action") told me when I asked him the question:
> "As it stands, RS 2000 and 2005 donâ't allow you to control the page break.
> Data regions have Keep Together and Before and After page breaks. I am afraid
> this is as far as you can control it at the moment."
> This jives with what we're experiencing, but sounds like there's a bug in
> the Keep Together function. Either that or the function is neither named nor
> documented properly. My expectation with a function named Keep Together is
> that it would work exactly the way you and I expect it to.
>
> "SharinDenver" wrote:
> > Peter, I need to do the same thing. I have a table that is displaying
> > information in groups. I want the page to break before a group starts if in
> > order to start that group on page one it will finish on page two. Has
> > anyting come of this? Is there a way to do this?
> >
> >
> > "Peter Manse" wrote:
> >
> > > Anybody on the MS SQL Reporting team have any ideas on this? I keep on trying
> > > different combinations but just haven't hit on one that will work. It's as if
> > > KeepTogether is being completely ignored.
> > >
> > > "Peter Manse" wrote:
> > >
> > > > I was excited for minute, but it didn't last!
> > > >
> > > > Here's what the pertinent properties for the table say:
> > > >
> > > > KeepTogether: True
> > > > PageBreakAtEnd: True
> > > > PageBreakAtStart: False
> > > > Parent: Body
> > > > RepeatFooterOnNewPage: False
> > > > RepeatHeaderOnNewPage: True
> > > >
> > > > The table is not kept together. Instead, the heading and as many detail
> > > > items as will fit on the page are printed. When there is no more room, a new
> > > > page is generated, the heading is reprinted, followed by the remaining detail
> > > > items.
> > > >
> > > > This is true of the export (pdf in my case), too, although the export takes
> > > > up 20 pages where the html takes up only 11.
> > > >
> > > > This is pretty basic formatting stuff, so I'm sure I must be doing something
> > > > wrong, but I just can't put my finger on it.
> > > >
> > > > Any more ideas?
> > > >
> > > > "goodman93" wrote:
> > > >
> > > > > Yes. This is actually a table setting though, not a group setting. It is
> > > > > KeepTogether.
> > > > >
> > > > > If you select the table, then select the little grey square at the very top
> > > > > left, you can get to the table properties.
> > > > >
> > > > >
> > > > > "Peter Manse" <PeterManse@.discussions.microsoft.com> wrote in message
> > > > > news:C02669DF-3B74-4F5F-B8F8-F7C511A33482@.microsoft.com...
> > > > > > In this case, I'm using a table.
> > > > > >
> > > > > > If I set a page break at the start of a new group, I'll be generating
> > > > > > 100's
> > > > > > of pages! Groups could have from 1 to 30 or more detail items. If I have
> > > > > > 10
> > > > > > groups with 1 detail item each, they should all fit on one page. If I have
> > > > > > 10
> > > > > > with 20 items each, the first 3 (or so, depending on spacing) should fit
> > > > > > on
> > > > > > one page and the 4th go to the next page because it wouldn't fit on the
> > > > > > current one.
> > > > > >
> > > > > > That's the way Access works.
> > > > > >
> > > > > > "Mahesh" wrote:
> > > > > >
> > > > > >> Which control are u using. U can set properties like Fit Matrix/Table
> > > > > >> in Page if possible property of table or matrix.
> > > > > >>
> > > > > >> If in table u can edit group to set properties for page break at
> > > > > >> start/end.
> > > > > >>
> > > > > >> Mahesh
> > > > > >>
> > > > > >>
> > > > >
> > > > >
> > > > >

KB934164 Report Services under VISTA -- RsAccess Denied Issue.

I am trying to test some reports and a report model built under VS-2005 under the Report Server and am NOT getting the content or property pages under folder.aspx, i.e., what typically shows when you run http://localhost\Reports.

Currently, http://localhost\Reports prompts me for my Windows login and password.

Was able to go in under SQL Server Management Studio connect to Report Services and add my local user account by granting it permissions. This enabled the "Site Settings" link at the upper right hand corner. So I am able to edit "Site Settings -- but not look at content.

Under http://localhost/ReportServer I get the infamous message: the permissions granted to user 'myMachine\mySelf' are insufficient for performing this operation (rsAccessDenied).

I am using Vista Ultimate, with IIS 7 / VS-2005 / SP1, SQL-Server 2005/SP2 on a home machine (not a corporate network).

Here are the steps for dealing with rsAccessDenied problem...

Follow the steps under KB934164.

Run SQL-Server 2005 Management Studio as as "Administrator" -- Connect to the Report Services Engine.

Step 1:

Right Click "localHost", go the Permissions page and grant your Service account permission. Also add your username for your Administrators.

Step 2:

Go down one level to "HOME". right click on Permission page and add you users or groups.

After doing this, I was able to see the Content page.

I received an access denied message when running a report against the Adventure Works database. Error messages such as "could not load file or asembly or dependencies.

Follow the "Hot To: Create a Service Account for an ASP.NET 2.0 Application. (Aspnet_regiis - ga ... ). This is under the patterns and practices under msdn2. ms998297.

This with applying settings under the Report Services Configuration utility seemed to address the problem.

KB934164 - Not Working under Vista..

I am trying to test some reports and a report model built under VS-2005 under the Report Server and am NOT getting the content or property pages under folder.aspx, i.e., what typically shows when you run http://localhost\Reports.

Currently, http://localhost\Reports prompts me for my Windows login and password.

Was able to go in under SQL Server Management Studio connect to Report Services and add my local user account by granting it permissions. This enabled the "Site Settings" link at the upper right hand corner. So I am able to edit "Site Settings -- but not look at content.

Under http://localhost/ReportServer I get the infamous message: the permissions granted to user 'myMachine\mySelf' are insufficient for performing this operation (rsAccessDenied).

I am using Vista Ultimate, with IIS 7 / VS-2005 / SP1, SQL-Server 2005/SP2 on a home machine (not a corporate network).

Here are the steps for dealing with rsAccessDenied problem...

Follow the steps under KB934164.

Run SQL-Server 2005 Management Studio as as "Administrator" -- Connect to the Report Services Engine.

Step 1:

Right Click "localHost", go the Permissions page and grant your Service account permission. Also add your username for your Administrators.

Step 2:

Go down one level to "HOME". right click on Permission page and add you users or groups.

After doing this, I was able to see the Content page.

I received an access denied message when running a report against the Adventure Works database. Error messages such as "could not load file or asembly or dependencies.

Follow the "Hot To: Create a Service Account for an ASP.NET 2.0 Application. (Aspnet_regiis - ga ... ). This is under the patterns and practices under msdn2. ms998297.

This with applying settings under the Report Services Configuration utility seemed to address the problem.