Link to home
Start Free TrialLog in
Avatar of Marcus Aurelius
Marcus AureliusFlag for United States of America

asked on

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....?
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Marcus Aurelius

ASKER

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