final product

Hi all,

I have build a small asp.net websites where I want to generate some reports, but I have some questions?

Do you have to install the reporting services on the system where the final product will be hosted?

I have tried to use the report viewer to build a local delivery report, but I can not see the printer, any solution to that ?

Yes you have to install Microsoft Report Viewer.

But, if you create a kit for your web site=> properties on the kit =>Prerequisites=>chech ""Microsoft Reports for VS 2005" (something like this) and also

VERY IMPORTANT chech the second options from down "Telecharger les components requires a partir de l'amplacement de mon application" (sorry but i have VS in french)

Thease option will create the kit with all you nead to run reports after the instalation!!!

Popa Iulia

__________________________________

MCP.MCAD.MCSD

|||

iuliax:

Yes you have to install Microsoft Report Viewer.

Popa Iulia

__________________________________

MCP.MCAD.MCSD

I know that report viewer has to be installed? What about SQL services?

When using report viewer with asp.net local delivery the printer icon disappear?

any solution to that?

|||

Hi there,

I am not sure if could solve that problem of not seeing printer on the reportviewer.

If you have solve the problem that please let me know, as I am also having same problem here. The printer does not appear on the toolbar. Even though I switched the Showprinter properties of reportviewer true.

Thanks

MEmam

kms

|||

Hi there,

I am still waiting for the any answer.

Please if there anyone is welcome to help me to solve the problem of why I can't see printer option on my reportviewer.

Thanks in advance if there is anyone who could provide me the answer. It will be a great help.

regards,

Emam

Final Part - Min() Function

I have the final part of my report to complete. Yesterday I posted the most confusing set of threads ever on a public forum. So to cut the confusion, I am starting this thread again as now I know exactly what I need.

I have two tables each of which have a function that gathers data from them based around a set of parameters. This is then manipulated by a third function and the results of which are passed into a Stored Procedure. Here is the T-SQL syntax used for the SP;

SELECT fnWTRalldataReport.Areacode, fnWTRalldataReport.siteref, fnWTRalldataReport.estatename, fnWTRalldataReport.Securitised,

fnWTRalldataReport.unitref, fnWTRalldataReport.unittype, fnWTRalldataReport.unittype_count, fnWTRalldataReport.tenantname,

fnWTRalldataReport.tenantstatus, fnWTRalldataReport.tenantstatus_count, fnWTRalldataReport.unitstatus, fnWTRalldataReport.unitstatus_count,

fnWTRalldataReport.floortotal, fnWTRalldataReport.floortotocc, fnWTRalldataReport.floorspaceperc, fnWTRalldataReport.initialvacarea,

fnWTRalldataReport.initialvacnet, fnWTRalldataReport.TotalRent, fnWTRalldataReport.NetRent, fnWTRalldataReport.FinalRtLsincSC,

fnWTRalldataReport.rentrolldiscperc, fnWTRalldataReport.netrentpersqft, fnWTRalldataReport.ErvTot, fnWTRalldataReport.tenancyterm,

fnWTRalldataReport.landact, fnWTRalldataReport.datadate, fnWTRalldataReport.div_mgr, fnWTRalldataReport.portfolio_mgr,

fnWTRalldataReport.propcat, fnWTRalldataReport.budgeted_net_rent, fnWTRalldataReport.budgeted_occupancy,

fnWTRbudgetdata_1.budgeted_net_rent AS budget_rent, fnWTRbudgetdata_1.budgeted_occupancy AS budget_occ

FROM dbo.fnWTRalldataReport(@.dt_src_date, @.chr_div, @.vch_portfolio_no, @.vch_prop_cat) AS fnWTRalldataReport LEFT OUTER JOIN

dbo.fnWTRbudgetdata(@.dt_src_date) AS fnWTRbudgetdata_1 ON fnWTRalldataReport.siteref = fnWTRbudgetdata_1.site_ref

The result of this SQL places a value for budget_rent and budget_occ against every row that the 1st function's result set. What I want to achieve is that where the site_ref is equal in both functions results, I want it to place the budget_rent & budget_occ value against the first row only of each site_ref only.

To explain briefly the structure. Table one has various fields including site_ref and unit_ref. There are many unit_ref's per site_ref in this table. Table 2 has only site_ref and budget info. Someone yesterday suggested that I could achieve this my using something along the lines of the Min() function e.g. Min(unit_ref).

Could someone please elaborate on this for me. I have gone through my SQL book and read about min() and also BOL, but I can't quite work the syntax out to put the budget info against only one line per site based around the lowest unit_ref per site_ref.

This might seem confusing, but it is easier to read than the other thread I assure you.

Regards

If this is just for display, then I would suggest you just let the UI sort through this.

If not, you could try using temp tables to hold the results of the function calls:

select *, cast(0 as bit) as maxUnitRef
into #FNWTRALLDATAREPORT
from dbo.FNWTRALLDATAREPORT(parms)

... same with the other, no added column though

Then

UPDATE #FNWTRALLDATAREPORT
SET maxUnitRef = 1
WHERE unitRef = (select max(unitRef)
from #FNWTRALLDATAREPORT as f2
where f2.siteRef = #FNWTRALLDATAREPORT.siteRef)

Then in the final output just:

case when maxUnitRef = 1 then #FNWTRALLDATAREPORT.BUDGETED_NET_RENT else null end as BUDGETED_NET_RENT..

Does this make any sense? You might also be able to fabricate the maxUnitRef column in the function. Either way it is far easier to do this in the UI, instead of using SQL.

|||

Thank you for this, I will sort through it now. Yes in answer to your question, it is purely for display purposes only on the report.

Regards

Final Model

I am creating a report model and have to keep going back and forth between VS2005 and Report Builder to see what the "Final" report is going to look like. I am using folders to group together some really nasty list of fields, denormalizing lookup tables, etc. Is there any way to see this without having to reload Report Builder each time? I have thought about an XSLT for the SMDL, but that is some seriously ugly XML (especially with the denormalizations).

R

If you leave RB open, create a new report based on some other model, then create a report on the model you're working on again, it will clear the cache and you will see your changes.

Inside Model Designer, you can edit the Expression property of any attribute, and the formula dialog will have a model explorer showing all your most recent changes. However, it will be rooted on the entity to which that attribute belongs, so it may take some navigation to see changes you've made to another entity.

Hope that helps!

Final Attemp -Sql Stored Procedure Tough Question

I have a procedure I need to get the values out of..I am using outputs...I have no idea why it wont work.....I need all values listed in the select part of procedure...

CREATE procedure dbo.Appt_Login_NET
(
@.LoginName nvarchar(15),
@.Password NvarChar(15),
@.UserName nvarchar(15)Output,
@.UserPassword nvarchar(15)Output,
@.UserClinic nvarchar(3)Output,
@.UserTester bit Output
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @.LoginName
and
UserPassword = @.Password

GO

my vb.net code to retrive this info is

Private Sub Button1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ServerClick
Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmd As New SqlCommand
Dim parmuser As SqlParameter
Dim parmus As SqlParameter
Dim parmpass As SqlParameter
Dim parmtest As SqlParameter
Dim struser As String
Dim strpass As String
Dim strclinic As String
Dim strnames As String
Dim tester As String
strpass = txtPass.Value
struser = txtUser.Value
cmd = New SqlCommand("Appt_Login_NET", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@.LoginName", struser)
cmd.Parameters.Add("@.Password", strpass)
parmus = cmd.Parameters.Add("@.UserName", SqlDbType.NVarChar)
parmus.Size = 15
parmus.Direction = ParameterDirection.Output
parmuser = cmd.Parameters.Add("@.UserClinic", SqlDbType.NVarChar)
parmuser.Size = 3
parmuser.Direction = ParameterDirection.Output
parmpass = cmd.Parameters.Add("@.UserPassword", SqlDbType.NVarChar)
parmpass.Size = 15
parmpass.Direction = ParameterDirection.Output
parmtest = cmd.Parameters.Add("@.UserTester", SqlDbType.Bit)
parmtest.Size = 1
parmtest.Direction = ParameterDirection.Output

con.Open()
cmd.ExecuteNonQuery()
If Not IsDBNull(cmd.Parameters("@.UserName").Value) Then
Label1.Text = cmd.Parameters("@.UserName").Value()
Else
Label1.Text = "No Results Found"
End If

con.Close()
End Sub

Why does this always show as "DBNUll" I get nothing when I debug any of my parm variables.I searched the SQl Server and in Query analyzer instead of the output variables in the procedure being just outputs they are input/outputs...............What does it take to get this working? Do I need a conversion datatype I would prefer I gain the values and store them in variables.....CREATE procedure dbo.Appt_Login_NET
(
@.LoginName nvarchar(15),
@.Password NvarChar(15)
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @.LoginName
and
UserPassword = @.Password
------------

You are trying to get all the information based on user/pass information passed into the SP. Then you won't need to have the other parameters in your SP since they are fields in your table.. Data contained in the table will get returned based on the user/pass credentials that were provided.

I am sure you were getting an error on the other parameters since you didn't provide data to those...

Then using a DataReader just use the field name to get the values out of them

Dim reader as SqlDataReader = command.ExecuteReader(...)

IF reader.Read THEN
string UserName = CType(reader["UserName"], string)
END IF|||So I should leave that procedure the way I had it like you have it and then how exactlly do I extract for example the User Clinic if I used this data reader thing.How might I set that up.....Thanks|||UserClinic is the clinic at which this user works then you can do the same thing I did in that IF statement...

dim user as string
dim uClinic as string
dim uTester as boolean

IF reader.Read THEN
user = CType(reader("username"), string)
uClinic = CType(reader("UserClinic"), string)
uTester = CType(reader("UserTester"), boolean)
END IF

sorry in previous post I used [] in the reader ordinal call...[] is a C# thing|||No, no no. You're are correct to use OUTPUT params and not to use a result set. You are trying to get a few columns of data for a single row. OUTPUT params is exactly the correct thing to use. Your problem is that you've not written your proc correctly. Open up Query Analyser, write the proc and test the thing. Once its working then you can then worry about the .net code.|||I have tried this code but I get an error on "Inncorrect Syntax" I really cant figure this out..help please...

here is the source error
Source Error:

Line 86: cmd.Parameters.Add("@.LoginName", txtUser.Value)
Line 87: cmd.Parameters.Add("@.Password", txtPass.Value)
Line 88: reader = cmd.ExecuteReader <--is highlighted in red
Line 89: If reader.Read Then
Line 90: user = CType(reader("UserName"), String)

my VB.net Code.......

Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim user As String
Dim uclinic As String
Dim uTester As Boolean
Dim reader As SqlDataReader
Dim cmd As SqlCommand

con.Open()
cmd = New SqlCommand("Appt_Login", con)
cmd.Parameters.Add("@.LoginName", txtUser.Value)
cmd.Parameters.Add("@.Password", txtPass.Value)
reader = cmd.ExecuteReader
If reader.Read Then
user = CType(reader("UserName"), String)
uclinic = CType(reader("UserClinic"), String)
End If

End Sub|||view post 510022|||Thanks guys for all the help....I got it working