Geek Answers Handbook

How do I get around Sum (First (...)), invalid restriction - SSRS2005

The problem I ran into is Reporting Services SQL Server does not like Sum (First ()) record. It will use only Sum () or First ().

Context
I am creating a reconciliation report. i.e. what sock we had at the beginning of the period, what was ordered and what stock we had at the end.

Dataset returns something like
Type, Product, Customer, Stock at Start (SAS), Ordered Qty, Stock At End (SAE)
Export, 1,1,100,5,90
Export, 1,2,100,5,90
Domestic, 2,1,200,10,150
Domestic, 2,2,200,20,150
Domestic, 2,3,200,30,150

I group by type, then by product, and the list of customers who bought this product. I want to display the total for SAS, Ordered Qty and SAE, but if I do Sum on SAS or SAE, I get a value of 200 and 600 for Product 1 and 2 respectively, when it should be 100 and 200 respectively.

I thought I could do Sum (First ()). However, SSRS complains that I cannot have an aggregate in the aggregate.

Ideally, SSRS needs Sum (Distinct ())

Solutions so far
1. Do not show stocks at the start and stocks at the end as part of the outcome.
2. Write the code in the report to make an extract. I tried it - it did not work, as I expected. 3. Write the assembly for calculation. (Not tried this one)

-
, , ( ). .
.

  • , (, )
  • ,
  • ,
  • ,
  • ,
  • .

, . , . , ( ) , .

+5
4

ROW_NUMBER, , SUM (IIR (myRowNumber = 1, myValue, 0)).

, , , , . , .

+5

! ?!

, SQL?

. , Min Max, ( ).

Group Value Min Max
A      3    2    9
A      7    2    9
A      9    2    9
A      2    2    9
B      5    5    7
B      7    5    7
C etc..

,

AggregateFunctionYouWant  OVER (WhatYouWantItGroupedBy, WhatYouWantItOrderedBy) as AggVal

+1

, , , .

: , , SAS, SAE, ,

, . , . sas sae , , . , , . sas sae ()

+1

.

SSRS Sum (Distinct())

, .

, , , , . , , , , "".

0

More articles:


All Articles