MDX

Syndicate content 5 

Preview Mode was added to InterSystems IRIS Business Intelligence to give designers a quick view of what their resulting Pivot Table will look like without needing to wait for the results to fully execute. This can be beneficial when designing pivot tables because if you are dragging and dropping elements to see how they look/work in your pivot table and seeing if they have the desired data. Since you are exploring and designing, you don't necessarily care about the results at the moment, but you would still like to see how your table looks with the changes you have made.

+ 2   0 1
0

replies

131

views

+ 2

rating

What is %SQLRESTRICT

%SQLRESTRICT is a special %FILTER clause for use in MDX queries in InterSystems IRIS Business Intelligence. Since this function begins with %, it means this is a special MDX extension created by InterSystems. It allows users to insert an SQL statement that will be used to restrict the returned records in the MDX Result Set. This SQL statement must return a set of Source Record IDs to limit the results by. Please see the documentation for more information.

Why is this useful?

This is useful because there are often times users want to restrict the results in their MDX Result Set based on information that is not in their cubes. It may be the case that this information may not make sense to be in the cube. Other times this can be useful when there is a large set of values you want to restrict. As mentioned before, this is not a standard MDX function, it was created by InterSystems to handle cases were queries were not performing well or cases that were not easily solved by existing functions.

+ 5   1 1
0

replies

220

views

+ 5

rating

BridgeWorks is pleased to announce a VDM, v9.1.0.1.  This release includes the following updates:

Updates

  • Historical Linking is now based off connection profile name
  • Saved Formatting is now based off connection profile name
  • Tables and Fields column headers no longer hide based on connection type

Bug Fixes

+ 1   1 1
0

replies

97

views

+ 1

rating

Hello InterSystems Community,

We're excited to announce that we've completed our first Open Exchange submission for InterSystems platforms.

BridgeWorks VDM is an ad hoc reporting and graphical SQL query builder application that was designed for any user who needs access to their SQL projections in InterSystems Caché, InterSystems IRIS, InterSystems IRIS for Health databases as well as access to InterSystems DeepSee and InterSystems IRIS BI Cubes with minimal SQL scripting experience.  

VDM features:

+ 2   1 2
0

replies

156

views

+ 2

rating

I want to have a generic PercentOfAll measure that can be used for any dimension of the cube. This PercentOfAll should act like Count, but instead of showing the number of rows in a cell, it will show the percentage  (100*number of rows for that member/all rows) .It should not matter what dimension is being viewed. Ex

There are a total of 100 rows in the source class.

0   0 1
138

views

0

rating

Hi, Community!

I’m sure you are using Developer Community analytics built with InterSystems Analytics technology DeepSee:

You can find DC analytics n InterSystems->Analytics menu.

DC Analytics shows interactive dashboards on key figures of DC entities: Posts, Comments, and Members. 

Since the last week, this analytics project is available for everyone with source code and data on DC Github!

Last reply 6 August 2018
+ 2   1 1
294

views

+ 2

rating

Hi, Community! 

This is the 3rd part of DeepSee Web story - Angular base UI for DeepSee Dashboards, see the beginning here.

By design, DSW provides an implementation for every widget in DeepSee library. But there are some extra features in DSW which make solutions built with DSW dashboards more functional.  This article describes it.

+ 2   1 1
0

replies

452

views

+ 2

rating

Hi, Community!

How can I manage to show in MDX query months where every month shows the value of the last month?

Say, in HoleFoods the query:

SELECT NON EMPTY [Measures].[Amount Sold] ON 0,NON EMPTY [DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HOLEFOODS]

will show me the sum of revenue per month.

This query:

Last reply 8 October 2017
0   0 0
1,340

views

0

rating

Based on user request in zenpage, i am running the method ConvertMDXtoJSON in background and loading the status in progressbar.

D ..%RunBackgroundMethod("ConvertMDXtoJSON",MDX)

From this background method ConvertMDXtoJSON() i want to return the value(sjson) and store it into session.

How can i do this?

Below is my code. 

Last reply 10 April 2017
0   0 0
1,350

views

0

rating

Hi!

Sometimes I need to filter the widget on a dashboard from a different cube. And I face the following problem:

Widget A refers to a query from Cube A and I want to filter Widget B from Widget B.

Widget's B pivot refers to Cube B, and which has different dimensions for the same data.

E.g. cube A has the dimension Author and the Cube B has the dimension Member for the same data. So  there is no way to filter such a widget B from the widget A.

0   0 0
195

views

0

rating

Hi,

suppose a part of my mdx query is [DIMENSION].[H1].[LEVEL1].CURRENTMEMBER.Property("PROP1").

If the source property of PROP1 is also used as a dimension level somehwere in the cube, this query will return a key  to the entry in the coresponding star table.

Does anybody know how I can follow that key and display the real value value?

 

 

0   0 3
651

views

0

rating

How are we doing THIS year versus the same period LAST year? 
This is a common need in Business Intelligence. In fact, many design specifications for reports make use of a comparison between a selected period (year, quarter, etc) up to a certain date (for example November 15th, 2016) and a summary of the same information for the previous year (i.e. up to November 15th, 2015). 
This post shows how to implement this in DeepSee.

Last reply 9 January 2017
+ 5   0 0
1,063

views

+ 5

rating

Hi-

I am trying to figure out how to compare two dimension values in a MDX query such that records where two dimension values are the same will be filtered out.

To test this, I have modified the HoleFoods cube in SAMPLES namespace to have a new dimension called RandomRegion

I want to create a MDX query where the [Outlet].[H1].[Region] value is not equal to the [RandomRegion].[H1].[RandomRegion] value.

Here's the query I need to add the filter to:

Last reply 7 December 2016
0   0 2
700

views

0

rating

Dear All,

From my web page, i am passing filter values to mdx query.

when mdx executes, i am getting Error #5001: Invalid member expression: empty name(2).

when i try the same in samples namespace (patient cube). I got the same error.

Below is my sample MDX from patients cube.

SELECT 
NON EMPTY %KPI("PluginDemo","HighScoreCount",,"%CONTEXT") 
ON 1 
FROM [PATIENTS] 
%FILTER %OR([HOMED].[H1].[ZIP].&[32000])

Result: 0   This works fine.

 

Last reply 1 December 2016
0   0 3
236

views

0

rating

I currently have an MDX query/calculated measure as per the below:

 
[DaysOnListRange].[H1].[DaysOnListRange].&[Days 91 - 180]+ [DaysOnListRange].[H1].[DaysOnListRange].&[Days 181 - 365]+
[DaysOnListRange].[H1].[DaysOnListRange].&[Days 365+]

however it currently doesn't sum when there is a blank in the data.
How can I amend this query to include empty cells/only sum cells with data?

I've tried using nonempty but it doesn't appear to work in this context

Last reply 22 November 2016
0   0 3
268

views

0

rating

I need to create a KPI similar to the  DeepSee.Model.KPIs.DemoTrendLines kpi in Samples.  This kpi supports a scorecard widget with a trend line.  It shows patient counts by city with a trend line of the count of patients over a decade.  What I want is to show the trend line based on the average allergy score ([Measures].[Avg Test Score]).  A pivot table query that shows the data I would want to base the trend line on is:

Last reply 14 October 2016
0   0 4
222

views

0

rating

In DeepSee, having a customer account cube. The cube includes a time dimension AccountCreationDate.

How do you create a calculated measure to get the total number of accounts from the beginning to a certain date in abscissa ?

I tried many things including this:
AGGREGATE(%TIMERANGE([AccountCreationDate].[H1].[MonthYear].&[190001],[AccountCreationDate].[H1].[MonthYear].CURRENTMEMBER) ,[Measures].[%Count])

But I don't get the right result because the date in abscissa creates a filter on the first date.

Last reply 8 June 2016
+ 1   0 1
674

views

+ 1

rating