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

[anushka04]

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

 

[foxdev]

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.

--------------

http://www.liquidsql.com

SQLS metasearch

 

[ShankarJ]

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