Geek asks and answers

SSRS axis names regardless of chart data

I have a chart with two axes. One of them is based on a number of dates, and the other is based on a year. My problem is that the person requesting the chart wants me to show the data one year before the date and the selected dates, but now I would like me to show all the available groups for the data, whether they exist in the data or not .

For a little more clarity. Here is the query I'm using:

SELECT     COUNT(AdDate) AS ErrorCountYTD, BusinessUnit, '' AS ErrorCountCur
FROM         MasterAnnotation
WHERE     (ActualAgencyError = 'Yes') AND (Client = @Client) AND (DATEPART(YY, AdDate) = DATEPART(YY, sysdatetime())) AND (BusinessUnit IS NOT NULL) AND 
                      (BusinessUnit <> '') AND (AnnotationDate = 'Final_Proof')
GROUP BY BusinessUnit
UNION ALL
SELECT     '' AS ErrorCount, BusinessUnit, COUNT(AdDate) AS ErrorCountCur
FROM         MasterAnnotation AS MasterAnnotation_1
WHERE     (ActualAgencyError = 'Yes') AND (Client = @Client) AND (AdDate IN (@ReleaseAD)) AND (BusinessUnit IS NOT NULL) AND (BusinessUnit <> '') AND 
                      (AnnotationDate = 'Final_Proof')
GROUP BY BusinessUnit

So, if the client has 15 business units, but their activity in 2014 will not be displayed, I will not show any data. Is there a way to write the first part of the query so that the business unit is not data dependent? I have some business units that operated in 2013, but I had no problems reporting for 2014.

, .

+3
1

, SQL. , , SQL. SQL, JOIN.

, , , , , -. , , - , .

, , , , :

SELECT
    ma1.BusinessUnit
    ,ma2.ErrorCountCur
    ,ma2.ErrorCountYTD
FROM
(
SELECT DISTINCT
    BusinessUnit
FROM
    MasterAnnotation
) ma1
LEFT JOIN
( 
    SELECT
        BusinessUnit 
        ,'' AS ErrorCountCur
        ,COUNT(AdDate) AS ErrorCountYTD
    FROM
        MasterAnnotation
    WHERE
        ActualAgencyError = 'Yes'
        AND Client = @Client
        AND DATEPART(YY, AdDate) = DATEPART(YY, sysdatetime()) 
        AND BusinessUnit IS NOT NULL
        AND BusinessUnit <> ''
        AND AnnotationDate = 'Final_Proof'
    GROUP BY
        BusinessUnit
    UNION ALL
    SELECT 
        BusinessUnit
        ,COUNT(AdDate) AS ErrorCountCur
        ,'' AS ErrorCountYTD
    FROM
        MasterAnnotation AS MasterAnnotation_1
    WHERE
        ActualAgencyError = 'Yes' 
        AND Client = @Client
        AND AdDate IN (@ReleaseAD)
        AND BusinessUnit IS NOT NULL
        AND BusinessUnit <> '' 
        AND (AnnotationDate = 'Final_Proof')
    GROUP BY 
        BusinessUnit
) ma2
ON
    ma1.BusinessUnit = ma2.BusinessUnit

"", "ma2". , - ( , ).

, , ma1 ( -) ErrorCounts - . ma2 ma2 , ErrorCount "" , ( , ..).

WHERE , , . GROUP BY , , . , .

+1

More articles:


All Articles