DISTINCT MDX 1

[Nesy123456789]

I have a dataset in Reporting Services 2005 which has fields from two cube dimensions.The first dimension has summary data and is displayed in a table- e.g Company Number, total sales, etc.
The second dimension has info across time periods - e.g. Company Number, weekly sales, weekNumber. I want to show the trend over time as a chart but not display the time info on the table.
My problem is that to access the time data I have to drag it into the dataset, which causes the table to display duplicate rows (one for each time period). I need a way of removing the duplicate rows from the table but still have access to more detailed time data for the chart.
I am doing this work in preparation of moving to 2008 and using the sparkline in the table.

http://thavash.spaces.live.com/

Or will this not be a problem in 2008?

 

[RiverGuy]

When I create reports like these, I create a separate report for the chart, which includes the dataset with the data detailed across time. I then add the chart's report as a subreport to a detail cell in the main report, passing in parameters to the chart report based on the values of the current row.

 

[Nesy123456789]

cheers I'll have a go. By the way - do you know how the 2008 sparkline handles this? Will it generate dupes?

 

[RiverGuy]

I wasn't aware that SSRS had native sparklines. Are you simply referring to placing a chart within a table cell?

 

[Nesy123456789]

Check out the link:

http://thavash.spaces.live.com/

it is in R2 2008.