Some fields display on SSRS and some don't

Discussion in 'microsoft.public.sqlserver.reportingsvcs' started by Dan Xing, Jan 26, 2012.

  1. Dan Xing

    Dan Xing Guest

    I have an Xml Schema Definition (Xsd) object in Visual Basic 2010
    Professional Edition. It works fine on its own and the data displays
    in the preview window perfectly.

    I have an SQL Server Reporting Services Report that is using this
    object. When run, the report displays most fields in the dataset just
    fine but there are some fields that will not display on the report.

    Fields that do not display: TotalHours, TotalHours2, TotalMinutes,
    TotalMinutes2, MinutesCharged, and MinutesOverall.

    Here is the SQL of this XSD dataset which in addition to the preview
    window in Visual Basic also runs fine in SQL Server in the view and
    when the SQL is run in a query pane:

    SELECT dbo.tblMainData.Facility, dbo.tblMainData.Contingency,
    dbo.tblMainData.ReasonCode, SUM(CAST(dbo.AuxMinutes.MinutesOverall AS
    float) / 60)
    AS TotalHours,
    SUM(dbo.AuxMinutes.MinutesOverall) AS TotalMinutes,
    dbo.tblMainData.Congestion, dbo.LUCongestionType.Limitation,
    dbo.AuxMinutes.MinutesCharged, dbo.tblMainData.RegionID,
    SUM(CAST(dbo.AuxMinutes.MinutesCharged AS
    float) / 60) AS TotalHours2, SUM(dbo.AuxMinutes.MinutesCharged) AS

    FROM dbo.AuxMinutes INNER JOIN
    dbo.tblMainData ON dbo.AuxMinutes.CongestionID =
    dbo.tblMainData.CongestionID AND
    dbo.AuxMinutes.Year = dbo.tblMainData.Year LEFT
    dbo.LUReason ON dbo.tblMainData.ReasonCode =
    dbo.LUReason.ReasonCode AND dbo.tblMainData.RegionID =
    dbo.LUReason.RegionID AND
    dbo.tblMainData.Year = dbo.LUReason.Year LEFT
    dbo.LUCongestionType ON
    dbo.tblMainData.CongestionType = dbo.LUCongestionType.CongestionType

    GROUP BY dbo.tblMainData.Facility, dbo.tblMainData.Contingency,
    dbo.tblMainData.ReasonCode, dbo.LUReason.Reason,
    dbo.LUCongestionType.Limitation, dbo.AuxMinutes.MinutesOverall,

    HAVING (dbo.LUCongestionType.Limitation = 0) AND
    (dbo.tblMainData.Congestion = 1) OR
    (dbo.tblMainData.Congestion = 1) AND
    (dbo.tblMainData.RegionID = 3)

    Thanks in advance for any suggestions.
    Dan Xing, Jan 26, 2012
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.