Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KJones140
Starting Member
1 Post |
 Posted - 2011-06-16 : 12:48:07
|
| I have recently built a data warehouse with SQL Server 2008 Enterprise Edition. I have created a report model for it with BIDS and deployed to Report Manager so that my users can do ad-hoc reporting with ReportBuilder. For the time being we have chosen to use ReportBuilder 1.0 since it automatically generates ClickThrough reports for our users. I have used my report model to generate some very simple reports, but have run into an issue. I am unable to generate "drill-across" reports properly with ReportBuilder when I want to group on more than one conformed dimension. Here is an example: I have 2 Fact Tables. FactPledges includes (among other measures) a PledgeAmount field. FactProjectFinancials includes a BudgetAmount field. Both use conformed dimensions of FiscalYear (DimYr) and Project (DimProject). I can easily build a report that shows me Project Name (from DimProject), Fiscal Year (from DimYr) and the aggregated Pledge Amounts. I can also build a similar report that shows Project Name, Fiscal Year and the Budget Amounts. However, I am unable to create an accurate report that shows the 2 measures (PledgeAmount and BudgetAmount) aggregated properly by Project Name AND Fiscal Year. This is the area where I believe there is a problem....When creating this report with ReportBuilder using my ReportModel, I drag the PledgeAmount from FactPledges, then from the associated DimProject and DimYr, I drag the ProjectName and FiscalYear fields respectively. To get the BudgetAmount field for the report, I then have to go back to either the DimProject -OR-DimYr entity to get to the associated FactProjectFinancials entity. When I use the FactPledges>DimProject>FactProjectFinancials path, the BudgetAmount on the end report is aggregated (as you would expect) only by Project. And, when I use the FactPledges>DimYr>FactProjectFinancials path, the BudgetAmount is only aggregated by Fiscal Year. I do not know how to indicate to ReportBuilder that I want the BudgetAmount aggregated by both ProjectName and FiscalYear.This is some very basic functionality, so I am guessing that I may have an issue with my DW Design or the Report Model that I created. Can anyone provide some guidance on where I should be focusing my efforts? |
|
|
|
|
|
|
|
Using ReportBuilder to Drill Across Fact Tables