Writing T-SQL for Triangulation Report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Writing T-SQL for Triangulation Report

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-11-29 : 06:57:35

Hello

I've been asked to write a triagulation report in SSRS, to do this I need to prepare the data in a flat structure using SQL. I am struggling to think how I would do this.

Firstly I guess you need to understand what triangulation report is (I cant see much mention of the type of thing I'm doing on google etc)

Basically you have the years going across and quarters going down, then you provide a running total of your premium so for example...

2009 2010 2011
1 10 20 70
2 30 50 90
3 70 80 160
4 100 120 190
--------------------
5 120 190
6 150 210
7 180 280
8 220 310
--------------------
9 290
10 320
11 400
12 500
---------------------

As you can see we have the premium values providing a running table, I delibrately put a line after each 4th as that indicates a full year so 2009 but row 12 is Q4 2011

Now I have a column which displays each quarter, a column which gives me each accounting year , and then a column which provides a premium value for them however I cant work out how best to turn this into a flat structure report to put in SSRS.

My manager (before she left the company) said the best way is to do a DENSE_RANK() on Display Quarter which is basically 20101, 20102, 20103,20104,20111,20112 etc and managed tog et that working to give me rows 1 through to x and then the sum(signedpremium) for that quarter, along with the running total but my problem is that it gives me running total for one year but then doesnt start again for the following accounting period

Apologies fi confusing, I don't think many will be able to help as its a bit convluated and specific to my task, but if you can it would be great! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 07:03:46

how are values coming from your table currently?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-11-29 : 07:08:28

Well I pulled them all into a tempoary table so it looks similar to this, in terms of data I can get I can add accounting period, or anything really, the data is in tables and I'm comfortable extracting it, its more how I extract it I'm struggling with, does that help?

ROWRANK Quarter Premium
1 Q4 2009 713.070951
2 Q1 2010 1182.061538
3 Q2 2010 1986.404566
4 Q3 2010 1704.976186
5 Q4 2010 705.478028
6 Q1 2011 1116.344589
7 Q2 2011 6204.116387
8 Q3 2011 7559.801751
9 Q4 2011 1074.662625

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-11-29 : 07:08:55

Apologies if I've misunderstood what your asking, I'm new to SQL (relatively)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 07:42:25

quote:
Originally posted by NickC

Well I pulled them all into a tempoary table so it looks similar to this, in terms of data I can get I can add accounting period, or anything really, the data is in tables and I'm comfortable extracting it, its more how I extract it I'm struggling with, does that help?

ROWRANK Quarter Premium
1 Q4 2009 713.070951
2 Q1 2010 1182.061538
3 Q2 2010 1986.404566
4 Q3 2010 1704.976186
5 Q4 2010 705.478028
6 Q1 2011 1116.344589
7 Q2 2011 6204.116387
8 Q3 2011 7559.801751
9 Q4 2011 1074.662625

Sorry I cant find these data in report you posted before.
Can you first give some data and then explain how you want them to appear in report?
for example how does above data appear in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-11-29 : 10:56:44

Hi Mate

Fixed the issue I was having, was quite simple in the end.

Although now come up with an additional problem which I'll repost as a different thread.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 11:22:49

ok...np

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources