How to determine its the sub/total row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

How to determine its the sub/total row

Author

Topic

bushfoot
Yak Posting Veteran

53 Posts

Posted - 2008-10-23 : 11:11:54

Hi:

I have created an rdl doc that contains a matrix. I would like to know how I can indicate in rdl that IF rendering the sub/total row count the rows of data. If data row, should a field from the data set.

For example my report looks kind of like this:

Datetime, Phone Number, Location, Duration, Cost
jan 1 08:00 xxxxxxxxxx whereever 2:00 $1
jan 1 08:00 xxxxxxxxxx whereever 3:00 $4
Total 2 2 5:00 $6

So as you can see in teh body of the report I want the phone and location to show as produced by the dataset , but if its the total line I want a count since the phone/location doesnt make sense in the total line .

I appreciate any guidance including "greasy hacks".

Thanks,
Kelley

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 11:26:49

you can add a subtotal column for showing total value and given expression using SUM() function. didnt understand the problem you're facing in that?

bushfoot
Yak Posting Veteran

53 Posts

Posted - 2008-10-23 : 11:39:54

I can't use sum. If I use sum, then the data rows show 1 instead of the phone /location (effectively i lose the phone/location data)

Ex:
Datetime, Phone Number, Location, Duration, Cost
jan 1 08:00 1 1 2:00 $1
jan 1 08:00 1 1 3:00 $4
Total 2 2 5:00 $6

Its like I need to set the expression of the column to =IIF(row==total, count(field!phone.value), field!phone.value)

I dont know how to do the IIF row == total part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 11:43:50

ok use this

=IIF(InScope("yourdatasetname"), field!phone.value,count(field!phone.value))

bushfoot
Yak Posting Veteran

53 Posts

Posted - 2008-10-23 : 11:55:12

Didn't work.. Well kind of it didnt work. My data rows remained the way I wanted them. My total line did not show a count value. So I guess my next question is "yourdatasetname" is this the name that is assigned to the DataSet that I see in the Data Table of the report viewer?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 12:44:03

quote:
Originally posted by bushfoot

Didn't work.. Well kind of it didnt work. My data rows remained the way I wanted them. My total line did not show a count value. So I guess my next question is "yourdatasetname" is this the name that is assigned to the DataSet that I see in the Data Table of the report viewer?

yup. you should give your datasets name. try below also


=IIF(InScope("yourdatasetname"), field!phone.value,count(field!phone.value,"yourdatasetname"))

bushfoot
Yak Posting Veteran

53 Posts

Posted - 2008-10-23 : 13:09:46

Apparently, I am still in scope!

I couldn't get it to work and was convinced I had the wrong "yourdatsetname". So I changed the expression to IIF(InScope("ReportData"), "inscope", "outofscope"), the field I want to count is still considered inscope even tho its the total line.

To make sure it was still good, I used TimeInterval for "yourdatasetname" which should be OUT of scope - and the rendered report showed out of scope.

So I have now proven the following
1 - I AM using the correct "yourdatsetname" :)
2 - the total line is still considered in scope.

Thanks for your help! I appreciate the prompt reply and the tips. If you have any other suggestions I am open and listening :)

Kelley

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 13:18:08

quote:
Originally posted by bushfoot

Apparently, I am still in scope!

I couldn't get it to work and was convinced I had the wrong "yourdatsetname". So I changed the expression to IIF(InScope("ReportData"), "inscope", "outofscope"), the field I want to count is still considered inscope even tho its the total line.

To make sure it was still good, I used TimeInterval for "yourdatasetname" which should be OUT of scope - and the rendered report showed out of scope.

So I have now proven the following
1 - I AM using the correct "yourdatsetname" :)
2 - the total line is still considered in scope.

Thanks for your help! I appreciate the prompt reply and the tips. If you have any other suggestions I am open and listening :)

Kelley

nope. if you're using the standard subtotal column of matrix as i asked initially, i'm 100 % sure that it is not in scope of dataset. I've myself used it for numerous occasions.

bushfoot
Yak Posting Veteran

53 Posts

Posted - 2008-10-23 : 13:30:48

As far as I know I am using a standard subtotal column. I right clicked on the first group by cell and selected subtotal.

There is no other way to determine that its the total line? I changed one of my report fields to be the Level("ReportData") but all rows had the same value.

It almost sounds like I don't really need a matrix that a table would have done. But how do I do a total on a table ?

bushfoot
Yak Posting Veteran

53 Posts

Posted - 2008-10-23 : 13:34:51

I guess my next question is how Scope works. If I use the name of my dataset then it should always be "inscope" as that one dataset is the source for all data on my report. The other dataset I have are for selecting the parameters to pass to the sql proc.

Can I use anything other than the dataset name for testing InScope?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 13:36:44

quote:
Originally posted by bushfoot

I guess my next question is how Scope works. If I use the name of my dataset then it should always be "inscope" as that one dataset is the source for all data on my report. The other dataset I have are for selecting the parameters to pass to the sql proc.

Can I use anything other than the dataset name for testing InScope?

reading your last reply, i guess you're using more than one group in matrix,Can you just try giving grouping name instead of dataset name and see if it works?

bushfoot
Yak Posting Veteran

53 Posts

Posted - 2008-10-23 : 13:38:34

BINGO!

I used the name of my Grouping (matrix1_rowGroup2 - very discriptive huh) and I get the right value (in vs out of scope)!

You rock .. Thanks again for the help!

Be warned.. I will likely be back ;)

Kelley

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 14:23:19

quote:
Originally posted by bushfoot

BINGO!

I used the name of my Grouping (matrix1_rowGroup2 - very discriptive huh) and I get the right value (in vs out of scope)!

You rock .. Thanks again for the help!

Be warned.. I will likely be back ;)

Kelley

Welcome

No worries, i will help you in whatever way i can

bushfoot
Yak Posting Veteran

53 Posts

Posted - 2009-08-06 : 16:00:19

visahk16 (if you are still around):
I don't know if it's because I upgraded to Sql 2008 or what, but now this doesn't work anymore. Any idea why inscope behavior may have changed?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources