.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

MDX Query Filtering Out Data That I Want

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server

I am still learning MDX and am struggling with a query that is feeding a SSRS report. What I am trying to do is provide a data set that breaks down the measures by Product Family, Machine and then Product Code. This is a parameterized query from SSRS where the three groupings are all multi-select parameters.

I have been experimenting with Non Empty and the NonEmpty function trying to eliminate rows that contain all NULL's for all measures. The query below does accomplish that. However, during my troubleshooting, I noticed one case when I failed to eliminate the null rows. In that case, there was one row that had 2 columns with data. All the rest were NULL. No matter how I formulated the query I could not get that one row to show up with the others AND have the rest of the rows with NO data filtered out.

The query below I captured using the Profiler & Excel and then hacked it to give me what I wanted. The result set includes many columns that are percentages so my desire was to use the Aggregate function in the report for the grouped roll-ups to ensure that I am getting the appropriate aggregates. Speaking of which, how do I have the rollup aggregate values restricted to only the data displayed in the report? In other words, what I am seeing is that the aggregate is for the entire grouping including items filtered out of the report. For example, I see t

View Complete Post

More Related Resource Links

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Data Points: Standard Query Operators with LINQ


LINQ to Objects and LINQ to Entities have an arsenal of Standard Query Operators that operate on sequences to perform a wide variety of operations.

John Papa

MSDN Magazine March 2008

Web Q&A: Storing SQL Data, URL Query Length, and More


Find out the best way to store large amounts of XML data in SQL Server, along with the performance implications. What's the maximum length of an XML query to SQL Server in a URL?

Edited by Nancy Michell

MSDN Magazine May 2003

Disable filtering on a Dynamic Data list view.


I have an entity set of NotificationTemplates, and each one of these has a collection of zero-many SmsTemplate entities. When editing or viewing a NotificationTemplate, I have a link to View SMS Templates. That link takes me to a List view for the SmsTemplates entity set, filtered for the NotificationTemplate I was viewing.

How can I prevent the user changing this filter to show SmsTemplates for another NotificationTemplate? That is, I want the filter, but it must be read only. The drop-down just mustn't drop down, it must just display the name of the NotificationTemplate that these SmsTemplates belong to. To view SmsTemplates for another NotificationTemplate, the user must click View SMS Templates from that other template.

This was originally asked by myself on SatckOverflow at: http://stackoverflow.com/questions/3514373/disable-filtering-on-a-dynamic-data-list-view

SQL Query to retrive the data in XML Predefined tag format

I am looking out the way for  instead of using column names as tags, be able to use a predefined mapping. For example Select firstname from employee <firstname>John</firstname> Instead, Have a file that maps the firstname to fname so when we run the TSQL should get:  <fname>John</fname> Please consider that there are over 100 columns in the Query for which how can I use the predefined format.  Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!

Data query task issue

Hi Someone help me undersand how to implement a Data meaning query  in SSIS project step by step. even a small package for testing this task ThanksThe complexity resides in the simplicity

How to query for id's of the fields with List Data Retrival Services Query() web service

Since i marked a post as answer in my previous topic, it seems that people dont visit the topic anymore as they see it as "answered" while it actually is only partially answered. So i guess i have to ask again in a different topic.    I need to get data from Sharepoint Server (3.0 i belive) for my Integration Services project. For that i use Sharepoint's webservices. I've read tutorials and downloaded sample programs to talk to WSS web services. I realise i have two ways to get items in a list: Use Query() or GetListItems(). Problem is, GetListItems() has a mandatory "Row limit" field and i might have to import ALOT of fields, so i thought Query() is better for me.  But, when using GetListItems(), the WSS returns the ID of the fields (for example, for a field of type user, something like "395;Ivanov Ivan Ivanovich" is returned) but when using Query() for the same list, WSS returns only the field value ("Ivanov Ivan Ivanovich") without the ID. I need it to return the ID as well. How can i set it up, on my side or server side, so i would receive the ID's, not only the values of the fields that are actually linking to something by ID when i use Query() web service?     

ASP.NET Dynamic Data Filtering not working

 Hello. I am trying to use this DLL to make searches on dynamic data pageshttp://www.codeplex.com/DynamicDataFilteringI pasted the following code, but nothing happens    <cc1:DynamicFilterForm ID="DynamicFilterForm1" DataSourceID="GridDataSource" runat="server">             <FilterTemplate>                                             <table>                <tr>                    <td>                        Nombre activo</td>                    <td>                          <cc1:DynamicFilterControl ID="DynamicFilterControl3

How to filter managed meta data column using page query string?

I have two managed term sets as follows: Articles:Downloads Projects:Downloads I am trying to use a content query web part to filter a list based on the above manged meta data column. I want to filter out only "Articles:Downloads" Scenario 1: When I edit the web part and directly set the filter option with the managed meta data column to "Articles:Downloads" it works fine. When I export the web part, in the .webpart file, the "FilterValue1" property has a value "Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910". Scenario 2: I want the web part to get the filter information from query string [PageQueryString:pageType]. So I tried using these options: a) pageType=Downloads      This works but returns downloads under 'projects' as well. (i want downloads only under 'articles')  b) pageType=Articles:Downloads     Does not work and shows no records c) pageType = Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work. (This value is the same as one found in 'FilterValue1' of the static web part). c) pageType = Articles:Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work. d) pageType = 9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work.   What am I missing here? Can some one please help?

Filtering Chart data based on querystring

i need to present chart that is based on the list but i need it filtered by query string. is this even possible to do?

BCS Data List - Unable to Allow Filtering

Hello,   I'm using the BCS Data List to display values from the 'Contoso Inventory List' on the Microsoft SharePoint 2010 demo machine.  I'm unable to allow user filtering of data - the options appear grayed out.  Here's what I did: Dropped a BCS Data List web part on my page Selected my External Content Type Data appeared as expected I then clicked the 'Edit View' link to modify the BCS Data List view The selections under 'Items to Retrieve' are grayed out - with "Retrieve All Items" selected by default.   I had hoped to use 'Retrieve items specified by the user' which in SharePoint 2007 BDC gave the user filtering options.I'm hoping someone can help me understand what I'm doing wrong.     Thanks!   Steven   ps - I get the same behavior with different SharePoint sites and External Content Types, so I don't believe it's a problem with my SharePoint site.

Analysis Service Oracle Number inconsistent Data Type for TABLE or Named Query

Dear Gurus, I'd VERY OLD PROBLEM. And I believe it addressed since 2006. When I design DataSource Views from Oracle Data Source. I found it return different oracle number data type for TABLE or NAMED QUERY   Provider Data Type Column Data Type Data Source View Data Type Oracle OLE DB Provider (OraOLEDB.Oracle.1) Table Number System.Int64   View Number System.Decimal   Named Querey Number System.Decimal Microsoft OLE DB Provider for Oracle (MSDAORA.1) Table Number System.Double   View Number System.Double   Named Query   1 System.Int64   Named Query   1.1234 System.Int64 Althought I know I can fix IT via MANUALLY EDIT DATASOURCE VIEW XML SOURCE. But I don't think this is a better solution. Is anybody have ideas ?  Wilson

How can i show the data thourgh query

I have a  Table named Test. Only one column is there that is INT colun.Data in that column is 1 2 3 My expected result is   1 2 1 3 2 1 2 3 3 1 3 2 How can i show this?  Ramesh.M

Data View and Scheduled Start & End Dates Filtering

I have a data view that needs to display all pages that are current and the closing date is greater than or equal to today's date. The issue I am having is that the data view is displaying pages that are have the a future scheduled start date or the scheduled end date is past, i.e. something that has a start date of tomorrow, is displaying and a page that has an end date of yesterday is displaying. I can tried to filter the data view on the scheduled start and end dates but I can't see it in the Filter Options of the 'Common Data View Tasks'. I have also tried adding XSLT Filtering but am unsure how to set it up.  I have added the following as an XSLT filtering [@PublishingStartDate<=$Today or string-length(@PublishingStartDate)=0] I am wanting to check whether I have done this correctly or not. Some pages won't have a scheduled start date but all will have an end date. Below is the code from my data view: <WebPartPages:DataFormWebPart runat="server" ShowWithSampleData="False" AllowRemove="True" AllowHide="True" SuppressWebPartChrome="False" PartImageLarge="" ViewContentTypeId="" ExportMode="All" Dir="Default" MissingAssembly="Cannot import this Web Part." ID="g_fdf00570_38e8_4df9_8ab3_5ca3ed974c41" DataSourceID="" IsIncludedFilter=""

how to query return all data in Array Object ,Collection Object

how to query return all data in Array Object ,Hash Table ,And DataTable dataRowSelect * From Doc a

Query to the data connection or webservice

Dear Infopath Gurus, I am facing a problem while fetching data from a (web-based) Infopath form library, as a secondary data source. Background: I have connected text-boxes on the form with a form library (secondary source), these text boxes are suppose to fetch values from the associated columns (dynamic in nature) and display it on the web form. But whenever I reopen the form, these text boxes do not fetch or display the updated values in the columns. The temporary solution that I applied was, applying a rule (Query using a data connection) whenever a form opens/loads. This solution was bringing the updated values alright but as the number of forms in the form library increased, a form was taking more than a minute to load. Which is totally unacceptable at user end. I also tried to apply a similar rule (Query using a data connection) to the text boxes on the form but for some unknown reason its not fetching the updated values from the form library. I am carefully putting an easy condition to trigger the last mentioned rule but its not giving the required result. Problem: Despite associated/connected with the secondary source columns, the form is not fetching the updated values on its own If I associate a (data query) rule whenever the form opens or loads, it takes ages to load If I associate a (data query) rule with the text boxes instead it

Infopath 2007: Cant we fetch data from a list or form library without using a Query to the data conn

I am trying to fetch data from a form library and a list (both secondary data sources). But cant do it without using a rule (Query using a data connection). However when I use the 'Refresh' control on the form it brings the value. I find it quite weird, as the connections are made properly and logically speaking all the data connetions should fetch dynamic data once the form opens or loads. Cant we do it without using a (data query) rule? Update: I am using a web based infopath forms, while using the same form on client its working fine. Why is that?
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend