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

ExpertUserId
ExpertUserId used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.

Author

Commented:
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?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
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.

Author

Commented:
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

Author

Commented:
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 issue has been resolved.  It was not a parameter issue.  It was in the data.  The values for StoreItems were being concatenated in the where clause.   I changed it to remove all spaces between the two components of the item and then alway add two spaces.  I don't really know where the spaces were getting off.  But, it seems to work now.

Thanks for you thoughts
ExpertUser!
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial