how to create report without Report wizard

Hi,

Whenever I tried creating reports under-->BUsiness Intelligence Projects-->REport wizard or Report type.Everytime Report wizard opens.What i am trying to say is I want to build manually(Like in crytsal reports).But I don't see any option.One more Question---Once after Designing the table in Report Wizard,like Page fields,Group fields,Diaplay Fields,Is there any option that I can add more fields OR Change Existing fields.Do I have to do in Report designer Layout??I don't see any option for doing in SSRS 2000.Sorry for small questions,I am very new to SSRS2000 ,and programming too.Thank you.

HI,srijyothi:

You can check out this article about:

Generate Ad-hoc Reports with Microsoft Reporting Services 2005

http://www.devx.com/dbzone/Article/28047/1954?pf=true

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a

convenience to you. Microsoft does not control these sites and has not tested any software or information found on these

sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or

information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions

you to make sure that you completely understand the risk before retrieving any software from the Internet.

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

HI,srijyothi:

We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

How to create report using WebService as DataSource.

Hi,

We created a simple web serivce having a Web Method which accepts a string parameter and returns "Hello World" concatenated with parameter value. We tested this web service by browsing it through IE and it was working fine by accepting a parameter and displaying the "Hello World " concated with the value given through the parameter when clicked on "Invoke" button.

We now created a Reporting Services 2005 project using BI having a report for which the data source will be the web service which is mentioned above. We defined a parameter for the report which will pass the value to the webservice and the report will display the returned value from the web service.

When we have done the report and executed by giving a value to the parameter and clicking on view report button it was displaying the "Hello World" but the parameter which we passed to the web service is not getting concatenated. When we tried to trace the application we could see that the value passed to the web service web method was comming as null. So kindly help me where I went wrong in developing such a kind of report which is not passing a parmeter to webservice.

Kindly provide us the solution in order to make this issue to be resolved.

Thanks in Advance.I can concur with VDeevi, I was able to recreate the exact same issue. In my case I'm passing parameters to a web service that is returning a dataset. When I set a default value in the webmethod it returns a dataset successfully. My problem is also that the parameters are not getting passed to the web service.

What I think is strange is that I've done the BOL sample with the SSRS web service Listchildren method and it works successfully. Does that mean that the problem is not in SSRS but in my implementation of the web service?

This works:

<WebMethod()> _
Public Function GetTestDataset() As System.Xml.XmlElement
Dim TestXDataset As New TestXDataset()
TestXDataset.Fill(1)
Dim xdd As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(TestXDataset)
Dim docElem As System.Xml.XmlElement = xdd.DocumentElement
Return docElem
End Function

This doesn't work:

<WebMethod()> _
Public Function GetTestDataset(ByVal ID As Integer) As System.Xml.XmlElement
Dim TestXDataset As New TestXDataset()
TestXDataset.Fill(ID)
Dim xdd As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(TestXDataset)
Dim docElem As System.Xml.XmlElement = xdd.DocumentElement
Return docElem
End Function

|||

It's probably not your implementation, but in the parsing specifics of the SoapAction and Namespace.

For example, if your SoapAction is http://tempuri.org/HelloMyNameIs, we split it into http://tempuri.org and HelloMyNameIs throwing away the separating / character.

But if you look at the way webservice is defined, the namespace is actually http://tempuri.org/ - with a trailing /.

So in this case, when method name/namespace can not be correctly derived from SoapAction and when SoapAction can not be correctly derived from method name (in the above case we would get http://tempuri.org//HelloMyNameIs) you should use both <Method …/> and <SoapAction …/>, e.g.

<Query>
<Method Namespace="http://tempuri.org/" Name="HelloMyNameIs"/>
<SoapAction>http://tempuri.org/HelloMyNameIs</SoapAction>
</Query>

We are working on updates to the documentation.

|||Thanks Brain, it really helped us to solve this issue.

Thanks once again,|||Worked for me as well. Thanks a lot!

How to create report parameter programmatically?

Hi,

I'm working on custom report manager. It manages "report entities" and

"report templates" (actually, RDLs uploaded on the server) and stores

one-to-many relation between them. The task is to store

"MasterEntityID" report parameter in every RDL and keep it up in actual

state whether RDL is being assigned to another entity or new RDL is

being uploaded and assigned. I've covered the first issue with

SetReportParameters() web method, but how should I deal with the second

one? Uploaded RDL may be short of the param, so I have to add it

programmatically while uploading.

Thanks,

AnatolyWhat exactly is the second issue? it looks like you have a custom application layer which can have complete control over the parameters.|||Yes, but actually I'm interested in adding new report parameter via web service. My application should do this if a developer of the uploading report hasn't defined necessary parameter manually. Is it possible?|||

The new parameter must be added to the report definition under the <ReportParameters> element. SetReportParameters won't let you configure the new parameter if it is not prior added to the report definition. This makes sense considering that you will probably need also to link the parameter to the report query. In fact, you won't need SetReportParameters for new parameters b/c once you add the parameter to RDL and call SetReportDefinition, the parameter will be created automatically for you.

As a side note, if you your application requires extensive reading/writing to RDL, you may benefit from buidling an object model on top of RDL that knows how to deserialize/serialize from/to RDL to avoid tedious XMLDOM programming and XPATH references.

|||

Teo, thanks for your clear reply. I'd think of object model. Mostly because I'm going to generate some rdl templates (with datasources, datasets and queries definitions) programmatically in future.

Thank you once again and Happy New Year!)

|||Happy New Year to you too.

how to create report from multiple tables

hi all,
i want to create report from 4 to 5 tables by providing one value
through which i move from one table to other.
how that is possible . can any body help me .
looking for reply
good bye
umarWhen creating the report, add all the required tables and field and link them by common column|||hi
thanks for reply but i want to create report via code by providing
one value at run time and clicking button to generate report.

Thanking u for ur help
good bye
umar|||hi
try like this

Dim cr As New SaleBalanceReport
da = New SqlDataAdapter("select s.*, d.* from saleorder s, dispatchdetails d where d.Sale_note_no=s.saleorderno and s.saleorderno='" & txtsalenote.Text & "'", con)
da.Fill(ds, "salebalance")
cr.SetDataSource(ds.Tables(0))
SBRViewer1.ReportSource = cr
SBRViewer1.Refresh()
ds.Clear()

happy programming
kameswararao

how to create report by month from vb

hi all, i m using this code to display report of month selected from combo1
it shows records but only single of current month and moreover if there is no record of a month it shows previous result.
plz help me soon
bye

///////////
mon = Combo1.Text
cmd.CommandText = "Select * from travel"
rs2.Open cmd, , adOpenStatic, adLockReadOnly
Do While Not rs2.EOF
mo = Format(rs2.Fields("report_date"), "mmm")

If mo = mon Then

crxreport.RecordSelectionFormula = "{travel.travel_id} = " & rs2.Fields("travel_id")
CRViewer1.ReportSource = crxreport

End If

rs2.MoveNext

Loop
rs2.Close

CRViewer1.Zoom (100)
CRViewer1.ViewReportu cn do it easily by inserting the required field of that month into a temp table.

& then link the temp table to ur report

i think it will do|||thanks for response,
sorry i not understand what u mean by temp table
and how i can pass month name from combo box in vb to crystal report for displaying record of that month

waiting for reply
bye|||insert a combobox containg all the month in the form where u u load ur report .
Then make a query(for ur defined month from the combobox) 2 select the data from the original .insert these data 2 a temp table which is linked 2 the crystal report.

Then load the report.

bye.|||thanks
please tell me what is problem in my code given above
or give some sample code
to pass month name from vb form
to report
thankssql

How to Create reference for Composite key

Hi All,

Can anyone tell me how to create a reference for composite key.

For ex, I have created tblEmp table successfully.

create tblEmp

(

empId varchar(100),

RegId varchar(100),

empname varchar(100),

constraint pk_add

primary key(empId, RegId)

)

And now, I am going to create another table which references the composite key.

create table tblAccount

(

acctId varchar(100) primary key,

empId varchar(100) references tblEmp(empId),

RegId varchar(100) references tblEmp(RegId)

)

But it gives error like

Server: Msg 1776, Level 16, State 1, Line 1
There are no primary or candidate keys in the referenced table 'tblEmp' that match the referencing column list in the foreign key 'FK__tbl'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Could anyone please let me know how to create reference for composite key.

Thanks in advance,

Arun.

The below code will do:

create table tblEmp( empIdvarchar(100), RegIdvarchar(100), empnamevarchar(100),constraint pk_addprimary key(empId, RegId) )create table tblAccount( acctIdvarchar(100)primary key, empIdvarchar(100) , RegIdvarchar(100),constraint fk_tblAccountforeign key ( empId, RegId)references tblEmp( empId, RegId) )

In case you've already created both tables, you can use below query to set the foreign key in the account table.

alter table tblAccountadd constraint fk_tblAccountforeign key ( empId, RegId)references tblEmp( empId, RegId)

Hope this will help.

|||

Hi,

We can also do it by

create tblEmp

(

empId varchar(100),

RegId varchar(100),

empname varchar(100),

primary key(empId, RegId)

)

And now, I am going to create another table which references the composite key.

create table tblAccount

(

acctId varchar(100) primary key,

empId varchar(100) ,

RegId varchar(100) ,

foreign key(empId,RegId) references tblEmp(empId,RegId)

)

It works perfect

|||

arunkumar.niit:

primary key(empId, RegId)

arunkumar.niit:

foreign key(empId,RegId) references tblEmp(empId,RegId)

Hey, your queries are exactly the same as what I've posted except one thing that I've given my own names to the constraints and you've left it over the SQL Server.

|||

Hi,

Thanks for your reply.

Thanks & Regards,

Arun.

How to Create Read Only Stored Procedre!

Hi,
does anyone know how to create "READ ONLY" Soted Procedure Like System
Stored Proc.
Thanks
DishanThere is nothing like the syatem stored procedures whch makes them "read only". Can you expand on
what you want to do?
You can create the proc in master and name it sp_... This will mean that you can access it from
every db without qualifying the database name. Is this what you want?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Dishan" <d700693@.hotmail.com> wrote in message news:OhjXbRtZDHA.1748@.TK2MSFTNGP12.phx.gbl...
> Hi,
> does anyone know how to create "READ ONLY" Soted Procedure Like System
> Stored Proc.
> Thanks
> Dishan
>|||No no..,
Say if I want to Encrypt Some stored procedure .Then WE Use Encryption
key word as
CREATE PROCEDURE mysp
WITH ENCRYPTION
AS ...
if we want to Create stored procedure READ ONLY. (Read only means when u
click it on Enterprise Manager . The stored procedure cant be edited!)
How can I do It? any key word LIKE..
CREATE PROCEDURE mysp
WITH READ ONLY
AS ...
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OrjQ0KuZDHA.2136@.TK2MSFTNGP10.phx.gbl...
> There is nothing like the syatem stored procedures whch makes them "read
only". Can you expand on
> what you want to do?
> You can create the proc in master and name it sp_... This will mean that
you can access it from
> every db without qualifying the database name. Is this what you want?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Dishan" <d700693@.hotmail.com> wrote in message
news:OhjXbRtZDHA.1748@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> > does anyone know how to create "READ ONLY" Soted Procedure Like
System
> > Stored Proc.
> >
> > Thanks
> > Dishan
> >
> >
>|||There is no such thing as a read only stored procedure in SQL Server. EM will not allow you to
"edit" the system stored procedures, but that is only an EM thing so you don't mess up SQL Server.
AFAIK, EM check whether a stored procedure is a system stored procedure. Use the recommendation as
posted in some other post to mark the proc as a system proc if you really want to do this, but that
is not documented - not supported.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Dishan" <d700693@.hotmail.com> wrote in message news:OvspJN5ZDHA.2932@.tk2msftngp13.phx.gbl...
> No no..,
> Say if I want to Encrypt Some stored procedure .Then WE Use Encryption
> key word as
> CREATE PROCEDURE mysp
> WITH ENCRYPTION
> AS ...
> if we want to Create stored procedure READ ONLY. (Read only means when u
> click it on Enterprise Manager . The stored procedure cant be edited!)
> How can I do It? any key word LIKE..
> CREATE PROCEDURE mysp
> WITH READ ONLY
> AS ...
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OrjQ0KuZDHA.2136@.TK2MSFTNGP10.phx.gbl...
> > There is nothing like the syatem stored procedures whch makes them "read
> only". Can you expand on
> > what you want to do?
> > You can create the proc in master and name it sp_... This will mean that
> you can access it from
> > every db without qualifying the database name. Is this what you want?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Dishan" <d700693@.hotmail.com> wrote in message
> news:OhjXbRtZDHA.1748@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > > does anyone know how to create "READ ONLY" Soted Procedure Like
> System
> > > Stored Proc.
> > >
> > > Thanks
> > > Dishan
> > >
> > >
> >
> >
>