SSRS 2008 Report - data in dataset, but Chart says "No Data Available"

Sounds like you have an error in the chart code so it only shows certain data.

Hey ExpertUserId,
Is your dataset having any Parameter?

can you do 1 thing,
add one table to report pointing to same dataset. and display fields in that table..
So that you can easily identify where does this problem belongs to dataset or graph.

In case, tables also says "No Data Available".  
then you can say that there is issue with dataset, its not returning anything.
To dig in further you can use SQL profiler so that you can identify which query is executing exactly, with all parameters(If any). and hence you can easily identify issue and i think you can update SQL query of dataset accordingly.

Let me know if there is still an issue.

I am using four parameters; two with drop down lists and two other Date parameters.
I have a table containing just the data from the same dataset as the graph.   It also shows "No Data Available."   But, still when in Design mode I right click on the dataset and run the SQL query, the query always returns data.  

Yes.  I think there is something wrong in the back-end code that says dataset is empty when in reality it has data.   Or the connection to the database is shut down some how right in the middle of the report execution.  something in the code between the SQL Server and the SSRS.  

I have run SQL Profiler and I don't see anything that stands out that would expain the issue.  Please note I am not very experienced using Profiler.   Any other ideas?

planocz:  an error in the chart code.
Good supposition.  So I deleted the chart and rebuilt it.  No luck.   So then I deleted the report and created a new report.  No luck.   The report only fails to pull in the data from the dataset when I run it with specific parameter values.   (No input parameters just drop down choices.)  Anyway I just don't know what to do next.

Thanks for any ideas...
 ExpertUser

Can you post the sql query? May have a parameter problem still.

I think your correct.  This first query is what populates the @StoreItems used in the main query which would be the parameter that may be causing the problem.  

-- StoreItems Query
SELECT DISTINCT ItemId + '  ' + ContainerType AS 'ItemDescription'
FROM            MediaStore
WHERE        (StoreType = @StoreType)
ORDER BY 'ItemDescription'

 -- -Main dataset
SELECT        MS.ItemId + ' ' + MS.ContainerType AS StoreItem, MS.Quantity AS QtyInStore, DATEADD(HH, 20, T.date) AS Date
FROM            MediaStore AS MS left JOIN
                         DatesTable AS T ON T.date BETWEEN @BeginDate AND @EndDate AND MS.EntryDate <= DATEADD(hh, 20, T.date)
WHERE        (MS.StoreType = @StoreType) AND ((MS.ItemId + ' ' + MS.ContainerType) IN (@StoreItems))
ORDER BY StoreItem, T.date

I'm not completely sure it is a parameter.   However, I ran a test where I replaced the multivalued Variable @StoreItems with a hard coded string value.   When I run the report with the new query it does show that value in the Graph.   So, this is why I suspect the problem to be the IN (@StoreItems)) parameter.  Although I have no idea how else I would write this part of the query.   I have to cary multiple items in the variable.   Should I open a new thread?

Thanks,
ExpertUser

If you are bring in multi- values, you may have to take your @Storetype and split it on the different values (ei, One long string)

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.