After nearly 8 years of T-SQL development I have finally had to give in and learn reporting services (2000 since it's for an older system that won't be upgraded for a bit). I'm playing about with parameterised queries and calling reports from ASP and one of the reports I created threw up an odd quirk and I'm wondering if anyone knows how to get round it.
Basically, I have created a simple SP which returns me some grouped data ordered by the day of the week. Now for me, the first day of the week is Monday and my data reflects that when you run the SP in query analyser. This is achieved by ordering the data using the following calculation:
(DATEPART(day, r.timesent) - @@DATEFIRST + 6) % 7 + 1
This gives the day numbers as 1-7 Monday-Sunday.
Some exmaple output might be as follows:
Monday voda Vodafone 5500 12
Monday voda Vodafone 6030 1
Monday voda Vodafone 6230 2
Monday voda Vodafone 6230i 4
Monday voda Vodafone 6233 20
Tuesday tmti Asda PAYG 5300 2
Tuesday tmti Asda PAYG V3i 2
Tuesday tmti Vodafone 1 1
Tuesday tmti Vodafone 7650 2
Tuesday tmti Vodafone KE850 Prada 8
Tuesday voda Vodafone 5140i 2
Wednesday tmti Asda PAYG 6070 1
Wednesday tmti Asda PAYG C130 2
Wednesday tmti Asda PAYG E390 6
Wednesday tmti Asda PAYG KG800 Chocolate 4
Wednesday tmti Asda PAYG W220 4
And so on which is just what I want.
Thing is, when my report uses the SP, the report displays Sunday as the first day of the week no matter what I do despite the fact that the data is already ordered from Monday to Sunday. Does RS have some kind of implicit ordering going on here which I can alter or is something else happening? Although this is just a mess-about reprt it is highly likely that I will need to do this kind of ordering for real reports in the very near future so I want to find out what is going on.