Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
Suppress Duplicate data in a matrix

已答复 Suppress Duplicate data in a matrix

  • Thursday, March 01, 2012 9:51 PM
     
     
    Sign In to Vote
    0

    Hi:

    I am new ssrs 2008 r2 . I got the data to display the sum of quarterly data.  In order for me to get the quarter data I am using a runningvalue formula.  I am trying to supress the quarter data that are previously the same.   I am trying to suppress 10/2012 and 11/2012.  And leave 12/2012 and 11/2013 with the quarterly total data on it.    I tried using row visiblity based on the runningvalue formula.  That did not work. 

    Any idea. 

    Thanks,

    kkmick

     

     

All Replies

  • Friday, March 02, 2012 7:13 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi kkmick

    Thanks for you post.

    According to your description, you would like to hide the duplicate data in a matrix, could you show me your report without apply the hide expression to your row visiblility, and the expected result you want, which may assist me to understand clearly about your requirement.

    Thanks,
    Bill Lu

    Bill Lu

    TechNet Community Support

     
  • Friday, March 02, 2012 8:12 AM
     
      Has Code
    Sign In to Vote
    0

    Hi kkmick,

    Hopefully the RDL is of some use which i gave you in the last post.

    Kindly use the Below expression for Quarter Row Values in the expression box.

    =IIF(RunningValue(Count(Fields!Month.Value), COUNTDISTINCT, "Calendar_Quarter")=CountDistinct(Fields!Month.Value,"Calendar_Quarter"),Fields!Internet_Sales_Amount.Value,nothing)

    Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

    Dont forget to mark it as Answered if found useful | myspeakonbi

     
  • Friday, March 02, 2012 1:40 PM
     
      Has Code
    Sign In to Vote
    0

    Hi Rakesh:

    I did use your rdl to show the data.  It allows to get me the quarter sum information.  Thank you so much.  Maybe you can tell me what I did wrong.   It does sum up the quarterly data.   I want to leave 12/2012 and 11/2013 with the sum of the data.  Even though 11/2013 only has one month of information.  

    This is the current formula in the quarter value expression box. 

    =iif(RunningValue(Count(Fields!forecastbookdate.Value) ,

    COUNTDISTINCT, "matrix1_fiscal_quarter")=1 or RunningValue(Count(Fields!forecastbookdate.Value) , COUNTDISTINCT, "matrix1_fiscal_quarter")=2 or RunningValue(Count(Fields!forecastbookdate.Value) , COUNTDISTINCT, "matrix1_fiscal_quarter")=3 or RunningValue(Count(Fields!forecastbookdate.Value) , COUNTDISTINCT, "matrix1_fiscal_quarter")=4, Sum(Fields!project_percent.Value,"matrix1_fiscal_quarter"),Sum(Fields!project_percent.Value,"matrix1_fiscal_quarter"))

    This is the rdl.  

    <?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <Body>
    <ReportItems>
      <Tablix Name="matrix1">
        <TablixCorner>
          <TablixCornerRows>
            <TablixCornerRow>
              <TablixCornerCell>
                <CellContents>
                  <Textbox Name="textbox3">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value />
                            <Style>
                              <FontFamily>Tahoma</FontFamily>
                            </Style>
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>textbox3</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixCornerCell>
            </TablixCornerRow>
            <TablixCornerRow>
              <TablixCornerCell>
                <CellContents>
                  <Textbox Name="Textbox4">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value />
                            <Style>
                              <FontFamily>Tahoma</FontFamily>
                            </Style>
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>Textbox4</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixCornerCell>
            </TablixCornerRow>
          </TablixCornerRows>
        </TablixCorner>
        <TablixBody>
          <TablixColumns>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
          </TablixColumns>
          <TablixRows>
            <TablixRow>
              <Height>0.21in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="textbox2">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Sum(Fields!project_percent.Value)</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>textbox2</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                  <DataElementOutput>Output</DataElementOutput>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox10">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Sum(Fields!project_percent.Value)</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox10</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                  <DataElementOutput>Output</DataElementOutput>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox12">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=iif(RunningValue(Count(Fields!forecastbookdate.Value) , COUNTDISTINCT, "matrix1_fiscal_quarter")=1 or RunningValue(Count(Fields!forecastbookdate.Value) , COUNTDISTINCT, "matrix1_fiscal_quarter")=2 or RunningValue(Count(Fields!forecastbookdate.Value) , COUNTDISTINCT, "matrix1_fiscal_quarter")=3 or RunningValue(Count(Fields!forecastbookdate.Value) , COUNTDISTINCT, "matrix1_fiscal_quarter")=4,  Sum(Fields!project_percent.Value,"matrix1_fiscal_quarter"),Sum(Fields!project_percent.Value,"matrix1_fiscal_quarter"))</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox12</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                  <DataElementOutput>Output</DataElementOutput>
                </TablixCell>
              </TablixCells>
            </TablixRow>
          </TablixRows>
        </TablixBody>
        <TablixColumnHierarchy>
          <TablixMembers>
            <TablixMember>
              <Group Name="matrix1_fiscal_quarter">
                <GroupExpressions>
                  <GroupExpression>=Fields!fiscal_quarter.Value</GroupExpression>
                </GroupExpressions>
              </Group>
              <SortExpressions>
                <SortExpression>
                  <Value>=Fields!fiscal_quarter.Value</Value>
                </SortExpression>
              </SortExpressions>
              <TablixHeader>
                <Size>0.21in</Size>
                <CellContents>
                  <Textbox Name="Textbox5">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value>=Fields!fiscal_quarter.Value</Value>
                            <Style>
                              <FontFamily>Tahoma</FontFamily>
                              <FontWeight>Bold</FontWeight>
                              <Color>White</Color>
                            </Style>
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>Textbox5</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <BackgroundColor>#6e9eca</BackgroundColor>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixHeader>
              <TablixMembers>
                <TablixMember>
                  <Group Name="Group1">
                    <GroupExpressions>
                      <GroupExpression>=Month(Fields!forecastbookdate.Value) &amp; "/" &amp; Year(Fields!forecastbookdate.Value)</GroupExpression>
                    </GroupExpressions>
                  </Group>
                  <SortExpressions>
                    <SortExpression>
                      <Value>=Month(Fields!forecastbookdate.Value) &amp; "/" &amp; Year(Fields!forecastbookdate.Value)</Value>
                    </SortExpression>
                  </SortExpressions>
                  <TablixHeader>
                    <Size>0.25in</Size>
                    <CellContents>
                      <Textbox Name="Group1">
                        <CanGrow>true</CanGrow>
                        <KeepTogether>true</KeepTogether>
                        <Paragraphs>
                          <Paragraph>
                            <TextRuns>
                              <TextRun>
                                <Value>=Month(Fields!forecastbookdate.Value) &amp; "/" &amp; Year(Fields!forecastbookdate.Value)</Value>
                                <Style>
                                  <FontFamily>Tahoma</FontFamily>
                                  <FontWeight>Bold</FontWeight>
                                  <Color>White</Color>
                                </Style>
                              </TextRun>
                            </TextRuns>
                            <Style />
                          </Paragraph>
                        </Paragraphs>
                        <rd:DefaultName>Group1</rd:DefaultName>
                        <Style>
                          <Border>
                            <Color>LightGrey</Color>
                            <Style>Solid</Style>
                          </Border>
                          <BackgroundColor>#6e9eca</BackgroundColor>
                          <PaddingLeft>2pt</PaddingLeft>
                          <PaddingRight>2pt</PaddingRight>
                          <PaddingTop>2pt</PaddingTop>
                          <PaddingBottom>2pt</PaddingBottom>
                        </Style>
                      </Textbox>
                    </CellContents>
                  </TablixHeader>
                </TablixMember>
              </TablixMembers>
              <DataElementOutput>Output</DataElementOutput>
              <KeepTogether>true</KeepTogether>
            </TablixMember>
          </TablixMembers>
        </TablixColumnHierarchy>
        <TablixRowHierarchy>
          <TablixMembers>
            <TablixMember>
              <Group Name="matrix1_region">
                <GroupExpressions>
                  <GroupExpression>=Fields!region.Value</GroupExpression>
                </GroupExpressions>
              </Group>
              <SortExpressions>
                <SortExpression>
                  <Value>=Fields!region.Value</Value>
                </SortExpression>
              </SortExpressions>
              <TablixHeader>
                <Size>1in</Size>
                <CellContents>
                  <Textbox Name="region">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value>=Fields!region.Value</Value>
                            <Style>
                              <FontFamily>Tahoma</FontFamily>
                              <FontWeight>Bold</FontWeight>
                              <Color>White</Color>
                            </Style>
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>region</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <BackgroundColor>#6e9eca</BackgroundColor>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixHeader>
              <DataElementOutput>Output</DataElementOutput>
              <KeepTogether>true</KeepTogether>
            </TablixMember>
            <TablixMember>
              <TablixHeader>
                <Size>1in</Size>
                <CellContents>
                  <Textbox Name="Textbox9">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value>Total</Value>
                            <Style>
                              <FontFamily>Tahoma</FontFamily>
                              <FontWeight>Bold</FontWeight>
                              <Color>White</Color>
                            </Style>
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>Textbox9</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <BackgroundColor>#6e9eca</BackgroundColor>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixHeader>
              <KeepWithGroup>Before</KeepWithGroup>
            </TablixMember>
            <TablixMember>
              <TablixHeader>
                <Size>1in</Size>
                <CellContents>
                  <Textbox Name="Textbox11">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value>Quarter</Value>
                            <Style>
                              <FontFamily>Tahoma</FontFamily>
                              <FontWeight>Bold</FontWeight>
                              <Color>White</Color>
                            </Style>
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>Textbox11</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <BackgroundColor>#6e9eca</BackgroundColor>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixHeader>
              <KeepWithGroup>Before</KeepWithGroup>
            </TablixMember>
          </TablixMembers>
        </TablixRowHierarchy>
        <RepeatColumnHeaders>true</RepeatColumnHeaders>
        <RepeatRowHeaders>true</RepeatRowHeaders>
        <DataSetName>DataSet1</DataSetName>
        <Top>0.36in</Top>
        <Height>1.17in</Height>
        <Width>2in</Width>
        <Style />
      </Tablix>
      <Textbox Name="textbox1">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>Report1</Value>
                <Style>
                  <FontFamily>Tahoma</FontFamily>
                  <FontSize>20pt</FontSize>
                  <FontWeight>Bold</FontWeight>
                  <Color>SteelBlue</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style />
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>textbox1</rd:DefaultName>
        <Height>0.36in</Height>
        <Width>3.27083in</Width>
        <ZIndex>1</ZIndex>
        <Style>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
    </ReportItems>
    <Height>1.53in</Height>
    <Style />
  </Body>
  <Width>3.27083in</Width>
  <Page>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style />
  </Page>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="DataSource1">
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=xxx;Initial Catalog=xxx</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
      <rd:SecurityType>Integrated</rd:SecurityType>
      <rd:DataSourceID>xxx</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>execute store proc </CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="region">
          <DataField>region</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="PipelineStage">
          <DataField>PipelineStage</DataField>
          <rd:TypeName>System.Int16</rd:TypeName>
        </Field>
        <Field Name="forecastbookdate">
          <DataField>forecastbookdate</DataField>
          <rd:TypeName>System.DateTime</rd:TypeName>
        </Field>
        <Field Name="fiscal_quarter">
          <DataField>fiscal_quarter</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="ProjClose">
          <DataField>ProjClose</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="EstimatedValue">
          <DataField>EstimatedValue</DataField>
          <rd:TypeName>System.Double</rd:TypeName>
        </Field>
        <Field Name="Estimate">
          <DataField>Estimate</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="chance_of_winning">
          <DataField>chance_of_winning</DataField>
          <rd:TypeName>System.Double</rd:TypeName>
        </Field>
        <Field Name="project_percent">
          <DataField>project_percent</DataField>
          <rd:TypeName>System.Double</rd:TypeName>
        </Field>
        <Field Name="description">
          <DataField>description</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="companyname">
          <DataField>companyname</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="methodname">
          <DataField>methodname</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Airport_Code">
          <DataField>Airport_Code</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="CountryCode">
          <DataField>CountryCode</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="creationdate">
          <DataField>creationdate</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Partial_Booking_Amount">
          <DataField>Partial_Booking_Amount</DataField>
          <rd:TypeName>System.Decimal</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <Language>en-US</Language>
  <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>6c517904-7775-48b1-a6a4-20ae123a3e8c</rd:ReportID>
</Report>

    Thanks ,

    kkmick



    • Edited by kkmick Friday, March 02, 2012 1:57 PM
    • Edited by kkmick Friday, March 02, 2012 2:01 PM
    •  
     
  • Friday, March 02, 2012 3:39 PM
     
     
    Sign In to Vote
    0
    You want to show data or do not want to show data at quarter for 12/2012 and 11/2013 ?? Do you need this only for this 2 values ?? Kindly request you to show the desired output in an excel so that we can suggest you a solution ..In the mean time try the above expression to hide duplicates.

    Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

    Dont forget to mark it as Answered if found useful | myspeakonbi

     
  • Friday, March 02, 2012 6:48 PM
     
     
    Sign In to Vote
    0

    Hi Rakesh:

    This is what I want to see. 

    Thanks,

    kkmick

     
  • Saturday, March 03, 2012 2:31 AM
     
      Has Code
    Sign In to Vote
    0

    Kkmick,

    Kindly use the expression below in your Quarter Row details..In the value expression paste the below code.Not in visibility or anywhere else

    =IIF(RunningValue(Count(Fields!forecastbookdate.Value), COUNTDISTINCT, "matrix1_fiscal_quarter")=CountDistinct(Fields!forecastbookdate.Value,"matrix1_fiscal_quarter"),Fields!project_percent.Value,nothing)

    Let me know if it works...

    Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

    Dont forget to mark it as Answered if found useful | myspeakonbi

     
  • Monday, March 05, 2012 3:11 PM
     
     
    Sign In to Vote
    0

    Rakesh:

    Thanks for your input again.  I am using the formula that you showed me.  Let me know if I am missing anything. 

    =iif(RunningValue(Count(Fields!forecastbookdate.Value) ,

    COUNTDISTINCT, "matrix1_fiscal_quarter")= COUNTDISTINCT(Fields!forecastbookdate.Value,"matrix1_fiscal_quarter"),Fields!project_percent.Value,nothing)   This is what i got.  Here is a picture of it. 

     
  • Monday, March 05, 2012 3:16 PM
     
     
    Sign In to Vote
    0

    I was having problem to upload the picture in the previous response.    It still needs some adjusting to the formula.   If you look at 4cy2013.  It should contain the value 72133.98.25.  I am not where the 55341.9875 .  3y2013 is correct.  But for 3cy2012.  There is no  value at all. 

    thanks,

    kkmick


    • Edited by kkmick Monday, March 05, 2012 3:17 PM
    •  
     
  • Tuesday, March 06, 2012 4:24 AM
     
     
    Sign In to Vote
    0

    Hi kkmick,

    The formula which you have used is fine. I am assuming 2 things ..

    1.Fields!forecastbookdate.Value is the month level which is below the Quarter Level.

    2.Fields!project_percent.Value is the measure in your detail.

    3.Kindly also remove "Sort" in your quarter Group and check it again.

    4. I have tested the same using adventure works and the results are as expected. Let me know your RDL has the same design as above.

    Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

    Dont forget to mark it as Answered if found useful | myspeakonbi

     
  • Tuesday, March 06, 2012 2:08 PM
     
      Has Code
    Sign In to Vote
    0

    Hi Rakesh:

    Here are the images and rdl.   It will show you I have the same thing that you had.  My formula is at the detail section.  This is the result that I got. 

    Thanks for your help again.

    <?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <Body>
    <ReportItems>
      <Tablix Name="matrix1">
        <TablixCorner>
          <TablixCornerRows>
            <TablixCornerRow>
              <TablixCornerCell>
                <CellContents>
                  <Textbox Name="textbox3">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value />
                            <Style>
                              <FontFamily>Tahoma</FontFamily>
                            </Style>
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>textbox3</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixCornerCell>
            </TablixCornerRow>
            <TablixCornerRow>
              <TablixCornerCell>
                <CellContents>
                  <Textbox Name="Textbox4">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value />
                            <Style>
                              <FontFamily>Tahoma</FontFamily>
                            </Style>
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>Textbox4</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixCornerCell>
            </TablixCornerRow>
          </TablixCornerRows>
        </TablixCorner>
        <TablixBody>
          <TablixColumns>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
          </TablixColumns>
          <TablixRows>
            <TablixRow>
              <Height>0.21in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="textbox2">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Sum(Fields!project_percent.Value)</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>textbox2</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                  <DataElementOutput>Output</DataElementOutput>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox10">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Sum(Fields!project_percent.Value)</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox10</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                  <DataElementOutput>Output</DataElementOutput>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox12">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=iif(RunningValue(Count(Fields!forecastbookdate.Value) , COUNTDISTINCT, "matrix1_fiscal_quarter")= COUNTDISTINCT(Fields!forecastbookdate.Value,"matrix1_fiscal_quarter"),Fields!project_percent.Value,nothing)</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox12</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                  <DataElementOutput>Output</DataElementOutput>
                </TablixCell>
              </TablixCells>
            </TablixRow>
          </TablixRows>
        </TablixBody>
        <TablixColumnHierarchy>
          <TablixMembers>
            <TablixMember>
              <Group Name="matrix1_fiscal_quarter">
                <GroupExpressions>
                  <GroupExpression>=Fields!fiscal_quarter.Value</GroupExpression>
                </GroupExpressions>
              </Group>
              <SortExpressions>
                <SortExpression>
                  <Value>=Fields!fiscal_quarter.Value</Value>
                </SortExpression>
              </SortExpressions>
              <TablixHeader>
                <Size>0.21in</Size>
                <CellContents>
                  <Textbox Name="Textbox5">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value>=Fields!fiscal_quarter.Value</Value>
                            <Style>
                              <FontFamily>Tahoma</FontFamily>
                              <FontWeight>Bold</FontWeight>
                              <Color>White</Color>
                            </Style>
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>Textbox5</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <BackgroundColor>#6e9eca</BackgroundColor>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixHeader>
              <TablixMembers>
                <TablixMember>
                  <Group Name="Group1">
                    <GroupExpressions>
                      <GroupExpression>=Month(Fields!forecastbookdate.Value) &amp; "/" &amp; Year(Fields!forecastbookdate.Value)</GroupExpression>
                    </GroupExpressions>
                  </Group>
                  <SortExpressions>
                    <SortExpression>
                      <Value>=Month(Fields!forecastbookdate.Value) &amp; "/" &amp; Year(Fields!forecastbookdate.Value)</Value>
                    </SortExpression>
                  </SortExpressions>
                  <TablixHeader>
                    <Size>0.25in</Size>
                    <CellContents>
                      <Textbox Name="Group1">
                        <CanGrow>true</CanGrow>
                        <KeepTogether>true</KeepTogether>
                        <Paragraphs>
                          <Paragraph>
                            <TextRuns>
                              <TextRun>
                                <Value>=Month(Fields!forecastbookdate.Value) &amp; "/" &amp; Year(Fields!forecastbookdate.Value)</Value>
                                <Style>
                                  <FontFamily>Tahoma</FontFamily>
                                  <FontWeight>Bold</FontWeight>
                                  <Color>White</Color>
                                </Style>
                              </TextRun>
                            </TextRuns>
                            <Style />
                          </Paragraph>
                        </Paragraphs>
                        <rd:DefaultName>Group1</rd:DefaultName>
                        <Style>
                          <Border>
                            <Color>LightGrey</Color>
                            <Style>Solid</Style>
                          </Border>
                          <BackgroundColor>#6e9eca</BackgroundColor>
                          <PaddingLeft>2pt</PaddingLeft>
                          <PaddingRight>2pt</PaddingRight>
                          <PaddingTop>2pt</PaddingTop>
                          <PaddingBottom>2pt</PaddingBottom>
                        </Style>
                      </Textbox>
                    </CellContents>
                  </TablixHeader>
                </TablixMember>
              </TablixMembers>
              <DataElementOutput>Output</DataElementOutput>
              <KeepTogether>true</KeepTogether>
            </TablixMember>
          </TablixMembers>
        </TablixColumnHierarchy>
        <TablixRowHierarchy>
          <TablixMembers>
            <TablixMember>
              <Group Name="matrix1_region">
                <GroupExpressions>
                  <GroupExpression>=Fields!region.Value</GroupExpression>
                </GroupExpressions>
              </Group>
              <SortExpressions>
                <SortExpression>
                  <Value>=Fields!region.Value</Value>
                </SortExpression>
              </SortExpressions>
              <TablixHeader>
                <Size>1in</Size>
                <CellContents>
                  <Textbox Name="region">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value>=Fields!region.Value</Value>
                            <Style>
                              <FontFamily>Tahoma</FontFamily>
                              <FontWeight>Bold</FontWeight>
                              <Color>White</Color>
                            </Style>
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>region</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <BackgroundColor>#6e9eca</BackgroundColor>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixHeader>
              <DataElementOutput>Output</DataElementOutput>
              <KeepTogether>true</KeepTogether>
            </TablixMember>
            <TablixMember>
              <TablixHeader>
                <Size>1in</Size>
                <CellContents>
                  <Textbox Name="Textbox9">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value>Total</Value>
                            <Style>
                              <FontFamily>Tahoma</FontFamily>
                              <FontWeight>Bold</FontWeight>
                              <Color>White</Color>
                            </Style>
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>Textbox9</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <BackgroundColor>#6e9eca</BackgroundColor>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixHeader>
              <KeepWithGroup>Before</KeepWithGroup>
            </TablixMember>
            <TablixMember>
              <TablixHeader>
                <Size>1in</Size>
                <CellContents>
                  <Textbox Name="Textbox11">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value>Quarter</Value>
                            <Style>
                              <FontFamily>Tahoma</FontFamily>
                              <FontWeight>Bold</FontWeight>
                              <Color>White</Color>
                            </Style>
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>Textbox11</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <BackgroundColor>#6e9eca</BackgroundColor>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixHeader>
              <KeepWithGroup>Before</KeepWithGroup>
            </TablixMember>
          </TablixMembers>
        </TablixRowHierarchy>
        <RepeatColumnHeaders>true</RepeatColumnHeaders>
        <RepeatRowHeaders>true</RepeatRowHeaders>
        <DataSetName>DataSet1</DataSetName>
        <Top>0.36in</Top>
        <Height>1.17in</Height>
        <Width>2in</Width>
        <Style />
      </Tablix>
      <Textbox Name="textbox1">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>Report1</Value>
                <Style>
                  <FontFamily>Tahoma</FontFamily>
                  <FontSize>20pt</FontSize>
                  <FontWeight>Bold</FontWeight>
                  <Color>SteelBlue</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style />
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>textbox1</rd:DefaultName>
        <Height>0.36in</Height>
        <Width>3.27083in</Width>
        <ZIndex>1</ZIndex>
        <Style>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
    </ReportItems>
    <Height>1.53in</Height>
    <Style />
  </Body>
  <Width>3.27083in</Width>
  <Page>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style />
  </Page>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="DataSource1">
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=**Database**;Initial Catalog=**DatabaseName**</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
      <rd:SecurityType>Integrated</rd:SecurityType>
      <rd:DataSourceID>0248b4b4-f2dd-4029-9ae8-9ac5b1c35ac4</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>**StoreProcedure**</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="region">
          <DataField>region</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="PipelineStage">
          <DataField>PipelineStage</DataField>
          <rd:TypeName>System.Int16</rd:TypeName>
        </Field>
        <Field Name="forecastbookdate">
          <DataField>forecastbookdate</DataField>
          <rd:TypeName>System.DateTime</rd:TypeName>
        </Field>
        <Field Name="fiscal_quarter">
          <DataField>fiscal_quarter</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="ProjClose">
          <DataField>ProjClose</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="EstimatedValue">
          <DataField>EstimatedValue</DataField>
          <rd:TypeName>System.Double</rd:TypeName>
        </Field>
        <Field Name="Estimate">
          <DataField>Estimate</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="chance_of_winning">
          <DataField>chance_of_winning</DataField>
          <rd:TypeName>System.Double</rd:TypeName>
        </Field>
        <Field Name="project_percent">
          <DataField>project_percent</DataField>
          <rd:TypeName>System.Double</rd:TypeName>
        </Field>
        <Field Name="description">
          <DataField>description</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="companyname">
          <DataField>companyname</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="methodname">
          <DataField>methodname</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Airport_Code">
          <DataField>Airport_Code</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="CountryCode">
          <DataField>CountryCode</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="creationdate">
          <DataField>creationdate</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Partial_Booking_Amount">
          <DataField>Partial_Booking_Amount</DataField>
          <rd:TypeName>System.Decimal</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <Language>en-US</Language>
  <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>6c517904-7775-48b1-a6a4-20ae123a3e8c</rd:ReportID>
</Report>

     
  • Wednesday, March 07, 2012 6:27 AM
     
      Has Code
    Sign In to Vote
    0

    Can you kindly create a calculated member with the below expression  and Name it as "MonthValue"

    =Month(Fields!forecastbookdate.Value) & "/" & Year(Fields!forecastbookdate.Value)

    Later in the detail expression replace "Fields!forecastbookdate.Value" with "Fields!MonthValue.Value" .Let me know if it works.


    Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

    Dont forget to mark it as Answered if found useful | myspeakonbi

     
  • Wednesday, March 07, 2012 2:11 PM
     
     
    Sign In to Vote
    0

    Rakesh:

    I don't think it is possible to create a calcuated member.  The is on our old sql server 2000.  And I do not see Analysis server was installed on it. 

    kkmick

     
  • Wednesday, March 07, 2012 3:20 PM
     
     
    Sign In to Vote
    0

    i meant not to create a calculated member in cubes..i meant to create it in rdl under dataset.

    Right click on the dataset and add calculated member.

    Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

    Dont forget to mark it as Answered if found useful | myspeakonbi

     
  • Thursday, March 08, 2012 6:09 PM
     
     
    Sign In to Vote
    0

    Hi Rakesh:

    This is what I got.  After inserting the MonthValue. It is summing the value.   But you see how it is duplicating the values.   I would like  12/2012 has the 266... values. 

    I replaced the MonthValue formula on the header (10/2012 line) and Quarter line detail information. 

    Quarter Detail Line Formula:

    =iif(RunningValue(Count(Fields!MonthValue.Value) , COUNTDISTINCT, "matrix1_fiscal_quarter")=1 or RunningValue(Count(Fields!MonthValue.Value) , COUNTDISTINCT, "matrix1_fiscal_quarter")=2 or RunningValue(Count(Fields!MonthValue.Value) , COUNTDISTINCT, "matrix1_fiscal_quarter")=3 or RunningValue(Count(Fields!MonthValue.Value) , COUNTDISTINCT, "matrix1_fiscal_quarter")=4,  Sum(Fields!project_percent.Value,"matrix1_fiscal_quarter"),Sum(Fields!project_percent.Value,"matrix1_fiscal_quarter"))

    Thanks for your help again. 

    kkmick

     
  • Friday, March 09, 2012 9:03 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi kkmick,

    Kindly use the below formula in Quarter Line detail formula :

    =IIF(RunningValue(Count(Fields!MonthValue.Value), COUNTDISTINCT, "matrix1_fiscal_quarter")=CountDistinct(Fields!MonthValue.Value,"matrix1_fiscal_quarter"),Sum(Fields!project_percent.Value,"matrix1_fiscal_quarter"),nothing)

    If the problem still persists send the .rdl to rakesh.mandyajayaram@hotmail.com

    i am intrested to look into the issue and aslo kindly attach the sample data in an excel file i will revert to on the same

    Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

    Dont forget to mark it as Answered if found useful | myspeakonbi


     
  • Friday, March 09, 2012 1:41 PM
     
     
    Sign In to Vote
    0

    Hi Rakesh:

     

    Thank you so much for all of your time and effort.  It did work.  Do you think you can explain to me on how the formula reads?  Just want to understand it.  I am still very new to ssrs. 

     

    Thanks,

    kkmick