SQL Clone
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 (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 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 (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 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
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3757 Visits: 4110
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
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 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
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 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