?
Solved

Roll up Year to day balance from Data Warehouse

Posted on 2011-10-13
6
Medium Priority
?
257 Views
Last Modified: 2012-06-27
I have a dimension table and a fact table with the schema listed below. Is it possible to roll up a year to date figure such that in a user select Year 2011 Period 8. The value will be displayed a accumuated value, rather than a current period figure. Can I achieve the result in Excel ?

Thanks



Dimension
CREATE TABLE [dbo].[DimCOA](
	[ACTKEY] [int] IDENTITY(1,1) NOT NULL,
	[ACTINDX] [int] NOT NULL,
	[ACTNUMBR_1] [char](11) NOT NULL,
	[ACTNUMBR_2] [char](11) NOT NULL,
	[ACTNUMBR_3] [char](11) NOT NULL,
	[ACTNUMBR_4] [char](11) NOT NULL,
	[ACTNUMBR_5] [char](11) NOT NULL,
	[ACTNUMBR_6] [char](11) NOT NULL,
	[ACTNUMBR_7] [char](11) NULL,
	[ACTNUMBR_8] [char](11) NULL,
	[ACTNUMBR_9] [char](11) NULL,
	[ACTNUMBR_10] [char](11) NULL,
	[ACTDESCR] [char](51) NOT NULL,
	[ACCATDSC] [char](51) NOT NULL,
	[ACTTYPE] [char](2) NOT NULL,
 CONSTRAINT [PK_DimCOA] PRIMARY KEY NONCLUSTERED 
(
	[ACTKEY] ASC
))

Fact
CREATE TABLE [dbo].[FactPERBAL](
        [TRXNO] [int] IDENTITY(1,1) NOT NULL,
 	[ACTKEY] [int],
	[ACTINDX] [int] NOT NULL,
	[YEAR1] [smallint] NOT NULL,
	[PERIODID] [smallint] NOT NULL,
	[PERDBLNC] [numeric](19, 5) NOT NULL,
	[CRDTAMNT] [numeric](19, 5) NOT NULL,
	[DEBITAMT] [numeric](19, 5) NOT NULL,
 CONSTRAINT [PK_FactPERBAL] PRIMARY KEY NONCLUSTERED 
(
	[TRXNO] ASC
))

Open in new window

0
Comment
Question by:AXISHK
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36962160
The short answer - Yes.

You'll want to look into Group By and the Sum command.  The other fun part is making sure you are rolling up the correct figures.

It shouldn't be too bad depending on what exactly you are totalling and how the data is laid out.

Drew
0
 

Author Comment

by:AXISHK
ID: 36966186
Do you mean do this in SSIS or pviot table in SQL 2008 ? Any you give me more idea how to archieve it ?

Thanks

for example,
year   : 2011
Period :  8

Open Bal                         Dr           Cr           NetBal            Closing Period
(YTD up to Period 7)      (current)   (curent)   (current)         (YTD up to Period 8)
0
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36968371
If you want to lay the data out like that you should look into SSRS.

An example SQL statement would be

select [year], period, sum(periodvalues)
from <your table>

group by year, period
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:AXISHK
ID: 36972001
Tks, but users prefer to get the result in Excel..

Is there a way to calculate some calcuation field to arhieve the purpose ? Have to create it using the existing data in Data warehouse ?

Tks
0
 
LVL 9

Accepted Solution

by:
DrewKjell earned 2000 total points
ID: 36979799
There is a sum command in Excel as well that may meet your needs.  You can create functions to put logic behind the value assigned to a cell.

Drew
0
 

Author Closing Comment

by:AXISHK
ID: 37004045
Tks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question