Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

financial report (e.g. P&L, Balance sheets etc).

Status
Not open for further replies.

anushka04

Technical User
Jul 7, 2005
28
GB
I am writing some financial report (e.g. P&L, Balance sheets etc).

Can any body please advise me on how I can bring data from multiple quires into the same data table?

i.e. Revenue in column and budgets etc on 1 column so I can do calculation.

I used crystal reports before where I could use link reports and bring values through to the main report for calculation. Is this possible in Reporting services, if yes how can I achieve this.

The format I require is :


Current period YTD

Ledger code/Dim rev bud var% rev bud var%



My idea is to bring budget related information into the main table using link reports or other datasets.

But I am not sure how I can relate 1 dataset to another

Eg. Data set1 brings revenue info for an account

Dateset2 brings budget info for the same account

I want use both values in the same table so that I can calculate the variance


Any help will appreciated
 
For my complex reports (those that are beyond a simple SQL statement) I create a stored procedure and do all my data gathering and manipulation there. From what I understand of your project, I think you can fairly easily have a SProc that uses table variables/temp tables to gather diverse results together for use by the report.

--------------
SQLS metasearch
 
Hi!

SELECT ACT.*, REV.*, BUD.*
FROM ACCOUNT ACT
LEFT JOIN (SELECT ACCOUNT, ... FROM REVENUE WHERE ... [GROUP BY ACCOUNT]) REV ON ACT.ACCOUNT = REV.ACCOUNT
LEFT JOIN (SELECT ACCOUNT, ... FROM BUDGET WHERE ... [GROUP BY ACCOUNT]) BUD ON ACT.ACCOUNT = BUD.ACCOUNT
WHERE ...
GROUP BY ...

where

ACCOUNT is the Account Table
REVENUE is the Revenue/Postings Table
BUDGET is the Budget Table

and assuming that ACCOUNT is the link columns across these tables.

Regards
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top