How To Add An Attribute Only In The Data Set And Not In The Query Builder???
May 15, 2008
In my reports I want Date attribute to be added only in the data set and not in the query builder, As if I am adding it in the query builder report is taking very long time to generate and I am getting around 7000 pages of data which is absurd.So, if I can add it only in the data set then I can use filters for my reports and get only the required data. So, is there any way where in which I can add the Date attribute only in the dataset and not in the query builder????
I have found that in the autogenerated model attributes are missing for those fields that have relations to other tables. At first, it may look reasonable since a user can still get down to the field's value through the relation/related table. However, if the relation's key fields is the only thing the user wants to display, then going down to the related table is an overkill.
I can add an attribute manually and bind it to the key field(s). Is there an option in the autogeneration process to do it automatically? The only post I've found so far suggests to do everything manually (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1152575&SiteID=1). Is this the only way?
We have SQL Server 6.5 with an Access 8.0 front-end. We are about to re-write our front-end in VB6 using RDO. Obviously Access has a nice fast and easy to use query builder. My question therefore is - is there any such query builder that can be used within VB6 to simplify query writing ? Or do I have to write all query's and and stored procedures using straight SQL and/or stored procedures. I do not want to use Visdata as it doesn't appear up to the task.
I wrote a data mining plug-in algorithm in SSAS and now I am trying to view the lift chart in Management Studio. The studio gives the following error:
"The Data Mining Query builder control cannot be initialized due to the following problem: Deserialization failed: Multiple definition of element 'Column12' causes the content model to become ambigious. A content model must be formed such that during validation of an element information item sequence, the particle contained directly, indirectly or implicitly therein with which to attempt to validate each item in the sequence in turn can be uniquely determined without examining the content or attributes of that item, and without any information about the items in the remainder of the sequence."
I really do not understand what i did wrong. Can anybody help me?
Currently, I want to get images of an item in my report for illustration purpose.
Below is my query to get image for item 'GL-10000' in the database. However, I would like to pass a parameter value, '@sItem', from my report so that it would get all related pics of items.
SQL Query:
Select top 1 item_picture_mst.picture from item_picture_mst where item_picture_mst.item in (select item_all.item from item_all where item_all.item = 'GL-10000') ORDER BY item_picture_mst.picture DESC;
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
I am in the process of develping a MSRS report using an MSAS 2005 OLAP cube as my data source. In the MSRS Query Builder, I am using an MDX query which successfully executes in Management Studio. Something like the following:
SELECT
NON EMPTY { [Measures].[Fact Count] } ON COLUMNS, NON EMPTY{ ( [Dim Attribute].[Hierarchy Not Visible].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [MyCube]
The twist is that the AttributeHierarchyVisible property of the [Dim Attribute].[Hierarchy Not Visible] is set to False.
As mentioned previously, the query successfully executes in Management Studio. However when it is executed in the MSRS Query Builder, the following error message is displayed:
The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension.. Parameter name: mdx (MDXQueryGenerator)
Is there a way to successfully query dimension attributes whose hierarchies are not visible?
Access 97 SR 2 Front EndSQL Server 7.00.1063 Back EndWhen I attempt to relink the tables in my Access app. I am suddenlygetting this weird error message = "ODBC--Call Failed", followed by"[Microsoft][ODBC SQL Server Driver]Restricted Data Type AttributeViolation(#0)". This problem did not manifest last time I tried a fewweeks ago, and so far as I know there have been no software changessuch as Service Packs etc.The error occurs when attempting to link a view. Here is the scriptfor the tables and the view siipl_DoesNotWork:==================================CREATE TABLE [dbo].[tblBOMEntries] ([fldID] [int] IDENTITY (1, 1) NOT NULL ,[fldCMMPartID] [int] NULL ,[fldSIPartNo] [varchar] (20) NULL ,[fldDescription] [varchar] (50) NULL ,[fldQtyAssy] [int] NULL ,[fldItemNo] [int] NULL ,[fldOnIPL] [bit] NOT NULL ,[fldRefDesig] [text] NULL ,[fldParentID] [int] NULL ,[fldBOMLevel] [int] NULL ,[sys_ts] [timestamp] NULL ,[fldObsDate] [varchar] (15) NULL ,[fldEffDate] [varchar] (15) NULL ,[fldItemRevNo] [int] NULL ,[fldModNo] [varchar] (10) NULL ,[fldNewBOMFlag] [smallint] NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TABLE [dbo].[tblCMMIndex] ([fldCMMPartID] [int] IDENTITY (1, 1) NOT NULL ,[fldMainDescription] [varchar] (50) NULL ,[fldSIPartNo] [varchar] (50) NULL ,[fldBOMRevision] [varchar] (5) NULL ,[fldBOMAttached] [bit] NOT NULL ,[fldBOMDate] [datetime] NULL ,[fldCMMID] [int] NULL ,[sys_ts] [timestamp] NULL ,[fldLink] [int] NULL ,[fldNewBOMRevision] [varchar] (5) NULL ,[fldNewBOMAttached] [bit] NOT NULL ,[fldNewBOMDate] [datetime] NULL ,[fldSiteID] [int] NULL) ON [PRIMARY]GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOsetuser N'siipl'GOCREATE VIEW siipl.DoesNotWorkASSELECT dbo.tblBOMEntries.*,dbo.tblCMMIndex.fldCMMPartID AS Expr1FROM dbo.tblCMMIndex INNER JOINdbo.tblBOMEntries ONdbo.tblCMMIndex.fldCMMPartID = dbo.tblBOMEntries.fldCMMPartID================================However, if I create a new view based on exactly the same SQL asDoesNotWork I don't get the same error message:CREATE VIEW dbo.DoesWorkASSELECT dbo.tblBOMEntries.*, dbo.tblCMMIndex.fldCMMPartID AS Expr1FROM dbo.tblCMMIndex INNER JOINdbo.tblBOMEntries ONdbo.tblCMMIndex.fldCMMPartID = dbo.tblBOMEntries.fldCMMPartIDThe only difference that I can deduce between siipl.DoesNotWork anddbo.DoesWork is the ownership. Can that be the problem? If so, why?TIAEdward
I want to hide the Code and Name attribute, i set the Display Width Pixel to 0 but it doesn't work, i tried to set "Deny" Permission to those attribute but it hasn't any effect.Im working with Master Data Services 2014/ SQL Server 2014 SP1.
Hello, I use a XML Data flow item transformation in SSIS (XML Source). Here is an example of the input of xml file content:<extract date="2007-12-05"> <counters> <counter category="dispatcher" name="server1"> <runtime>6</runtime> <queue>3</queue> <maxrequest>8</maxrequest> <color>blue</color> <host> <name>svo2555</name> <path>\dispatcher</path> <lastaccessed>2007-02-03</lastaccessed> </host> </counter> <counter category="gateway" name="server1"> <runtime>1</runtime> <queue>10</queue> <maxrequest>10</maxrequest> <color>purple</color> <host> <name>svo2555</name> <path>\gateway</path> <lastaccessed>2007-02-03</lastaccessed> </host> </counter> </counters></extract> How do I catch the extract date in the transformation? SSIS only shows me the <counters> element and not the <extract> one. Why? Is there a way to get it?Regards,
I've selected a domain-based attribute for one of the leaf member attributes in the same entity, aka parent id, since it's a self-referencing entity. However, I cannot find a way to display anything but the code value in the drop-down (see below).
Is there a way to change the display value so that I can choose the attribute from the entity from which I want the user to choose? In other words, I would like to display the hierarchy name instead of the code, which is really just the primary id.
I have an entity (A), in which I use domain based attribute. The second entity (B) has several attributes. My problem is that, I would like to filter the first entity (A) based on an attribute that belongs to the second entity. The only way I can filter it (in MDS Excel add-in or Explorer) is by using Code or Name from the second entity.
I have in mind a couple of solutions, but they require some coding with xml saved query from Excel.
I have a column in SQL server which is of type ntext. Selecting the specific column to view it in report builder, an error message appears with the following description:
- Cannot run this report. The grouping expression 'nameofcolumn' returns the datatype binary. The Grouping Expression cannot return binary data.
Report Builder recognises this as if it was an image...
I would like to know if there are any good MDX query builder tools out there. The one that is shipped with AS does not suffice in that it does not support more than 2 axes. Also if there are any are there any that make life a little easier in that they are drag and drop and all but create the statement for you?
I need to create a matrix in SSRS using cubes and in doing so need to write MDX queries, not my strong point. Are there any tools that can help me "auto create" the statement with drag and drop functionality?
Hi people!I'm looking for an SQL query builder application that helps me constructSELECT queries with full respect of underlaying relational model. It wouldbe nice if the app is database independant because my work covers MSSQL,mySQL and Oracle DB.OpenSource and/or comercial suggestions are welcome!*** thanx ***
HI, in a lookup component, when I want to use the query builder, it will show tables from a different connection manager than the one I picked for being the lookup source. E.g. connection manager A points to a schema that contains table1 and table2 and connection manager B points to a schema that contains table 5 and table 6.
When I configure the lookup component to use connection manager B, I still see connection manager A tablles; isntead of seeing table 5 and 6 I see table 1 and 2.
I am trying to implement data masking based on user login and not sure why this is not working. I have the dimensions DimBrand, DimProduct and DimUser. I should mask the BrandCode with 'XXXX' nothing but in the report all the BrandCode should appear but few of the code will be masked if the user is not belongs to that group. I have a fact table FactProduct in this. In the cube I created all these 3 dimensions and the fact table. I created a new dimension DimBrandMask and I separated the code over there with a relationship with the actual DimBrand dimension. In the cube a reference relationship is set up with the measure group. Created a role with read access.
In the dimension data tab of role I put the below MDX to allowed set.
hi,i can do"select * from products where name = @name" kind of statements in query builder but"select * from products where name LIKE @name" dosen't work!" any ideas? i'm using sql server express. thanks
Hi All, For one of our projects, we are supposed to write a query builder to display reports in tabular format. For the filter criteria ( i.e where clause ) user can select from a list of predefined columns and assign conditions to it. For example, user can select 'CustomerName' as the column and specify condition where name begines with 'A'. Similarly he can also select ouput columns Problem The columns that user selects can come from different tables. We are planning to store mapping between tables and columns and also their primary and foreign keys in an xml file. But I would want to understand how to build the join clause. For example, say there are 4 tables customer, customer address, order, and items. Say user wants the number of items purchased by each customer who stays in 'Delhi'. 1. In output column he selects customer.name and count(items.id) 2. In filter criteria he selects address.city = 'delhi' How would the query builder know that to join customer and items, order also has to be joined. Any pointers would be helpful.
I hope you mind a questions from an inexperienced SQL 2000 user.
I don't have much experience with building queries for SQL2000. So I am lookinf for a software tool that will help me create simple queries. I'm also looking for an online (either on the web or a downloadable program) query reference to help remine me how to make these queries.
I am trying to emulate the "Find unmatched Records" Access Query using DTS. I have a small table with a list of codes that I need to use to exclude records in my text export.
I have created a left outer join and set the criteria for the joined field in the 'right' table to be null. I am repeatedly getting no records as a result.
Wouldn't the joined field in the right table for unmatched records by default be null? I'm stumped as to why this seamingly simple process won't work for me. I have attached the select statement for your review.
On pages 123, 124 of Data Mining with SQL Server 2005, I can't finish the example. When I add 'Monthly' to the end of the expression "[Home Owners].[Theater Freq]" in the Criteria/Argument column, I usually get an error message saying the syntax is incorrect when I switch to the Results view. If I place a dot after the original expression, like this: [Home Owners].[Theater Freq].Monthly, I get an error message that "The dot expression is not allowed in the context at line 4, column 23. Use sub-SELECT instead." What am I doing wrong?
My company is new to MDS. I am trying to set an attritube in an entity to read only so the users can't change the value in that field. When I did that, the whole model disappeared. I thought I had deleted it by accident so I created a test model and tried to do the same. The test model disappeared. This time, before saving the new settings I took a snapshot. After saving I took another snapshot. You can see that the whole model is gone (zz_RN_Permissions_Test). I tried every other coworker with admin rights and nobody shows it on the Models list. The behavior on the Excel add-in is correct. I can't change any values on that column. But I need to keep the models available.
Either edit the query from Dataset edit dialog or in the query builder (switch the design mode to show the query string), I can't save the query and always get the following error:
TITLE: Microsoft Visual Studio ------------------------------
The query cannot be retrieved from the query builder. Check the query for syntax errors. Reporting Services will continue to use the most recent valid query.
NOTE: I am using Brian Larsons book Delivering Business Intelligence with SQL Server 2005. I am stuck on page 588 - step 20. I am using Windows Vista Ultimate
Once all the Dimensions & attributes I have selected are dragged into the query area in MDX query builder no data is displayed. There is a message: "No rows found. Click to execute the query." - clicking does nothing.
Hi, I am trying to generate a report and I am using the query builder feature in SQL Server Reporting Services.
I just entered a simple query (actually the query builder generated it for me):
SELECT ID, pname FROM jira393.project
and it seems to run fine but when I click "OK" to go to the next step, I keep getting an invalid object name error: 'jira393.project'
I don't know what is wrong right now since when I run it, it seems to display the correct information but trying to go to the next step displays that invalid object error.
I have created a model from the underlying database. Now I want the capability to write SQL Queries when creating reports instead of drag and drop the reports. Is there anyway we can achieve it in MS Report Builder. I would like to know the options available in MS world on this.
Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.
My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.
