Adding Static Months to Line Chart????

[sconti11]

oK...I may be missing something here, but I can not seem to figure out how to get the Category groups of a Line Chart to reflect certain number of months (i.e Sep - May).

This would be consistent for each chart. I have a dataset that does carry dates, but as soon as I apply a where clause to show me specific scores, it will give me , lets say 2 scores for Sep and Oct. But I want the chart to have those scores but ploted against the 9 months in X Axis.

Anybody understand what I am trying to do here?

 

[RiverGuy]

You usually handle this in the query. You take a date table, or a derived or table variable that has a row for each date or month, and outer join your transactions to that date table. You put the where clause against the dates against the date table.

 

[sconti11]

I think I understand...but let me know if I am on the right track.

I have a dataset where I query a table of data that retrieves 52 rows. Within this data is a date field..I could join this table with a date dimension table by the related date fields. My result would be 52 rows, and I can make use of such fields like MonthName found in the joined date dimension table.

The problem is when I apply the where clause to filter the data to be used in the line charts. My SSRS report will have 4-6 Line bar charts based on 4-6 data sets which have where clauses. So..when I apply this where clause it also reduces the date field..so I no longer have the static range of months that I want applied to the charts.

I think I may not be using the OUTER JOIN correctly based on your suggestion...or maybe there is a better way to handle this??

Does my example make sense?

 

[RiverGuy]

If you outer join your main table to your date table, but put a where clause against your main table, you may as well have an inner join, because you are going to filter out what you are doing today.

A workaround would be to alter your where clause like the following:

WHERE 
(
MainTable.SomeColumn = 'SomeValue'
OR 
MainTable.SomeColumn IS NULL -- This would be a column that should not be null, but ends up being null because there is no match
)

 

[sconti11]

I am not sure I get exactly what you mean within the WHERE clause...sorry.

Below is an example of the sql I am working with:

SELECT Test_Admin_Assessment.ShortName, Test_Results.SchoolYear, Test_Results.TestAdministrationDate, Test_Results_Trait.Score,
TD_Available_Traits.FullName AS Trait_FN, TD_Available_Traits.ShortName AS Trait_SN, TD_Available_Trait_Score_Sub_Category.ScoreMinimum,
TD_Available_Trait_Score_Sub_Category.ScoreMaximum, dim_Date.MonthName
FROM Test_Admin_Assessment INNER JOIN
Test_Admin_Trait ON Test_Admin_Assessment.ID = Test_Admin_Trait.Test_Admin_AssessmentID INNER JOIN
Test_Results ON Test_Admin_Assessment.ID = Test_Results.Test_Admin_AssessmentID INNER JOIN
Test_Results_Trait ON Test_Admin_Trait.ID = Test_Results_Trait.Test_Admin_Trait_ID AND Test_Results.ID = Test_Results_Trait.Test_Results_ID INNER JOIN
TD_Assessment_Grade ON Test_Admin_Assessment.AssessmentGradeID = TD_Assessment_Grade.ID INNER JOIN
TD_Traits ON Test_Admin_Trait.TestedTraitID = TD_Traits.ID AND TD_Assessment_Grade.ID = TD_Traits.AssessmentGradeID INNER JOIN
TD_Available_Traits ON TD_Traits.AvailableTraitID = TD_Available_Traits.ID INNER JOIN
TD_SubTraits ON TD_Traits.ID = TD_SubTraits.TraitID INNER JOIN
TD_Available_Trait_Score_Sub_Category ON TD_SubTraits.AvailableSubTraitID = TD_Available_Trait_Score_Sub_Category.ID OUTER JOIN
dim_Date ON Test_Results.TestAdministrationDate = dim_Date.Date

But this is not working within the SSRS dataset...but at least it gives you what I am trying to do...I appreciate the assistance you have given thus far...I just need a little more help

 

[RiverGuy]

Try something like this, obviously you will need to modify the where clause

SELECT
d.[MonthName],
a.*
FROM
dim_Date d
LEFT OUTER JOIN
	(SELECT        Test_Admin_Assessment.ShortName, Test_Results.SchoolYear, Test_Results.TestAdministrationDate, Test_Results_Trait.Score,
							 TD_Available_Traits.FullName AS Trait_FN, TD_Available_Traits.ShortName AS Trait_SN, TD_Available_Trait_Score_Sub_Category.ScoreMinimum,
							 TD_Available_Trait_Score_Sub_Category.ScoreMaximum
	FROM            Test_Admin_Assessment INNER JOIN
							 Test_Admin_Trait ON Test_Admin_Assessment.ID = Test_Admin_Trait.Test_Admin_AssessmentID INNER JOIN
							 Test_Results ON Test_Admin_Assessment.ID = Test_Results.Test_Admin_AssessmentID INNER JOIN
							 Test_Results_Trait ON Test_Admin_Trait.ID = Test_Results_Trait.Test_Admin_Trait_ID AND Test_Results.ID = Test_Results_Trait.Test_Results_ID INNER JOIN
							 TD_Assessment_Grade ON Test_Admin_Assessment.AssessmentGradeID = TD_Assessment_Grade.ID INNER JOIN
							 TD_Traits ON Test_Admin_Trait.TestedTraitID = TD_Traits.ID AND TD_Assessment_Grade.ID = TD_Traits.AssessmentGradeID INNER JOIN
							 TD_Available_Traits ON TD_Traits.AvailableTraitID = TD_Available_Traits.ID INNER JOIN
							 TD_SubTraits ON TD_Traits.ID = TD_SubTraits.TraitID INNER JOIN
							 TD_Available_Trait_Score_Sub_Category ON TD_SubTraits.AvailableSubTraitID = TD_Available_Trait_Score_Sub_Category.ID) a
ON a.TestAdministrationDate = d.[Date]
WHERE (d.[Date] BETWEEN '20110101' AND GETDATE())

 

[sconti11]

ok...so this gives me a combined query of all months within the range of the Where clause and those data fields that apply. But how do I now plot this into the x-axis of the chart.

I need to somehow capture the distinct values of the MonthName (Sep - May), this is the static 9 months I need to show???

And also a question about the where I clause...why use the BETWEEN??

I look forward to your response.

 

[sconti11]

This is still a big issue for me! Now it is critical for the rest of my reports.

Even though I create an OUTER JOIN to the date dimension table as suggested, I am still unable to show the months (Sep-Jun) in the correct order...it would be months 9, 10, 11, etc. for the school year.

It will not allow me to sort the x-axis in that way, since the data set is showing all months and weeks regardless if there is a score for it (which ironically is what I want), it is just that I can not manipulate the report to show it in the correct order???

Please assist if you have more suggestions.

 

[xlbo]

you need to either order by DATE (which includes the year so order will be correct) or create a calculated field that determines the "correct" order as ordering by month in a standard date / time way will work either in calendar month order or alphabetically depending on how you are producing the month name

CASE d.[MonthName]
WHEN 'Sep' Then 1
WHEN 'Oct' then 2
WHEN 'Nov' Then 3
etc etc
END as MonthOrder

Alternatively add an ordering number to your dim_Date table

then on the chart simply add a sort to the x-axis based on this new field

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question