Create a sales report

I am using SQL 2000 reporting service. I need to create a sales report
described below.
1. Return the top 10 clients in terms of the sales in Month/Year such as
Sep. 2006.
2. Return the sales to these 10 clients in the past 11 months of the above
entered date such as from Aug. 2005 to Aug. 2006.
3. Line them up to compare the sales in 12 months.
It's very easy to return the top 10 clients. However, I really don't know
how to complete the second retrun. Since the result should be based on the
return of the first query and the result of the first query is dynamic. Is it
possible to have this done in SQL reporting service? I can't find any related
document. Please advice.
Thanks.the Employee Sales Report sample report for Adventureworks does something
very similar to this...The drill down, I think, is either a link or subreport.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Jun" wrote:
> I am using SQL 2000 reporting service. I need to create a sales report
> described below.
> 1. Return the top 10 clients in terms of the sales in Month/Year such as
> Sep. 2006.
> 2. Return the sales to these 10 clients in the past 11 months of the above
> entered date such as from Aug. 2005 to Aug. 2006.
> 3. Line them up to compare the sales in 12 months.
> It's very easy to return the top 10 clients. However, I really don't know
> how to complete the second retrun. Since the result should be based on the
> return of the first query and the result of the first query is dynamic. Is it
> possible to have this done in SQL reporting service? I can't find any related
> document. Please advice.
> Thanks.

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

CountRows

I have a query structured to return products sold by date. I'm then grouping them by the year they were sold, and totalling up sales..etc. The problem is when I run my sql query that retrieves the data in the SQL designer, the query retrieves 97 rows. When I run the report, and check the number of rows returned (with CountRows()) I get 67. However the remaining 30 rows still show up and are grouped on the next page! Why is this happening? Thanks!It would appear that it's splitting the data into two queries... one with 67 rows and one with 30 rows. I do not want this behavior to occur. What may I do?|||

Try moving the counter out of the group row and put CountRows() in the table row.

Tableheader CountRows()

Group1header "Not here"

data

group1footer

Tablefooter "or here" CountRows()

|||

Thanks for the speedy reply! With that in the table header it's still returning 67/30. I think I've narrowed the problem down to the List grouping expression, but I don't understand why it considers the same value to belong to a different group?

|||It had to do with the list... Removing it and throwing in just a table fixed the problem. When would you ever need to use a list? Thanks