Filtered Values

Posted by: danreber on 3 August 2017, 3:49 pm EST

  • Posted 3 August 2017, 3:49 pm EST

    In order to generate the SQL syntax needed to preform a drill-through I will need a list of filtered values for the page dimension. Is this available?



    Thanks



  • Replied 3 August 2017, 3:49 pm EST

    This is very disappointing. I asked multiple times for this to be available in the newsgroups and it is just now being entered as a suggestion? The work that DD did ( I appreciate that it was done ) to get the selected cell's parent dimensions won't work until filtered values are exposed.



    Thanks



  • Replied 3 August 2017, 3:49 pm EST

    Dan,
    Unfortunately, this cannot currently be done.  I have entered a suggestion case (Case 48121) to have this included.  You should receive an email shortly confirming that your email has been included with the case.  Also if you have any additional details that you would like to have included, please feel free to let me know.
  • Replied 3 August 2017, 3:49 pm EST

    Nickf,

    We need to be able to get a list of filtered items for each dimension on the page shelf (I don't need the dimensions on other shelves but I am sure someone else would).  The reason for this is because when you added the functionality to get the intersection of the selected cells parent dimensions, that was only half what was needed. Sure, I can create a SQL statement based on the visible row/column member values but that will not tell me that the summarized values (measures) are also limited by the filtered values in the page dimension fields.  I need to include the filtered values in my WHERE clause in the SQL statement.

    nickf said:
    "When in bound mode, you would not need to generate the a query to send
    to the datasource every time a user drills through the data displayed as
    ddAnalysis will handle this for you."

    How can ddAnalysis perform a drill-through to the detail data?  How will the data be displayed?  Also, the fields in the drill-through will more than likely be different than what is displayed in ddAnalysis.  I think you may be thinking of a drill-down (expanding a hierarchy).  And I definitely don't want ddAnalysis hitting a large data warehouse (we have clients with ~ 1 terabyte of data) every time someone drills-down.
  • Replied 3 August 2017, 3:49 pm EST

    Will this be part of the initial release?



    Thanks
  • Replied 3 August 2017, 3:49 pm EST

    Dan,
    Unfortunately, we are not currently planning on implementing this in the initial release.  However, we may include this in a future maintaince release.  Also, could you please provide me with a detailed description of what you are trying to accomplish that you need to deterimine the filtered values so that I may better describe the desired functionality to our development and management staff?

    As an alternative, it may be much simpler for you to achieve the drilling functionality by using ddAnalysis in bound mode and connecting ddAnalysis directly to your datasource.  When in bound mode, you would not need to generate the a query to send to the datasource everytime a user drills through the data displayed as ddAnalysis will handle this for you.
  • Replied 3 August 2017, 3:49 pm EST

    Dan,



    I have some questions for you. First let me set the record strait that we are NOT recreating DynamiCube. We have a different goal with this product and we are targeting a different market. We did not create this control with the intention of fully customizing it with version 1. It appears to me like you are attempting to expose every aspect of the control just to prove it will fit your needs. Sorry if this is not your intention, but it is what I'm gathering from reading your posts.



    Why not use the built in features of the control to deliver an visual analysis solution. The control offers a lot of features built right in. Using our LayoutActions implementation, you are able to programmatically manipulate the control and you get Undo functionality with every 'LayoutAction' you execute. You are also able to Allow/Dissallow users from performing certain actions using our built-in Permissions implementation. Best of all we provide all the necessary commands and methods to share your findings with others using our Read()/Write() methods for layouts, as well as allowing you to copy graphical results to the clipboard and printing results to the printer. It appears as though you are only interested in unbound mode and comparing the feature set to DynamiCube. Again, it is not our goal to recreate DynamiCube and expose every aspect for our V1 release.



    1.) Why do you need access to all filters on the PageShelf? ddAnalysis PageShelf works a lot differently than DynamiCube Page area worked. ddAnalysis PageFields create tuples and act as actual pages of data where DynamiCube Page area was a 'catch all' and allowed the values to aggregate as a group. The concepts are fundamentally different.



    2.) ddAnalysis does not have the ability to drill through detail data in an unbound mode as you've described. I understand your requirements, but why not change your app to get around the problem. Your suggestion is not the only solution possible. One idea would be to put the data into a small db (Access, CSV, SQL Server Express, etc..), then allow ddAnalysis to query that detail data instead? It may be a hack for you, but it would solve your problem until we provide the ability to 'drill through' unbound data.



    3.) Have you considered looking into SQL Server Analysis Services 2005? It sounds like you are going against a lot of data (~ 1 terabyte) and I can tell you that ddAnalysis will slow down when dealing with any amount of data near this, for we put the values in memory to aggregate it. However, the control is highly optimized for querying SSAS and you would be able to get your drill through with no additional code.



    Nick and I are working on a sample for you to allow you to find all filters in the display. However, in the mean time, please consider my comments and suggestions.



    Hope this helps,

    Luc
  • Replied 3 August 2017, 3:49 pm EST

    Of course I am trying to get ddAnalsys to fit my needs but my needs will also help your component. Without being able to perform a drill-through you only give your users half a tool because you are only displaying half the story. This is what makes my application so popular, we have consistently beat Cognos, MicroStrategy, Business Objects and others because of it.

    I know that you are not recreating Dynamicube because there are many things that ddAnalsys can do that DC could not. If you can't do it yet then that is fine but in my other post asking DD to add code to get the selected cells parent dimensions where I also said that I needed the filtered values from the page dimension I was told that it would be in the initial release. After I was told that I delayed my release so I could add ddAnalysis into my application.  I would not have been upset if I was told from the beginning that it could not until a maintenance release. (well, I would have been upset but I would not have posted it)

    Below are my answers to the rest of your questions.

    1) I need to access the filtered values of the page shelf because the values are not visible so I can't use the code that you added to get the intersection of the selected cells parent dimensions. Without knowing those filtered values the SQL syntax that I would generate would be incomplete because it is not taking into account that values that have been filtered out. For example, say you have Product as a row dimension and Total Cost as a measure and Year as a page dimension.  If you filter to show only 2007 how will I be able to limit the detail rows to not only the select product value but also year = 2007? Without it the values will not add up to what is currently displayed in ddAnalysis because it will be returning all years.

    2) I don't want ddAnalysis to perform a drill-through, I will do that based on the selected cells and the filtered page values and generate my own SQL statement and populate a grid based on the results. 

    3) SSAS does not work well in the medical industry because of the complexity and nuances of the data.  I will not be sending ddAnalsys detail data, I will be sending it pre aggregated data. Even if I did use SSAS how would ddAnalysis display the detail data?  Would I be able to add additional fields to be displayed?

    "Nick and I are working on a sample for you to allow you to find all filters in the display"

    If you are able to get all the displayed filtered values, what is all the fuss about :-)
  • Replied 3 August 2017, 3:49 pm EST

    Dan,

    Thanks for your patience in my response. I've discussed this issue in detail with our development staff and it appears that your requirement is a lot trickier than I had originally thought. ddAnalysis has a lot different filter types and rules for calculating results. Thus, exposing all aspects of the view to enable you to drill into detail data is extremely difficult, and may not give you the results you would expect. To understand this better, let me explain a scenario that would prove to be extremely complex to extract detail records:

    One thing to consider is how tuples are navigated. For example, assume the following TupleSet as the current page slice
       {(Red,Socks), (Blue,Jeans), (Black,Pants)}


    The above tuple may result to a query as such:

      "WHERE ((Color='Red' and Type='Socks') or (Color='Blue' and
    Type='Jeans') or (Color='Black' and Type='Pants'))"

    Such a query isn't very efficient on most SQL platforms, and this is an extremely simplified example.


    Another (more complex) example involves TOPN filters. The user could potentially specify a TOPN filter for each levels in each hierarchy in the layout. Knowing this fact, it makes it very complex for us to expose detail data or expose the query since it
    requires preaggregated data to be ranked and then ran against a condition over and over (for each) just to get to the detail context before other filters are applied.
    As you can see, it's extremely complex to return detail data against such a filter except for some simplified cases.

    Also, we need to consider the fact that every attribute can have up to 3 types of filters applied to them at any given time (Value filter, Heading filter, and Manual filter) and depending on where they are located, determines the order of their filter precedence. Lastly, measure filters add yet another level of complexity because the slicer axis affects TOPN for each attribute in the display before measure filters are even evaluated.

    As you can see, ddAnalysis has a very sophisticated filtering and summary engine, which makes your requirement extremely complex. I'm not sure how to move forward with this... However, we are continuing our discussion here at the office about your requirement. Of course, if you have any ideas, comments, or thoughts, please post them to the list.

    Something that you may consider might be to persist your layout to an XML file and then run XPath against it to find the various filters set on your layout, keeping in mind that you could have up to 3 types per attribute/level and 1 for each measure. I've not tried this yet, but it sounds like a feasible work around until I have some more news for you. :)

    Thanks again!
    Luc
  • Replied 3 August 2017, 3:49 pm EST

    "Something that you may consider might be to persist your layout to an XML file and then run XPath against it to find the various filters set on your layout, keeping in mind that you could have up to 3 types per attribute/level and 1 for each measure. I've not tried this yet, but it sounds like a feasible work around until I have some more news for you"



    This may be a viable solution for me. I will look into it next week and let you know.



    By the way, we won't be using your context menus or shelves to start so the users will be limited to using simple filters.



    Thanks

  • Replied 3 August 2017, 3:49 pm EST

    "Nick and I are working on a sample for you to allow you to find all filters in the display"





    Was this example ever created?



    Thanks
  • Replied 3 August 2017, 3:49 pm EST

    nickf,



    Do you have any questions?



    Thanks



  • Replied 3 August 2017, 3:49 pm EST

    We already postponed our release waiting for the the ability to get the selected parent dimensions.  Please add this functionality as soon as possible

    Thank You.


  • Replied 3 August 2017, 3:49 pm EST

    Is the functionality available though?



    Thanks
  • Replied 3 August 2017, 3:49 pm EST

    Dan and anyone else who may be interested,
    I've created a small sample which demonstrates how to extract the filters from a ddAnalysis view.  This sample can be used as a building block to accessing and determining the filters on a current view.

    2008/12/FilterExtraction.zip
  • Replied 3 August 2017, 3:49 pm EST

    Dan,
    It may be possible to use an instance of the ILayoutState interface to determine the filters that are currently applied to the view.  An instance of the ILayoutState interface can be acquired by calling the PivotView's GetLayoutState method.  From here you can get a list of filters that you can use to iterate over the Filters in the view and cast each FieldFilter to the appropriate filter type (i.e. IHeadingFilter, IValueFilter, IMemberFilter, IRangeFilter, or ITopNFilter).  Once this is done, the properties from each specific filter interface could be used to determine the actual filter that is being used.  Additional details on each of these interfaces can be found in the following links to the documentation:

    IHeadingFilter: http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.Layout.IHeadingFilter.html
    IMemberFilter: http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.Layout.IMemberFilter_members.html
    IRangeFilter: http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.Layout.IRangeFilter_members.html
    ITopNFilter: http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.Layout.ITopNFilter_members.html
    IValueFilter: http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.Layout.IValueFilter_members.html
  • Replied 3 August 2017, 3:49 pm EST

    Just to reiterate what I need and don't need. Based on Luc's last post it looks like there was some confusion.



    What I DO need to do is get a list of all dimensions with their filtered values and comparators (=, !=, > etc..).



    What I DON'T need is for ddAnalysis to do any drill-throughs or displaying of the data that makes up the current view or anything else like that. Other users may, but I don't.



    Thanks



  • Replied 3 August 2017, 3:49 pm EST

    Dan,
    Unfortunately, we do not have a sample available which demonstrates finding all the filters in the display.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels