How to change the values of the tablix depending on the parameter

Question

I have a dataset which uses a parameter in the where clause  for Year.And in the report i have 4 tablixes which must  use the same dataset.

Here is the question :- 

So,if i pick the parameter year 2012 then is there anyway where can i see the sales for 1 tablix related to  Q1 and second one as Q2 third one as Q3 and fourth one as Q4.

Answer 1

Yep you can.

1)  Set your dataset to return all data for 2012. 

2)  Include a column to show which quarter the data applies to.

3)  Set up your tablix to show all data in it, but put a filter on the first tablix to only show data where 'quarter' = 'Q1'.  Do this by right clicking on the tablix, select properties and then select filter where you cna enter the condition.

4)  Amend the other tablixes (tablixi?) to filter Q2, Q3 and Q4 respectively. 

5)  You're done :)

Regards

James

---

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 2

HI J Still,

Here is my MDX

WITH 

SET [Top X Master Silhouettes] AS TopCount([Product].[Master Silhouette].[Master Silhouette].Members, 1000,[Measures].[Net Sales Dollars])
SET [Top X Master Silhouette Report] AS[Top X Master Silhouettes]
MEMBER [Measures].[Master Silhouette Rank Report] AS Rank([Product].[Master Silhouette].CurrentMember, [Top X Master Silhouettes])
MEMBER [Measures].[Group 1] AS
CASE
WHEN [Measures].[Master Silhouette Rank Report] > 0
AND [Measures].[Master Silhouette Rank Report] <= 10
THEN "1-10"
WHEN [Measures].[Master Silhouette Rank Report] > 10
AND [Measures].[Master Silhouette Rank Report] <= 30
THEN "11-30"
WHEN [Measures].[Master Silhouette Rank Report] > 30
AND [Measures].[Master Silhouette Rank Report] <= 60
THEN "31-60"
WHEN [Measures].[Master Silhouette Rank Report] > 60
AND [Measures].[Master Silhouette Rank Report] <= 100
THEN "61-100"
WHEN [Measures].[Master Silhouette Rank Report] > 100
AND [Measures].[Master Silhouette Rank Report] <= 1000
THEN "100+"
END

MEMBER [Measures].[Group 2] AS
CASE
WHEN [Measures].[Master Silhouette Rank Report] > 0
AND [Measures].[Master Silhouette Rank Report] <= 30
THEN "Top 30"
WHEN [Measures].[Master Silhouette Rank Report] > 30
AND [Measures].[Master Silhouette Rank Report] <= 100
THEN "31-100"
END


MEMBER [Measures].[Group 3] AS
CASE
WHEN [Measures].[Master Silhouette Rank Report] > 0
AND [Measures].[Master Silhouette Rank Report] <= 100
THEN "Top 100"
END



member [sales] as ([Measures].[Net Sales Dollars]/1000)
member [LY sales] as SUM([Fiscal Date Calculations].[Date Calculation].&[LY Day],[Measures].[Net Sales Dollars])/1000

member [units] as ([Measures].[Net Sales Units]/1000)
member [LY Units] as SUM([Fiscal Date Calculations].[Date Calculation].&[LY Day],[Measures].[Net Sales Units])/1000

member [GM Per] as [Measures].[Net Sales Product Margin]
member [LY GM Per] as Sum([Fiscal Date Calculations].[Date Calculation].&[LY Day],[Measures].[Net Sales Product Margin])


SELECT
{
[Measures].[Master Silhouette Rank Report]
,[Measures].[Group 1],[Measures].[Group 2],[Measures].[Group 3]
,[Sales],[LY sales],[units],[GM Per],[LY Units],[LY GM Per]
} ON 0,
non empty{
([Top X Master Silhouette Report]*
except([Financial Transaction Channel].[Transaction Super Channel Desc].[Transaction Super Channel Desc].members,{[Financial Transaction Channel].[Transaction Super Channel Desc].&[NONE],[Financial Transaction Channel].[Transaction Super Channel Desc].&[UNCODED]}),
[Date].[Fiscal Quarter].[Fiscal Quarter],[Date].[Fiscal Half].[Fiscal Half],[Date].[Fiscal Month].[Fiscal Month])
} ON 1
FROM EDW

where ([Date].[Hierarchy - Fiscal].[Fiscal Year].&[Year 2012])

Can you please help me how to get the do the point 2 in the mdx which says like set up a column for quarter can you please help me with 1 quarter.

Thanks

Answer 3

Oooh - I've never used MDX before, I'm usually just create straight datasets, but let's give it a go :)

Is there any chance you can add [Date].[Hierarchy - Fiscal].[Fiscal Quarter] in the list of values returned by the SELECT statement? 

I'm not sure of the exact syntax, but I tihnk if you can get it in there, you'll get a row returned for each quarter instead of for the year overall and can filter on it.

Does that help at all?

Regards

James

---

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 4

Hi There

Thanks for your posting. As I can see you aleardy selecting [Date].[Fiscal Quarter].[Fiscal Quarter]

So please create four tablix  and give all tablix the same dataset as source(Say your dataset Name is FiscalDataset

Now as James describe above go the tablix properties of every tablix and create a filter

Say for example the filter for first table might look like this on

Expression: =Fields!Fiscal_Quarter.Value

Operator: =

Value =1

Say for example the filter for Second  table might look like this on

Expression: =Fields!Fiscal_Quarter.Value

Operator: =

Value =2

the filter for third table might look like this on

Expression: =Fields!Fiscal_Quarter.Value

Operator: =

Value =3

Say for example the filter for fourth table might look like this on

Expression: =Fields!Fiscal_Quarter.Value

Operator: =

Value =4

If you have any questions please do ask

Many Thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.