Help with SSRS report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Help with SSRS report

Author

Topic

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-14 : 09:06:52

Hi,
I am creating a report. The stored proc returns three fields currency, amount and code.

In the report I need to add all the -ve values of the amount and all the +ve values of the amount group by currency. Finally if the code is 1 then i have to display only sum of -ve values, if code is 2 then i have to display only +ve values and if 3, then display both the values.

Currency Amount Code
GBP -5 3
GBP 10 3
GBP -3 3
GBP 20 3
EUR -50 3
EUR -200 3
EUR -500 3
EUR 1000 3

So since the code is 3. I need to display

GBP -8
GBP 30
EUR -750
EUR 1000

Kindly help me with this.

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 09:16:05

make query like


SELECT currency,CASE WHEN Amount < 0 THEN 1 ELSE 2 END AS Cat,SUM(Amount)
FROM table
GROUP BY currency,CASE WHEN Amount < 0 THEN 1 ELSE 2 END

and in report group by currency,cat

add a filter for cat based on value for code in container properties like

=Fields!Cat.Value =IIF(Parameters!code.value=3,Fields!Cat.value,parameters!code.value)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-14 : 09:32:33

Thanks. But in report there are two tables one that displays the summary
with values
GBP -8
GBP 30
EUR -750
EUR 1000

the other table displays the values

Currency Amount Code
GBP -5 3
GBP 10 3
GBP -3 3
GBP 20 3
EUR -50 3
EUR -200 3
EUR -500 3
EUR 1000 3

So the stored procedure would return all the records.

Kindly let me know the method to filter the data or group the data in report viewer.

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 10:33:34

then do the grouping in reports

in grouping properties add expression as below

=Fields!currency!value
=IIF(Val(fields!Amount.value <0),1,2)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-17 : 10:43:46

One more query to that.

I have 10 columns returning from stored proc.
so if value of balance column is 1 then I need to display the rows with the category column value 1,
if the value of balance column is 2 then I need to display the rows with the category column value 2,
if the value of balance column is 3 then I need to display the rows with the category column value both 1 and 2.

So how do write the expression for each column in the table to display?

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 10:57:26

is balance column part of resultset? will value be same for all the rows in resultset?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-17 : 10:59:20

balance is part of result set. yes, it will be same for all the rows.

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 11:04:02

then use a filter in table as follows

=Fields!category.value =IIF(First(Fields!balance.value,"yourdataset")=3,Fields!category.value,First(Fields!balance.value,"yourdataset"))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-17 : 11:14:08

How about the filter in other columns?

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 11:17:44

why do you need to filter on other columns? as per your reqmnt you've only to filter based on category column. the expression i gave is for tablix filter not column (cell) filter.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-17 : 12:23:07

Got the following error.

[rsAggregateInFilterExpression] A FilterValue for the table ‘table2’ includes an aggregate function. Aggregate functions cannot be used in data set filters or data region filters.

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 12:34:53

try like this

=Fields!category.value =IIF(Fields!balance.value=3,Fields!category.value,Fields!balance.value)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-10-19 : 11:15:34

Thanks Visakh. It worked fine.

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 00:44:54

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources