Link to home
Start Free TrialLog in
Avatar of Leo Torres
Leo TorresFlag for United States of America

asked on

SSRS Filtering data incorrectly

Not sure whats the best way to explain this be here we go.

OK here are the facts
chart diagram, January 2011 has a Correct value of 20.733(verified inside the query Designer)

My Issue is if I select a range of January 2011 thru June 2011 on the chart my January 2011 value is 14.66 when it should be 20.733

If I select July 2010 thru June 2011 then I get 20.733 value on my chart for January 2011.

Little FYI on these numbers these numbers represent and average for the past 6 months. The 20.733 is the average for January 2011 and 6 Months back.

The 14.66 represents the average for only January 2011 in order to get the correct January 2011 number I have to start 6Months before January 2011.


I want graph to display correct January 2011 of 20.733 if i pick that as the starting point.

I hope this makes sense I have attached images below to help understand... This is happening to first 6 months of report I need to correct issue
EE1.png
EE2.png
Avatar of Rainer Jeschor
Rainer Jeschor
Flag of Germany image

Hi,
could you post / attach the query you used?

Without knowing the details, but you have to get the records for your selected start and end date + the 6 months before as base data and then display only the selected date range.
Sample:
SELECT
   TBL.*,
  CASE WHEN YourDateCol < @SelectedStartDate THEN 0 ELSE 1 END AS [ShowRecord]
 FROM youTable TBL
WHERE YourDateCol >= DATEADD(m,-6,@SelectedStartDate) AND YourDateCol < @SelectedEndDate

Now you have the base records to have a correct calculation and you can show all records which have [ShowRecord] = 1

HTH
Rainer
Avatar of Leo Torres

ASKER

Query Used..

I dont see how it can be query..

The value in the red selection have been verified
Average6 Month value is what is showing up as 14.66 when you can see value 20.73


WITH


  MEMBER [Measures].[Last6Months] AS 
    Sum
    ( { LastPeriods (6 ,[Time].[Date Tree].currentmember )  }
     ,[Measures].[Defect Count]
    ) 
    
     
    Member [Measures].[Average6Months] as (
    [Measures].[Last6Months] / [Measures].[Last6MonthDistinct])
    
    
SELECT    
  {
    [Measures].[Last6Months]
    ,[Measures].[Defect Count]
    ,[Measures].[Last6MonthDistinct]
    ,[Measures].[Average6Months]

   
  } ON COLUMNS

 // strtomember( @StartMOntYear) : strtomember( @EndMOntYear) on rows
  
  ,Descendants
  (
    [Time].[Date Tree].[Year Int]
   ,[Time].[Date Tree].Month
   ,self
  ) ON ROWS
  
FROM ( 
     SELECT ( STRTOMEMBER(@FromTimeDateTree, CONSTRAINED) : STRTOMEMBER(@ToTimeDateTree, CONSTRAINED) ) ON COLUMNS 
       FROM [QA Square1 Quality Control] ) 

Open in new window

MDXQuery.jpg
ASKER CERTIFIED SOLUTION
Avatar of Leo Torres
Leo Torres
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I've requested that this question be deleted for the following reason:

Got the answer my self