Hello all,
I have a problem to do a count disctinct on two different fields within reporting services using an expression.
In my dataset i have a field called Description and a field called Type.
I would like to do a count distinct for every Description with Type=0 and a count distinct for every Description with Type=1.
Can this be done with an expression within RS (and not within my sql query from my dataset)
Greetings
Vinnie
Hi,
I¡¯m sorry to say that so far there is not direct support distinct calculate in SSRS (to Wei, is this correct Should we say direct distinct calculate function is not supported in SSRS). As far as I know, there is two possible ways work around this:
1. Have an another dataset which does the distinct calculation, and bind this dataset in your total textbox.
2. Use custom code. Create a function, dim a variable, and have a expression like ¡°a=a+b.value¡±. Then call this function when the Type=0, or call another function like this when Type=1. In your total textbox, show the result value.
I hope this helps. If anything is unclear, please feel free to post back.
Thanks.
Hello,
I don't see how i can do a distinct count per type
Could you help me out some more with an example
Already, thanks in advance for your help !
Vinnie
Hii
so you mean to say for field type 0 you have some datas under the field description
then you can add a group your field expression by the field Type
now for each group u can count distinct of the fields,group name.
hope it will work for you .
anyway let me know if it works
Thanks Mahasweta
Hi,
When you talk about group field, you mean in a table The thing is i don't want to display
the count distinct result in a table, but in a textbox (just to show as info how many different distinct
articles i have with type 0).
Afterwards in my table i do want to display every distinct article with some more details.
Do you see what I want to achieve
Greetings
Vinnie
Hi Vinnie,
If you just want to display count distinct result in a textbox, the only way you can do is to have another dataset.
For example: let¡¯s say you have dataset1 and dataset2, and in dataset1, you have SQL query like ¡°Select Count (Distinct A.a) As Count1 From A Where A.Type=0¡±. And in dataset2, SQL query is like ¡°Select Count (Distinct A.a) As Count2 From A where A.type=1¡±. In Layout view, in textbox1, use the expression like ¡°=First(Fields!Count1.Value, "dataset1"). Also, in textbox2, use the expression like ¡°=First(Fields!Count2.Value, "dataset2")¡±.
In conclusion, we have dataset do the distinct calculate, but not Report Designer.
I hope this helps. Thanks.
Hi Yao,
I created 2 new datasets like you said and implemented this in my report.
It works great. thanks a lot.
Greetings V.