Counting grouped values

[joda84]

Dear Reader,

I build a query that gives me information about our computers(1200) and their software. This query gives me for example the following results:

Sql Example:

Devicename OS Location Software

Comp1 WinXp office office 2003
Comp1 WinXp office Project 2003
Comp1 WinXp office Visio 2003
Comp2 WinXp office Office 2003
Comp2 WinXp office Project 2003
Comp2 WinXp office Visio 2003

To organize this information and to do some counting I made an report with reporting service. I placed the Devicename, Os and location value in group 1, and software in group 2. This yields:

Report Example:

Devicename OS Location Software

Comp1 WinXp office Office 2003
Project 2003
Visio 2003
Comp2 WinXp office Office 2003
Project 2003
Visio 2003

Now here is my problem. I use expressions to count the software. For example to count the total of the product office 2003:

=sum(iif(Fields!title.Value = "Office 2003",1,0))

This works fine. But I also want to count the number of computers and the type of operating systems. So I use:

=sum(iif(Fields!OSType.Value = "WinXP",1,0))

If you look at my report example I want this to count Winxp 2 times. But this expression counts the sql data and counts Winxp 5 times (sql example).

Is it also possible to count only values in an certain group? Or can I resolve this Issue in my sql query? I was first thinking of distinct but that doesnt apply because there are more software products linked to the same computer. My actual query is a bit more complex then my example. The query looks like this:

select computer.ldaplocation, computer.devicename,Operating_System.OSType, computer.loginname as 'user', product.title,
max(FileinfoInstance.SCM_LastSessionStart) as 'app last run', Operating_System.LastStartUpTime as 'pc last startup'


from dbo.computer, dbo.product, dbo.fileinfoinstance, dbo.fileinfo, dbo.productfile, dbperating_System

--some filtering for locations and products


group by computer.ldaplocation, computer.devicename, Operating_System.OSType, computer.loginname, product.title, Operating_System.LastStartUpTime

order by computer.devicename

Does anyone have some ideas how I can resolve this one?

Thanks in advance!

 

[joda84]

I tried using a scope in my expressions for the grouped data. I grouped the values Devicename, OStype, PC laststartup and user value in a group called "table1_Group2" If I try to set the scope for my expression like this: =sum(iif(Fields!OSType.Value = "Microsoft Windows XP Professional",1,0),"table1_Group2")

Reporting services returns me that the scope parameter is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a data region, or the name of a data set.

I used the name of the containing group but I get this error.. any thoughts on this? It seems to work fine if I use the name of the dataset but I need the grouped data as my scope.

I also noticed that the expression with the scope works when I put it in a data row. But still the expressions counts WinXP 3 times for Comp1(report Exmaple)

When I put the expressions in the footer(where all the calculations are displayed) the above error apears. Why cant the cells in the footer area use the data in my group(s)?