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.

Thanks
Sumit
www.sumitkhemka.com

View 4 Replies


ADVERTISEMENT

Accessing Data Directly From A SqlDataSource?

Mar 7, 2007

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 

View 1 Replies View Related

How To Load SSAS Cube Partitions With Dynamic Name Directly From Flat Files

Feb 1, 2008

HI,

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.

Thanks.

Jun

View 4 Replies View Related

Can We Use Details Hard Coded Into Exe Directly In Reports Without Storing In DB

Jul 23, 2005

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

View 1 Replies View Related

Exclude NULL-Values Directly From ADSI-SELECT

Jun 28, 2007

Hi all,



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.

Thanks in advance!



Regards,

caracol

View 2 Replies View Related

Accessing SSAS Cube From Report Manager Error

Jul 3, 2006

Hey Forum Community

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.?

Many Thanks


Scotty

View 2 Replies View Related

Cristal Reports For OLAP CUBE

Jul 20, 2005

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.

View 3 Replies View Related

Accessing Values Parameter Values From Another Report

Sep 20, 2007

Hi, How can I display a value of a report parameter from one report into a textbox on another report??

View 1 Replies View Related

Report Model Accessing SSAS Cube In SSRS Sharepoint Integrated Mode----- Perspective ID Error

Dec 11, 2007

Hi,

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.

Thanks,
Larry




View 2 Replies View Related

Accessing Reports On Local LAN

Aug 21, 2007



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.

Thanks in advance..

View 3 Replies View Related

Accessing Reports Once Deployed?

Apr 27, 2007

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.



Thanks in advance

View 3 Replies View Related

How To Pass Parameters To Sub Reports?

May 8, 2008



how do u pass parameters to a subreport.... i tried doin it but got an error msg sayin "Sub report cannot be displayed"
any solution????

View 4 Replies View Related

Report On Users Accessing Reports

May 13, 2008

All,

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.

View 5 Replies View Related

Accessing Registery Key In SSRS Reports

Feb 29, 2008



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.

View 3 Replies View Related

Accessing SQL 2005 Reports Externally

Mar 28, 2008



Hello,

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."

Any ideas on how i could resolve this issue?

Thanks.

View 18 Replies View Related

Links That Pass Parameters To Other Reports?

Dec 27, 2007



How do you create a link on one report to run another report passing a parameter (or two).

View 4 Replies View Related

Accessing Sub Reports In Asp.net. (Drilling Inside The Other Report)

Jan 31, 2008

Dear Technical Team,

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.

Please help
Regards,
Nazim

View 1 Replies View Related

Security Errors Accessing Reports From Sever

Jun 4, 2007

First of all, following is my setup.

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?



Thanks

rajeshbhavsar

View 3 Replies View Related

Externally Accessing Localmachine To View Reports

Feb 15, 2007

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

View 1 Replies View Related

How To Pass Multiple Parameters Across Reports In SSRS

Apr 25, 2008



Hi,



I am working on SSRS. I need to open a new report from one report when user clicks on some

particular summarized count link.


It is a sort of drilled down report. I am not getting how to pass the respective Ids (more

than one) to the next report when user clicks on the summarized link in the 1st report.

These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from

the database.

View 3 Replies View Related

Sub: How To Pass Multiple Parameters Across Reports In SSRS

Apr 25, 2008

Hi,


I am working on SSRS. I need to open a new report from one report when user clicks on some

particular summarized count link.



It is a sort of drilled down report. I am not getting how to pass the respective Ids (more

than one) to the next report when user clicks on the summarized link in the 1st report.

These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from

the database.

View 1 Replies View Related

Showing Empty Cells From OLAP Cube In Report Builder Reports

Nov 5, 2007

Hi all,

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?

Any help appreciated.

View 2 Replies View Related

Crystal Reports And ODBC Getting Access Denied On Pass Thru

Jul 27, 2006

Hello,



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.



thanks

View 1 Replies View Related

Accessing SQL Server Database From Crystal Reports - Being Prompted To Log On Every Time?

Oct 4, 2005

Hello, this is my first time posting here.

View 1 Replies View Related

Excel 2007 Issue (KB 929766) For Cube Reports; Doesn't Make Any Sense

Mar 12, 2007

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?

Any thoughts?

View 6 Replies View Related

How To Pass Master Reports (fields!OrderId) Value To A Subreport As A Parameter Value

May 11, 2008

How to pass master reports (fields!OrderId) value to a subreport as a parameter value.


if i right click the subreport control on the main report layout, i do see parameters tab, under that would like to pass the (fields!orderID).


Thanks for the info.


View 4 Replies View Related

NULL Values In Cube Dimension

Apr 16, 2004

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?

Thank you!

View 2 Replies View Related

How To Pass Values In Dynamic Sql

Jan 23, 2007

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.

View 3 Replies View Related

How To Pass Values In Q Analyzer

Aug 10, 2006

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

View 6 Replies View Related

How Do I Pass A Group Of Values In A Parameter? Like Where Id In (@Value)

Mar 30, 2006

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

View 3 Replies View Related

Pass The Mulitple Values To A Query

Jun 12, 2001

Hi!

Can anybody help me in solving this problem

SCRIPT

declare @var1 varchar(10)
set @var1 = '1,4,9,10'
select @var1
select TGNO, TGNAME from carriers where TGNO in (@var1)

RESULT

----------
1,4,9,10

(1 row(s) affected)

Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '1,4,9,10' to a column of data type smallint.

I want to pass a list of values to a column of DATA Type smallint.

Thanks in advance.

View 1 Replies View Related

Pass The Mulitple Values To A Query

Jun 13, 2001

What is the way to pass the values throug varibale as shown below?

SCRIPT

declare @var1 varchar(10)
set @var1 = '1,4,9,10'
select @var1
select TGNO, TGNAME from carriers where TGNO in (@var1)

RESULT

----------
1,4,9,10

(1 row(s) affected)

Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '1,4,9,10' to a column of data type smallint.

I want to pass a list of values to a column of DATA Type smallint.

View 1 Replies View Related

How Do You Pass Values From VB To A DTS Global Variable?

Jun 24, 2002

I am trying to pass a value from a VB Custom Task to
a DTS. The DTS doesn't get the value and I do not understand
why. Snipet follows:

oPKG.GlobalVariables.Item("gsAnyTypeData").Value = "Hello World"
oPKG.GlobalVariables.AddGlobalVariable ("gsAnyTypeData"), "Hello World"
oPKG.LoadFromSQLServer ".", , , 256, , , , DTSpackage_to_execute
oPKG.Execute

I've tried declaring the global variable in the called DTS and
I've tried without it. Neither contain the value when the DTS is
executed.

Thanks for your time and help,
Martin

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved