locked
SSRS can display data in Preview, but it is fail to display data in report, what to do? RRS feed

  • Question

  • User-1853164522 posted

    Hi Guys,

    my report works fine in "Preview" and data can be present. But in report data is blank(Table can be present with column name).  Below is my SQL query and i just paste the script into Dataset. and bound the table with the dataset. Do you know how to fix the issue?

    declare @TotalSum int;

     

    set @TotalSum=

    (

    select sum(DB1.[DB1_Estimate])

    from DB1

    right outer join [dbo].[DB2]

    on DB1.ID=[DB2].ID

    )

     

    select

    QueryData.[ Category] as [Category],

    QueryData.EstimateTime as EstimateTime,

    convert(float,Round(QueryData.EstimateTime*100.0/@TotalSum,2)) as Percentage,

    @TotalSum as Total

    from

    (

    select [DB2].[Category],ISNULL(sum(DB1.[DB1_Estimate]),0) as EstimateTime

    from DB1

    right outer join [dbo].[DB2]

    on DB1.ID=[DB2].ID

    group by [DB2]. [Category]

    ) as QueryData

    order by QueryData.EstimateTime ASC

    Tuesday, April 22, 2014 6:08 AM

All replies

  • User-993404089 posted


    What if you remove the set TotalSum and set the the variable to a specific number in your query (ie 100).  Do you still get the same result.

    select
    
    QueryData.[ Category] as [Category],
    
    QueryData.EstimateTime as EstimateTime,
    
    convert(float,Round(QueryData.EstimateTime*100.0/@TotalSum,2)) as Percentage,
    
    100 as Total
    
    from
    
    (
    
    select [DB2].[Category],ISNULL(sum(DB1.[DB1_Estimate]),0) as EstimateTime
    
    from DB1
    
    right outer join [dbo].[DB2]
    
    on DB1.ID=[DB2].ID
    
    group by [DB2]. [Category]
    
    ) as QueryData
    
    order by QueryData.EstimateTime ASC
    
    Tuesday, April 22, 2014 9:28 AM
  • User1140095199 posted

    Hi,

    SSRS can display data in Preview, but it is fail to display data in report, what to do?

    This happens mostly if the Name of the DataSet specified in the Report and the Name specified in the Query is NOT same. Could you Post some more information?

    The Name of the DataSet there and the name of the DataSet in the Code must be same.

    Refer to the following Post - http://forums.asp.net/t/1978586.aspx

    Meanwhile, If will try to Post some useful code it might be helpful if you are populating the Report Dynamically:

    protected void page_Init(object sender, EventArgs e)
        {
            
            //http://msdn.microsoft.com/en-us/library/aa337091.aspx
            ReportViewer1.ProcessingMode = ProcessingMode.Local;
            LocalReport localReport = ReportViewer1.LocalReport;
            //localReport.ReportPath = Server.MapPath("/ReportDemos/Report.rdlc");
            localReport.ReportPath = Server.MapPath("/ReportDemos/BlankReport.rdlc");
            localReport.DataSources.Clear();
            //*********************************************************
            DataSet ds = null;
            string str = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(str))
            {
                con.Open();
                using (SqlDataAdapter adp = new SqlDataAdapter("select * from MyData", con))
                {
                    ds = new DataSet();
                    adp.Fill(ds);
                }
            }
            //*********************************************************
            ReportDataSource rs = new ReportDataSource("DataSet1", ds.Tables[0]);
            localReport.DataSources.Add(rs);
            localReport.Refresh();
        }

    Notice that I have used the name DataSet1 in my C# code too.

    Do get back some more information if I am missing a point here.

    Best Regards!

    Friday, April 25, 2014 3:48 AM