Calling Reports without URL

Hi,

I am working in SSRS2005.creating reports using VS2005 and deploying reports in report server(http://localhost/reportserver/).
I am using URL for calling reports.
Now,I am working on another application which is runing on session.I am asked to develop a report for this session application.In this application security is an important factor.

Without explicitly calling an URL how can I call a report in a button click event .I tried using Iframe.Is there any other way.

Help me in this regard.

Thanks,
Prabaharaprabu

You could use the report execution web service to render the report and then stream the result via your own website. See the docs here.

http://msdn2.microsoft.com/en-us/library/ms155071.aspx

Let me know if you need more information.

Calling parameterised views from VB

I'd really appreciate your help creating a parameterised view in SQL
Server, which I can then invoke from VB5. I'm using DAO and ODBC to
connect to SQL Server 2000.

I can open Tables and Views, but can't find how to create or use a
"query" parameter. If I use the "?" (in the SQL Enterprise manager
View design mode), the syntax checker verifies the query ok, but I
then get the message "Parameters cannot be used in this query type".
It doesn't like the %var% terminology at all here.

I am sure I could build the full query in VB and save it to the Server
"on-the-fly", but this partly defeats the object, doesn't it?

I could really do with a couple of pointers: (a) how to define the
view/query in Ent Manager and (b) how to invoke it from VB, supplying
the parameter.

Thanks ever so in anticipation.

PhilPhil Sowden (philsowden@.dataservicesltd.co.uk) writes:
> I'd really appreciate your help creating a parameterised view in SQL
> Server, which I can then invoke from VB5. I'm using DAO and ODBC to
> connect to SQL Server 2000.
> I can open Tables and Views, but can't find how to create or use a
> "query" parameter. If I use the "?" (in the SQL Enterprise manager
> View design mode), the syntax checker verifies the query ok, but I
> then get the message "Parameters cannot be used in this query type".
> It doesn't like the %var% terminology at all here.
> I am sure I could build the full query in VB and save it to the Server
> "on-the-fly", but this partly defeats the object, doesn't it?
> I could really do with a couple of pointers: (a) how to define the
> view/query in Ent Manager and (b) how to invoke it from VB, supplying
> the parameter.

Views cannot be parameterized in SQL Server. However there are table-valued
functions, which are about the same thing. Example:

CREATE FUNCTION myorders (@.custid nchar(5)) RETURNS TABLE AS
RETURN (SELECT * FROM Northwind..Orders WHERE CustomerID = @.custid)
go
SELECT * FROM myorders(N'ALFKI')

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks very much indeed for your help, Erland.

I had tried this, amongst many other things. However, I didn't realise
I'd got it right, as I couldn't work out how to populate a VB recordset
object from an SQL function.

I normally (in Access) open a database object, then use something like:

lsSQL = "Select * from Northwind.Orders WHERE CustomerID = " &
llCustID
Set lrsOrders = gdbMain.OpenRecordset(lsSQL)
lrsOrders.MoveFirst

where:
gdbMain is a globally-defined database object
lrsOrders is a locally-defined recordset object

This second part of my question probably belongs in the VB or Access
newsgroups, I'm sure, but maybe you can help again, Erland?

Phil

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Phil (philsowden@.nospam.dataservicesltd.co.uk) writes:
> I had tried this, amongst many other things. However, I didn't realise
> I'd got it right, as I couldn't work out how to populate a VB recordset
> object from an SQL function.
> I normally (in Access) open a database object, then use something like:
> lsSQL = "Select * from Northwind.Orders WHERE CustomerID = " &
> llCustID
> Set lrsOrders = gdbMain.OpenRecordset(lsSQL)
> lrsOrders.MoveFirst
> where:
> gdbMain is a globally-defined database object
> lrsOrders is a locally-defined recordset object

So to call the table function you would say:

lsSQL = "Select * from myorders(" & llCustID & ")"

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's brilliant, thanks, Erland. I've applied that example to my
system and got it to work fine within SQL Server now.

However, when I try:
lsSQL = "Select * from FindCust('07940437220')"
Set lrsTemp = gdbSparc.OpenRecordset(lsSQL)

...in VB, I get "Error 3131 - Syntax Error in FROM clause". It's as if
VB doesn't recognise the function exists at all?

The identical code works fine (as a View) within SQL Server (n.b. the
customer reference I'm using is a 14 character nvarchar field).

I'm sorry to be such a novice! I also realise that this may not be the
correct forum - but it may be that you are able to help.

Thanks once again,

Phil

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Phil (philsowden@.nospam.dataservicesltd.co.uk) writes:
> That's brilliant, thanks, Erland. I've applied that example to my
> system and got it to work fine within SQL Server now.
> However, when I try:
> lsSQL = "Select * from FindCust('07940437220')"
> Set lrsTemp = gdbSparc.OpenRecordset(lsSQL)
> ..in VB, I get "Error 3131 - Syntax Error in FROM clause". It's as if
> VB doesn't recognise the function exists at all?

That right. In fact VB does not know anything about SQL at all. I don't
know what this OpenRecordset is, but that much I can tell that the
error message you get does not come from SQL Server. And since VB
does not SQL, it must be someother data source you are querying. Didn't
you mention Access? Well, I don't know anything about Access, so...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Cool. Thanks again for your help, Erland, I'm sure I'll resolve it
shortly.

Phil

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||In article <403a0e0e$0$198$75868355@.news.frii.net>, Phil <philsowden@.nospam.dataservicesltd.co.uk> wrote:
>That's brilliant, thanks, Erland. I've applied that example to my
>system and got it to work fine within SQL Server now.
>However, when I try:
>lsSQL = "Select * from FindCust('07940437220')"
>Set lrsTemp = gdbSparc.OpenRecordset(lsSQL)
>...in VB, I get "Error 3131 - Syntax Error in FROM clause". It's as if
>VB doesn't recognise the function exists at all?
>The identical code works fine (as a View) within SQL Server (n.b. the
>customer reference I'm using is a 14 character nvarchar field).
>I'm sorry to be such a novice! I also realise that this may not be the
>correct forum - but it may be that you are able to help.
>Thanks once again,
>Phil
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!

You have a table named FindCust('07940437220') in your database? Who came
up with an impossible to remember name like that?|||Rico,

Thanks for your interest.

If you're able to tell me how to design a parameterised SQL View and
call it from VB, I'd be ecstatic! I'm sure I can fix that function name
quite easily!

The SQL table is called "Cust", but I had to create a table-type
function to return a view / query of selected records with a parameter.
The "FindCust()" function returns a recordset of customers found for the
parameter value given. I thought the name was quite meaningful,
actually!

The call you're looking at is my failed attempt to call that same
function from within VB (version 5, using DAO with ODBC connection to
SQL Server).

Ideas most welcome...!

Phil

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Phil (philsowden@.nospam.dataservicesltd.co.uk) writes:
> The call you're looking at is my failed attempt to call that same
> function from within VB (version 5, using DAO with ODBC connection to
> SQL Server).

Aha, so that OpenRowset is DAO? Now, I don't know DAO, and it might be
that DAO is itself trying interpreting the SQL statement. In such case
you are probably out of luck, because DAO is too old to know about
table-value functions added to SQL2000. I would guess that DAO is not
equipped to handle that. But if it's ODBC which performs the syntax
check, then it's only a matter of getting version 2.6 or later of the
MDAC. They are available at:
http://www.microsoft.com/downloads/...&DisplayLang=en

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Call RS from

Hi,
I am creating an asp.net app and was wondering how I can call up a report
created in RS from my app?
Does anyone have any websites or docs on how to use RS?
Thanks,
JJTake a look at the ReportViewer sample code from Microsoft. It should land
in your install folder under Samples\Applications.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"JJ" <JJ@.discussions.microsoft.com> wrote in message
news:AC6C1281-C687-41A0-A37D-11E1C2F7F71D@.microsoft.com...
> Hi,
> I am creating an asp.net app and was wondering how I can call up a report
> created in RS from my app?
> Does anyone have any websites or docs on how to use RS?
> Thanks,
> JJ|||You can find more info about RS here:
www.ReportingServicesFaq.com
And there is a RS Wiki community being built here:
www.ReportingServicesFaq.com/ow.asp
Jeff A. Stucker wrote:
> Take a look at the ReportViewer sample code from Microsoft. It should land
> in your install folder under Samples\Applications.
> Cheers,
>|||go thorugh this link a wonderful article on how to: reporting services
http://www.15seconds.com/issue/041013.htm
raj.
"RS Guy" wrote:
> You can find more info about RS here:
> www.ReportingServicesFaq.com
> And there is a RS Wiki community being built here:
> www.ReportingServicesFaq.com/ow.asp
>
> Jeff A. Stucker wrote:
> > Take a look at the ReportViewer sample code from Microsoft. It should land
> > in your install folder under Samples\Applications.
> >
> > Cheers,
> >
>

calendar control for parameters in SQL 2000 Reporting?

As the subject says, I'd like to know if calendar controls are available for
reporting creating using VS2003 running on SQL 2000 (for internal reasons we
cannot upgrade to SQL 2005 for some time)No. You need RS 2005. Note that you can go to RS 2005 without upgrading your
database to SQL 2000. You need a SQL Server 2005 license but you can keep
your database at 2000 and just upgrade RS to RS 2005. I did this (although I
have since upgrade the db) and it works and is fully supported.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KBlount" <KBlount@.discussions.microsoft.com> wrote in message
news:B87C7B57-AF42-4C88-B762-69F3522F20FB@.microsoft.com...
> As the subject says, I'd like to know if calendar controls are available
> for
> reporting creating using VS2003 running on SQL 2000 (for internal reasons
> we
> cannot upgrade to SQL 2005 for some time)|||Thanks for the response Bruce. I'm currently investigating using .NET
calendar controls and integrating them with SSRS 2000 - we'll see how hairy
that gets! heh
Thanks for the info about being able to use RS 2005 on SQL 2000. I'll pass
on your post to my IT team and let them investigate that further. It would be
so convenient to simply change a paramter type to DateTime ;)
Cheers
Kevin
"Bruce L-C [MVP]" wrote:
> No. You need RS 2005. Note that you can go to RS 2005 without upgrading your
> database to SQL 2000. You need a SQL Server 2005 license but you can keep
> your database at 2000 and just upgrade RS to RS 2005. I did this (although I
> have since upgrade the db) and it works and is fully supported.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "KBlount" <KBlount@.discussions.microsoft.com> wrote in message
> news:B87C7B57-AF42-4C88-B762-69F3522F20FB@.microsoft.com...
> > As the subject says, I'd like to know if calendar controls are available
> > for
> > reporting creating using VS2003 running on SQL 2000 (for internal reasons
> > we
> > cannot upgrade to SQL 2005 for some time)
>
>|||For further ammunition. RS 2005 has end user sorting, renders to pdf and
excel much better (I used to regularly have my server lock up and that does
not happen anymore). It also has multi-select parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KBlount" <KBlount@.discussions.microsoft.com> wrote in message
news:2C79A69D-226E-4D64-BF0A-20AE98D76FFE@.microsoft.com...
> Thanks for the response Bruce. I'm currently investigating using .NET
> calendar controls and integrating them with SSRS 2000 - we'll see how
> hairy
> that gets! heh
> Thanks for the info about being able to use RS 2005 on SQL 2000. I'll pass
> on your post to my IT team and let them investigate that further. It would
> be
> so convenient to simply change a paramter type to DateTime ;)
> Cheers
> Kevin
> "Bruce L-C [MVP]" wrote:
>> No. You need RS 2005. Note that you can go to RS 2005 without upgrading
>> your
>> database to SQL 2000. You need a SQL Server 2005 license but you can keep
>> your database at 2000 and just upgrade RS to RS 2005. I did this
>> (although I
>> have since upgrade the db) and it works and is fully supported.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "KBlount" <KBlount@.discussions.microsoft.com> wrote in message
>> news:B87C7B57-AF42-4C88-B762-69F3522F20FB@.microsoft.com...
>> > As the subject says, I'd like to know if calendar controls are
>> > available
>> > for
>> > reporting creating using VS2003 running on SQL 2000 (for internal
>> > reasons
>> > we
>> > cannot upgrade to SQL 2005 for some time)
>>|||Thanks for the extra info, Bruce.
I attended (and passed: 88%! hehe wooo) the LearningTree "SQL Server
Reporting Services: Hands-On" course this time last year, and naturally I've
not had a project to use what I learned since.. until now. Now that you've
mention those other benefits, especially the multi-select parameter, I can
definitely see a need for the upgrade, rather than just a :wouldn't it be
nice".. the email to my IT team was sent earlier today... time will tell.
"Bruce L-C [MVP]" wrote:
> For further ammunition. RS 2005 has end user sorting, renders to pdf and
> excel much better (I used to regularly have my server lock up and that does
> not happen anymore). It also has multi-select parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KBlount" <KBlount@.discussions.microsoft.com> wrote in message
> news:2C79A69D-226E-4D64-BF0A-20AE98D76FFE@.microsoft.com...
> > Thanks for the response Bruce. I'm currently investigating using .NET
> > calendar controls and integrating them with SSRS 2000 - we'll see how
> > hairy
> > that gets! heh
> >
> > Thanks for the info about being able to use RS 2005 on SQL 2000. I'll pass
> > on your post to my IT team and let them investigate that further. It would
> > be
> > so convenient to simply change a paramter type to DateTime ;)
> >
> > Cheers
> >
> > Kevin
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No. You need RS 2005. Note that you can go to RS 2005 without upgrading
> >> your
> >> database to SQL 2000. You need a SQL Server 2005 license but you can keep
> >> your database at 2000 and just upgrade RS to RS 2005. I did this
> >> (although I
> >> have since upgrade the db) and it works and is fully supported.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "KBlount" <KBlount@.discussions.microsoft.com> wrote in message
> >> news:B87C7B57-AF42-4C88-B762-69F3522F20FB@.microsoft.com...
> >> > As the subject says, I'd like to know if calendar controls are
> >> > available
> >> > for
> >> > reporting creating using VS2003 running on SQL 2000 (for internal
> >> > reasons
> >> > we
> >> > cannot upgrade to SQL 2005 for some time)
> >>
> >>
> >>
>
>

Calculating percentages

I am creating a report like this.

I have totals by SalesPerson.

FOr each salesperson section, I have a total for the type of orders they have such as

Salesperson Code Total Sales

XXXX 1000.00 ( this is sum field by SalesPerson). This is group section

Type of order Sales$ for order % of total

Retail 300 (Sum field again by SP and Type of Order) Group Section

Resale 500

Other 200

I am trying to calculate the % of total field for each order type as Sales$per Order/TotalSales per Salesperson, however when I put in the expression it is using the Total Sales for the whole report and it comes up with a low number. DO any of you have any ideas on how to make this work, so that I can calculate the correct percentage ?

Thanks

In the percentage expression, add the grouping scope for the data that you would like the total sales to aggregate over. For example,

=Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value, "SalesPerson") * 100

Ian

Calculated values and database design.

Hi everybody,

I need some help on calculated values in my database design.

I'm creating an accounting / business management application with the
usual modules like A/R, A/P, Inventory, etc.

I don't know how to handle calculated values in fields like "Customer
Balance", "Inventory Item Qty on Hand", "Inventory Item Qty Last cost"
and other similar.

I don't want to create fields in the database to store these values
but I want to create them on the fly instead. Another thing I prefer
to do is use code in my application (vb.net) not T-SQL to do this
(stored procedure or views), because this way I'm moving business
logic into SQL; so far all the logic is located in a Business Rules
DLL.

My question is how can I do that and at the same time achieve "easy"
joins.

For example:
Every time I retrieve some customer's info I need his balance. Let's
say if my query is:

SELECT CustomerID, CustomerName, Address from Customers
Where CustomerID = xxxxxx'

How can I retrieve the balance, when is calculated from other fields
in other tables, such as Invoices, Credit Memos and Cash Receipts?

I thought to use views and add a join to the above query. Is that good
or bad, performance wise?

What other options I have?

Looking forward for your answer.

Thanks in advanced.
StefanosBusiness logic belongs server-side if it involves summarizing data.
Otherwise you might as well use a file-server database like Access. You
don't want to have to retrieve all the data just to add it up at the client.

I can only guess at your table structure and keys but maybe this example
will help:

SELECT C.customerid, C.customername, C.address,
SUM(T.trans_amount) AS balance
FROM Customers AS C
JOIN Transactions AS T
ON C.customerid = T.customerid
AND C.customerID = 'xxxxxx'

--
David Portas
SQL Server MVP
--|||OOPS! That wasn't very good!

Correction:

SELECT C.customerid, C.customername, C.address,
SUM(T.trans_amount) AS balance
FROM Customers AS C
JOIN Transactions AS T
ON C.customerid = T.customerid
AND C.customerID = 'xxxxxx'
GROUP BY C.customerid, C.customername, C.address

--
David Portas
SQL Server MVP
--|||David,

Thanks for your reply.

I'm following a "fat-client" implementation.
I agree that business rules belong to the server or in my case the
"fat client".
I don't want to put any logic on the database in the form of sprocs or
similar;
I have enough layers in my design already and I don't want to be tied
in a particular RDBS (currently using MS SQL 2000).

A query like the one you posted is what I've thinking to do, but I
hoped maybe someone has a better solution.
Queries like these are very hard to create in multiple joins.

In your example the table "Transactions" isn't enough.

I have to get totals from:
* InvoiceHeader
* CreaditMemoHeader
* CashReceiptsHeader

Imagine now if I have to get the balance in a query that has another
2-3 joins on it.

Stefanos

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<OpidnUri8Z4fmRndRVn-sQ@.giganews.com>...
> OOPS! That wasn't very good!
> Correction:
> SELECT C.customerid, C.customername, C.address,
> SUM(T.trans_amount) AS balance
> FROM Customers AS C
> JOIN Transactions AS T
> ON C.customerid = T.customerid
> AND C.customerID = 'xxxxxx'
> GROUP BY C.customerid, C.customername, C.address

Calculated Members

I have a reporting services report that I'm creating from an Analysis Services cube. I created two calculated memebers. When I put either one of the calculated members in the report and try to render the query or generate the report, I get an error: Memory Error: Allocation Failure: Not enough storage is avaliable to process this command. I have 21 GB of hard drive space.
I do only have 1 GB or RAM, put I thin that should be enought. Any ideas as to why I get this problem when I run the report including a calculated member? BTW, the report works okay (not great in regards to timing) when I don't include the calculated memebers.
Thanks,Hi
I am facing the exact issue.
Can anyone point me to the right direction?

Thankyou
Vidya|||

We you run the MDX directly against the cube, how many rows are returned (with and without the calculated members)?

|||When i Run the MDX Expression direcly against the cube with the Calculated Measure, i simply get the Error .
when i dont use the Calculated Measure , i get the result in just 2 seconds.

Thankyou
VIdya

Calculated Members

I have a reporting services report that I'm creating from an Analysis Services cube. I created two calculated memebers. When I put either one of the calculated members in the report and try to render the query or generate the report, I get an error: Memory Error: Allocation Failure: Not enough storage is avaliable to process this command. I have 21 GB of hard drive space.
I do only have 1 GB or RAM, put I thin that should be enought. Any ideas as to why I get this problem when I run the report including a calculated member? BTW, the report works okay (not great in regards to timing) when I don't include the calculated memebers.
Thanks,Hi
I am facing the exact issue.
Can anyone point me to the right direction?

Thankyou
Vidya|||

We you run the MDX directly against the cube, how many rows are returned (with and without the calculated members)?

|||When i Run the MDX Expression direcly against the cube with the Calculated Measure, i simply get the Error .
when i dont use the Calculated Measure , i get the result in just 2 seconds.

Thankyou
VIdya

Calculated Member to count fact table

Hi,

I'm having problems creating a calculated member to count specific rows of my fact table.

I need the number of rows matching a filter criteria. I tried it with using the filter expression but without any success.

Lets assume the following:

ID ColumnX ColumnY

1 A ABC1

2 B ABC2

3 A DEF1

DimensionX is based on ColumnX

DimensionY is based on ColumnY

Totals is a measure with a row binding lets say count(ID)

TotalsNoBinding is a measure with row binding and NO aggregation (None)

I would assume that a Calculated measure based on

Count(Filter([Measures].[TotalsNoBinding], [DimensionX].[A ]))

would result the count of "rows" affected by filtering the DimensionX by just allowing the Value A.

I guess I have a understanding problem somewhere. I hope someone can give the right hint to fix the problem. What would be the right way to implement this?

Thanks.

ErtanO

Try to use named calculation in datasource view in spite of create calculated member...

regards!

|||

PedroCGD

Thank you...that was the right hint!

Calculated member question

I have a problem creating a calculated member correct.

Business problem: I have an organisation using MS Project and registering their project time against different projects (cube 1). Every organisation unit part of a project will be charged cost internally based on an agreement with a percentage split (write enabled cube 2). The project "customers" are the same organisation units as the participant organisation units.

Cube 1: Projects and customer cost

Cust1 Cust2
Proj1 100 200 (total project cost:300)
Proj2 10 50 (total project cost: 60)

Cube 2: Project charging %

Cust 1 Cust2
Proj1 50% 50%
Proj2 40% 60%

Now I want cube 3 (virtual cube) calculate how much each customer should be charged;

Cube3: Project cost "invoice"

Cust 1 Cust 2
Proj1 150 150 (50% of 300)
Proj2 24 36

But if I in cube 3 add a custom member like this:

[Measures.Cost] x ([Measures.Percentage] / 100)

I end up with the wrong result:

Cust 1 Cust 2
Proj1 50 100 (50% of 100 and 200 from cube 1, not based on 300)
Proj2 4 30

Can anyone help? How should I write the formula?

/Erik

Try somehting like this...

([Measures.Cost],[Customers].[Customers].[All]) x ([Measures.Percentage] / 100)

...basically telling the MDX to get the Cost for All customers, not to use the current customer context.