Sorting a Groups by formula ??

[jcoleman]

HI guys and gals,

First, I'm running CR 10 and working with a SQL database.

Back ground.....

I have created a report that takes product information from the past 12 months and displays a bunch of sums, averages and calulated totals. The report is grouped by product number and is therefore sorted by product number. If the user clicks on the product number it displays the detail records as well as a sub-report showing which locations have inventory of the product.

My problem is.......

The VP came to me and would like to sort the report differently. And as luck would have it he wants it sorted on the columns that have calulated totals.
So I can not use the regular group sort utility to accomplish this.

To start with I would like to sort the report on one column that shows the 'days of inventory on hand'. This is calulated by using the lastest 'On hand' value divided by the sum of the 'Sales Count' column and multipling by 365.

According to what I have read; mostly posted by lbass; I would be required to create a SQL expression, place it in the detail line and some how either sum it or take the maximum. What ever works.

I have never used the SQL expressions within CR so bare with me. This is what I have to calulate the total of the sales count column by product, this works the way I want though SQL query analyzer.

(select sum([Pre-Month Sales Cnt])
  from [Sales History] 
  where DateInserted > dateadd("m",-11,'2005/02/28')
  and [Short Sku] = [Sales History].[Short Sku]
  group by [Short Sku])

That code is giving me the error 'Subquery returns more than 1 value.'

Any ideas on how I would fix this?

Sorry this post is so long I didn't want to miss anything.

jc

 

[synapsevampire]

SQL Database means SQL Server?

Consider building a Stored Procedure to accomplish this, it'll be faster and simpler to maintain.

The SQL Error makes sense as it doesn't appear that you're referencing the parent table.

Once you build and place a SQL Expression, check the Database->Show SQL Query to see what has been cosntructed and you can even copy and paste into Query Analyzer to tweak it.

-k

 

[jcoleman]

-k

Thanks, for the reply.

Yes, I'm using sql server.

A couple of questions about the stored procedure method.

1st - can I pass a date parameter directly from CR to the procedure.

2nd - I can figure out how to select the data that I want but is there a way of taking one selection and dividing but another selection??

That second one might sound like a really stupid question but I'm still kind of a newbie, if you didn't notice.