Hi Rodrigo,
This MDX query uses several suggestions from my comments below:
WITH MEMBER [Measures].[CondicionesContCompletaCalc] AS 'ISNULL([Measures].[CondicionesContCompleta],0)' SELECT NON EMPTY [Gerencia].[Gerencia].[Gerencia].MEMBERS ON 1, NON EMPTY [Measures].[CondicionesContCompletaCalc] ON 0 FROM [Condiciones]
If you try that and it doesn't work, please feel free to contact InterSystems support and we can help you with further debugging.
After looking at this some more, there are another couple of suggestions I would make:
- If you want to display each member of the [Gerencia].[Gerencia].[Gerencia] level in its own row, you should use [Gerencia].[Gerencia].[Gerencia].MEMBERS as the set expression on rows. Your current syntax will return a single row.
- It's unnecessary to wrap the set expressions on rows and columns in parentheses. (You may, of course, need to use parentheses in these expressions if they involve functions such as NONEMPTYCROSSJOIN(level1.MEMBERS,level2.MEMBERS), but an expression like [Gerencia].[Gerencia].[Gerencia].MEMBERS by itself doesn't need them.)
- If you aren't already using it, you may find it helpful to use the Analyzer, as this gives you an interface to create pivot tables to display the data you want without having to write the MDX from scratch and worry about its syntax. When you create or modify a pivot table in the Analyzer, the underlying MDX query is generated, and is executed either automatically (by default) or when you refresh the pivot. If your goal is to create pivot tables and dashboards, you may not need to interact with the query text at all if you don't want to; if you're running queries programmatically, you can create a pivot table and then view the MDX that was generated (click on the pencil-and-paper icon above the "Columns" box) and use it as a template for your queries.
I agree with Peter's suggestions, and I would also recommend wrapping the value expression for your calculated measure in single quotes rather than parentheses. After making these changes (and adding a missing [ to the hierarchy of the level you put on rows), your query might be roughly this:
WITH MEMBER [Measures].[CondicionesContCompletaCalc] AS 'ISNULL([Measures].[CondicionesContCompleta],0)' SELECT NON EMPTY ([Gerencia].[Gerencia].[Gerencia]) ON 1, NON EMPTY ([Measures].[CondicionesContCompletaCalc]) ON 0 FROM [Condiciones]
This article assumes that a user is seeing this error message when viewing a listing on a pivot in Analyzer, but it's also applicable when a user sees this error message in a widget on a dashboard. When that happens, you can first determine the data source for the widget, open that pivot in Analyzer, view a detail listing if one is not displayed by default, and then follow these steps to add the necessary SQL table privileges.
Hi Laura,
I haven't looked into this in enough detail to say whether or not it has to do with security or privileges - it's certainly possible that it could. I do think that in the past, I've found I needed to restart after installing Java before I could export PDFs from DeepSee in this way, but I'm not sure that a restart will be either necessary or sufficient to solve the problem you're seeing.
If you'd like assistance figuring out the cause of this problem, please feel free to contact the WRC and open a new support issue - we'd be happy to work through this with you in more detail.
Hi Lawrence,
You can create cube relationships as documented here, which will allow you to reference data in one cube from another cube (if records in the source class of one cube are related to records in the source class of the other). However, if I'm understanding your question correctly, that may not be the best approach in your case.
For one thing, I'm not sure why you have created two cubes instead of one cube with some additional elements. Is each of these items you're discussing a single record (i.e. a fact in your cube)? Or is it a property of each record (i.e. a model element in your cube)? If it's the former, you could add properties to your existing levels as necessary, whose values could be the human-readable names, generated/fetched if necessary by source expressions. If it's the latter, you could add display names to these elements in your cube definition.
It sounds to me like it might make sense to open a WRC issue to work with support on these questions, since they're fairly specific to the cubes you are creating - please feel free to do that if you'd like, and one of my colleagues or I will be able to work with you on this.
Hi Laura,
After installing the JRE, I believe you need to restart either the Caché instance you're using or the server you just installed it on before you can use it to export PDFs from DeepSee - have you tried doing that? I haven't had a chance yet to test and figure out which is necessary (based on the fact that you can use the JRE from the command line, restarting the instance might be sufficient), but if it's convenient to try that, it might help.
The place to specify the sort order for a level is in the cube definition. In the Architect, open the cube you are using, select the level that you want to change the sort order for, and look at the Details panel for that level (on the right-hand side of the Architect page). In the "Sort option" dropdown, choose "asc numeric", and then compile your cube definition. (It is not necessary to rebuild the cube after making this change.) You should then be able to refresh the Analyzer, put the same level on rows, and see that it sorts in ascending numeric order.
Hi Lawrence,
Here's a general outline of the steps to create a DeepSee dashboard, starting from data that is somewhere on your system but not yet in a DeepSee cube:
- Get the data you want to use into a persistent class, referred to as your source class. Depending on what you are doing with the data that comes in via your REST service, you may already have such a class, or you may need to create one and store a copy of your data there after it comes in.
- Use the DeepSee Architect to define a cube based on your source class. This will allow you to specify which properties of your source class you want available for use in pivots and dashboards. You can then compile and build your cube, which will create a fact table (and several other tables) to store an indexed copy of the data you have specified. There is documentation on defining cube models here.
- Use the DeepSee Analyzer to create one or more pivot tables that display data from your cube. There is documentation on creating pivot tables here.
- In the DeepSee User Portal, create a new dashboard and add one or more widgets to it. For each widget, specify a pivot table as its data source, and then customize the widget to display a table or chart, adding any filters or other controls that you want to give users access to. There is documentation on creating dashboards here. (It sounds like you may already be familiar with this step, once you've created a pivot, but I'm including it for completeness.)
For some of these steps, there are alternative options to the ones I've mentioned here, but these are the most common options and I would recommend using them to start out (and really whenever possible). Please let me know if you're having problems with the specifics of some part of this.
Hi Hamza,
I don't believe there is an MDX function that works the same way as ISCROSSFILTERED(). If you'd like to describe what you hope to do using this function, I'll let you know if I can think of a practical way to do this with MDX. To identify whether a query is using filters on the cube in question or a related cube, I would probably try to get the MDX query text and parse it, looking for %FILTER and WHERE clauses, but that would have to be done from outside of the query itself (e.g. in ObjectScript).
When I try to play that video using Firefox it stops at that point (without displaying any error messages, so your problem may be different). But reloading the page and clicking to a point a few seconds later seems to work for me - if you haven't tried that, it might allow you to pick up just after the place where it stopped.
It plays without stopping in Internet Explorer.
For reference, this seems to be the video:
http://video.intersystems.com/video/Video.Pages.VideoLibrary.cls?video=…
For what it's worth, it looks like it's possible to vote without subscribing to any of the updates - I filled in my information, left all four subscription check-boxes blank, and selected "verstuur", and received an email with a link to vote.
Hi Neerav,
I believe the correct syntax is to just use & (not &) for the ampersand before each parameter, and to use %26 (not &) for the ampersand before the member key. So your URL would be something like
....dashboard&SETTINGS=FILTER:[Account].[H1].[Account].%26[1];&NOTITLE=1
Please give this a try and let me know whether it works.
For the Fact and Indices databases journaling is optional and depends on the business needs. It might preferable to disable journaling when cubes are relatively small and fast to build, or cubes are scheduled to rebuild periodically.
Enable journaling on this database when cubes are relatively big and it takes too long to rebuild them. The ideal case to keep journaling on is when cubes are in a stable state and only get periodically synchronized, but not built. One way to safely build cubes is to temporarily disable journaling for the Fact database.
One thing to keep in mind is that when DeepSee synchronizes a cube, each update/insertion of a fact (along with the associated updates/insertions to the DeepSee indices) is done inside of a transaction. This means that there will be journal activity on ^DeepSee.Fact and ^DeepSee.Index when synchronizing even if those globals are mapped to a database (such as APP-FACT, in this example) that does not have journaling enabled.
When DeepSee builds a cube, updates and insertions on the fact table and indices are not done inside of a transaction. A build should cause journal activity on ^DeepSee.Fact and ^DeepSee.Index if and only if those globals are mapped to a database for which journaling is enabled.
There is one other mapping that might be useful to add in this architecture: The ^DeepSee.Listing global can be mapped to the APP-CACHE database, as described here. A large amount of data can be written to ^DeepSee.Listing if listings or plugins are run on cells containing many facts, and this global does not need to be journaled - listing queries will be rerun every time the listing in question is viewed.
Hi Tuan,
Could you please be more specific about what your question is? Are you trying to modify the row spec of a pivot table and having trouble getting it to work as expected?
Hi Karthik,
You could try creating a custom action to do something similar to this. There is documentation on custom actions here. For example, I believe you could define a KPI class with a custom action that opens a web page with the information you want to display in a new window. You could then add a control to your widget to run the custom action when clicked. If you do this, you can add your own custom icon for the control, as described here, and it will be displayed with the other controls, below the widget title.
Hi Karthik,
Can you provide any more information this error? What are you trying to do in DeepSee when the error occurs, and what other information is in the error message?
It might be easier to help you with this error if you open a WRC issue. Please feel free to do that by emailing support@intersystems.com or calling +1 617 621-0700 if you think that would be helpful.
Hi José,
Yes, the filterSpec URL parameter is required, according to the documentation here.
It looks to me like the /Info/FilterMembers service expects to get a single level (of a specified dimension and hierarchy) as the filterSpec. Although the level names are the same for the two LevelCs, they are unrelated as far as the REST service is concerned because they are in different dimensions and hierarchies.
I don't know exactly what you are trying to do with this combined list of filter members, since it may not be an accurate list of the members of either level if some items appear in one but not the other. In terms of creating the list, though, you could try running /Info/FilterMembers once for each of the two levels, and writing code to combine the result sets while removing duplicates.
One thing to keep in mind when creating a hierarchy is that each member of a lower level in the hierarchy must correspond to a single member of each higher level. For instance, in the hierarchy you propose, if you have prefectures named PrefA and PrefB, you would need to make sure that there isn't a Comisaria named Com1 in both PrefA and PrefB, and so on.
One possible way around this, if you notice that this could be a problem, is to include information from the higher levels in the members of the lower levels, so that your Comisaria members are named PrefA-Com1 and PrefB-Com1, for example. In this way, each Comisaria member will belong to only one Prefecture.
This is related to the issue discussed here (Lexi Hayden's comment in particular is useful): https://community.intersystems.com/post/tips-tricks-be-aware-month-week-hierarchy-deepsee
I'm not sure whether the four fields in your sample data correspond to the four levels you want to include in the hierarchy. If so, though, it would be a problem to put UNI_TIPOUNIDAD below UNI_DESCRIPCION in a hierarchy, because the member "50" of the UNI_TIPOUNIDAD level would correspond to both the "38A. COM. PUENTE ALTO" and the "1RA. COM. ARICA" members of the UNI_DESCRIPCION level.
Hi Lin,
You can export shared calculated members and other DeepSee folder items to a container class, which will hold a version of those items in XML format. You can then import the folder items from the container class into the new environment. The documentation on this topic is here: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY…
Please let me know if you have any questions.
Hi Jaqueline,
This Developer Community post has some information that you may find useful: https://community.intersystems.com/post/deepsee-period-date-vs-same-per…
It deals specifically with comparing results for this year to date versus the same time period in the previous year, but you might be able to apply some of the same concepts when writing your MDX expressions. Let me know if you need clarification, or if this doesn't enable you to make the calculations you intend.
Best,
Sam Duncan
I'm still seeing this issue. The last answer/last comment links under each post on the front page link to the answers/comments on the black box post (currently the top post on the front page).
Looking at my own profile, I also see this list showing some posts that I have not read. It isn't always the same as the list of posts on the homepage, either. I tried viewing different pages and then reloading my profile, and it seems that after viewing a page with a list of posts (e.g. one of the pages under "Browse by Tag" in the sidebar), posts from that page often show up in my Recently Read list, whether or not I click on them - but not every post, nor every time I reload a page of that sort.
I don't know if you were concerned about your profile showing inaccurate information to other users, or just pointing out that this feature doesn't seem to be behaving as expected - if it's the former, though, it does not appear that any list of recently read posts is shown when viewing a user profile other than your own.