Can some one assist with this SQL Query for a Bar Chart using classification

Karen_Bruster1

I am trying to create a Bar chart using sql that is ordered by Classification (Incidents only) and I created a new data source on the portal. It will not let me select category or value field.

Here is the code I have so far:

USE [DWDataMart];

WITH IR_CLASSIFICATION_ENUM AS
(
     SELECT Enum.[IncidentClassificationId] AS 'EnumDimKey'
           ,DS.[DisplayName] AS 'EnumDisplayName'
           ,Enum.[ParentId] as 'EnumParentDimKey'
     FROM [dbo].[IncidentClassificationvw] Enum (nolock)

     JOIN [dbo].[DisplayStringDimvw] DS (nolock)
     ON DS.[BaseManagedEntityId] = Enum.[EnumTypeId]
     AND DS.[LanguageCode] = 'ENU'
)
,IR_CLASSIFICATION_FULLENUM AS
(
     SELECT Enum.[EnumDimKey]
           ,cast(Enum.[EnumDisplayName] as nvarchar(max)) AS 'EnumDisplayName'
           ,Enum.[EnumParentDimKey]
     FROM IR_CLASSIFICATION_ENUM Enum
     WHERE Enum.[EnumParentDimKey] IS NULL
     UNION ALL
     SELECT Enum.[EnumDimKey]
           ,cast(FullEnum.[EnumDisplayName] + '\' + Enum.[EnumDisplayName] as nvarchar(max)) AS 'EnumDisplayName'
           ,Enum.[EnumParentDimKey]
     FROM IR_CLASSIFICATION_ENUM Enum
     JOIN IR_CLASSIFICATION_FULLENUM FullEnum on Enum.[EnumParentDimKey] = FullEnum.[EnumDimKey]
)

SELECT IR.[DisplayName]
--DATA TESTING ONLY: IR.[Classification_IncidentClassificationId] & IR_CLASSIFICATION.[EnumDisplayName].

      /*,IR.[Classification_IncidentClassificationId]
      ,IR_CLASSIFICATION.[EnumDisplayName] AS 'Full Classification'*/
         /*,'eReg (CustomerApp)' = COUNT(CASE
WHEN IR_CLASSIFICATION.[EnumDisplayName] in ('Incident Classification\Applications\eReg (CustomerApp)') THEN 'eReg (CustomerApp)'
END)*/

--Translates "Full Classification" into two tiers "Incident Type" & "Incident Classification".
       ,'CustomerApp Incident Type' = CASE
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('Incident Classification\Applications\eReg (CustomerApp)%') THEN 'eReg (CustomerApp)'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('Incident Classification\Applications\eInfo (CustomerApp)%') THEN 'eInfo (CustomerApp)'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('Incident Classification\Applications\eFiling (CustomerApp)%') THEN 'eFiling (CustomerApp)'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('Incident Classification\Applications\eROP (CustomerApp)%') THEN 'eROP (CustomerApp)'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('Incident Classification\Applications\Working Tools (CustomerApp)%') THEN 'Working Tools (CustomerApp)'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('Incident Classification\Applications\Special Portal (CustomerApp)%') THEN 'Special Portal (CustomerApp)'
END

       ,'CustomerApp Incident Classification' = CASE
WHEN IR_CLASSIFICATION.[EnumDisplayName] = ('Incident Classification\Applications\eReg (CustomerApp)') THEN 'eReg (CustomerApp)'
WHEN IR_CLASSIFICATION.[EnumDisplayName] = ('Incident Classification\Applications\eInfo (CustomerApp)') THEN 'eInfo (CustomerApp)'
WHEN IR_CLASSIFICATION.[EnumDisplayName] = ('Incident Classification\Applications\eFiling (CustomerApp)') THEN 'eFiling (CustomerApp)'
WHEN IR_CLASSIFICATION.[EnumDisplayName] = ('Incident Classification\Applications\eROP (CustomerApp)') THEN 'eROP (CustomerApp)'
WHEN IR_CLASSIFICATION.[EnumDisplayName] = ('Incident Classification\Applications\Working Tools (CustomerApp)') THEN 'Working Tools (CustomerApp)'
WHEN IR_CLASSIFICATION.[EnumDisplayName] = ('Incident Classification\Applications\Special Portal (CustomerApp)') THEN 'Special Portal (CustomerApp)'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('%\Service Fault%') THEN 'Service Fault'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('%\Business Function%') THEN 'Business Function'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('%\Account Re-activation%') THEN 'Account Re-activation'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('%\Access%') THEN 'Access'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('%\Password Reset%') THEN 'Password Reset'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('%\eReg Other%') THEN 'eReg Other'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('%\Non-Technical%') THEN 'Non-Technical'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('%\Report%') THEN 'Report'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('%\Outage%') THEN 'Outage'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('%\Service Degradation%') THEN 'Service Degradation'
WHEN IR_CLASSIFICATION.[EnumDisplayName] LIKE ('%\Training%') THEN 'Training'
END
--Translates Source Type.
         ,'Source'= CASE
WHEN IR.Source in ('IncidentSourceEnum.Phone') THEN 'Phone'
WHEN IR.Source in ('IncidentSourceEnum.Console') THEN 'Console'
WHEN IR.Source in ('IncidentSourceEnum.Email') THEN 'e-mail'
WHEN IR.Source in ('IncidentSourceEnum.Portal') THEN 'Portal'
WHEN IR.Source in ('Enum.27164bd736b8464fa033c60acfecfd5d') THEN 'Walk In'
END
       ,IR.CreatedDate AS 'Date Created'
--Translates Status.
       , 'Status' = CASE
WHEN IR.Status in ('IncidentStatusEnum.Closed') THEN 'Closed'
WHEN IR.Status in ('IncidentStatusEnum.Active') THEN 'Active'
WHEN IR.Status in ('IncidentStatusEnum.Resolved') THEN 'Resolved'
END
,IR.ResolvedDate AS 'Date Resolved'
,IR.Description AS 'Description'

FROM [dbo].[IncidentDimvw] IR (nolock)

LEFT JOIN IR_CLASSIFICATION_FULLENUM IR_CLASSIFICATION
ON IR_CLASSIFICATION.[EnumDimKey] = IR.[Classification_IncidentClassificationId]

WHERE [EnumDisplayName] LIKE '%CustomerApp%'
ORDER BY [DisplayName]

Karen_Bruster1

Does anyone have a suggestion?
All I want is a BAR chart, that is drillable, and shows specific classifications with parent classification? Oh and a count

Karen_Bruster1

@Justin_Workman
Hi Justin do you maybe have an idea how I can get this accomplished easier?

Justin_Workman

Hey Karen,
I used your query(of course I edited it slightly to account for my Classifications) and was able to get a Bar chart with count and drill-through.  I was able to group on Type or Classification.  If you run the same query through just a SQL Table widget does it bring back results?

Karen_Bruster1

Justin_Workman

I am getting neither a table nor a chart, it just shows a blank chart widget or table widget.  

Justin_Workman

Can you try another more simple query against that datasource?  Maybe it's just not querying the datasource?  

Karen_Bruster1

@Justin_Workman

I ran the following query and nothing came back.

/* Incidents By Affected User (Top 15) – Last 30 Days (DWDataMart) */

SELECT TOP (15) U.UserDimKey, U.DisplayName, WIAU.IncidentCount
FROM
( SELECT WIAU.WorkItemAffectedUser_UserDimKey, Count(*) AS IncidentCount
FROM WorkItemAffectedUserFactVw AS WIAU
where wiau.CreatedDate > DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), dateadd(day, -30, getdate()))
--and WIAU.WorkItemAffectedUser_UserDimKey != '3515'
--and WIAU.WorkItemAffectedUser_UserDimKey != '3516'
GROUP BY WIAU.WorkItemAffectedUser_UserDimKey

) AS WIAU
INNER JOIN UserDimVw AS U ON U.UserDimKey = WIAU.WorkItemAffectedUser_UserDimKey
--WHERE WIAU.IncidentCount > 2
ORDER BY WIAU.IncidentCount DESC

*************************************************

Can you send me a sample of the connection string

Here is what I have for my data source:

Data Source=SMSCLCEN3\SCSMDW;Initial Catalog=DWDataMart;Integrated Security=SSPI;

Thanks