May 17, 2011 at 10:15 am
I have a matrix grouped by Months, but I need to figure out a way to set the order of these months from Sep - June, since the data reflects a typical school year.
I have tried to adjust the data set, but I still can not seem to affect the matrix correctly.
If anyone has any ideas that I may try, it would be a great help!
May 17, 2011 at 10:22 am
Sort by Year, month
May 17, 2011 at 11:06 am
I allready have a Year prompt for the school year (i.e. 2011-12) and so that gives me a result set of months Jan - Dec....but what I need to do is show my matrix with the data starting from Sep through June.
The challenge is...I think, that a school year spans over two fiscal years.
Does this make sense???
May 17, 2011 at 11:11 am
Yes. In the sort of the groupings. Sort by 2 columns. #1 is the year, #2 is the month.
so that should give :
2011, 09
2011, 10
2011, 11
2011, 12
2012, 01
2012, 02...
May 17, 2011 at 11:40 am
I completely understand the logic in the sorting....which leads me to believe that it could be in my query..
Below is my query that I am using to build the data for the matrix. This query contains an OUTER JOIN to a date table for the simple reason that I need to still show all weeks of a month in the report regardless if there is a score for it.
SELECT a.FullName, a.SchoolYear, a.TestAdministrationDate, a.Score, a.Student_FN, a.Student_LN, a.Teacher_LN, b.MonthName, b.WeekOfMonth, b.Year
FROM (SELECT Test_Admin_Assessment.FullName, Test_Results.SchoolYear,
Test_Results.TestAdministrationDate, Test_Results_Trait.Score, Student.Student_FN, Student.Student_LN, Teacher.Teacher_LN
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) AS a RIGHT OUTER JOIN
(SELECT DISTINCT MonthName, WeekOfMonth, Date, SchoolYear, Year
FROM dim_Date
WHERE (SchoolYear = @schoolyr)) AS b ON a.TestAdministrationDate = b.Date AND a.SchoolYear = b.SchoolYear
ORDER BY a.TestAdministrationDate
The result set shows all the date data i need and school score data, but the first 100 or so rows is where the tables do not match showing null values for the school data, and the MonthName begins with Jan...when I group by MonthName in the report, I can not seem to get it to start with Sep, since the data set begins with Jan...
I hope this helps a little to see my issue.
May 17, 2011 at 11:43 am
Are you sure the date are no in strings instead of datetime?
More to the point, I don't see what the query has to do with the fact that you can't sort in the matrix.
Assuming int datatypes it WILL work, period.
May 17, 2011 at 11:45 am
I wouldn't group by the month name, I'd also group by both the year and month (both ints). That will work, that's what I do here.
May 17, 2011 at 11:57 am
Yes...even when I do what you suggested it is still not sorting. Again ..it is showing the months exactly as seen in the query of the data set....
So let me put it this way, if I disregard the date table, and use only the dates from the student score table, then I would have to apply the Month() to the standard date so as to use in the report. Then I could sort since it is using the true date to sort by. Unfortunately this will only give me Months and Weeks where there is a score...and I need to see all weeks of the month regradless if there is a score. That is why I went to a OUTER JOIN.
Does that make sense??
May 17, 2011 at 12:01 pm
Please post sample data...
May 17, 2011 at 12:12 pm
I am new to this forum....how can I display some data or screenshots for you?
May 17, 2011 at 12:18 pm
Below is the beginning of the result set of the query joined to the date dimension table..
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary12011-12201101
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary22011-12201101
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary22011-12201101
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary22011-12201101
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary22011-12201101
Below is a conitnuation of the result set from the query
Beginning of YearBeg2011-129/5/201151LNF010RedKenjiaSampsontest2September22011-12201109
Beginning of YearBeg2011-129/5/201121PSF010RedKenjiaSampsontest2September22011-12201109
Beginning of YearBeg2011-129/5/201161NWC010RedKenjiaSampsontest2September22011-12201109
May 17, 2011 at 1:24 pm
I am not sure if this will help, but here is some data from the result of my query...it is not all but it gives a good idea:
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary12011-1220110101/01/2011
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary22011-1220110101/02/2011
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary22011-1220110101/03/2011
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary22011-1220110101/04/2011
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary22011-1220110101/05/2011
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary22011-1220110101/06/2011
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary22011-1220110101/07/2011
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary22011-1220110101/08/2011
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary32011-1220110101/09/2011
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary32011-1220110101/10/2011
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLJanuary32011-1220110101/11/2011
...and here is some of the data that shows where the tables match:
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNovember42011-1220111111/23/2011
NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNovember42011-1220111111/24/2011
Beginning of YearBeg2011-129/5/201151LNF010RedKenjiaSampsontest2September22011-1220110909/05/2011
Beginning of YearBeg2011-129/5/201121PSF010RedKenjiaSampsontest2September22011-1220110909/05/2011
Beginning of YearBeg2011-129/5/201161NWC010RedKenjiaSampsontest2September22011-1220110909/05/2011
So you can see that when using the MonthName or the Month field from this query, that it automatically brings back the first month as Jan, even when i set the order to be 1st Year and then Month..it will not work.
When I sort using the TestAdministrationDate, it still does not put it correctly...
I think I am losing it now..:w00t:
May 17, 2011 at 1:45 pm
Hi,
I just tried one way for your unique sorting requirement and it works well.
If your table is using MonthName as Columns...eg If you have January, February etc then you can do the sorting the way you want.
Right Click on the Grouping in the Grouping window below your report and go to Sorting tab and in Option 'Sorting by', Click on expressions and do something like below
=IIF(Fields!MonthName.Value="September","A",
IIF(Fields!MonthName.Value="October","B",
IIF(Fields!MonthName.Value="November","C",
IIF(Fields!MonthName.Value="Decemberber","D",
IIF(Fields!MonthName.Value="January","E",
IIF(Fields!MonthName.Value="February","F",
IIF(Fields!MonthName.Value="March","G",
IIF(Fields!MonthName.Value="April","H",
IIF(Fields!MonthName.Value="May","I",
..........
<same code till August>
nothing)))))))))
Note that I have given values A,B,C to months in order I want here...which is starting from September...And then Sort Order would be A-Z(which is Default)
And you are Done :w00t:
Does that help ??
Regards,
Athar Iqbal
May 17, 2011 at 1:59 pm
Very creative!
I wish I would have thought of that....anyway it will do the job...thanks again!
May 17, 2011 at 2:21 pm
How many times do I have to say this before you try it out???
Sampson test2 September 2 2011-12 2011 09
Sort by 2011 THEN 09 and IT WILL WORK 100% SURE. It will also outperform the iif by a big factor. Very usefull if you more than a little data.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply