Avatar of ExpertUserId

asked on 

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

I have an SSRS 2008 Report - has a graph with that uses a dataset of 3 pieces of data.  X,Y and Store items for seriers/ categories.   Sometimes when I run the report for a certain series type the chart comes up empty.   I check the data in dataset and it has data, but Chart says "No Data Available".  

I've tried simplifying the report and even rebuilding the chart.  But, no luck.

-Expert User.
SSRSMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Mike McCracken
Avatar of Howard Cantrell
Howard Cantrell
Flag of United States of America image

Sounds like you have an error in the chart code so it only shows certain data.
Avatar of MadRacer1
Flag of India image

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.
Avatar of ExpertUserId


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?
Avatar of ExpertUserId


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...
Can you post the sql query? May have a parameter problem still.
Avatar of ExpertUserId


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
Avatar of ExpertUserId


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?

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)
Avatar of ExpertUserId

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Microsoft SQL Server 2008
Microsoft SQL Server 2008

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo