August 9, 2010 at 10:39 am
Hi All,
I have Simple Column Chart ,Data is Pulled out From one ofthe Sql Table,
in My Sql table for some Date i dont have the Data/Record,
Say for Example First Day of the Every Month no transaction happens so table Doesnt hold any Data For those Dates.
But Our Business Chart Needs to show up all the Dates.For Now i have the Data starting From the 2nd,But Chart Needs To be Displayed From the 1st,
How Can i Add th Addtional ReCord to the Chart in SSRS ,So that 1st Day Appears.
Let Me Know Plz.
Thanks in Advance.
August 20, 2010 at 1:43 am
I would suggest that you use a pre-populated dates table in your query and use this as the basis for your report. You might have a query something like:
SELECT DatesTable.Date, Sum(MyTable.Value) as MyChartValue
FROM DatesTable
LEFT OUTER JOIN MyTable ON MyTable.Date=DatesTable.Date
WHERE DatesTable.Date BETWEEN @FromDate AND @ToDate
GROUP BY DatesTable.Date
Using this type of query you will generate a row for each date within the range you are looking at, even when there are no values to report.
HTH
Kevin
August 20, 2010 at 7:25 am
In this situation it is common to use a Date or Calendar table. There are several good articles on building and using them right here on SSC. Basically, it just a table that lists every date and various useful permutations of the date.
Quick example:
Date, Year, Month, Day, Quarter, Month Name...etc.
1/1/10, 2010, 1, 1, Q1, Jan
......
5/15/10, 2010, 5, 15, Q2, May
......
9/21/10, 2010, 9, 21, Q3, Sep
Put this together as Kevin suggests and you should be good.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply