Thanks
JWWe don't document the tables structure for the RS catalog. If you want to interract with the report server, you should use the SOAP APIs.
Thanks
Tudor
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])
sqlHi,
I have a fact table with 2 columns value A and value B
In Analysis server I add a calculated column C as A / B
Now my data looks like this
A B C
0 10 0
5 10 0.5
If you add totals in Analysis server you get
5 20 and 0.25 which is correct
If you add totals in Reporting server you get
5 20 and 0.5 which is not correct.
How can I fix this ?
Thanks in advance
Constantijn
If you are using a table report, in your group footer you could add an expression for C that equates to
=sum(Fields!A.value)/sum(Fields!B.Value)
|||Or, assuming that the report uses the SSAS cube, instead of using SUM use Aggregate as explained here.
|||Teo,
Many thanks for that tip - I had totally missed that little trick.
Will
|||Thanks, I wasn't aware of the that functionHi,
I have a fact table with 2 columns value A and value B
In Analysis server I add a calculated column C as A / B
Now my data looks like this
A B C
0 10 0
5 10 0.5
If you add totals in Analysis server you get
5 20 and 0.25 which is correct
If you add totals in Reporting server you get
5 20 and 0.5 which is not correct.
How can I fix this ?
Thanks in advance
Constantijn
If you are using a table report, in your group footer you could add an expression for C that equates to
=sum(Fields!A.value)/sum(Fields!B.Value)
|||Or, assuming that the report uses the SSAS cube, instead of using SUM use Aggregate as explained here.
|||Teo,
Many thanks for that tip - I had totally missed that little trick.
Will
|||Thanks, I wasn't aware of the that function