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 |
|
hanavan
Starting Member
25 Posts |
 Posted - 2009-01-18 : 18:58:46
|
| Situation:I have an employee cube with 2 facttablesboth have a different granularity.first facttable givesemployeeid, day, category, hours (multiple category a day are possible)second facttable gives employeeid, day, workinghours (hours employee has to work on a day)Now I want to create a report which combines in a table both facttables.Report should look like thisemployeename |day | category | hours | hours/hoursworkedwith subtotals on month, day, employeeProblem:I've tried multiple ways to solve this, but never reached my endgoal. First I created a report without data from the hoursworked table. So I got all values and subtotals, then I wanted to add the hoursworked a day values. Adding it to the same dataset made it run forever to get result (columns and rows set to none empty). Then I tried using it in a subreport, this works but I can only add it to the report, but I can't make calculations. Because my explanation can be strange, I created an image: [url]http://users.telenet.be/hanavan/example.jpg[/url] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-18 : 23:11:53
|
| i think what you need is to group the report based on month, day, employee and give aggregated expression like=SUM(Fields!yourfield.value) in group footers. this will give you the subtotals. Also table footer giving same expression gives you total. from cube you need to bring only reqd measures dirctly using MDX using month,employee & day details from respective dimensions. |
 |
|
|
|
|
|
|
|
compare 2 MDX datasets