Counting differtent Values 1

[joda84]

Dear Reader,

I have build a query that returns me information of used microsoft products within our organization. One row gives back one of the following values:

Microsoft Office 97 Standard

Microsoft Office 97 professional

Microsoft Office 2000 Standard

Microsoft Office 2000 Professional

Microsoft Office 2000 Premium

Microsoft Office XP Standard

Microsoft Office XP Professional

Microsoft Office 2003 standard

I want to use the count expresions in the footer so the end of the report returns something like this:

Product Total

Microsoft Office 97 Standard 5
Microsoft Office 97 professional 6
Microsoft Office 2000 Standard 8
Microsoft Office 2000 Professional 9
Microsoft Office 2000 Premium 10
Microsoft Office XP Standard 11
Microsoft Office XP Professional 13
Microsoft Office 2003 standard 5

Can I use the count expresion to only count a certain given value? Also after the counting I want to do some calculations. For example I want to substract the professional count from the standard versions. For example: Microsoft Office XP Standard(total) - Microsoft Office XP Professional(total)

Do you guys have some pointers for me how I can realize this?

Thanks in advance!

 

[joda84]

Is it also possible to do for example, =Count(Fields!software.Value, "Microsoft Office XP standard") and =Count(Fields!software.Value, "Microsoft Office XP professional") ?

 

[swhitten]

I'm brand new to SRS myself, but I'll try to help with my limited knowledge.

My table (SALES) has a list of product IDs sold (similar to your licenses owned.) If product A is sold 34 times, it will be in the list 34 times.

I tried two approaches to your question. First Approach:
1. I just created a simple report that lists everything in the table, ordered by item number.
SELECT ITEMNMBR, ITEMDESC
FROM SALES
ORDER BY ITEMNMBR

2. Then I inserted a group by item number. Then in the group header row, I added an expression field. =count(Fields!ITEMNMBR.Value). So this counts every occurence of Product A and puts 34 next to the Item Number in the header row.

3. Then I suppressed the detail row. So all that shows now is a list and quantity:

Product A 34
Product B 27

etc.

BUT I don't see how to do calculations as requested - Product A total minus Product B total for example.


Approach #2:
Do the counting in the query.
SELECT COUNT(ITEMNMBR) AS Count, ITEMNMBR
FROM SALES
GROUP BY ITEMNMBR
ORDER BY ITEMNMBR

This yields results like this in the actual query:
Product A 34
Product B 27

But I'm still striking out on calculations. Will be interested to see other replys.

 

[joda84]

Dear Swhitten,

Thanks for your reply! The problem is that the same software needs to be shown per different computer + user combination. A subtotal of the products in the query results dousn't give me enough flexibility. I'm now trying to make function that count some values trough an if statement.

Here's my query:

select computer.ldaplocation, computer.devicename, computer.loginname as 'user', product.title as 'software', max(FileinfoInstance.SCM_LastSessionStart) as 'app last run', Operating_System.LastStartUpTime as 'pc last startup'
from ld88.dbo.computer, ld88.dbo.product, ld88.dbo.fileinfoinstance, ld88.dbo.fileinfo, ld88.dbo.productfile, ld88.dbperating_System
where computer.computer_idn = fileinfoinstance.computer_idn
and Operating_System.Computer_Idn = computer.Computer_Idn
and fileinfoinstance.fileinfo_idn = fileinfo.fileinfo_idn
and fileinfo.fileinfo_idn = productfile.Fileinfo_idn
and product.product_idn = productfile.product_idn
and product.product_Idn IN ('14049','14050','14044','14045','5','28','14003','14004','14008','14024','14025','14026','14027','14028','14029','14038','14039','14040','14042','14041','14043')

and Operating_System.LastStartUpTime > '2009-02-01'
group by computer.ldaplocation, computer.devicename, computer.loginname, product.title, Operating_System.LastStartUpTime
order by computer.devicename

 

[xlbo]

Either use the grouping functionality (as outlined above but with multiple groups as per your requirements) or do the grouping in the SQL itself

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[joda84]

I tried the following.

I made the following custom code:

Dim getal As String
Dim leeg As String

Public Function getcount(Value as String) As String
For i As Integer = 0 to 1000 - 1
if Value = "Microsoft Office 2003 standard" then
getal = getal +1 else
leeg = leeg + 1
end If
Next
Return getal
End Function

In reporting service I call this code by: =code.getcount(Fields!software.Value) I thought that by doing this all the values in the software field are parsed to the function and then counted if they are Microsoft Office 2003 standard. The problem is however that it only retrieves the first value that is noted in the software field instead of all the values. So now it count 1000 times Microsoft Office 2003 standard, because its the only value It recieves (altough there are other values like xp standard etc)

 

[joda84]

I tried to put the retrieved software values in an array and then tried to run the scipt but I can't get it working..

 

[xlbo]

I would suggest making your "footer" a subreport that takes a summary set of data into it rather than the whole set

Alternatively, if you have only a smallish subset of products, you could create a formula such as:


=SUM(iif(Fields!Software.Value = "Microsoft Office 2003 standard",1,0))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[joda84]

Dear xlbo,

Thanks for your reply. If I use =SUM(iif(Fields!Software.Value = "Microsoft Office 2003 standard",1,0)) I get:

[rsFieldReference] The Value expression for the textbox ‘textbox49’ refers to the field ‘Software’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

The result of fields!software.value are in my current data set(the query) so why do I get this result?

I will try to make a subreport that only list a summary of the products instead of the whole data set.

 

[joda84]

I solved it by using:

=sum(iif(Fields!title.Value = "Microsoft Office 97 Standard",1,0))

Another problem was that I named the title field in my query as software. When I used software in the expressions or script It didn't work beacause it thought it was another dataset. Renaming it back to title, and requesting this field as title solved it

I do have another question,

I can do for instance:

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

But I want to substract both the professional and the ultimate from the standard like this:

=sum(iif(Fields!title.Value = "Microsoft Office 2003 Standard",1,0)) - sum(iif(Fields!title.Value = "Microsoft Office 2003 professional",1,0)) - sum(iif(Fields!title.Value = "Microsoft Office 2003 ultimate",1,0))

Can I group or combine the total of professional and ultimate and then substract it from the standard sum?

 

[xlbo]

Just use brackets to keep the calc in order:

=sum(iif(Fields!title.Value = "Microsoft Office 2003 Standard",1,0)) - (sum(iif(Fields!title.Value = "Microsoft Office 2003 professional",1,0)) + sum(iif(Fields!title.Value = "Microsoft Office 2003 ultimate",1,0)))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question