How to create a sql report using Matrix control run time dynamically (not a pre build report), I mean using the RDL to build xml,
I have seen a sample for Table control but not for matrix control , becoz I want display the Product dimension with measures. as follows,

Description M1 M2 M3
+All Product xxxx xxxxx xxxxxxx
and when u click on (+All product) it will drill down
+Drink xxxx xxx xxxxxxxxx
+Food xxxx xxx xxxxxxxxx
+Non-Consumerble.....etc.

any help would be much apprecited!!!!!
and is it possible to use more than one dimension for report, I mean multiple dimension.

Thanks.

What you described below is really a table report. Matrix reports allow you
to expand the members on teh rows and the columns, but you're only expanding
the rows.

See
http://msdn.microsoft.com/library/de...lapasandrs.asp
for how to build reports using Analysis Services as a data source.

-Sean

Sean Boon
Microsoft Office BI


"Summa" <Summa (AT) discussions (DOT) microsoft.com> wrote

Hi,

Thanks for the reply,
Your correct, here I forget to mention the Columns of members, suppose I have all rows and columns in my Matrix for the Product dimension,
here is MDX query I used,

SELECT
{ [Measures].Members} on Columns,
{ [Product].Members } on rows
FROM SALES

but my problem is when u run this query in Sql Report DATA Tab , there is no "All Products" in "Prodcut Family" column in the query result pane.
So in my report I am getting a Blank cell for All products, then I managed get it done using this ,

=IIf(IsNothing(Fields!Product_Product_Family.Value ), "All Products", Fields!Product_Product_Family.Value)

formula to get "All Product " text on that particular cell , but I want my report to work like this, as u run the report I want only the All product level to be shown,

I mean , I want first level as "All Products" , report preview as eg

Unit Sale Store Cost Store Sales sales Count Store Sales Net
+All Product xxxx xxxxx xxxxxxx xxxxxxx xxxxxx

then when u click on All Products other levels will be displayed as appropriate.

I saw the Food Sales sample but it has used (Combo Box ) Filter for Prodcut Family ( Drink ,Food, Non- Consumable) level and it dose not have a Level "All Products" in the combo box and report is consist with Prduct Department Level to Brand Name Level , But In my case I want all the levels in my report.
(Family level, Department , Category , Sub Category, Brand Name and Product Name Level)








"Sean Boon [MS]" <seanboon (AT) online (DOT) microsoft.com> wrote

What you described below is really a table report. Matrix reports allow you
to expand the members on teh rows and the columns, but you're only expanding
the rows.

See
http://msdn.microsoft.com/library/de...lapasandrs.asp
for how to build reports using Analysis Services as a data source.

-Sean

Sean Boon
Microsoft Office BI


"Summa" <Summa (AT) discussions (DOT) microsoft.com> wrote