Report Model: Drill On Calculated Field In Report Builder
Feb 11, 2007
Hi,
I've created dsv that contain all fields from table database. in the smdl I've remove some fileds due to security. All fields in the smdl do not contain drill.
Issue: When I created calculated field in the report builder the field has a link. When I clicked the drill I saw all the record data including field that not in the smdl.
Questions:
1) Can I remove the link from the calculated fields?
2) Can I prevent from users drill to fields that not in the smdl?
Hello - I am having difficulty enabling the automatic click-through functionality for my model. I have two entities - Customer & Incident. Incident is a role/FK/OptionalMany relationship to customer.
I've read through earlier postings on this subject (below) and have verified that the #Incidents attribute in my incident entity has the EnableDrillThrough=true.
My test report is simple - Customer Name (from customer) and #Incidents (from incident). When I run it, the click-through option is not available (the cursor stays as an arrow and doesn't change to a finger).
Are there other properties or settings that I need to be working with? I have SP2 (9.0.3042) installed on our Standard edition of SQL 2005. I know Infinite Click-Through is not in Std edition, but a few levels should be, correct?
I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.
Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?
I have a report builder drill down report. I have row groups with totals. It looks like the attached. The problem is when the report is not expanded the Grand Totals column is not accurate... it is displaying the totals of one of the rows when expanded.The expression in the Total Show text box is
I deployed the same report model to 2 different SSRS servers. The report only has 1 field with count of records from 1 table. I can drill down on this column on 1 server, but not on the other server. This column is set to drillabe on the report model.
Is there any SSRS configuration that is stopping me drilling down on it?
I want to use this as the data from which to build a report model. As linked servers don't show up in the Data Source View wizard, I created a view in SQL Server:
create view MyExcel as select * from XL_SPS_1...Sheet1$
Okay, great, now the view shows up in the DSV wizard and I can create the data source view. However, when I create a new report model based on this data source view, the Report Model Wizard tells me at "Create entities for all tables" that I've got an error when it processes dbo_MyExcel that "Table does not have a primary key."
I assume this is where the identifying attributes for the entities in the report model are taken from, so I really can't go further. Does anyone have an idea as to how to add a primary key to a linked server (Excel) in SQL 2005? Can this be done? Other than importing spreadsheet data to a SQL table, how can I get around this?
Is it possible to dynamically refresh the report model of the report builder?
could it even be using code with any of the interfaces?
When we add a table or add a column to the table in database , will the report model get refreshed automatically or do we need to do it externally. If so, can we use any of the objects and write a custom code in VB.
How do I get data on my linked report based on my grouped subtotal and grand total from the main report. The subtotal and grand total are calculated columns.
I have a 3 columns in my matrix in the SSRS summary report. Actn_COAST, ActnCITY and NumbOfAccts.
The following is code for my summary report. The results are shown below.
SELECT Distinct ActnCITY, Count(ACCT) as NumbOfAccts, CASE WHEN ActnCITY in ('NY', 'OH', IN, 'NJ', 'SC', 'NC') THEN 'EAST COAST' WHEN ActnCITY IN ('CA'. 'NV', 'UT', 'WA', 'OR') THEN 'WEST COAST' ELSE 'OTHER' END AS Actn_COAST
FROM tbl1 where ACTNDATE between @STARTDT and @EndDT
Code for my detail report contains the following SQL
SELECT * FROM tbl1 where ACTNDATE between @STARTDT and @EndDT AND @ActnCITY = ActnCITY
I have linked my report based on the NumbOfAccts column. I am able to get data if I click any of the NumbOfAccts values related to the state I want. However when I am not sure how to make the subtotal and grand total work. I want when I click on the subtotal of either coast, I should be able to see records of that coast e.g., if I select 37 I should be able to see all the records in East Coast. If I click on the Grand Total, I only want data related to those 2 coasts.
I have added a calculated member in my Analysis Service Cube, but this member does not show up in Report Builder. It is in my cube. I have tried to rebuild the model and to generate a new model. I can create a report from Visual Studio 2005 with the calculated member. Does not Report Builder support calculated members?
I am using Report Builder 3.0 and i have report which has a drill down group. The report opens in a collapsed form.This report has interactive sorting on a column. But,when we drill down and sort the column, the group collapses and the details disappear.
In some posts, i have seen that instead of interactive sorting, people recommended a parameter. But i have to do interactive sorting only.
My environment: Computer A - SQL 2005 SP2 (32 bits) - Oracle 9i client I am the local administrator
Server A - Oracle 9i Enterprise Edition (64 bit) I have read only access to Oracle DB.
I am able to create a report model with Oracle DB and deployed it successfully. In my report model (rmOracle), I named my data source: dsOracle and dsvOracle for my data source view which contains one named query. Then I set a logical primary key for the named query, everything is OK so far.
Now, in Report Builder (RB) I have created a simple report (table) based on the report model I created before. Then when I preview it, I got the following error message:
Microsoft Report Builder:
Report execution error The report might not be valid or the server could not process the data.
Details: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater. ---------------------------- Cannot create a connection to data source 'dataSource1'. ---------------------------- An error has occurred during report processing.
I tried to look more information by saving the report and run it from the report server directly. And I got the following error message:
An error has occurred during report processing. Cannot create a connection to data source 'dataSource1'. System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
I open the RDL file and found that when creating a report with RB, it creates its own data source, which is fine. The problem here is that data source points to the wrong connection, which I thought it should point to the same connection of the report model its based on. In this case it should point to Oracle connection (dsOracle).
Anyone has any idea how to solve it? Or did I miss something here?
I have built a data model in visual studio that I am using with report builder in reporting services. The data model is using data from a view in SQL Server 2005. I am trying to apply a filter to the data model to restrict the data available in report builder. I know that I could change the code for the view and filter that data that way, but I would rather creates two models each one producing differnt sets of filtered data. I have tried adding a filter to the data model, but it does not seem to work. Do anyone have any suggestions?
I created a simple report model, which includes both tables and views as a data source view. So here are the problems I am facing when I try to use a report builder to build a report.
1. In the Report Builder window, on the FIELDS column, I am unable to see the primary key column of any of the tables.
2. I am unable to see the VIEWS in the ENTITIES column of the Report Builder; I can see only the tables.
Does anybody has some idea to deal with the above issues?
I have created security roles that restrict access to a certain Dimension and a member therein.
The security works fine when the users finened in it runs a report: The data is accordingley ristricted.
The problem is when those same users run Report Builder and create a report, those members are no longer restricted and they have acces to absoluteley all data from the cubes.
Is there some way that I can force the Data Model to follow the cube's security roles?
Any help is much appreciated...
Gerhard Davids
EDIT: I have found that it uses the wrong user when opening report builder. For some reason it uses my windows account instead of the one I used to log onto report manager. This is way the security isnt working
I get the following error when I try to deploy a report builder model. The SQL Server 2005 "Reporting Services" are installed and running on the local machine.
TITLE: Microsoft Semantic Model Designer ------------------------------
A connection could not be made to the report server http://localhost/ReportServer.
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <html> <head> <title> SQL Server Reporting Services </title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.2047.00" /> <meta name="HTTP Status" content="500" /> <meta name="ProductLocaleID" content="9" /> <meta name="CountryLocaleID" content="1033" /> <meta name="StackTrace" content=" at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Load() at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Construct(String configFileName) at Microsoft.ReportingServices.Diagnostics.RSConfiguration..ctor(String configFileName, String location) at Microsoft.ReportingServices.Diagnostics.RSConfigurationManager..ctor(String configFileName, String configLocation) at Microsoft.ReportingServices.Library.Global.get_ConfigurationManager() at Microsoft.ReportingServices.WebServer.Global.StartApp() at Microsoft.ReportingServices.WebServer.Global.Application_BeginRequest(Object sender, EventArgs e)" /> <style> BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black} H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt} LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline} .ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray} A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none} A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300; TEXT-DECORATION:underline} A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none} A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300; TEXT-DECORATION:underline}
</style> </head><body bgcolor="white"> <h1> Reporting Services Error<hr width="100%" size="1" color="silver" /> </h1><ul> <li>The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) </li><ul> <li>Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.4Reporting ServicesReportServerRSReportServer.config' is denied.</li> </ul> </ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span> </body> </html> --. (Microsoft.ReportingServices.SemanticQueryDesign)
I am trying to develop a data model that will filter data based on the user logged in. SUSER_SNAME() I have a list of User Id's in a look up table, a where clause in a view that filters the data based on who is logged in. This works fine and the data even seems to be filtered when I explore the data in the Data Source View. How ever when I publish the data model to the reporting services site and use report builder the user logged in is no longer recognized. All I am really trying to do is set up report builder so that users can generate reports and only have access to thier data via a customer number. I just can't allow users to see other users data and I don't want to set up a different datasource view for each user. Does any one have any experience with this?
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?
OK I have a report that needs an interactive sort on a calculated field. I get the message: "Report items cannot be used in sort expressions"
That's the whole reason we purchased SS*S and are putting up a Data Warehouse, so we can rank and analyze our data. Surely there is a way to do this?? Thanks for any advice!
I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?
Hi all, I am stuck in the following situation, I have following query:
SELECT EM1.VALUE AS 'P_ABC', EM2.VALUE AS 'P_XYZ', COUNT(EM1.VALUE) AS 'COUNTS' FROM TABLE_1 EM1, TABLE_1 EM2 WHERE EM1.EXTENDED_PROPERTY_GID IN (SELECT GID FROM TABLE_3 WHERE NAME = 'ABC' ) AND EM2.EXTENDED_PROPERTY_GID IN (SELECT GID FROM TABLE_3 WHERE NAME = 'XYZ' ) AND EM1.DOCUMENT_METADATA_ENTRY_GID = EM2.DOCUMENT_METADATA_ENTRY_GID group by EM1.VALUE,EM2.VALUE
There is foreign key relation between EXTENDED_PROPERTY_GID OF TABLE_1 AND GID FROM TABLE_3
Now, I want to create an ad hoc report but the problem is when I add EM1.VALUE to display the P_ABC, I am not able to add EM2.VALUE after that, may be because refere to same column of same table. I have to add EM1.VALUE and EM2.VALUE both to display result but I am not able to do it.
What is the solution for this problem ? Its kind of urgent.
I just created a report builder. I have a main report and i wanted to create a sub report. why i cant or i cant view the path or the folder of my .rdl file to be use as my sub report.
After I use the report builder to create a generic report, how do I actually get that report into the report designer so that I can modify it more effectivly?
The issue that I have now is that the file on the report server is not a .rdl file and if I simply save it as one and then bring it into VS to modify it the code file is a html structure rater than a XML file type.
I am trying to develop a report from cube,and it has one drill through report.when i click on sales amount field on main report,then it open the drill through report with details.Here i passed the **(category,subcat,product)parameters(Cascaded)** to drill through report in action part of main report.In my main report i have two columns .
when i click on 100, parameters are passed & it open the detail report correctly, but when i click on 50,the values for subcat parameter is not getting values in detail report and same as the product also.
I need to create a SSRS report using the Warehouse tfs_warehouse DB Report need to be drill down on WorkItemType Parents and child values. So, If we click on Product Back Log Item will get all the tasks inside it and so on....
------->Product BackLog items ----------> Bugs Or Tasks -----------> Tasks
I have done research and found out how to do it in OLAP cube ..but still can't figure out the TSQL query to be used.Found one of the query on forum:
SELECT * FROM DimWorkItem DWI INNER JOIN FactWorkItemLinkHistory FWILH ON DWI.System_Id = FWILH.TargetWorkItemID WHERE FWILH.WorkItemLinkTypeSK IN (SELECT DWILT.WorkItemLinkTypeSK FROM DimWorkItemLinkType DWILT WHERE DWILT.LinkName = N'Child') AND DWI.System_WorkItemType = N'Task' AND DWI.System_Rev = (SELECT MAX(DWI1.System_Rev) FROM DimWorkItem DWI1 WHERE DWI1.System_Id = DWI.System_Id) AND FWILH.SourceWorkItemID IN (SELECT DWI2.System_Id FROM DimWorkItem DWI2 WHERE DWI2.System_WorkItemType = N'Bug');
In report builder 2.0, I cannot determine how to display the number of times a field has been filled out when a one to many relationship is involved. I get an error every time I use two aggregates.
We have a remote business client who has a proxy server setup and this translates into receiving a 407 proxy authentication blocking error when attempting to download the report builder application for deployment off the report server which is housed locally with us. The proxy server does not allow them to download application type files off another site. At the moment, they would not like to disable the proxy for these users to allow them to deploy the application on their client or add the report server as a trusted site. Is there a method by which Report Builder can be locally installed on the client pc instead of online only with it knowing the correct report server to access and then still launch correctly when the report builder button is pressed through report manager?
I am not a web application guru by any means and we have tried to replicate the oneclick deployment and what occurs in the local settings/apps/2.0 subdirectories, but the application still attempts to download/install again. If we attempt to just launch the local executable on the client, report builder assumes the client pc (localhost) is the report server to access. Is there a method which will succeed?
I have installed SQL Server Reporting Service on window xp. everything working fine except one thing.
I have installed sql server with my a/c. my a/c have admin rights. when i giving http://dineshpatel/reports it is displaying page but Report Builder and other option are not displaying. I hope i don't have admin rights.
I have checked with local administrator login also but same problem.
what additional setting are require for admin rights?
I added a new windows user "ReportUser" which is a local user (not in administrator group). When I connect to the report manage's web page, I use reportuser to log in (when the anonymous access is disabled it will ask a log in). Report builder icon is not showing up on the report manager home page.
If I log in as a user in administrator group, the reportr builder icon shows up so I can download it and build report.
I have assigned reportuser "Content manager" and "repoter builder" access to the home folder. Is there anything else I have to do to make the report build icon show up on the home page?
I have client machine in one domain, say A domain , on which I have installed .net 2.0 without any problem. I have report server running in other domain (B domain) where I have deployed few models which will be used from domain A to create ad hoc reports by launching Report Builder. I can access other reports in domain B from A
But when I click on Report Builder ..it tries to connect to server in domain B but fails to connect and shows following log in a text pop up
I know, problem is because of cross domain communication but question is then how to fix it ? what changes I need to do for that in firewall, domain etc etc settings?
Here is the error, I have highlighted lines which might give some clue:
ERROR SUMMARY Below is a summary of the errors, details of these errors are listed later in the log. * Activation of http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application resulted in exception. Following failure messages were detected: + Downloading http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application did not succeed. + The remote server returned an error: (500) Internal Server Error.
COMPONENT STORE TRANSACTION FAILURE SUMMARY No transaction error was detected.
WARNINGS There were no warnings during this operation.
OPERATION PROGRESS STATUS * [12/19/2006 6:49:58 AM] : Activation of http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application has started.
ERROR DETAILS Following errors were detected during this operation. * [12/19/2006 6:50:14 AM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype) - Downloading http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application did not succeed. - Source: System.Deployment - Stack trace: at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next) at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles() at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState) at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation) at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirectBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation) at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options) at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut) at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state) --- Inner Exception --- System.Net.WebException - The remote server returned an error: (500) Internal Server Error. - Source: System - Stack trace: at System.Net.HttpWebRequest.GetResponse() at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
COMPONENT STORE TRANSACTION DETAILS No transaction information is available.
