How To Pass Cube Values For Directly Accessing Reports From A URL
Feb 19, 2008
Hi,
Actually we have multiple Sub Reports which we want to open from the Main Report. Initially when i used "Jump To Report" property it worked fine but in the browser the Main Report used to get refreshed everytime i had to Go Back to Main Report from Sub Report.
Second solution which i want to try is using "Jump To URL" property.
Some Details:
I have multiple parameters in the report.
My parameters are getting data from a Cube.
Parameters can have Multiple Vaue selected at a time.
I want to pass all the values to the URL.
Say
Parameter1 is DimCountryCountry
can have values such as:
[Dim Country].[Country].[All]
[Dim Country].[Country].&[India]
[Dim Country].[Country].&[Germany]
......
Parameter2 is DimCountryState
can have values such as:
[Dim Country].[State].[All]
[Dim Country].[State].&[Delhi]
[Dim Country].[State].[Punjab]
.....
Now i want to create a URL using these values,since each parameter should be separated by "&"(ampersand) my URL looks like one below.
Following URL Works fine:
javascript:void(window.open'(http://MyReportServer/ReportServer/Pages/Report.aspx?/MyReports/Project1/Country+Data&rcarameters=false&DimCountryCountry=[Dim Country].[Country].[All]&DimCountryState=[Dim Country].[State].[All]'))
as long as there is no "&" in the Parameter Value the URL works fine.
But when i try to insert values other than "[All]" it starts failing because "[Dim Country].[Country].&[India]" contains "&", which gives error due to ambiguity, (whether this "&" is a parameter separater or is it a part of parameter value)
So i found a solution for that, which is to replace "&" with "%26" escape sequence characters.This works fine as long as we specify this escape sequence in address bar. i.e if we directly paste the following URL in the address bar it will work.
http://MyReportServer/ReportServer/Pages/Report.aspx?/MyReports/Project1/Country+Data&rcarameters=false&DimCountryCountry=[Dim Country].[Country].%26[India]&DimCountryState=[Dim Country].[State].%26[Delhi]
but if we put this URL in the Javascript, it doesnot work, because the WIndow.Open function automatically replaces the
"%26" characters with "&", which we know gives ambiguity error.
Please let me know if you have any suggesiton for it. Anyway any better solution for my problem.
First let me give you a little back ground on me. I'm very new to the ASP, ASP.NET, Visual Studio, Sql Server, Frameworks....thing. I am coming over from a PHP/MySql background of over 5 years. The change over to VBScript and VB has been to tough and I have a basic liking for the Visual Studio 2005 and the ease of putting things together. However, I have come across something that to me seems like it should be relatively simple, but haven't been able to find the documentation or samples to describe what I'm looking to do. Rough Need: 1. Start with a form will a couple labels and a singe textbox to get the lookup date from an user. 2. Query one table/view based on the users choice of date and select only one field of returned data.(Doing this by itself is not a problem and I can display my results in a Gridview, but this is where it starts getting tricky and the gridviews won't work for me.) 3. If there is something returned, I need to start a HTML table layout or possibly some form of a Gridview(I don't see how I would use the Gridview) and start a loop, adding the first returned row from this query into the first cell. 4. Now, based on that same User Date and the returned row value from the previous query, I need to query another Table/view and return another single field, which might return 1 or multiple rows, which I need to start a loop to display unique items in cells under the first on above. 5. Based on the original User Date and each returned row from the first query, I need to query two other Table/views and get some additional information. 4-5 fields will be returned and be displayed on one row in the number of necessary columns. 6. This would finish the first row from the first query, so I would need to loop back up to see if there were any further results and continue looping until the get to the last of the results from the first query. 7. Finally, I would just need to close up the Table/gridview. The basic results I'm looking for would be similar to the following: Results for 10/11/2005
Field name 1 Field name 2 Field name 3 Field name 4 Field name 5
First Result from query 11 of 4 results from Query 2, but only 1 unique result
Info from Query 3
Info from Query 3 Info from Query 3 Info from Query 4 Info from Query 4
Info from Query 3 Info from Query 3. Info from Query 3 Info from Query 4 Info from Query 4
Info from Query 3
Info from Query 3
Info from Query 3 Info from Query 4 Info from Query 4
Info from Query 3 Info from Query 3 Info from Query 3 Info from Query 4 Info from Query 4
Second Result from query 1First result from Query 2 after looping though the first query Second result from Query 2 after looping though the first query
Info from Query 3
Info from Query 3 Info from Query 3 Info from Query 4 Info from Query 4
Info from Query 3 Info from Query 3. Info from Query 3 Info from Query 4 Info from Query 4
Info from Query 3
Info from Query 3
Info from Query 3 Info from Query 4 Info from Query 4
Info from Query 3 Info from Query 3 Info from Query 3 Info from Query 4 Info from Query 4 It seems me from what I have read and slowly figuring out, is that I should be able to directly access the DataSet returned by four SqlDataSources, one for each of the above querys and then just write my own VB to handle the necessary looping, table format and such. I can easily add the for SqlDataSources to the page and add a Gridview for each one and get 4 separate chunks of info, but can't see a way with GridViews to intermingle the info like displayed above. So, if it can be done with Gridviews, then I would love to see how that is done. But, if someone could explain to me how I access the DataSets directly that I get from the 4 SqlDataSources, then that would be ok too. I have figured enough out with VB, that I can write the code to do my looping requirements, if I can just access the information I get back. Thanks for reading all the way through this long post. Jack
As the source data of the cube is from MySQL, and the source data volume is more than 80M row per month, I have to build multiple partition in the cube, each partition contain only one month data, even though, the time to load data directly from MySQL is still too long, and because the mysql .Net provider and is not mature enough, the connection often break while loading, so I have to try to load from flat file which was exported from MySQL. The Partition Processing Destination seems support this way, but, even it shows the partition name in component editor, it actually process the partition with partition ID, and there's not any way to change destination partition name for this component. Unless I have to change the SSIS package every month, looks like it is impossible to make a smart ETL program that can dynamic create new partition with the ID and name as YYYYMM every month and load data directly from the flat csv file exported from big MySQL table.
Does anyone know how to load data from flat text, and also support dynamic destination partition name?
And I also find a bug in Partition Processing Destination, even 2005 SP2, if there's more than one cube in a SSAS database, and if two partition name in different cube is same, in component Editor, you can not set mapping to the second one with the same name, even you point to the second one, and click ok, the next time you open the editor, you will find it high light at the first one. And even it shows name in editor, it actually process with partition ID instead of partition name, this make it is not possible by change the partition name which need to process to a constant name, say currentMonth to force the component process it.
Dear Reader ,There are some details <facts>, which can be stored (Hard coded) in exe.example: Measurement Units and their relations.Now I want to print the list of those Measurement Units and Relationships between them.Can we print them directly , without bringing them into Database?Can we use them and make reports merging details from the Database?Am using SQL 2000 and VB.Net 2003SuryaPrakash Patel--Message posted via http://www.sqlmonster.com
is it possible to exclude empty records from an ADSI QuerySelect?
I got a query like
Code SnippetSELECT objectGUID FROM OpenQuery(ADSI, 'SELECT objectGUID FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User'' AND objectCategory = ''Person''')
and would like to have it like
Code Snippet
SELECT objectGUID FROM OpenQuery(ADSI, 'SELECT objectGUID FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User'' AND objectCategory = ''Person'' AND homeDirectory IS NOT NULL')
or
Code SnippetSELECT objectGUID FROM OpenQuery(ADSI, 'SELECT objectGUID FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User'' AND objectCategory = ''Person'' AND NOT(homeDirectory = NULL) ')
The problem is that I can only perform a query like
Code SnippetSELECT objectGUID FROM OpenQuery(ADSI, 'SELECT objectGUID FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User'' AND objectCategory = ''Person'' AND NOT(homeDirectory = '''')')
which results in the error
"Could not fetch a row from OLE DB provider 'ADSDSOObject'."
There are many objects which do have a homedirectory, though, and therefore should be fetched.
Any comments or hints on where my error hides?
I know I could go on fetching all objects and exclude the unwanted ones later on but I'd simply run into the limitation of data records LDAP would provide me with.
I seem to have a problem creating a model for my AS cube through Report Manager.
I have set up the data source as follows:
Name: Gates Aust Business Intelligence
Connection Type: MS SQL Server Analysis Services
Connection String: Data Source=MANT4003;initial catalog="GAPL Sales Analysis"
Connect Using: Windows Interegrated Security
This part works, but when i go to generate the model, i get the following error in the browser:
"
Cannot create a connection to data source ''. (rsErrorOpeningConnection) Get Online Help
For more information about this error navigate to the report server on the local server machine, or enable remote errors
In the reporting services log file i have the following errors:
w3wp!library!7!07/03/2006-10:51:40:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.DataSourceOpenException: Cannot create a connection to data source ''., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.DataSourceOpenException: Cannot create a connection to data source ''. ---> Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: The connection either timed out or was lost. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags) at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) --- End of inner exception stack trace --- at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count) at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length) at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader() at Microsoft.AnalysisServices.AdomdClient.DimeRecord..ctor(Stream stream) at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord() at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetDataType() --- End of inner exception stack trace --- at Microsoft.AnalysisServices.AdomdClient.XmlaClient.EndRequest() at Microsoft.AnalysisServices.AdomdClient.XmlaClient.CreateSession(ListDictionary properties, Boolean sendNamespaceCompatibility) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.CreateSession(Boolean sendNamespaceCompatibility) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open() at Microsoft.ReportingServices.Library.RSService.OpenDataSourceConnection(DataSourceInfo dataSourceInfo, CreateDataExtensionInstance createDataExtensionInstanceFunction, Boolean isUnattendedExecution, Boolean unwrapConnection, IntPtr clientToken, IDbConnection& unwrappedConnection) --- End of inner exception stack trace --- w3wp!library!1!07/03/2006-10:51:59:: i INFO: Call to GetPermissions:/ w3wp!library!1!07/03/2006-10:53:02:: i INFO: Call to GetPermissions:/
I have tried searching the forums and cannot find anything really helpful. I have had one cube setup and a model generated before and all was workin, but i had to delete it and i recreated the SSAS cube from scratch.
My user account has admin rights on the cube and also on the servers.
Server is Windows 2003
Client is Windows XP.
Any one got anythoughts on this or how i can determine what is wrong.?
I have created a crystal report connecting an OLAP cube .But once itry to view a report it took around 15 minutes and finaly gave anerror like "cannot retrieve data from cube".I want to know why it isand how can i create a crystal report for OLAP CUB.
I am receiving the following error when I run the report builder query. I am able to successfully select the dimensions and measures that I am intrested in but when I run the report I get the error message.
The 'PerspectiveID' custom property for the 'query' perspective is either not set or is not set to the string data type. ---------------------------- Semantic query execution failed. ---------------------------- Query execution failed for data set 'dataSet'. ---------------------------- An error has occurred during report processing.
I am testing this against both Adventureworks standard and enterprise cubes. Additionality I saw a post in this forum http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=368962&SiteID=1 that seemed related but I am using reporting services in sharepoint integrated mode so I am having a hard time trying to troubleshoot this.
Hi, My reports are deployed on reporting server installed on my machine. Now i want to access the reports on other machines which are on local LAN along with my machine. When i try to access reports from some other machine browser gives error page not found. Can any one tell me how to do this.
I have created several reports in SRS and I deploy them by establishing a VPN connection than deploying. That much works fine.
Now I want to allow user of my website to access the reports by clicking on a link. Do I need to establish user level access for the reports for each user or just one public access account? How do I pass login and credential information with the link so that users can access the report.
I would like a report to display the number of users and the reports accessed for a certain duration of time[being able to go back and check for previous years as well].
I read on the msdn that 60 days worth of report log is kept,and the rest gets wipped off.Any suggestions on how I would be able to get user access list for the past year as well.
I am not able to access registery key in report when report is deployed on reporting server. But when that report (.rdl) is opened in a Business Intelligence project it is able to access registry key. So, my question is what need to be done to get value of registery key in reports deployed on reporting server.
I am trying to access some SQL 2005 reports externally from the network, however when i try to open the reports i get the following error -
· "The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. The remote certificate is invalid according to the validation procedure."
I am facing an error while displaying a sql reporting service report on my asp.net page. The report it self is build using Report server project. It consists of a Main report which contains a bar graph. Upon clicking on the bar graph, it takes you to another report (i have given in the jump to report section), call it Report 2. Upon clicking on Report 2 it shows another report say Report 3. It is displayed properly as long as i run the preview of the report in my report project. When i deploy this report on the report server and trying it to access using my asp.net code behind. The main reports runs very fine. Upon clicking, it does not do anything and when i click again it display error, the report does not contain any thing. Sometime it does not show even the error. But just could not drill me in.
I presume on the click event of the report which is a char item, it calls the page load event, and it loses it parent report information or something like that. Please can any body guide me how to do this.
The following functionality i need to achieve with my web page.
My main report : Main report :upon click on any char item, it should take you to the second report. My second report : and on clicking on charitem on the the second report, it should take me to the third report.
I have Reporting Services installed on my web server (Windows 2003) where I am hosting my website too. While installing Reporting Services, I used my database server as SQL Server (Windows 2000, SQL Server 2000). Now when I try to access from browser using http://servername/reports it's giving me following error: "The underlying connection was closed: Could not establish trust relationship with remote server." Also when I try to access http://servername/reportserver/ it gives me this error: "The permissions granted to user '{servername}IUSR_{servername}' are insufficient for performing this operation. (rsAccessDenied) Get Online Help" I have given full access to "ASPNET", as well as "IUSR_{servername}" on the folder "C:Program FilesMicrosoft SQL ServerMSSQLReporting Services" While installing reporting services, I used SQL mode of authentication. My goal is to have reports on internet for public view. I have "Enable Anonymous access" check box checked on IIS with user name "IUSR_{servername}"
I have looked many places for the error and while many people is having same error, everyone has different reason and different solutions.
Not sure what I am missing here...Can anybody help?
How is it possible for others to browse to my localmachine and view the reports? They tried browsing to http://machinename/reports and http://10.1.3.123/reports
But the message the users get is: You are not authorized to view this page.
Then In my localmachine, I changed the IIS setting for the Reports and ReportServer to allow anonymous access. Then the users were able to see the Home page of the reports but not the reports themselves to click on for viewing. Any thoughts please? Thanks
I'm trying to get empty cells from an OLAP cube to display in a report designed using the Report Builder. Of the three report types, only the Table report shows the empty cells. Is there any option/setting that I need to enable to get this to work for the Matrix or Chart reports? Something like the "Show Empty Cells" option that is available in the pivot table used by BI Dev Studio?
Environment - Windows 2003 standard server SP1, SQL 2000 SP4 (installed mixed mode)
Goal: To have Crystal reports run via odbc dsn using windows authentication.
Big Honkin' Problem: I should say that if the users are local admins on this server then the windows authentication works just great, if not a local admin then it doesn't work. SQL gives an error access denied. (this shows in the logs btw.. to the end user crystal displays a generic database error) Since windows authentication failed, I then decided to use the dbo and switch to SQL auth. This dbo did not have a password and everything ran great. Then the IT dept. asked me to give the user a password, so I did. Then everytime Crystal opened, a window would appear asking the user to type the database , user and password. In Windows everytime this was done the crystal control files would change ownership to whomever entered the information making the report useless to any other user until I manually went into the file properties and changed it back. I develop these reports thru an VPN connection and on my desktop I said phooey on re-entering the password 50 zillion times while developing so I opened the dsn and put the PWD line in and it ran great. So we figured why not put this on the server - the password does not pass thru to SQL, even as admin user. The final problem that came up in relation to this is that I then created another user in SQL with no password so the reports would run. I developed a report that takes the current user credentials and passes it to the report via dde link. On my machine running over the VPN and launching the database program (GoldMine) over the WAN the report did everything it was suppose to. I high-fived myself and installed it on the server where the users would launch it and the dang thing wont pass the credentials over to the report.
I know there is not much to SQL security - it's either SQL or mixed mode. I have it on mixed mode and I know for fact it runs because Goldmine runs. I have to have some sort of security 'thang' in Windows 2003 somewhere that doesn't allow the passord to move and SQL only sees a blank password.
If any body can give me any help - I would so greatly appreciate it.
In short, we have started deploying Office 2007 to our users and Excel is currently the only client we use to interact with our AS2005 cubes.
A few users have reported issues (which I've verified), but the explanation in the KB article doesn't make any sense to me. These reports were originally developed in Excel 2003 and when opening them up in Excel 2007, we'll see a message saying that Excel found unreadable content in the .xls file and after clicking 'Yes' to recover contents of the workbook, we then receive a message that a PivotTable report was discarded due to integrity problems. If I opened up this report in Excel 2003, I don't receive these errors or messages.
Per the KB's explanation (http://support.microsoft.com/default.aspx/kb/929766):
This issue occurs if the following conditions are true: €¢The workbook contains a PivotTable that uses key performance indicators (KPIs).€¢The KPIs are created in the Analysis Services Business Intelligence Development Studio.€¢One or more of the KPIs have an expression in the Current Time Member property. Now, we are running 2005 Standard Edition with no SP, but will be deploying SP2 in a few days. Our cubes do not have any KPIs defined. Can I even define KPIs if we are only running Standard Edition?
I have a fact table with a simple integer lookup key into a basic dimension table. However, some of the fact lookup key fields are NULL. I would like the Analaysis Services reports to show this NULL category. Instead, Analysis Services discards any NULL entries and the records are completely absent from the reports. What is the best way to achieve this?
hi, i have this sql statement: @uname varchar(20) select@sql = 'select user_name, password, role_code, expiry_date,effective_from,active from usermaster where userid='1' and username like'@uname%'
it is not worrking for me.i don't know how to pass values in dynamicsql,can any one answer for me please.
I am using the Query analyzer and i have this stored procedure and i need to pass the ProductID and the OrderID and in query analyzer i call the "EXEC CustOrdersDetail 75 10347" but i get a syntax error near 10374...so my question is how do i pass multiple paramters in the query anlyzer to see what the stored procedure will return....
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
ALTER PROCEDURE CustOrdersDetail @OrderID int AS SELECT ProductName, UnitPrice=ROUND(Od.UnitPrice, 2), Quantity, Discount=CONVERT(int, Discount * 100), ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2) FROM Products P, [Order Details] Od WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I want to do something like this SELECT LocationID, Description, ActiveFROM dbo.CapLocationWHERE (Active = 1) AND (LocationID NOT IN (2)) AND (LocationID NOT IN (@LocationID) OR @LocationID IS NULL)ORDER BY Description For the life of my I can not figure out how to pass a group of values like 1,2,3. Any help would be greatly appreciated, Bryan