SSRS Expression for Dataset analysis. IF or CASE statement?

Experts:

I'm using the following dataset in an SSRS report:

UserID, Completed_Flag
100,0
100,1
100,1
100,1
101,1
101,1
101,0
102,1
102,1
102,1
102,1

I need an aggregate summary formula/Expression to use that will analyze the dataset to see if for any USERID grouping, if any Completed_Flag =0 then the entire USERID will be considered "Not Completed and should display a "0".

The result for the above dataset example should be this:

UserID, Completed_Flag
100,0
101,0
102,1

How can I accomplish this within the SSRS report using the EXPRESSION?

Thanks
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
You can try like this:

create table #t (UserID smallint, Completed_Flag tinyint)
insert into #t
select 100,0
union all
select 100,1
union all
select 100,1
union all
select 100,1
union all
select 101,1
union all
select 101,1
union all
select 101,0
union all
select 102,1
union all
select 102,1
union all
select 102,1
union all
select 102,1

select userid, cast(min(Completed_Flag) as bit) as Completed_Flag from #t
      group by userid
0
MIKESoftware Solutions ConsultantAuthor Commented:
As mentioned, I need a formula to use in the EXPRESSION field in the report cell field.

Does anyone know the formula or syntax to use IN THE REPORT..not database.

The dataset is being pulled in from a SSAS CUBE and cannot change.
0
grendel777Commented:
Since you're using a numeric boolean value (0, 1), you can use the MIN function:
=Min(Fields!Completed_Flag.Value)

Open in new window

0
MIKESoftware Solutions ConsultantAuthor Commented:
In a tabular like report, how does it know to GROUP BY "USER_ID"..??

Again,..I have a tabular report that is displaying the above dataset. So are you saying that the GROUPING of the report itself will be used as the GROUP BY clause for this MIN() function?
0
grendel777Commented:
Ideally in the query, as @lcohan said, but you can finagle it in the report. I'll give instructions for using BIDS or Visual Studio - if you're using that Report Builder tool I'm not sure how it works, but hopefully it's similar.

Select the entire table, either by selecting any cell and then clicking the grey box in the upper left, or by selecting the the tablix in the dropdown at the top of the Properties window. At the bottom you should see a section called "Row Groups". Click the dropdown next to "Details", then Add Group -> Parent Group -> Group By -> select UserID. Don't add a header or footer. The only tricky part is that now the report will add a new column with the UserID, and it will still list every line, so you have to tweak two things. First, hide that column: click the grey column header and in the Properties window find Visibility:Hidden and change it to True. Second, change the UserID field to the expression =First(Fields!UserID.Value) to just pull one UserID value into the form. Now as long as the flag field is =MIN() it should only show one row per UserID with a 0 if there are any false flags.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.