January 9, 2008 at 4:39 am
Hi Guys
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 Vodafone5500 12
Monday voda Vodafone6030 1
Monday voda Vodafone6230 2
Monday voda Vodafone6230i 4
Monday voda Vodafone6233 20
Tuesday tmti Asda PAYG5300 2
Tuesday tmti Asda PAYGV3i 2
Tuesday tmti Vodafone1 1
Tuesday tmti Vodafone7650 2
Tuesday tmti VodafoneKE850 Prada 8
Tuesday voda Vodafone5140i 2
Wednesdaytmti Asda PAYG6070 1
Wednesdaytmti Asda PAYGC130 2
Wednesdaytmti Asda PAYGE390 6
Wednesdaytmti Asda PAYGKG800 Chocolate 4
Wednesdaytmti Asda PAYGW220 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.
Cheers all
January 9, 2008 at 5:27 am
Actually, I think this is my bad for being a thicko. I've got my day of week shift thingy wrong - re-working it now.
June 19, 2014 at 3:20 pm
Did you resolve this issue with fix in your formula?
I'm having a very similar issue. I have a data that comes from a sproc which I've included a field called DataSort. I've pre-sorted in the sproc and when I put the data in a tablix some tables sort just fine but when I include expressions in some of the data fields the sort goes out of wack.
Thanks!
June 19, 2014 at 3:36 pm
SET DATEFIRST to 1, then include DATEPART(weekday,r.timesent) in your results set, then order by that.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 19, 2014 at 3:49 pm
Sorry I don't think I explained my issue quite well. So I'm not doing anything with a date I have data in my sproc based on a field I've added called DataSort. This field contains (1 - 6) and when executed the data comes out sorted as expected (see below - FYI my data is a little more complex than this but gives you an idea of what I'm doing). If I put it into a tablix (filtered by FieldEligibility = 1) it shows as expected. However when I start adding expressions in the data fields the sort is all messed up.
Field1FieldCountFieldAmountFieldEligibilityFieldGroupFieldSort
High 100011
High 2150012
High 3431043013
Medium 1661066024
Medium 278788025
Low12001200000026
High 11100111
High 27100112
High 322100113
Medium 1551077124
Medium 2457880125
Low75095768126
June 19, 2014 at 6:42 pm
Ok I believe I found my issue and will be reporting this to Microsoft (if they haven't resolved it in later versions of SSRS). The issue has to do with the values in Field1 containing the same name as sproc column names. So if I have sproc column Field1 and the values are 'Field record 1', 'Field record 2', etc it will get confused. I will need to rename my sproc columns.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply