SSRS CountDistinct Expression

Not sure what you want to do. Do you want to count the number of distinct ordernumbers for which the sum of the TotalSales field equals zero? Can you add some more records to your sample data (and the output you need from that).
Maybe you can adapt the sample in this article to get what you want:
http://blogs.msdn.com/b/robertbruckner/archive/2008/07/20/using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx

But it might be easier to get the answer from a query. Just add another dataset to your report that does the counting and refer to this dataset in the textbox that needs to display the value.

>>Do you want to count the number of distinct ordernumbers for which the sum of the TotalSales field equals zero?

That is what i want to do....

I have a second expression where it is greater than 0.

It's not going to be possible to add a sql statement since I am using a SSAS cube for report data.

Even if I add another dataset .... could I 1) can I use more then 1 dataset per report ... I thought the answer to that quesion was no.

2) My total is in a group --- so I would probable have to have serveral datasets?

What I thought I MIGHT be able to do was this:

=CountDistinct(IIF(Fields!IsValid.Value = "No"
  And  Year(Fields!Some_Date.Value) = 2011
  And Sum(Fields!TotalSales.Value, "mygroup") = 0
  And Fields!SomeDateValue.Value > "2012-05-31", Fields!OrderNumber.Value, nothing))

Before I start trying to create the report (which I'll do tomorrow because on this part of the world the day is almost over)  I want to make sure I'm doing the right thing. I suppose your dataset looks something like this:
customer ordernumber   TotalSales
cust 1       34433               1000
cust 1       34433               -1000
cust 1       34434               2000
cust 1       34434               -2000
cust 1       34435               1000
cust 2       34436               2000
cust 2       34436               -1000
cust 2       34437               2000
cust 2       34437               -2000
cust 2       34438               200
cust 2       34438               -200

And in the report you want to group by customer. Show the  sum of the amount per order per customer and show the numbers of orders that total to 0 for each customer. Is that correct? This would result in something like

cust1
       34433     0
       34434     0
       34435     1000
   zero orders: 2
cust 2
       34436     1000
       34437      0
       34438      0
  zero orders: 2

If this is not what you need, please attach a screenshot of the report as it is now, with annotations showing what you need.

This function might be able to do the trick BTW:
http://msdn.microsoft.com/en-us/library/ms159136(SQL.100).aspx

My report is done for the most part it doesn't really matter what it looks like for the sake of discussion.

The expression I gave as an example is in a text box which is part of a group. It should return me only a count.


In your example data, the totals you return are correct.. that is what i need. I need to put that value in a textbox

So, i need to do something similar for what you did with zero orders: 2

I think I got it working. See the attached example. I couldn't find a way to do this with an expression, so I used some custom code. This custom code can be found in the Code property of the report itself. This custom code is called from the 'Order total' and 'Zero orders' textboxes. Please let me know if you have any questions about this solution.

About adding a second dataset: This is possible in SSRS. It's great for creating dashboards. You can use the lookup function to retrieve data from another dataset in a table that is based on one dataset. So if you can add a dataset to your report that holds the number of 'zero orders' per customer, you can retrieve that data in the table with the order data.
CountDistinct.rdl

I just looked at your code briefly and I'm not sure it is what i need.

I need counts where the sum is greater than zero per each distinct order number

so if order number 5555

has orders of 77 and -77 that would equal zero so a 1 would be returned to the textbox.

if 66666 had orders of 0 and 0 and 7 this total would be 7 so nothing would be counted. so for the group that contains 5555 and 66666 their would be a total of 1.

I do have to come up with a count for those > 1 as well in another text box.

I will look at this more carefully when i get into the office

Were you able to run my sample report? You need to change the data source to your SQL Server, but the report doesn't need any tables. The report pretty much does what I described in my post with ID 38272955. I only made a small change to the data so the results are a bit different.

You say: "I need counts where the sum is greater than zero per each distinct order number" and "has orders of 77 and -77 that would equal zero so a 1 would be returned to the textbox". I suppose "where the sum is greater than zero" should be "where the sum is zero".

When you need the nonzero number you can just substract the zero count from the total numbers of order, so that should not be a problem.

It would be really helpful if you could show me a screenshot of the report as it is now with annotations that indicate what you need.

OK based on what i saw of the code it didn't seem right but I will be in the office in a couple of hours and will take a look then. It will make more sense when I am looking at the report itself.

I get an error when i run the report:

"query execution failed for dataset 'orders'

the sytax for union is incorrect"

I don't see how that code would word for what i need.... it doesn't take into consideration the order number

The query is MS SQL syntax, not SSAS. So you should connect the report to an SQL Server data source. With this data:

          select 'cust 1' as customer, 34433 as ordernumber, 1000 as TotalSales
union all select 'cust 1' as customer, 34433 as ordernumber, -1000 as TotalSales
union all select 'cust 1' as customer, 34434 as ordernumber, 2000 as TotalSales
union all select 'cust 1' as customer, 34434 as ordernumber, -2000 as TotalSales
union all select 'cust 1' as customer, 34435 as ordernumber, 1000 as TotalSales
union all select 'cust 2' as customer, 34436 as ordernumber, 2000 as TotalSales
union all select 'cust 2' as customer, 34436 as ordernumber, -1000 as TotalSales
union all select 'cust 2' as customer, 34437 as ordernumber, 2000 as TotalSales
union all select 'cust 2' as customer, 34437 as ordernumber, -2000 as TotalSales
union all select 'cust 2' as customer, 34438 as ordernumber, 200 as TotalSales
union all select 'cust 2' as customer, 34438 as ordernumber, 100 as TotalSales

Select allOpen in new window

The report produces this result:
So it does count the number of orders per customer for which the sum of the total sales is 0.

It does take in account the order number because the Code.AddValue function is called from the ordernumber footer with sum(Fields!TotalSales.Value) as a parameter.

That report does look correct.

What should i do for connnection string?

In my case I have:

Type: Microsoft SQL Server
Connection string: Data Source=(local)\SQLExpress2008r2;Initial Catalog=FS

Select allOpen in new window

For Data Source enter the server (and maybe the instance). Initial Catalog is the database.

If you can't get the report working, but you can access the design, you could try to port my solution to your report, and see if you can get it working there.
First you need to add the code to your report (code property of the report itself).
You'll need a footer for the ordernumber group and in this footer you need to add a textbox with this expression:

=Code.AddValue(sum(Fields!TotalSales.Value))

Select allOpen in new window

In the customer footer you need to add a textbox with this expression:

=Code.GetTotal()

Select allOpen in new window

Yeah, i am going to add your solution to my report latter and play around with it

You might also want to read this article (if not already), because that is what I used to create my solution:
http://blogs.msdn.com/b/robertbruckner/archive/2008/07/20/using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx

I think i am making progress but I am geting some wierd results.


I was not getting the correct total so I wanted to see how the number was progressing line by line

so, in the code you gave me             

GetTotal() i took out the line (temporarily) to clear out the zero. Then, i added the code so I could watch it on every line item.

I noticed the following

The total was be accumulated every time there was a zero ... as it should but at some point that seem random the number was
being reset to zero
      

so. as it hits a 0 the total count goes up 1, 2, 3, 4, etc but at some point it resets itself to 0.

I will attach the file if you wouldn't mind looking

Select allOpen in new window

Select allOpen in new window

Select allOpen in new window

TestL.rdl

Because I don't have your data the report didn't work at my computer of course. So I had to create my own data. With this data (only 18 rows) the report worked fine as you can see below.

If this is the actual report as you need it. Can't you do the grouping an summing in the SSAS query already. Since you don't display the detail data you might not need it in the dataset. If the total is in the dataset already it's easy to count the zeros.

If that's not a solution can you post a screenshot of the output and/or an export of the data so I can run the report with the same data.

Are there any warnings in the Error List of BIDS (if that's what you are using) when you preview the report?

Is the Direct_Claim_Incurred_Loss_ITD field a numeric field or a text field?

Also you can change the code to:

Dim ZeroOrders As Integer
Dim DebugText as string

Function AddValue(ByVal OrderTotal As Decimal)
   if OrderTotal = 0 then
      ZeroOrders = ZeroOrders + 1
   end if
   AddValue = OrderTotal

   DebugText  = DebugText  & "Value received: " & OrderTotal & " Total: " & ZeroOrders & vbcrlf 
End Function

Function GetTotal() As string
   GetTotal= ZeroOrders
End Function
	
Function GetDebug() As string
   GetDebug= DebugText 
End Function

Select allOpen in new window

And use =Code.GetDebug() as Expression for the Textbox22. This gives you detailed information of what the code does.

The field you asked about is numeric..

There is more going on then what I showed you I just created this to help you understand what is going on.

Thank you. I will use what you gave me.

How do i check warning messages ?

In BIDS (SQL Server Business Intelligence Development Studio) open the report in Preview. Then choose View, Error List.

i appologies this is taking so long. I could award the points now and then set up a new quesion if you want...

No problem. I'm not here for the point, but to learn something while finding answers for others. I want to know too why the solution with the custom code is not working.

Thank you I appreciate all your help.

I suspect something wierd is happening with me report so I might rebuild it crom scratch

This is the weird thing that happens. It appears that the ZeroOrders variable resets to zero at page breaks. On page one it goes up 1, 2, 3, etc and then it get to page to and starts over.

hmmmmmm

Does it go out of scope?

I figured out the last problem the variables needed to be declared as:

Public Shared Dim

<<the ZeroOrders variable resets to zero at page breaks>>
And you called that "at some point that seem random" before :-)

<<Public Shared Dim>>
Good thinking, glad you found that out. That's one to remember.

I was even looking at this technique:
http://blogs.msdn.com/b/bwelcker/archive/2007/07/11/laser-guided-missiles-report-localization-through-parameters.aspx
to load all the data into the custom code and use that to calculate the values. But that would have gotten rather complicated.

Do you have the actual report working now, or just the proof of concept?

Yeah, it did seem random before but I created a new report and noticed it wasn't :)

This is pretty much the remaining issue.

I am calling Code.AddValue this way:


=IIF(Fields!Fields!IsValid = "No"
 And Year(Fields!DateCheck.Value) = 2011
  And Cdate(Fields!CompareThisDate.Value) > cdate("2012-05-31")
, Code.AddValue(Sum(Fields!NumberField.Value)), 999)


Basicly, if it meets the critera the procedure AddValue should be called. If not, it should return 999 to the textbox.

I am noticing that 999 is returned to the textbox when the critera is not met. That is perfect.

But, I am calling GetDebug text on each line too and am finding that the value is being returned with the information that
proves that the procedure AddValue is being called EVERY time.

I want to ONLY call add value if the critera above was met......how?

This is my code that i am using now:


Public Shared Dim ZeroOrders As Integer
Public Shared Dim DebugText as string

Function AddValue(ByVal OrderTotal As Decimal)
   if OrderTotal = 0 then
 DebugText  = DebugText  & "Value received: " & OrderTotal & " Total: " & ZeroOrders & vbcrlf
      ZeroOrders = ZeroOrders + 1

   end if
   AddValue = OrderTotal

   DebugText  = "Not a Zero!!!!"

End Function

Function GetTotal() As string
   GetTotal= ZeroOrders
End Function
      
Function GetDebug() As string
   GetDebug= DebugText
    DebugText =""
End Function

I am updating the code funtion to receive the variables do the checking there.

So far so good....

When you use the IIf function both parts of the IIf function are evaluated (the True and the False part), but only one of them is returned. So the AddValue is indeed called on every row. This is by design, though confusing. You'll need to put the IIf inside the AddValue call, or pass all the values that are used in the criteria and do the check inside the AddValue function.

I have everything going ok as far as I can tell and everything works ok

BUT

I don't need the detail lines so when I go and hide them my details rows all show as 0


I believe this is because I am calling my code from the detail rows and it is NOT being called when the detail rows are hidden.

Does calling the code from a group variable help?
http://blogs.msdn.com/b/robertbruckner/archive/2008/07/20/using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx

I am not getting the correct totals for the first group.

That's great.

Unfortunatly, it seems that the code is ONLY working for the first group .... it is not totaling for
all the other groups.  I use the debug to take a look at what is going on at it looks like the code to do the counting (in your example code.addvalue) which gets called in the detail line but when I go to get total I get the same counts for each group. I'm not sure why this might be.

Please Help! I am so close

Strange. Take a look at this report. It uses the group variable to make the function call. Looks like all the calls are done before the report starts rendering. So in the first record all the values are already displayed. If you can get the same behavior in your report you can use this to rewrite the code so it holds the totals per Annual_Statement_Line in a collection. When you need the total with GetTotal this function has to get the right total from the collection.
CountDistinct2.rdl

Thanks. I will be out of the office for a couple of days but I am looking forward to giving it a try.

Your code was giving me wierd results to.

I will attach mine. It works GREAT for the first group but every group afterwards always is returning the same total.  

For example if filter the report to only return 1 line if works great and returns the correct result but when it is returning all the lines only the FIRST line returns correctly and all others return the same result.

As far as i was able to experiment, all the code is getting called but for whatever reason the variables are not changing.....

Any ideas?

Yes, I think it would be a great idea if you attached your report :-)

I think I got it. I will keep this thread opened for now but i don't think i need any more help...

Thank you for all your help. I so appreciate it! I might be back with an additional question latter today but I think i will close this thread.

ty