Pivoting data
-
Hi
This is our query :
SELECT
CAST(SUM(Amount) AS FLOAT) AS [Costs], CAST('' AS FLOAT) AS [Turnover], LED.[Dimension Code], LED.[Dimension Value Code], DV.[Name],
MONTH([Posting Date]) AS month, YEAR([Posting Date]) AS year
FROM
[FIN SEPIA$G_L Entry] AS GLE JOIN
[FIN SEPIA$Ledger Entry Dimension]
AS LED ON GLE.[Entry No_] = LED.[Entry No_] JOIN
[FIN SEPIA$Dimension Value]
AS DV ON LED.[Dimension Code] = DV.[Dimension Code] AND LED.[Dimension Value Code] = DV.[Code]
WHERE
GLE.[G_L Account No_] LIKE '60%' AND year([Posting Date]) = 2007
GROUP
BY LED.[Dimension Code], LED.[Dimension Value Code], DV.[Name], MONTH([Posting Date]), YEAR([Posting Date])
Now I get results like :
Directe Kost - Omzet - Dimensioncode - Dimension Value Code - Month - Year
2486 €- 0 - Projectbudget - PEEM01011 - Test123 - 4 - 2007
2486 € - 0 - Division - 03 - Testing - 4 - 2007
I need in a report (reporting services) like this :
Month - Division - Projectbudget - Amount
4 - 03 - PEEM01011 - 2486 €
How can I get the result to report like this ?
Thx in advance
JV -
Have you checked out the new PIVOT operator in Books Online?
I'm just learning it myself, so I can't give you a lot of information. Though I'm not sure you can pivot on multiple columns at the same time. I think it pivots only on one column.
Also, Reporting Services has its own tools to re-represent your data in different formats than T-SQL allows. Have you actually checked out the tools in SSRS?
-
yes, tried tables and matrices in reporting.
Trying the pivot now, but as you stated yrself, need a lot of info in the header
thx anyway !
JV -
You know, it occurs to me that you might be heading in the wrong direction. Especially as some of your row results turn out to be column headers, but the rest of your results stay as row results (if I'm reading your example correctly).
Instead of Pivoting, maybe you need to create an SSIS package to completely deconstruct your data, then reconstruct the data in a staging table which is then picked up by SSRS in the correct format.
-
Hi Brandie
Guess u r right. Can u help me out where to start ?
Thx in advance
JV -
JV,
Have you ever used SSIS before? What about DTS? If you've never used either, I recommend getting an SSIS book. If you've used DTS but not SSIS, you might be able to get by without a book, but you still might want one just in case.
First thing, though, is ditch the query. I don't mean delete it. I mean, for the moment, forget it even exists. Your solution should be based on what the data is and how you want it to end up looking, not based off the query you have available. Queries can be rewritten. Solutions are harder to rewrite.
Start over from scratch. Look at your data. In the 2 examples you provided, you seem to want to combine them. Not knowing what the rest of your data looks like, or what "Projectbudget" truly represents (will this be a consistant value for every different division?, etc.), I can't give you specifics. But you should lay out several different divisions worth of data and look at them. Compare them to the results you really want. If this "ProjectBudget" only exists in one Division, you can't use it as a header for a column. You'll have to use something else.
Once you sketch out what everything starts as and everything ends as, your next step is to create your SSIS package, create the connections to your sources and begin some data flow tasks that transform the data accordingly. You may have to use several temp/staging tables to get it from its current form to the end form. You'll have to play with the objects in SSIS. The "Derived Columns" task may or maynot help you.
Anyway, once you're done, you've got a staging table in SQL Server that you can report on. You'll probably want to test the package several times to make sure it works consistantly, then put it in Production with a job that fires it off every X number of days. That way, the data stays current in the staging table.
I'm sorry I can't be more specific. Unfortunately, there is no "one way" to fix things that works for everyone. Your solution will be based off your schema and your data, which is completely different from mine. Without knowing your system inside and out, I can't give you more precise details for resolving your issue.
-
Hi
We've already have worked with some SSIS.
Well the problem is plain simple
I have a table containing :
Year, Month, Amount, Entry Number
eg. 2007 - 4 - 500 € - 24587
Another table is the analytical table :
Entry Number, Dimension, Dimension Value
eg. :
24587 - DIVISION - 03
24587 - PRINCIPAL - AUBM
24587 - BUSINESS - PROD
24587 - PROJECTBUDGET - AUBM10001
Result in report should be :
YEAR - MONTH - DIVISION - PRINCIPAL - BUSINESS - PROJECTBUDGET - AMOUNT
2007 - 4 - 03 - AUBM - PROD - AUBM10001 - 500 €
There is no correlation between the Dimensions made in the databases (cannot be created since it is an ERP system).
Hope this clarifies things ?
Thx
JV -
JV,
Noeld posted this link for me in my "dynamic pivoting" thread. Take a look at it to see if maybe it'll help you.
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
-
Hi
Looks very interesting !
Looks great. We thought about using union all queries to solve this one, but i'm looking in this one
Thx a lot.
Where r u at ?
JV -
Florida. Where are you?
-
belgium
JV
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply