Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Re-Ordering My ata


SSRS Re-Ordering My ata

Author
Message
Steve Pettifer
Steve Pettifer
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 207
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    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.

Cheers all



Steve Pettifer
Steve Pettifer
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 207
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.



Anthony Caravia
Anthony Caravia
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 86
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!
Jason Selburg
Jason Selburg
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2803 Visits: 4101
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Anthony Caravia
Anthony Caravia
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 86
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.

Field1   FieldCount   FieldAmount   FieldEligibility   FieldGroup   FieldSort
High 1   0   0   0   1   1
High 2   1   50   0   1   2
High 3   43   1043   0   1   3
Medium 1   66   1066   0   2   4
Medium 2   78   788   0   2   5
Low   1200   1200000   0   2   6
High 1   1   100   1   1   1
High 2   7   100   1   1   2
High 3   22   100   1   1   3
Medium 1   55   1077   1   2   4
Medium 2   45   7880   1   2   5
Low   750   95768   1   2   6
Anthony Caravia
Anthony Caravia
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 86
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search