Post reply

Data set query keeps changing?

  • March 30, 2011 at 12:29 am

    #248804

    Hi guys,

    i am using SSRS 2008.

    When i have more than one dataset and I change one, the other one changes as well and I have to go back and change the first one before running the report.

    Anyone know why and how I can fix it.

    Example

    I have two data sets. one to bring back time periods for a parameter, the other to bring back data to populate the report.

    //reporting month

    WITH MEMBER [Measures].[ParameterCaption]

    AS [REPORTING DATE].[REPORTING MONTH].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue]

    AS [REPORTING DATE].[REPORTING MONTH].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel]

    AS [REPORTING DATE].[REPORTING MONTH].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , FILTER([REPORTING DATE].[REPORTING MONTH].[MONTH].MEMBERS, [Measures].[REPRICEGAP] <> 0) ON ROWS

    FROM [NI]

    --Data query

    ...

    SELECT

    {[Measures].[REPRICEGAP], [Measures].[NON RATE]} ON COLUMNS,

    {[Periods]} * {[LineItems]} ON ROWS

    FROM

    [NI]

    WHERE

    (

    [NI SOURCE].[SOURCE].[NIALLPRD],

    [SCENARIO].[SCENARIO].&[ BASE: 000],

    [PRODUCT].[ENTITY].&[Regulated 3010],

    {

    [RUN].[RUN NAME].&[NI01],

    [RUN].[RUN NAME].&[NI02],

    [RUN].[RUN NAME].&[NI03],

    [RUN].[RUN NAME].&[NI04],

    [RUN].[RUN NAME].&[NI34],

    [RUN].[RUN NAME].&[NI32]

    },

    STRTOMEMBER(@ReportingMonth)

    , STRTOSET(@Product)

    if I preview data at this point, its fine. Queries havent changed.

    As soon as I comment out the last line in the data query or make any changes to it and preview the report, the reporting period query changes to

    WITH MEMBER [Measures].[ParameterCaption] AS [REPORTING DATE].[REPORTING MONTH].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue]

    AS [REPORTING DATE].[REPORTING MONTH].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel]

    AS [REPORTING DATE].[REPORTING MONTH].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [REPORTING DATE].[REPORTING MONTH].ALLMEMBERS ON ROWS

    FROM [NI]

    Any ideas why? How can I fix it?

    it is so irritatating. The queries have nothng to do with each other.

    Thanks guys

    Ian

    Ian Cockcroft
    MCITP BI Specialist

  • March 30, 2011 at 2:26 am

    #1305291

    Mods, please delete this post, i have moved it.

    ta

    Ian Cockcroft
    MCITP BI Specialist

  • March 30, 2011 at 2:44 am

    #1305298

    No replies to this thread please. Direct replies to http://qa.sqlservercentral.com/Forums/Topic1086036-1633-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply