SSRS - SUM of Flag Column that contains "1" or "-" (dash)...?

Experts,

Have a dataset like this in my report that I obtain from a CUBE:

UserID, Flag_1, Flag_2
100,1,-
100,0,1
100,1,-
101,1,-
101,0,1
101,1,-

Simply put I have a "-" dash in place of a ZERO.

Can I sum up the FLAG_2 column using the SUM() function in SSRS or,..with the dashes be an issue....?
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The dash is likely the formatting and not the actual value, so if that's the case then yes.

If not, then the T-SQL you can embed in your data source to pull this off will go something like this...
SELECT SUM(CASE WHEN Flag2 = '-' THEN 0 WHEN '1' THEN 1 END) as column_name

Open in new window


SSRS should be the same concept but instead of CASE use IIF
=Sum(IIF(Field2="'", 0, 1))

Open in new window


<updated after initial post>
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
MIKESoftware Solutions ConsultantAuthor Commented:
I'm using MDX, here is the Query:
(Note: [Measures].[Status Complete2] is the TEST field, [Measures].[Status Complete] is the actual field)

with        
            Member [Measures].[Status Complete] as
                  left([Measures].[Completed Flag]/[Measures].[Assigned Flag],1)
            Member [Measures].[Status In Progress] as
                          left([Measures].[Inprogress Flag]/[Measures].[Inprogress Flag],1)
                  Member [Measures].[Status Past Due] as
                          left([Measures].[Pastdue Flag]/[Measures].[Pastdue Flag],1)  
                  Member [Measures].[Status Complete2] as
                  CASE
                  WHEN left([Measures].[Completed Flag]/[Measures].[Assigned Flag],1) = 1 THEN 1
                  WHEN left([Measures].[Completed Flag]/[Measures].[Assigned Flag],1) = '-' THEN 0
                  END
SELECT
{
      [Measures].[Assigned Flag],
      [Measures].[Completed Flag],
      [Measures].[Inprogress Flag],
      [Measures].[Pastdue Flag],
      [Measures].[Pending Completion Approval Flag],
    [Measures].[Not Started Flag],
    [Measures].[Status Complete],
    [Measures].[Status Complete2],
    [Measures].[Status In Progress],
    [Measures].[Status Past Due]
} on columns,
non empty{
      [Store].[Store Hierarchy].[Store].MEMBERS  *
      [Employee Hierarchy].[Employee Name].[Employee Name].MEMBERS
} on rows
from LD_Cube
0
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.