Calculation using count function in SQL server Business Intelligence development studio

Hello All,

I am working on a report using SQL server Business Intelligence development studio in which I need to count the total number of saleslines that either has a status : Delivered or Invoiced.

I am using the inbuilt count function under the calcuations, as: Count(Fields!SalesStatus.Value, scope). I am not sure what scope means here however, I read that scope refers to some dataset, dataregion or grouping. I dont know what that means. I shall be really thankful if someone can help me with this.

Regards,

Rashi

Scope is optional and if not given, will refer to the current scope.

The current scope is exactly what you've said. If your dataset name is "dataset1" and you put a textbox outside a table or matrix and put the value: Count(Fields!SalesStatus.Value, "dataset1"), you'll get the accurate count of all items within that dataset.

If you were doing some grouping inside your table and wanted to get the count for each group, in a group header or footer, you could do: Count(Fields!SalesStatus.Value, "table1_YourGroupName") to acheive this.|||An alternative approach to using the Count function would be to add a calculated column to your dataset, setting the value to 0 or 1 depending on whether the row meets your conditions. Then sum the calculated column.|||

Thanks for your response!

Well, the dataset is salesstatus which is a field in the salestable. Now, this field "salesstatus" has some values like: invoiced, delivered, pending, on hold etc on each line-item. I just want to count the line items that have a sales status equals to delievered or invoiced. I am not sure if there is a way to calculate that. I am doing reporting for the first time so I might not be sure.

What I am trying to achieve is to calculate the total number of orders shipped. For this, I am thinkin if I can get a count of lineitems that have a salesstatus = delivered or invoiced I can acheive that. I would appreciate your response on this one.

Thanks,

Rashi

|||Try adding a calculated field with "=iif(fields!salesstatus = "delivered", 1, 0)" (freehanding this so it may not be 100% correct syntax, but it's close). To check additional values, you could use nested a nested iif, or the switch function.|||You can group by salestatus then count the group. You might want to add drill down capability on this group.|||

Thanks all

I have been able to figure it out. I grouped all and used a count function and it just works great!!!

Thanks again,

Rashi