asked on

Ignore one of the parameters in a tablix

Hi,
I have a SSRS report with two parameters, Year and Months.
I have then a tablix with a column and in that column I want to show sales data for last year, but I don't want to show only for the months you choose from the filter, but instead I want only the value from the Year parameter to filter that column.
I then have another column in the same tablix, and that column should filter the data based on the values in the year and the Month parameters.

example:

Total sales for region 1 in year 2014 = 800.000
Sales for region 1 in jan,feb and March 2015 = 250.000

Parameter Year value = 2015
Parameter month value = Jan,feb,march

Tablix:
Region Sale data 2014 Sales data jan-march 2015
reg 1 800.000 250.000

Is this possible?

Manju

You can get this info directly from the sql query used inside SSRS.

For Ex:
SQL query for the tablix:
Select Region, salesdata, salesdatamonth from tbl1 where month = @month and year = @year

Change this to

Select coalesce(a.region, b.region) as Region, a.Salesdata, b.Salesmonth from

(Select Region, Salesdata, salesmonth from tbl1 where year = @year) a
Inner join
(Select Region, salesdata, salesmonth from tbl2 where month = @month and year = @year) b

on a.Region = b.Region

Cheers,
Manju

gosi75

ASKER

Hi, thanks for the solution, I forgot to mention that my datasource is SSAS cube, is it possible to get similar results with SSAS as a datasource?

Manju

yes, we're just working in the query. so give it a go

cheers

ASKER CERTIFIED SOLUTION

Arifhusen Ansari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gosi75

ASKER

Thanks for the help