Report Builder: Is there a way to have a repeating page header and page footer?

Dear Anyone,

Is there a way to have a repeating page header and page footer in report builder? I know this is possible with Reporting Services but I cant seem to find any info regarding this Report Builder? Can anyone shed a light in this?
Thanks,
JosephPage headers/footers are not supported in RB in this release. You can add them to an RB report by opening it in Report Designer and modifying the layout there.

Report Builder: Is there a way to filter a filter?

Dear Anyone,

We have a report model that connects to a single table. Some of the columns of the table contains blank values which is regarded as a valid value in our reports.

We have a requirement that the filter selection should not contain any blank values. They do not want to include a blank value for the users to choose but if the users decide not to use a filter, those blank values should still be included in the report.

Does anyone have any suggestions on how to do this in RB? We already made this work in RS2k5 but we can't seem to find any way to do this in RB.

Thanks,
Joseph

No, there is no way to control the list of values presented to the user. It is always a list of all existing values in the underlying database.

Report Builder, Aggregation Scope and Running Values

Dear Anyone,

I need to create a measure/column that is percent of column (or row, not sure). In Reporting services, we were able to do this because aggregations there had scope.

=Sum(Fields!Open_Demand.Value)/Sum(Fields!Open_Demand.Value,"GSD")

I can't seem to do this in RB because the aggregation there does not seem to have a scope. Nor does it have the function running value.

Does anyone have an idea on how to do this on Report Builder?
Thanks,
Joseph

Report Builder does not expose the scope parameter for aggregation. You can often get the same results, however, by including a separate field that retrieves the total for the set you want.

For example, you can create a (hypothetical) report like this:

Category | Product Name | Total Sales | % of Category

...if Category is an entity in your report model. If it is a lookup entity, you will need to turn on View->Advanced Mode in RB to see it.

Here's how:

Select Product Entity
Add Product Name field to report
Add Category field to left of Product Name
Add Sales->Total Sales to report TWICE
Right-click on the value cell of the second Total Sales field, choose Edit Formula
Type "/" after the Total Sales field in the formula textbox
Add Product->Category->Products->Sales->Total Sales to the formula
- this is the total sales for all products in the same category as this product
Click OK
Change the format on the second field from $ to %
Run the report

This will be notably slower than using RDL scoping, but it should work.

PS: Due to a known issue in RTM, the subtotals you display for the % field above will be incorrect. This will be fixed in SP1. Meanwhile, if you choose "Server Totals" in the Report->Report Properties dialog, you will get the correct subtotals as well.

|||

Hi Bob,

We've tried what you suggested but we seem to be getting a 1 result. We've enabled server totals and the answer still is the same. We are currently using the RTM build.

Thanks,

Joseph

Report Builder, Aggregation Scope and Running Values

Dear Anyone,

I need to create a measure/column that is percent of column (or row, not sure). In Reporting services, we were able to do this because aggregations there had scope.

=Sum(Fields!Open_Demand.Value)/Sum(Fields!Open_Demand.Value,"GSD")

I can't seem to do this in RB because the aggregation there does not seem to have a scope. Nor does it have the function running value.

Does anyone have an idea on how to do this on Report Builder?
Thanks,
Joseph

Report Builder does not expose the scope parameter for aggregation. You can often get the same results, however, by including a separate field that retrieves the total for the set you want.

For example, you can create a (hypothetical) report like this:

Category | Product Name | Total Sales | % of Category

...if Category is an entity in your report model. If it is a lookup entity, you will need to turn on View->Advanced Mode in RB to see it.

Here's how:

Select Product Entity
Add Product Name field to report
Add Category field to left of Product Name
Add Sales->Total Sales to report TWICE
Right-click on the value cell of the second Total Sales field, choose Edit Formula
Type "/" after the Total Sales field in the formula textbox
Add Product->Category->Products->Sales->Total Sales to the formula
- this is the total sales for all products in the same category as this product
Click OK
Change the format on the second field from $ to %
Run the report

This will be notably slower than using RDL scoping, but it should work.

PS: Due to a known issue in RTM, the subtotals you display for the % field above will be incorrect. This will be fixed in SP1. Meanwhile, if you choose "Server Totals" in the Report->Report Properties dialog, you will get the correct subtotals as well.

|||

Hi Bob,

We've tried what you suggested but we seem to be getting a 1 result. We've enabled server totals and the answer still is the same. We are currently using the RTM build.

Thanks,

Joseph

Report Builder Parameter Dependency

Dear Anyone,

Is it possible in report builder that for a report parameter to have a dependency on another report parameter?

Example:

Parameter 1: Country

Parameter 2: City

The selects a country called Philippines. When the user tries to select the specific city, only the cities that are under the selected country will be displayed.

Thanks,

Joseph

No, this is not possible in Report Builder parameters. You would need to import the report into Report Designer.

Report builder cannot be shown on other user login

Dear all specialists,

I am encountering a situation that the report buider feature cannot be shown on other user login. Would you guide me to solve this issue ?

Thanks for your help

Gary Ho

Gary,

For your user login, you will have to make sure that they have "Report Builder" under the "Edit Role Assignment". You will find this in your folder/properties area.

Ham

|||

Hi Ham,

Thanks for your feedback and comments. The user profile under "Edit Role Assignment" already ticked the "Report Builder". Would you have any idea ?

Cheers,

Gary

Reporing Service and deploy permission

Dear all,

I use Window Vista+ Sql server 2005 with SP 2

When I deploy the report in Report designer, the following error message display

The permissions granted to user 'winVista\admin' are insufficient for performing this operation.

The user "admin" is a admininstor and I assign the following group to it

(1) administors

(2)WINVISTA\SQLServer2005ReportingServicesWebServiceUser$WINVISTA$MSSQLSERVER

(3)WINVISTA\SQLServer2005ReportServerUser$WINVISTA$MSSQLSERVER

Which and Where I must assign the permissions to this account?

thanks you

If you connect to Reporting Services in Sql Server Management Studio, right click on the servername and select the properties of the server. Then select the permissions page. What's in there?

Reporing Service and deploy permission

Dear all,

I use Window Vista+ Sql server 2005 with SP 2

When I deploy the report in Report designer, the following error message display

The permissions granted to user 'winVista\admin' are insufficient for performing this operation.

The user "admin" is a admininstor and I assign the following group to it

(1) administors

(2)WINVISTA\SQLServer2005ReportingServicesWebServiceUser$WINVISTA$MSSQLSERVER

(3)WINVISTA\SQLServer2005ReportServerUser$WINVISTA$MSSQLSERVER

Which and Where I must assign the permissions to this account?

thanks you

If you connect to Reporting Services in Sql Server Management Studio, right click on the servername and select the properties of the server. Then select the permissions page. What's in there?

replication, mirroring and log ship

dear all,

I have a case.

I have 1 production server and wanto to add 1 more server.

my production server is using sql 2000 stnd edition.

I want to have an indentical database from my production server.

the problem is my second server is using sql 2005 stnd edition.

I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.

any idea or suggestion will be great....

regards,

-dedys

Transactional replication should work fine as it supports replicating between servers within two versions of each other.

Regards,

Matt Hollingsworth

SQL Server High Availability

|||

The only thing that would prevent you from using transactional replication is if you have tables that do not have primary keys.

As far as log shipping goes. You can configure log shipping from SQL Server 2000 to SQL Server 2005, because 2000 backups can be restored to 2005. You can not configure it the other way around.

|||

dear sir,

I already try to configure log ship on my server.

my primary server is on sql2000 and the secondary server is on sql 2005.

I find that on sql 2000 can't see the sql 2005.

do you have documentation regarding this. a web link ?

thanks

-dedys

|||You can't connect to and manage a 2005 instance from within Enterprise Manager. You can go the other way. Log shipping is nothing more than configuring jobs to backup the tran log, copy it to the secondary, and restore it on a recurring basis. From within Management Studio on 2005, you can point and click through the GUI and setup log shipping from 2000 to 2005. You can also just create three jobs manually and accomplish the same thing. I'm not sure what documentation you are looking for. The 2005 Books Online have step-by-step procedures for setting up log shipping.|||

hi,

I have tries to set log shipping from 2000 to 2005 but it seem can't be done.

example : Server A is 2005 and B is 2000

I'm using management studio from A and register B. but when I right click on the database B, I can't find log shipping.

so I can't configure log shipping from 2000 to 2005.

if you know how to set it. could you share it with me.

I also try to do it manually like you said. using 3 simple step.

step 1 and 2 is done. I have difficulties with step 3. that is restoring the transaction log.

do you know how to restore transaction log more that 2 files at the same time or the name is keep on changing ?

please advice

-dedys

|||You can't restore more than 1 tran log simultaneously. Just like you can't restore more than one of any backup at the same time to the same database. You have to restore 1 log and then the next and then the next etc.|||

hi mr michael,

I have tries but I still got problem.

the step that did,

restore fullbackup to other server.

then restore the transaction log one by one and at the last transaction log with recovery option.

that's part is ok.

but when I want add more transaction log, there is an error that said :

Msg 3117, Level 16, State 4, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

please advice

|||You have to restore all transaction logs using the NORECOVERY option. As soon as you recover the database, you can no longer restore tran logs.

replication, mirroring and log ship

dear all,

I have a case.

I have 1 production server and wanto to add 1 more server.

my production server is using sql 2000 stnd edition.

I want to have an indentical database from my production server.

the problem is my second server is using sql 2005 stnd edition.

I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.

any idea or suggestion will be great....

regards,

-dedys

Transactional replication should work fine as it supports replicating between servers within two versions of each other.

Regards,

Matt Hollingsworth

SQL Server High Availability

|||

The only thing that would prevent you from using transactional replication is if you have tables that do not have primary keys.

As far as log shipping goes. You can configure log shipping from SQL Server 2000 to SQL Server 2005, because 2000 backups can be restored to 2005. You can not configure it the other way around.

|||

dear sir,

I already try to configure log ship on my server.

my primary server is on sql2000 and the secondary server is on sql 2005.

I find that on sql 2000 can't see the sql 2005.

do you have documentation regarding this. a web link ?

thanks

-dedys

|||You can't connect to and manage a 2005 instance from within Enterprise Manager. You can go the other way. Log shipping is nothing more than configuring jobs to backup the tran log, copy it to the secondary, and restore it on a recurring basis. From within Management Studio on 2005, you can point and click through the GUI and setup log shipping from 2000 to 2005. You can also just create three jobs manually and accomplish the same thing. I'm not sure what documentation you are looking for. The 2005 Books Online have step-by-step procedures for setting up log shipping.|||

hi,

I have tries to set log shipping from 2000 to 2005 but it seem can't be done.

example : Server A is 2005 and B is 2000

I'm using management studio from A and register B. but when I right click on the database B, I can't find log shipping.

so I can't configure log shipping from 2000 to 2005.

if you know how to set it. could you share it with me.

I also try to do it manually like you said. using 3 simple step.

step 1 and 2 is done. I have difficulties with step 3. that is restoring the transaction log.

do you know how to restore transaction log more that 2 files at the same time or the name is keep on changing ?

please advice

-dedys

|||You can't restore more than 1 tran log simultaneously. Just like you can't restore more than one of any backup at the same time to the same database. You have to restore 1 log and then the next and then the next etc.|||

hi mr michael,

I have tries but I still got problem.

the step that did,

restore fullbackup to other server.

then restore the transaction log one by one and at the last transaction log with recovery option.

that's part is ok.

but when I want add more transaction log, there is an error that said :

Msg 3117, Level 16, State 4, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

please advice

|||You have to restore all transaction logs using the NORECOVERY option. As soon as you recover the database, you can no longer restore tran logs.

replication, mirroring and log ship

dear all,

I have a case.

I have 1 production server and wanto to add 1 more server.

my production server is using sql 2000 stnd edition.

I want to have an indentical database from my production server.

the problem is my second server is using sql 2005 stnd edition.

I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.

any idea or suggestion will be great....

regards,

-dedys

Transactional replication should work fine as it supports replicating between servers within two versions of each other.

Regards,

Matt Hollingsworth

SQL Server High Availability

|||

The only thing that would prevent you from using transactional replication is if you have tables that do not have primary keys.

As far as log shipping goes. You can configure log shipping from SQL Server 2000 to SQL Server 2005, because 2000 backups can be restored to 2005. You can not configure it the other way around.

|||

dear sir,

I already try to configure log ship on my server.

my primary server is on sql2000 and the secondary server is on sql 2005.

I find that on sql 2000 can't see the sql 2005.

do you have documentation regarding this. a web link ?

thanks

-dedys

|||You can't connect to and manage a 2005 instance from within Enterprise Manager. You can go the other way. Log shipping is nothing more than configuring jobs to backup the tran log, copy it to the secondary, and restore it on a recurring basis. From within Management Studio on 2005, you can point and click through the GUI and setup log shipping from 2000 to 2005. You can also just create three jobs manually and accomplish the same thing. I'm not sure what documentation you are looking for. The 2005 Books Online have step-by-step procedures for setting up log shipping.|||

hi,

I have tries to set log shipping from 2000 to 2005 but it seem can't be done.

example : Server A is 2005 and B is 2000

I'm using management studio from A and register B. but when I right click on the database B, I can't find log shipping.

so I can't configure log shipping from 2000 to 2005.

if you know how to set it. could you share it with me.

I also try to do it manually like you said. using 3 simple step.

step 1 and 2 is done. I have difficulties with step 3. that is restoring the transaction log.

do you know how to restore transaction log more that 2 files at the same time or the name is keep on changing ?

please advice

-dedys

|||You can't restore more than 1 tran log simultaneously. Just like you can't restore more than one of any backup at the same time to the same database. You have to restore 1 log and then the next and then the next etc.|||

hi mr michael,

I have tries but I still got problem.

the step that did,

restore fullbackup to other server.

then restore the transaction log one by one and at the last transaction log with recovery option.

that's part is ok.

but when I want add more transaction log, there is an error that said :

Msg 3117, Level 16, State 4, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

please advice

|||You have to restore all transaction logs using the NORECOVERY option. As soon as you recover the database, you can no longer restore tran logs.