Rowss Are Not Returned Comnpletely..HELP NEEDED/..URGENT..
-
Hello...
There is a lill prob that m facing wid the sql server reporting services..
In my MDX m using 5 dimensions including a Time Dimension..
I have passed 2 report parameters
1) Year Parameter..This takes in a particular year...
2) Dimensions Parameter..This takes a dimension (the remaining 4 outta 5) and displays the result of that dimension for the selected year(Argument 1)..
Now when i select the year lets say '2003' and select any dimension the result returned is OK but for ONE PARTICULAR dimension only 1 row is displayed outta 12 rows..But the data fr that shown row is accurate...
Now can anyone help me out...why is only one row being returned...
wat mite be the prob...
Any Ideas...
M stuck wid this prob fr 10 dayss or so...
HELP!!!!!!
-
Have u considered re-designing the cube? plz describe out the cube's description as i am unable to understand it...
wud try to help then... -
Yeah I have Tried Redesigning THE CUBE...
well the cubezz fine coz the data fr other dimensions is displayed correctly and even fr this Particular Dimension...the prob i face is that only one row is shown outta 12...
-
Can you post the query string that you're using? ie the dynamic MDX with parms included.
Steve.
-
="WITH MEMBER Measures.[Escrow Advance Average Balance Percent] AS '((["& Parameters!pDimension.Value &"].CURRENTMEMBER, Measures.[Escrow Advance Average Balance])/(["& Parameters!pDimension.Value &"].CURRENTMEMBER.PARENT, Measures.[Escrow Advance Average Balance]))*100', FORMAT_STRING = 'Percent' MEMBER Measures.[Escrow Advance Costs Percent] AS '((["& Parameters!pDimension.Value &"].CURRENTMEMBER, Measures.[Escrow Advance Costs])/(["& Parameters!pDimension.Value &"].CURRENTMEMBER.PARENT, Measures.[Escrow Advance Costs])) * 100 ',FORMAT_STRING = 'Percent'MEMBER Measures.[Percent Escrow Advance Active Loans] AS '((["& Parameters!pDimension.Value &"].CURRENTMEMBER,Measures.[Loans With Escrow Advance])/(["& Parameters!pDimension.Value &"].CURRENTMEMBER, Measures.[Entire Portfolio Loan Count])) * 100 ',FORMAT_STRING = 'Percent'Select{[Measures].[Escrow Advance Average Balance],[Measures].[Escrow Advance Costs],[Measures].[Entire Portfolio Loan Count],[Measures].[Loans With Escrow Advance],[Measures].[Escrow Advance Average Balance Percent],[Measures].[Escrow Advance Costs Percent],[Measures].[Percent Escrow Advance Active Loans]}ON COLUMNS,NonEmptyCrossJoin(NonEmptyCrossJoin(NonEmptyCrossJoin(NonEmptyCrossJoin({Descendants([TimeDimension].["& Parameters!YearParameter.Value &"],[TimeDimension].[Year Name],LEAVES)},[LoanTypeDimension]."& IIF(Parameters!pDimension.Value ="LoanTypeDimension","Children","members") &"),[InvestorDimension]."& IIF(Parameters!pDimension.Value ="InvestorDimension","Children","members") &"),[StateDimension]."& IIF(Parameters!pDimension.Value="StateDimension","Children","members") &"),[ChannelDimension]."& IIF(Parameters!pDimension.Value ="ChannelDimension","Children","members") &") ON ROWS FROM EscrowAdvancesCube"
PS: Problem is in the State Dimension..
I am calculating three measures here starting whit "WITH MEMBER" and "MEMBER"..
-
As a first run answer I'd say that the combination of the dimensions being crossjoined is excluding your records. Have you run up the possible queries (in say MDX sample app) using nonemptycrossjoins in the *same* order? Because you're specifying the order in which these are to be resolved, an "earlier" NECJ (non empty cross join) may eliminate records that would normally be included.
Have you run this query up in the MDX Sample App but replacing the parameters as appropriate and setting the year to different values (ie 2000, 2001, 2002, 2003 etc)?
Just out of interest, you know that you can supply >2 sets to the NECJ function?
Steve.
-
yeah steve i tried the same combination by providing values fr year as u mentioned...
But
I dint know abt NECJ can have more than 2 sets...
watss itss syntax...
Can u help me in dat...maybe dat will solve the probb...
Lookin forward...
-
NonEmptyCrossjoin(«Set1»[, «Set2»...][, «Crossjoin Set Count»])
Remarks
The NonEmptyCrossjoin function returns the cross product of two or more sets as a set, excluding empty tuples or tuples without data supplied by underlying fact tables; because of this, all calculated members are automatically excluded. If «Crossjoin Set Count» is not specified, all specified sets are crossjoined and empty members are excluded from the resulting set. If «Crossjoin Set Count» is specified, the number of sets specified in «Crossjoin Set Count», starting with «Set1», are crossjoined. The remaining sets are used to determine, in the resulting crossjoined set, which members are considered nonempty.
All of the above is from BOL, which has a reasonable description/example of usage.
What was the result when you ran the query in the MDX sample app? 1 row or 12?
Steve.
-
The result frm Sample App...was 12...
Thnx for the NECJ syntax thingie...
I m new actually to this MDX queries and dont reallt know alot...
-
So using the query below, and changing the year to the specified year(I set it to 2003), the sample app returns the correct row count but SSRS doesn't?
WITH
MEMBER Measures
.[Escrow Advance Average Balance Percent] AS '(([StateDimension].CURRENTMEMBER, Measures.[Escrow Advance Average Balance])/([StateDimension].CURRENTMEMBER.PARENT, Measures.[Escrow Advance Average Balance]))*100', FORMAT_STRING = 'Percent'
MEMBER Measures
.[Escrow Advance Costs Percent] AS '(([StateDimension].CURRENTMEMBER, Measures.[Escrow Advance Costs])/([StateDimension].CURRENTMEMBER.PARENT, Measures.[Escrow Advance Costs])) * 100 ',FORMAT_STRING = 'Percent'
MEMBER Measures
.[Percent Escrow Advance Active Loans] AS '(([StateDimension].CURRENTMEMBER,Measures.[Loans With Escrow Advance])/([StateDimension].CURRENTMEMBER, Measures.[Entire Portfolio Loan Count])) * 100 ',FORMAT_STRING = 'Percent'
SELECT
{[Measures].[Escrow Advance Average Balance],[Measures].[Escrow Advance Costs],[Measures].[Entire Portfolio Loan Count],[Measures].[Loans With Escrow Advance],[Measures].[Escrow Advance Average Balance Percent],[Measures].[Escrow Advance Costs Percent],[Measures].[Percent Escrow Advance Active Loans]}ON COLUMNS,
NonEmptyCrossJoin
(NonEmptyCrossJoin(NonEmptyCrossJoin(NonEmptyCrossJoin({Descendants([TimeDimension].[2003],[TimeDimension].[Year Name],LEAVES)},[LoanTypeDimension].MEMBERS),[InvestorDimension].MEMBERS),[StateDimension].CHILDREN),[ChannelDimension].MEMBERS) ON ROWS
FROM
[EscrowAdvancesCube]
Steve.
-
Yeah this query returned my desired result...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply