|
bigweed
Starting Member
19 Posts |
 Posted - 2010-12-04 : 18:00:21
|
| Hi all, hopefully someone can help me create a report! I am very new to SSRS and SQL so please forgive me if I have rambled on too much.I have 8 transaction tables, which will be used to create a report. The report is to be used to report on job performance. Each table has a field for the job number, so I can always identify what job every item in the table is for. What I want the report to look like is as follows:Job Performance Forecast Actual P&L WIPRevenue x x Materials x xSubcontractor x xLabour x xExpense x xNet x xThe user is prompted to enter a parameter for the job number when the report is run. This parameter is used to narrow down the transactions returned in the dataset to only those for the job selected.So the way the tables are used is as follows.- The tables are: RevenueActual, RevenueForecast, ItemActual, ItemForecast, ExpenseActual, ExpenseForecast, LabourActual, LabourForecast.- Both the ItemActual and ItemForecast tables contain values for both Materials and Subcontractor report items. These can be differentiated using the CategoryID field - the Materials category ID is 'Materials', the Subcontractor category ID is 'SubCon'. ie the ItemActual table contains actual costs of category Materials and category SubCon, the ItemForecast table contains forecast costs of category Materials and category SubCon- The values in the Forecast column on the report come only from the (xxx)Forecast tables ie the Forecast figure of Expense on the report is a sum of all values, for the job selected, from the ExpenseForecast table.- The values in the Actual column come only from the (xxx)Actual tables ie the Actual figure of Subcontractor on the report is a sum of all values for category ID SubCon, for the job selected, from the ItemActual table.- The values in the P&L column come only from the (xxx)Actual tables, where the TransactionStatus field is P&L, eg the P&L figure of Subcontractor on the report is a sum of all values for category SubCon, for the job selected, from the ItemActual table, where the TransactionStatus field is P&L- The values in the WIP column come only from the (xxx)Actual tables, where the TransactionStatus field is Balance or (Blank), eg the WIP figure of Revenue on the report is a sum of all values, for the job selected, from the RevenueActual table, where the TransactionStatus field is Balance or (Blank)- The total of the P&L and WIP columns for any report row should equal the Actual figure for that row on the report.There is actually also a ninth table, JobTable, this ninth table is being used to return details of the job itself, I am displaying this data inside a listbox data region. All of the above nine tables are within one dataset, AllCalls. The report also contains a second dataset, AllCompany, this dataset is used for the selection of a second parameter - the name of the company the user wants to base the report on. The eight transaction tables are all linked to the JobTable, using the job number field and the company field. The JobTable is being filtered on using the values selected for the 2 report parameters by the user.What I have at the moment is as follows. The Job Details from the JobTable are being displayed nicely, so that part is all fine. These job details are above the Job Performance section shown above. The Job Details are within a listbox control, with textboxes used to display the various pieces of information about the job.My problem is with the figures for the Job Performance section. I have tried so many times just to get the correct figure for the Materials Actual amount but could not make it work, and so I have no idea where to go for the rest of the expressions. The expression is as follows at the moment for the Materials Actual figure:=SUM(IIF(Fields!CATEGORYID.Value = "Materials",Fields!MXMPROJTOTALVALUE.Value, 0),"AllCalls")Can someone tell me where I am going wrong in that expression? If I can get this expression right, then I think I would be able to make the others work.I think a list report would work best, with textboxes for each of the above figures, which is how I have been designing it. Is that a good way to design it, for maximum speed of data retrieval? The tables will eventually have thousands of rows of data each, with the ItemActual table containing hundreds of thousands of rows.So it all really boils down to two questions:1 - How can I get the expression for the Materials Actual figure on the report to work?2 - Am I designing the report correctly, ie nine tables all in one dataset, and displaying the data within a listbox? |
|
SSRS 2005 - Guidance on creating this report pleas