Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS Re-Ordering My ata Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2008 4:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:18 AM
Points: 199, Visits: 194
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



Post #440534
Posted Wednesday, January 9, 2008 5:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:18 AM
Points: 199, Visits: 194
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.


Post #440555
Posted Thursday, June 19, 2014 3:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:51 PM
Points: 19, Visits: 78
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!
Post #1584063
Posted Thursday, June 19, 2014 3:36 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 2,694, Visits: 3,750
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
Post #1584067
Posted Thursday, June 19, 2014 3:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:51 PM
Points: 19, Visits: 78
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
Post #1584071
Posted Thursday, June 19, 2014 6:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:51 PM
Points: 19, Visits: 78
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.
Post #1584117
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse