ansaurus

Question

How do I get around the Sum(First(...)) not allowed limitation is SSRS2005

Answer 1

A:

Write a subquery.

Ideally SSRS needs a Sum(Distinct())

Re-write your query to do this correctly.

I suspect your problem is that you're written a query that gets you the wrong results, or you have poorly designed tables. Without knowing more about what you're trying to do, I can't tell you how to fix it, but it has a bad "smell".

tpdi 2009-04-21 06:21:07

Answer 2

+1 A:

Pooh! Where's my peg?!

Have you thought about using windowing/ranking functions in the SQL for this?

This allows you to aggregate data without losing detail

e.g. Imagine for a range of values, you want the Min and Max returning, but you also wish to return the initial data (no summary of data).

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..

Syntax looks odd but its just

AggregateFunctionYouWant  OVER (WhatYouWantItGroupedBy, WhatYouWantItOrderedBy) as AggVal

Windowing

Ranking

adolf garlic 2009-04-21 06:48:21

Yes I aggree, it does smell... I will looking to the windowing and ranking. The main problem is that it is really two seperate reports merged into one. A production report (stock at start, production, sales, stock at end) and a sales report(who those sales were to). Unfortunalty that is how management wanted the report.

Nathan Fisher 2009-08-04 23:34:26

Answer 3

+1 A:

I had a similar issue and ended up using ROW_NUMBER in my query to provide a integer for the row value and then using SUM(IIF(myRowNumber = 1, myValue, 0)).

I'll edit this when I get to work and provide more data, but thought this might be enough to get you started. I'm curious about Adolf's solution too.

Paul G 2009-04-21 10:29:18

Ok, I included a column like: SELECT ROW_NUMBER() OVER (Partition BY [keyFieldForRecGroup] ORDER BY [keyFieldForRecGroup] ) AS myRowNumberThis causes the row number to be reset on each new occurrence of keyFieldForRecGroup. Then you can use the SUM + IIF combination to get the desired result.

Paul G 2009-04-21 18:05:46

Should have used your field names:SELECT ROW_NUMBER() OVER (Partition BY [Type] ORDER BY [Type] ) AS myRowNumberSUM(IIF(myRowNumber = 1, [Ordered Qty], 0)

Paul G 2009-04-21 18:07:49

Answer 4

+1 A:

you're dataset is a little weird but i think i understand where you're going.

try making the dataset return in this order: Type, Product, SAS, SAE, Customer, Ordered Qty

what i would do is create a report with a table control. i would set up the type, product, and customer as three separate groups. i would put the sas and sae data on the same group as the product, and the quantity on the customer group. this should resemble what i believe you are trying to go for. your sas and sae should be in a first()

DForck42 2009-04-27 03:27:29

to me it seems that your data should be in a bit of a hierarchial structure, going from type to product to customer. having the dataset return in a similar structure allows (atleast to me) a better idea to plan out the report.

DForck42 2009-04-27 03:32:38

You are correct that the data should be a hierarchical structure, but how do you do that when the data-set in reporting server seems to only allow a flat structure. perhaps I have missed something obvious.

Nathan Fisher 2009-05-20 01:05:14

try following this blog and see if it helps you to understandhttp://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/creating-a-basic-drilldown-report-in-ssr-2005

DForck42 2009-05-21 14:44:47

Thanks for the blog. I think that this will certainly help for future reports. I will have to see how this style of report will translate to my requirements for this current scenario. I will have to do a little more playing around with the data and the report.

Nathan Fisher 2009-05-27 02:04:15

ansaurus

tags:

views:

answers:

How do I get around the Sum(First(...)) not allowed limitation is SSRS2005

related questions