March 30, 2011 at 12:29 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 30, 2011 at 2:26 am
Mods, please delete this post, i have moved it.
ta
Ian Cockcroft
MCITP BI Specialist
March 30, 2011 at 2:44 am
No replies to this thread please. Direct replies to http://www.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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply