March 30, 2011 at 2:24 am
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 31, 2011 at 12:37 am
It looks like it's removing the filter part of the query. Is this MDX? I'm not familiar with that, but I'll take a shot in the dark:
- Are you using the query designer? Try changing it to the plain text view and see if it makes any difference.
- I also noticed in another thread (http://www.sqlservercentral.com/Forums/Topic425919-17-1.aspx#bm427459) someone had to change the names of the DataSets (adding a parm prefix) to "stop RS from messing with them").
March 31, 2011 at 3:02 am
Thanks. did some more searching. It looks like if you can suppress this by MANUALLY ADDING in the source code: <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
seems to be working properly now.
Ian Cockcroft
MCITP BI Specialist
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply