TOPN as a Parameter-MDX
Hi All,
I have a requirement TOPN as a parameter. The following is the query from Cube.
Select [Measures].[Invoice Line Amount] On Columns,
TopCount([NQ_Supplier_AP].[Supplier Number].[Supplier Number].ALLMembers
, 1000
,[Measures].[Invoice Line Amount]) On Rows
From [TopSuppliersList]
Any suggestions would be appreciated.
Thanks
Tarak
May 5th, 2011 1:21pm
Hi Tarakq,
You can just replace the 1000 in your query with a parameter which the users can select. This will effectively give you the TopN functionality you are after.
Boyan Penev --- http://www.bp-msbi.com
Free Windows Admin Tool Kit Click here and download it now
May 6th, 2011 12:08am
If you change the above query to an expression by clicking the fX button, add a text parameter named TopCount
="Select [Measures].[Invoice Line Amount] On Columns," &
" TopCount([NQ_Supplier_AP].[Supplier Number].[Supplier Number].ALLMembers," &
Parameters!TopCount.Value &
" ,[Measures].[Invoice Line Amount]) On Rows" &
" From [TopSuppliersList]"
SSRS will have a problem getting the columns, but if you have the query w/o the dynamic param already added to the report, you'll be good to go.
May 6th, 2011 12:22am
Hi rparge,
Thanks for the response. You want me to create a new data set with the query what you mentioned and create a text parameter for TOP N.
"SSRS will have a problem getting the columns, but if you have the query w/o the dynamic param already added to the report, you'll be good to go."
I did not understand this. Can you elaborate more on it.
I appreciate your help on this issue.
Thanks
Tarak,
Free Windows Admin Tool Kit Click here and download it now
May 6th, 2011 10:57am
Sure, if the dataset is already created, you'll be OK, but when you use dynamic SQL (MDX) like this, you will get a warning message (in BIDS) or nothing (in Report Designer) that the columns cannot be created.
That's OK if you already have the dataset in the report. If not, drop the complete MDX in the designer first to get the columns added to your dataset, then turn the MDX into dynamic MDX.
If you need to add more columns later, you can do that manually in the data set properties, or do the above again.
May 6th, 2011 11:23am
No need to do any of this. All you have to do is create one parameter (say TopParam), which has a set of values - e.g. 1, 5, 10 and then replace only the 1000 in your TopCount MDX directly in your dataset with this parameter. Effectively, you get an MD expression
like:
Select [Measures].[Invoice Line Amount] On Columns,
TopCount([NQ_Supplier_AP].[Supplier Number].[Supplier Number].ALLMembers
, @TopParam
,[Measures].[Invoice Line Amount]) On Rows
From [TopSuppliersList]
In order for this to execute and find the metadata you need to go on the top of the query window in SSRS and click on the parameter "@" Dataset Parameters button. Add a new parameter TopParam (without the @) and give it a Default value of 1 (no need to select
the rest of the boxes).
Click Execute ("!") and then save the dataset. Now, when you flick your TopParam report parameter it will pass the value down to the dataset and the report will get filtered.
An example against Adventure Works: http://www.mediafire.com/?987s8pvr0vsqpox
Boyan Penev --- http://www.bp-msbi.com
Free Windows Admin Tool Kit Click here and download it now
May 6th, 2011 9:46pm
Hi Boyan Penev,
I edited as the following and get the desired output. Though It is not showing data while adding the following query to the main query. How can I get this done? I appreciate your time on this.
On Columns From ( SELECT ( Filter( [NQ_Supplier_AP].[Supplier].[Supplier].ALLMEMBERS, Instr( [NQ_Supplier_AP].[Supplier].currentmember.Properties( 'Member_Caption' ), @NQSupplierAPSupplier ) > 0 ) )
SELECT
NON EMPTY
{[Measures].[Invoice Line Amount]} ON COLUMNS
,NON EMPTY
TopCount
(
[NQ_Supplier_AP].[Supplier Name].[Supplier Name].ALLMEMBERS,@TopN
,[Measures].[Invoice Line Amount]
)
*
[NQ_Supplier_AP].[Supplier Number].[Supplier Number].ALLMEMBERS ON ROWS
FROM
(
SELECT
StrToMember
(@FromInvoiceDateStandardDate
,CONSTRAINED
)
:
StrToMember
(@ToInvoiceDateStandardDate
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@APDistributionDetailsPLChargeType
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@CategorySubCategoryName
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@CategoryCategoryName
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@ChargeToBranchBranch
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@ChargeToBranchDistrictName
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@ChargeToBranchRegionName
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@ChargeToBranchOrg
,CONSTRAINED
) ON COLUMNS
FROM [TopSuppliersList]
)
)
)
)
)
)
)
);
Thanks
Tarak.
May 8th, 2011 7:01pm
Hi Boyan Penev,
I edited as the following and get the desired output. Though It is not showing data while adding the following query to the main query. How can I get this done? I appreciate your time on this.
On Columns From ( SELECT ( Filter( [NQ_Supplier_AP].[Supplier].[Supplier].ALLMEMBERS, Instr( [NQ_Supplier_AP].[Supplier].currentmember.Properties( 'Member_Caption' ), @NQSupplierAPSupplier ) > 0 ) )
SELECT
NON EMPTY
{[Measures].[Invoice Line Amount]} ON COLUMNS
,NON EMPTY
TopCount
(
[NQ_Supplier_AP].[Supplier Name].[Supplier Name].ALLMEMBERS@TopN
,[Measures].[Invoice Line Amount]
)
*
[NQ_Supplier_AP].[Supplier Number].[Supplier Number].ALLMEMBERS ON ROWS
FROM
(
SELECT
StrToMember
(@FromInvoiceDateStandardDate
,CONSTRAINED
)
:
StrToMember
(@ToInvoiceDateStandardDate
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@APDistributionDetailsPLChargeType
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@CategorySubCategoryName
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@CategoryCategoryName
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@ChargeToBranchBranch
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@ChargeToBranchDistrictName
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@ChargeToBranchRegionName
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@ChargeToBranchOrg
,CONSTRAINED
) ON COLUMNS
FROM [TopSuppliersList]
)
)
)
)
)
)
)
);
Thanks
Tarak.
Free Windows Admin Tool Kit Click here and download it now
May 8th, 2011 7:01pm
Hi Boyan Penev,
Here is my dataset query. Though I created a namedset in the cube, it is not filtering the data in Data Designer(SSRS). Please advise me. What I have to change in my query to get 1000 top records and Top 1000 as a parameter.
SELECT
NON EMPTY
{[Measures].[Invoice Line Amount]} ON COLUMNS
,NON EMPTY
{
[NQ_Supplier_AP].[Supplier Name].[Supplier Name].ALLMEMBERS
*
[NQ_Supplier_AP].[Supplier Number].[Supplier Number].ALLMEMBERS
}
DIMENSION PROPERTIES
MEMBER_CAPTION
,MEMBER_UNIQUE_NAME
ON ROWS
FROM
(
SELECT
StrToSet
(@APDistributionDetailsPLChargeType
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@CategorySubCategoryName
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@CategoryCategoryName
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
Filter
(
[NQ_Supplier_AP].[Supplier Name].[Supplier Name].ALLMEMBERS
,
Instr
(
[NQ_Supplier_AP].[Supplier Name].CurrentMember.Properties('Member_Caption')@NQSupplierAPSupplierName
)
> 0
) ON COLUMNS
FROM
(
SELECT
Filter
(
[NQ_Supplier_AP].[Supplier Number].[Supplier Number].ALLMEMBERS
,
Instr
(
[NQ_Supplier_AP].[Supplier Number].CurrentMember.Properties('Member_Caption')@NQSupplierAPSupplierNumber
)
> 0
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@ChargeToBranchBranch
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@ChargeToBranchDistrictName
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@ChargeToBranchRegionName
,CONSTRAINED
) ON COLUMNS
FROM
(
SELECT
StrToSet
(@ChargeToBranchOrg
,CONSTRAINED
) ON COLUMNS
FROM [TopSuppliersList]
)
)
)
)
)
)
)
)
)
WHERE
(
IIF
(
StrToSet(@ChargeToBranchOrg,CONSTRAINED).Count = 1
,StrToSet
(@ChargeToBranchOrg
,CONSTRAINED
)
,[Charge To Branch].[Org].CurrentMember
)
,IIF
(
StrToSet(@ChargeToBranchRegionName,CONSTRAINED).Count = 1
,StrToSet
(@ChargeToBranchRegionName
,CONSTRAINED
)
,[Charge To Branch].[Region Name].CurrentMember
)
,IIF
(
StrToSet(@ChargeToBranchDistrictName,CONSTRAINED).Count = 1
,StrToSet
(@ChargeToBranchDistrictName
,CONSTRAINED
)
,[Charge To Branch].[District Name].CurrentMember
)
,IIF
(
StrToSet(@ChargeToBranchBranch,CONSTRAINED).Count = 1
,StrToSet
(@ChargeToBranchBranch
,CONSTRAINED
)
,[Charge To Branch].[Branch].CurrentMember
)
,IIF
(
StrToSet(@CategoryCategoryName,CONSTRAINED).Count = 1
,StrToSet
(@CategoryCategoryName
,CONSTRAINED
)
,[Category].[Category Name].CurrentMember
)
,IIF
(
StrToSet(@CategorySubCategoryName,CONSTRAINED).Count = 1
,StrToSet
(@CategorySubCategoryName
,CONSTRAINED
)
,[Category].[Sub Category Name].CurrentMember
)
,IIF
(
StrToSet(@APDistributionDetailsPLChargeType,CONSTRAINED).Count = 1
,StrToSet
(@APDistributionDetailsPLChargeType
,CONSTRAINED
)
,[APDistributionDetails].[PL Charge Type].CurrentMember
)
)
CELL PROPERTIES
VALUE
,BACK_COLOR
,FORE_COLOR
,FORMATTED_VALUE
,FORMAT_STRING
,FONT_NAME
,FONT_SIZE
,FONT_FLAGS;
Thanks
Tarak.
May 8th, 2011 7:02pm
This topic is archived. No further replies will be accepted.
Other recent topics
