November 2, 2007 at 1:39 am
Hi there,
We are working on a business intelligence solution and are breaking our heads on something that is too critical for us to proceed any further and support our stance. Please help. The case is as follows.
SELECT [Month]
,[Total Sales]
FROM [TryYas].[dbo].[Yearly]
Month Total Sales
Jan 1000
Feb 200
SELECT [Month]
,[Sale]
FROM [TryYas].[dbo].[Monthly]
Month Sale
Jan 100
Jan 300
Jan 400
Feb 100
Jan 50
Feb 60
Feb 40
Jan 150
Feb 100
Say there are two tables like the ones above.. We need to know if a report model can be built so that we can build reports where the details from the first table will be shown and when the user clicks on the Month (Say Jan) details for Jan Should be pulled from the second table, something like a drilldown would also be fine.
Now, with our understanding here makes us feel that it can't be done with a report model but is possible with Report Desinger. We need to know if it is possible to have such an implementation with a report model when a cube is built or not even with a cube. Any inputs, suggestion, explanations, references, code samples, etc.. etc.. would be of GREAT HELP.
Thanks and regards,
yaseen
PFA: A Report Designer based solution though we are interested in a report model based solution.
November 18, 2007 at 4:23 pm
Your question makes me think that you wish to use an SSAS cube but you are actually thinking about trying to solve the problem with SQL. You need to choose which way to solve the problem and stick with it. SSAS and SQL Server provide two entirely different ways to solve the same problem.
SSAS provides the more elegant solution, so I would certainly recommend that route.
Your problem is easily solved if you use a cube. To get you started, I suggest you create your first cube based on two tables: a time table containing all dates in the calendar years your sales data covers and your sales data. If you are not familiar with the technogy, I suggest you download the AdventureWorksDW sample database and take a look at the associated cube project.
November 19, 2007 at 8:23 am
I'm not sure why you would use multiple tables. Wouldn't the month simply be a grouping and you could expand/contract it from there? Or if using analysis service, you could enable drillthrough and have them drill through for the details. But your still only talking 1 fact table with a time dimension.
November 19, 2007 at 9:58 am
Absolutely - one fact table and a time dimension. So if you understand that, please explain what your problem is with getting the grouping?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply