Friday, February 24, 2012

Couple of questions over SSRS over SSAS

I've created a sales analysis cube using SQL Server 2005 and want to do some reporting using SSRS. My problems are as follow:

1. How can I change the report parameter from a Date type into a member of the Date dimension? I need to do this because it's easier for business user to select the date and I can set a sensible default to it.

2. How can I create a hourly sales analysis report for all branches? If I add a chart into SSRS, I will get all shop data summing up right now into a single chart. Instead, I would like to have a chart for each branch. My business user will not accept to manually select different branch and generate the required chart.

3. How can I add a percentage to the corresponding value? My business user may want to view the sales performance during 1:00pm ~ 2:00pm comparing to the whole day. Moreover, they may also want to compare the sales on Monday to the sales for the whole week in percentage sense.

Thanks for any advise.

Regards,
AlexI've solved the third question. In order to do this, I need to add a new column to the matrix. After that, I put the following into expression of the new cell to calculate the percentage.

=Count(Fields!Sales_Count.Value)/Count(Fields!Sales_Count.Value, "matrix1_Day")

matrix1_Day is the column group name that I found out by right click and select the "Edit Group".

This solution is available in the book Microsoft SQL Server 2005 Reporting Services by McGrawHill pp.327-331.

Hope this can help someone later on.

Regards,
Alex

No comments:

Post a Comment