Avatar of Leo Torres
Leo Torres
Flag 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
SSRSMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Leo Torres

8/22/2022 - Mon
Rainer Jeschor

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
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
Leo Torres

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Leo Torres

ASKER
I've requested that this question be deleted for the following reason:

Got the answer my self
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23