Hi,
I'm currently using AS 2005 and Reporting Services.
I have some results of a query split into age buckets (0-90 days, 91-180 days, etc). I would like to display the buckets, whether there are results or not as I am using them in the column . If I do not include "Non Empty", it returns a huge number of records because there is a cross join with another dimension which does not need to include all records.
For example, here is my query:
SELECT NON EMPTY { [Measures].[To Bill Amount] } ON COLUMNS,
NON EMPTY { (
[Product].[Product Name].[Product Name].ALLMEMBERS*
[Transaction Date].[Age Buckets].[Age Buckets].ALLMEMBERS ) }
ON ROWS
FROM ( SELECT ( { [Client].[Client Key].&[2] } ) ON COLUMNS
FROM [Cube])
WHERE ( [Client].[Client Key].&[2])
So, I would like to include all [Transaction Date].[Age Buckets].[Age Buckets] but only the Products that fall under the Client Key. Another thing to note is that Reporting Services seems to only allow measures on the Column axis.
Any help would be great.
Thanks.
Hi Dear
In SSRS try to make MDX in Such a way that result should look like Report output. By doing that U will save the design time as well as Report level calculation, I hope by doing this your report will be little bit faster. And see the functions like STRTOMEMBER, STRTOSET etc. You can use here Peramaters also. Using peramaters you will add a lot values in your reports. In your example I donnot know excatly how data U R storing in Age bukets. If there is only Age then make Calculated Members Or if there is age bucket in the desired format like [0-90 Days] etc. then use only U needed Buckets. Hope U will get some help.
WITH
MEMBER [Transaction Date].[Age Buckets].[0-90 days] AS
CASE WHEN
[Transaction Date].[Age Buckets].[Age Buckets] <= 90
THEN [Transaction Date].[Age Buckets].[Age Buckets]
ELSE NULL END
MEMBER [Transaction Date].[Age Buckets].[91-180 days] AS
CASE WHEN
[Transaction Date].[Age Buckets].[Age Buckets] > 90
AND [Transaction Date].[Age Buckets].[Age Buckets] <= 180
THEN [Transaction Date].[Age Buckets].[Age Buckets]
ELSE NULL END
SELECT
NON EMPTY
{[Measures].[To Bill Amount]} ON COLUMNS,
{
(
{[Product].[Product Name].[Product Name]}
*
{
[Transaction Date].[Age Buckets].[0-90 days],
[Transaction Date].[Age Buckets].[91-180 days]
}
)
}
ON ROWS
FROM (SELECT({[Client].[Client Key].&[2]}) ON COLUMNS
FROM [Cube])
WHERE ( [Client].[Client Key].&[2])
sql