locked
How to change the values of the tablix depending on the parameter RRS feed

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

    Thursday, September 13, 2012 6:17 PM

Answers

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

    • Proposed as answer by Charlie Liao Monday, September 24, 2012 1:15 AM
    • Marked as answer by Charlie Liao Tuesday, September 25, 2012 9:37 AM
    Thursday, September 13, 2012 9:45 PM

All replies

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

    • Proposed as answer by Martina White Thursday, September 13, 2012 8:38 PM
    Thursday, September 13, 2012 6:31 PM
  • 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

    Thursday, September 13, 2012 6:36 PM
  • 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.

    Thursday, September 13, 2012 6:58 PM
  • 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.

    • Proposed as answer by Charlie Liao Monday, September 24, 2012 1:15 AM
    • Marked as answer by Charlie Liao Tuesday, September 25, 2012 9:37 AM
    Thursday, September 13, 2012 9:45 PM