Working with "Measure Definitions Rowset Action" to show metric description

Topics: Standing Discussions
Sep 2, 2015 at 6:28 PM
Hi

i've created a table on my DW and tried to get description through this:

SELECT [COD_METRIC], [DSC_CUBE], [DSC_METRIC_LOGIC], [DSC_METRIC_DEFINITION],
[DSC_FORMULA DAX], [DSC_FORMULA]
FROM [BIDW_JP].[CFG_DATA_DICTIONARY]
WHERE [DSC_METRIC_LOGIC] = '" + [Measures].CurrentMember.UniqueName + "'

Only after this i've noticed that all the detail is in the cube. I'd like to ask if there is anyway to connect to a SQL Server table (like i was trying), and, seeing the existing example, what other information can i show or manually add (flexibility, basically).

Regards
Coordinator
Sep 2, 2015 at 9:52 PM
You could maybe try using a .Net Stored Procedure like the SQL Query one in the AS Stored Procedure Project Then you could construct a string expression in your action that would execute the stored proc which would run the given query and return the results as a datatable. If you use a rowset action you should then be able to get this working in Excel
Sep 3, 2015 at 12:31 AM
Hi dgosbell,

I was missing some details:, i will have information on a SSRS Report containing a list of Metrics with some details (let's call them MetricName, FieldA, FieldB, FieldC, FieldD).

Can you help me out with the properties settings, namely the ones with a **? The idea is to "link" to the report by the Metric name .

Property Setting
Name Definition
**Caption "Definition for " + [Product].[Category].CurrentMember.Member_Caption + " ..."
**Caption is MDX? Yes
Description This action will launch a report presenting the definition for a given measure.
Action Type Report
**Target Type AttributeMembers
**Target [Product].[Product Category Name]
Condition
(Report Parameters) ---
Parameter Name Parameter Value Expression
**ProductCategory UrlEscapeFragment( [Product].[Category].CurrentMember.UniqueName )
rs:Format "EXCEL"
Report Server localhost
Report Path ReportServer?/AdventureWorks Sample Reports/Sales Reason Comparisons
Invocation Interactive

Kind Regards
Coordinator
Sep 3, 2015 at 5:26 AM
I think the SSRS report route is a good way to go.

Try:
Caption: "Definition of " + [Measures].CurrentMember.Name + "..."
Target Type = Cells
Target = blank
Condition = true

For the parameter, your parameter name would be MeasureName and the expression would be:
UrlEscapeFragment([Measures].CurrentMember.Name)

Fix the report path and decide if you want it to view in the browser or export to EXCEL.
Sep 3, 2015 at 12:19 PM
Edited Sep 3, 2015 at 12:21 PM
Hi,
for now i only want to access all the list of metrics of the same cube, without filtering to the metric selected (it will be one of the several...).

With
report path: /Pages/Report.aspx?ItemPath=%2fABCD+BI+Reports%2fDEV%2fDicion%c3%a1rio+de+Dados%2fR07+Dicionario+Dados
and
parameter PRM_CUBE [Measures].CurrentMember.Properties("CUBE_NAME")

i already construct the link as

http://reports.ABCDEFJ.net/ReportsDW/Pages/Report.aspx?ItemPath=%2fABCD+BI+Reports%2fDEV%2fDicion%c3%a1rio+de+Dados%2fR07+Dicionario+Dados&PRM_CUBE=01_CUBE_STOCK&rs:Command=Render

The parameter that olds the cube's name is PRM_CUBE, and my SSRS report has this same parameter, can you explain me how to connect them (this is more SSRS than BIDS Helper...) , basically:
if PRM_CUBE=nothing, report is clean
if PRM_CUBE=01_CUBE_STOCK, then list these ones (how to simulate clicking on ok to list)
if PRM_CUBE=02_CUBE_XPTO...

Thanks in advance