backing up reporting services

A few questions regarding Reporting Services

what needs to be backed up for reporting services? We are currently backing up the ReportServer and ReportServerTempDB.

Where would the RDL files be saved on our reportserver?

Finally, is there anything else that should be backed up for disaster recovery purposes?

You don't need to backup ReportServerTempDB for disaster recover. All of the data in it is transient.

You should also backup the key which is used for encrypting data in the ReportServer database.

See this article for a pretty good rundown of all of the pieces that you will want to backup:

http://technet.microsoft.com/en-us/library/ms155814(SQL.90).aspx

|||

>>Where would the RDL files be saved on our reportserver?

I'm pretty sure that the RDL files are stored in binary form (in the Content column of the Catalog table?) in the database, not positive.

>>is there anything else that should be backed up for disaster recovery purposes?

I would also back up the various *.config files from the ReportServer/Manager installation, plus any custom DLLs you may have added to a vanilla installation.

>L<

|||The RDL is stored in the catalog. You can retrieve it by using the GetReportDefinition() SOAP API call.

Background color differences between Visual Studio & Reporting Services

I'm trying to deploy a report to Reporting Services, but I've found that there's a behaviour difference Visual Studio and Reporting Services with the Matrix control for the subtotals.

For the background color for the "textbox" on the cells, I've used the folowing expression:

=Iif(Fields!ID__vs_YAGO.Value < 1, "Red"

,Iif(Fields!ID__vs_YAGO.Value < 1.02, "Yellow"

,"GreenYellow"))

The individual cells alternate colors, based on the value in the cell. In Visual Studio, the Subtotal cells also alternate colors (which is correct). But when the report is deployed to Reporting Services, The Subtotal cells have a transparent background. (Incorrect).

If I set the same expression into the background setting for the Subtotal cells, the expression is evaluated once for the entire row and the entire row has the same color (different Subtotal lines will alternate). This is the same in both Visual Studio and Reporting Services.

I'm using SP2, 2005 (32 bit for Reporting Services). To ensure full compatibility, I've editted / deployed the report all on the same machine.

Does anyone know of this problem and how to resolve it?

Additional: In testing the various output formats, it seems to be a bug in the HTML rendering engine. PDF amd TIFF work fine. I believe Excel export uses either part of the HTML rendering engine or pieces of the same code base.

P.S. I'm detecting other reporting differences between Visual Studio and Reporting Services, such as how "null key records" (from Analysis Services) are handled in groups. Is there a compatibility list published somewhere?

I've been having the same issues. I have been pulling my hair out for weeks.

Things to add:

I noticed that when deployed to Reporting Services, if you export to a TIFF or PDF, you will get the intended result. However, displayed on the web, and when exported to excel, it gives the above problems.

Background color differences between Visual Studio & Reporting Services

I'm trying to deploy a report to Reporting Services, but I've found that there's a behaviour difference Visual Studio and Reporting Services with the Matrix control for the subtotals.

For the background color for the "textbox" on the cells, I've used the folowing expression:

=Iif(Fields!ID__vs_YAGO.Value < 1, "Red"

,Iif(Fields!ID__vs_YAGO.Value < 1.02, "Yellow"

,"GreenYellow"))

The individual cells alternate colors, based on the value in the cell. In Visual Studio, the Subtotal cells also alternate colors (which is correct). But when the report is deployed to Reporting Services, The Subtotal cells have a transparent background. (Incorrect).

If I set the same expression into the background setting for the Subtotal cells, the expression is evaluated once for the entire row and the entire row has the same color (different Subtotal lines will alternate). This is the same in both Visual Studio and Reporting Services.

I'm using SP2, 2005 (32 bit for Reporting Services). To ensure full compatibility, I've editted / deployed the report all on the same machine.

Does anyone know of this problem and how to resolve it?

Additional: In testing the various output formats, it seems to be a bug in the HTML rendering engine. PDF amd TIFF work fine. I believe Excel export uses either part of the HTML rendering engine or pieces of the same code base.

P.S. I'm detecting other reporting differences between Visual Studio and Reporting Services, such as how "null key records" (from Analysis Services) are handled in groups. Is there a compatibility list published somewhere?

I've been having the same issues. I have been pulling my hair out for weeks.

Things to add:

I noticed that when deployed to Reporting Services, if you export to a TIFF or PDF, you will get the intended result. However, displayed on the web, and when exported to excel, it gives the above problems.

Back Button in RS 2000?

Hello.
I'm developing reports in Reporting Services 2000 and Visual Studio 2003. I
have a "drill-through" report. Upon drilling through to the report, and then
clicking the back button to return to the main report, the report doesn't
even render. However, while developing the report and previewing it in VS
2003, there is a back button supplied there that works perfectly every time.
My question is this: is there a way to tell Reporting Services to include a
back button like the one in the report designer on the deployed report?
Alternatively, is there a way to provide a hyperlink in the drill-through
report that takes the user back to the original report (with all parameters
included in the link)?
Thanks!
JohnA small Back button is available in 2005. If it is not a parameter report
then it would have been easy, but since the main report is parameter based,
you can have a back button link and once you click; it comes back to the
main page and waits for parameter. So really it doesn;t serves your purpose.
But if the main page can wait for the input then you can use this method.
Amarnath
"JohnJohn" wrote:
> Hello.
> I'm developing reports in Reporting Services 2000 and Visual Studio 2003. I
> have a "drill-through" report. Upon drilling through to the report, and then
> clicking the back button to return to the main report, the report doesn't
> even render. However, while developing the report and previewing it in VS
> 2003, there is a back button supplied there that works perfectly every time.
> My question is this: is there a way to tell Reporting Services to include a
> back button like the one in the report designer on the deployed report?
> Alternatively, is there a way to provide a hyperlink in the drill-through
> report that takes the user back to the original report (with all parameters
> included in the link)?
> Thanks!
> John

AWE /3GB performance

Is it possible to use AWE and /3gb option and cause the OS to become
constrained? Is so what key OS services are critical and which ones should
be stopped?
thank
-JohnJohn
If you have Windows 2000(Advanced or Datacenter) and 4GB RAM use /3GB (AWE
support isn't used)
You can provide more info to get more accurate advice
"John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
news:617A3083-DBEA-46C4-AFBE-C501839D12C7@.microsoft.com...
> Is it possible to use AWE and /3gb option and cause the OS to become
> constrained? Is so what key OS services are critical and which ones
> should
> be stopped?
> thank
> -John|||Hello John,
You can also use \PAE option if memory is more on the server e.g.8 GB and
SQL Server 2005 will manage the memory dynamically after the enabling the
AWE option.
Good Luck!
MB
"John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
news:617A3083-DBEA-46C4-AFBE-C501839D12C7@.microsoft.com...
> Is it possible to use AWE and /3gb option and cause the OS to become
> constrained? Is so what key OS services are critical and which ones
> should
> be stopped?
> thank
> -John|||John
>The /3GB switch is used to tell SQL Server to take advantage of 3GB out of
>the base 4GB of RAM that Windows 2000/3 supports natively. If you don't
>specify >this option, then SQL Server will only take advantage of 2GB of
>the first 4GB of RAM in the server, essentially wasting 1GB of RAM.
http://www.sql-server-performance.com/awe_memory.asp
"John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
news:A6CF4DB6-FFEE-4AA0-82B8-DD3BB04E6D74@.microsoft.com...
>I have 8gb of memory, win2003 server advanced, sql server 2000 ent. The
> concern when using the 3gb option is that I force the OS into 1gb of
> memory
> and causing the OS to be memory starved. Is it possible to create this
> condition and what would be the minimum set of services that should be
> running on the SQL Server box. For example, print spooler, application
> layer
> gateway, etc.
>
> "Uri Dimant" wrote:
>> John
>> If you have Windows 2000(Advanced or Datacenter) and 4GB RAM use /3GB
>> (AWE
>> support isn't used)
>> You can provide more info to get more accurate advice
>> "John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
>> news:617A3083-DBEA-46C4-AFBE-C501839D12C7@.microsoft.com...
>> > Is it possible to use AWE and /3gb option and cause the OS to become
>> > constrained? Is so what key OS services are critical and which ones
>> > should
>> > be stopped?
>> >
>> > thank
>> > -John
>>|||John
Is it SQL Server running dedicated on the server?
Do you have an OLTP applications connect to the SQL Server?
I'd strat to to identify perfomance problems from the application side (
checking indexes, database design)
"John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
news:DD6405A3-56C5-4394-81D5-121D204420F3@.microsoft.com...
> Uri, thanks for the info. I have read the doc and understand AWE and the
> 3gb
> option. Also I noticed some more advanced boot.ini options to control the
> use of the memory above 1g.
> I have a sql server that is running slow. I have not been able to profile
> the server. What I want to understand is "Is it possible to cause the OS
> to
> be memory starved using the 3gb option causing sql server to appear slow?"
> For example if the OS is forced to swap to disk for normal OS activities.
> If it is possible what would be the telling signs?
> thanks
> "Uri Dimant" wrote:
>> John
>> >The /3GB switch is used to tell SQL Server to take advantage of 3GB out
>> >of
>> >the base 4GB of RAM that Windows 2000/3 supports natively. If you don't
>> >specify >this option, then SQL Server will only take advantage of 2GB of
>> >the first 4GB of RAM in the server, essentially wasting 1GB of RAM.
>> http://www.sql-server-performance.com/awe_memory.asp
>>
>>
>> "John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
>> news:A6CF4DB6-FFEE-4AA0-82B8-DD3BB04E6D74@.microsoft.com...
>> >I have 8gb of memory, win2003 server advanced, sql server 2000 ent. The
>> > concern when using the 3gb option is that I force the OS into 1gb of
>> > memory
>> > and causing the OS to be memory starved. Is it possible to create this
>> > condition and what would be the minimum set of services that should be
>> > running on the SQL Server box. For example, print spooler, application
>> > layer
>> > gateway, etc.
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> John
>> >>
>> >> If you have Windows 2000(Advanced or Datacenter) and 4GB RAM use
>> >> /3GB
>> >> (AWE
>> >> support isn't used)
>> >> You can provide more info to get more accurate advice
>> >>
>> >> "John Grant" <JohnGrant@.discussions.microsoft.com> wrote in message
>> >> news:617A3083-DBEA-46C4-AFBE-C501839D12C7@.microsoft.com...
>> >> > Is it possible to use AWE and /3gb option and cause the OS to become
>> >> > constrained? Is so what key OS services are critical and which ones
>> >> > should
>> >> > be stopped?
>> >> >
>> >> > thank
>> >> > -John
>> >>
>> >>
>> >>
>>

Avoiding the user login prompt for a reporting services report

Most of the stuff I have read on this is from 2004. I am wondering if
there is a easier fix to this problem.do you use NT authentication or basic authentication?
using NTLM the users can add the web site to the intranet list then the
current login/password will be used. (IE only) so there is no prompt for the
user.
basic authentication allways ask the user.
anonymous access... its anonymous, so no login but no security.
Enterprise edition of RS allow you to create your own authentication system
(like a form based authentication)
"Tim" <TimGoard@.gmail.com> wrote in message
news:1135719831.735427.54260@.f14g2000cwb.googlegroups.com...
> Most of the stuff I have read on this is from 2004. I am wondering if
> there is a easier fix to this problem.
>|||Also, just in case you have a weird problem. I had a case where the issue
was that the server (it was a test server) was using DHCP (dynamic tcpip
address instead of fixed). When I went to a fixed IP address the probelm
went away. Obscure but thought I would mention it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:u7ALlbzCGHA.2596@.TK2MSFTNGP10.phx.gbl...
> do you use NT authentication or basic authentication?
> using NTLM the users can add the web site to the intranet list then the
> current login/password will be used. (IE only) so there is no prompt for
> the user.
> basic authentication allways ask the user.
> anonymous access... its anonymous, so no login but no security.
> Enterprise edition of RS allow you to create your own authentication
> system (like a form based authentication)
> "Tim" <TimGoard@.gmail.com> wrote in message
> news:1135719831.735427.54260@.f14g2000cwb.googlegroups.com...
>> Most of the stuff I have read on this is from 2004. I am wondering if
>> there is a easier fix to this problem.
>|||I am new to web programming. I thought I needed to say that.
In my ASP.net app authentication is set to Windows. Is that the
authentication you are talking about? I set it to none and it stills
comes up with the login prompt.|||Could you open the following file:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\rsreportserver.config
Check the tag <UrlRoot> to see if your servername is entered correctly (e.g.
not set to localhost)
Hth,
Pieter
"Tim" <TimGoard@.gmail.com> wrote in message
news:1135792805.115690.18910@.g44g2000cwa.googlegroups.com...
>I am new to web programming. I thought I needed to say that.
> In my ASP.net app authentication is set to Windows. Is that the
> authentication you are talking about? I set it to none and it stills
> comes up with the login prompt.
>|||The <UrlRoot> is entered correctly.
The authentication tag in this file is:
<Authentication>
<Extension Name="Windows"
Type="Microsoft.ReportingServices.Authentication.WindowsAuthentication,
Microsoft.ReportingServices.Authorization"/>
</Authentication>
I still have no idea how to overcome the windows login prompt for
showing the report in my asp.net app.|||Does your url to your reporting server include your server's ip address? If
it does, you'll have to enter the address into the end users local Intranet
security site.
From the end users machine, open IE.
Click on Tools>Internet Options
Click on the Security Tab then Local Intranet
Click on Sites
Click on Advanced
Enter in the url of your reporting server and click add.
Save your changes and try to access the site.
HTH
"Tim" wrote:
> The <UrlRoot> is entered correctly.
> The authentication tag in this file is:
> <Authentication>
> <Extension Name="Windows"
> Type="Microsoft.ReportingServices.Authentication.WindowsAuthentication,
> Microsoft.ReportingServices.Authorization"/>
> </Authentication>
> I still have no idea how to overcome the windows login prompt for
> showing the report in my asp.net app.
>|||Where are you running the report off from? Your local Machine? or some
other machine?

Averaging on Multiple Levels.

Hello,

I am extremely new at MDX and Analysis Services.

I have read through chapter 7 of the fast track to MDX.It is a wonderful book.But it talks a lot about sales / time data.I know this is the way 99% of people will use cubes.I am trying to use a cube for another purpose and am having a hard time moving concepts on how we do things.I am the IT person for a Truss Plant in Northern Wisconsin.

This business is a job shop, 99% of the production is custom built trusses. Quoting the jobs for this type of business is always a struggle I would like to provide valuable information to management on how much it costs to build the trusses.I know this is an almost impossible task as the rules change constantly, but I believe we can use analysis services to help us find patterns in the data.I am hoping it has the capability to average measures on multiple levels without having to know what levels we want beforehand.

We have been collecting production times for about a year now. We don’t know exactly what we are looking for as of yet, but we have some ideas.I am thinking we need to start with pieces completed per man hour. But I would like to see how this changes based on all kinds of different dimensions (Truss Properties).

We have a measure called run_time_per_piece (Lumber pieces in the truss) . I would like to analyze what the average is based on different combinations of dimensions / hierarchies.I have tried using the standard AverageofChildren, but this is not working as I would think it should.I was expecting it to Average all returned values at each level no matter what the level is.One issue here is I don’t know what the hierarchies are going to be ahead of time. We only have a one measure we are looking at, at any one time but we don’t know what the hierarchies could be.

I would appreciate it if anyone could point me in the right direction. Am I using the right product for what I am trying to do?If so, how would I write the MDX statement to get the cube to average on all levels correctly.

Thanks in Advance.

Leo

Hi Leo,

If you browse the "Average Sales Amount" calculated measure in the Adventure Works cube, does it behave as you want, across hierarchies and levels? It is defined as:

Create Member CurrentCube.[Measures].[Average Sales Amount]

As [Measures].[Sales Amount]

/

[Measures].[Order Count],

Format_String = "Currency" ;

This is a common pattern for computing averages which work across all hierarchies, so it may help.

Averages in Matrix

I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1
1 2 3 4 5 6 7 8 9 10 11 12 13 Total 2004 80 60 70 95 70 85 60 90 110 55 90 90 50 1005 2005 5 5 10 20 5 5 5 5 5 10 75

The columns represent each week in a quarter and the row represents the year the week is in and the details is the number of new accounts created during that particular week of the year. I have couple of questions.

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1
1 2 3 4 5 6 7 8 9 10 11 12 13 Total AVG 2004 80 60 70 95 70 85 60 90 110 55 90 90 50 1005 77.31 2005 5 0 5 10 20 5 0 5 5 5 5 0 10 75 5.77 -93.8% -100.0% -92.9% -89.5% -71.4% -94.1% -100.0% -94.4% -95.5% -90.9% -94.4% -100.0% -80.0%

Thanks in Advance.

You can use the InScope function to distinguish subtotal cells from other cells. Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell (e.g. in subtotal or not). You would use an IIF-expression to set the cell expression based on the InScope return values. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.

=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

Replace "In Cell" with =Sum(Fields!Amount.Value)
Replace "In Subtotal..." with =Avg(Fields!Amount.Value)

However, note that since the subtotal cells share the same cell definition as the group instance cells, adding two "subtotals" (one for "Total", the other for "AVG") at the same level is not supported. One way of solving this is to add a rectangle into the matrix cell and use two textboxes to show the total and the average. Then use conditional visibility on the average textbox to only have it visible for subtotals.

-- Robert

|||

Hi Robert,

Can you pls let us know How can we create a rectangle in matrix?.It will be a great help to us if you can send the detail.Currently we are hanging on this issue.We not only need average,we need Count and total also .The Detail is involved expression to convert from minutes(from stored procedure) to Hours:min.When we are trying to Give sum on this field,it is unable to find sum.it's giving last value or first value

We need Multiple sub totals also.

Any help is highly appreciated

Thank you Robert,
Raj Deep.A

|||

we need all the three aggregations at the end of the Report as three rows

Thank you Robert,
Raj Deep.A

|||

i try the method that you mention above but when i try to put it at expression it give me some error. for example:

i declare a column group name as "matrix1_columngroup1"

=iif(InScope(matrix1_columngroup1), .....) but the system return "unrecogized identifier" .

can you please help me on this. thanks.

sara

|||

You need to use double quotes around the group name. You should be passing as string i.e.

=iif(InScope("matrix1_columngroup1"), .....)

|||somebody explain me step by step how to work with the InScope function, and if it's possible with SSRS 2000. I have less experience with Reporting Services. I alreay have the groups and all stuff. Thanks|||

Even I am facing same problem. Can someone please explain step by step. Thanks in advance.

Averages in Matrix

I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1

1

2

3

4

5

6

7

8

9

10

11

12

13

Total

2004

80

60

70

95

70

85

60

90

110

55

90

90

50

1005

2005

5

5

10

20

5

5

5

5

5

10

75

The columns represent each week in a quarter and the row represents the year the week is in and the details is the number of new accounts created during that particular week of the year. I have couple of questions.

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1

1

2

3

4

5

6

7

8

9

10

11

12

13

Total

AVG

2004

80

60

70

95

70

85

60

90

110

55

90

90

50

1005

77.31

2005

5

0

5

10

20

5

0

5

5

5

5

0

10

75

5.77

-93.8%

-100.0%

-92.9%

-89.5%

-71.4%

-94.1%

-100.0%

-94.4%

-95.5%

-90.9%

-94.4%

-100.0%

-80.0%

Thanks in Advance.

You can use the InScope function to distinguish subtotal cells from other cells. Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell (e.g. in subtotal or not). You would use an IIF-expression to set the cell expression based on the InScope return values. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.

=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

Replace "In Cell" with =Sum(Fields!Amount.Value)
Replace "In Subtotal..." with =Avg(Fields!Amount.Value)

However, note that since the subtotal cells share the same cell definition as the group instance cells, adding two "subtotals" (one for "Total", the other for "AVG") at the same level is not supported. One way of solving this is to add a rectangle into the matrix cell and use two textboxes to show the total and the average. Then use conditional visibility on the average textbox to only have it visible for subtotals.

-- Robert

|||

Hi Robert,

Can you pls let us know How can we create a rectangle in matrix?.It

will be a great help to us if you can send the detail.Currently we are

hanging on this issue.We not only need average,we need Count and total

also .The Detail is involved expression to convert from minutes(from

stored procedure) to Hours:min.When we are trying to Give sum on this field,it is unable to find sum.it's giving last value or first value

We need Multiple sub totals also.

Any help is highly appreciated

Thank you Robert,

Raj Deep.A

|||

we need all the three aggregations at the end of the Report as three rows

Thank you Robert,

Raj Deep.A

|||

i try the method that you mention above but when i try to put it at expression it give me some error. for example:

i declare a column group name as "matrix1_columngroup1"

=iif(InScope(matrix1_columngroup1), .....) but the system return "unrecogized identifier" .

can you please help me on this. thanks.

sara

|||

You need to use double quotes around the group name. You should be passing as string i.e.

=iif(InScope("matrix1_columngroup1"), .....)

|||somebody explain me step by step how to work with the InScope function, and if it's possible with SSRS 2000. I have less experience with Reporting Services. I alreay have the groups and all stuff. Thanks

Averages in Matrix

I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1

1

2

3

4

5

6

7

8

9

10

11

12

13

Total

2004

80

60

70

95

70

85

60

90

110

55

90

90

50

1005

2005

5

5

10

20

5

5

5

5

5

10

75

The columns represent each week in a quarter and the row represents the year the week is in and the details is the number of new accounts created during that particular week of the year. I have couple of questions.

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1

1

2

3

4

5

6

7

8

9

10

11

12

13

Total

AVG

2004

80

60

70

95

70

85

60

90

110

55

90

90

50

1005

77.31

2005

5

0

5

10

20

5

0

5

5

5

5

0

10

75

5.77

-93.8%

-100.0%

-92.9%

-89.5%

-71.4%

-94.1%

-100.0%

-94.4%

-95.5%

-90.9%

-94.4%

-100.0%

-80.0%

Thanks in Advance.

You can use the InScope function to distinguish subtotal cells from other cells. Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell (e.g. in subtotal or not). You would use an IIF-expression to set the cell expression based on the InScope return values. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.

=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

Replace "In Cell" with =Sum(Fields!Amount.Value)
Replace "In Subtotal..." with =Avg(Fields!Amount.Value)

However, note that since the subtotal cells share the same cell definition as the group instance cells, adding two "subtotals" (one for "Total", the other for "AVG") at the same level is not supported. One way of solving this is to add a rectangle into the matrix cell and use two textboxes to show the total and the average. Then use conditional visibility on the average textbox to only have it visible for subtotals.

-- Robert

|||

Hi Robert,

Can you pls let us know How can we create a rectangle in matrix?.It

will be a great help to us if you can send the detail.Currently we are

hanging on this issue.We not only need average,we need Count and total

also .The Detail is involved expression to convert from minutes(from

stored procedure) to Hours:min.When we are trying to Give sum on this field,it is unable to find sum.it's giving last value or first value

We need Multiple sub totals also.

Any help is highly appreciated

Thank you Robert,

Raj Deep.A

|||

we need all the three aggregations at the end of the Report as three rows

Thank you Robert,

Raj Deep.A

|||

i try the method that you mention above but when i try to put it at expression it give me some error. for example:

i declare a column group name as "matrix1_columngroup1"

=iif(InScope(matrix1_columngroup1), .....) but the system return "unrecogized identifier" .

can you please help me on this. thanks.

sara

|||

You need to use double quotes around the group name. You should be passing as string i.e.

=iif(InScope("matrix1_columngroup1"), .....)

|||somebody explain me step by step how to work with the InScope function, and if it's possible with SSRS 2000. I have less experience with Reporting Services. I alreay have the groups and all stuff. Thanks