Help in crystal reports 8.5

Hi all

I am working on Crystal reports 8.5
In one report we need to display weekdays and weekends in every month for the given date range

I mean

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Days of Service
Weekdays Number of weekdays in the month
Weekends Number of weekends in the month

And I need to calculate total of some items from Friday after 3 pm

2) In another report I am using stored procedure to get fields
As per my knowledge we cannot use other than one stored procedure in crystal reports 8.5
So I am using sub report to get some more fields other than stored procedure fields

But in main report I need to get sub- total of fields in sub report.

Could any one please help me out?You would use the Crystal Reports (http://www.saveonsupport.com) shared variable functionality. In your subreport you would have a formula field something like this:

WhilePrintingRecords;
Shared numbervar myTotal;
myTotal := myTotal + {field name}

Add this field to the subreport and suppress it.
Then in the main report you need another formula something like this:

WhilePrintingRecords;
Shared numbervar myTotal;
myTotal

The subreport keeps adjusting the total while printing records and the main report simply displays this value.

Hop this helps!|||Thanks for your reply
it works for me

help for a SQL Query

I would like to display a query result in this way
http://xavier.bouedo.free.fr/texte.gif

in the first table, it's the result that I would like to have and in the second is the result I have

If u want to see my draft
http://xavier.bouedo.free.fr/table.gif

thank for ur help
XavierXavier,

I don't know what language you use to display your data, but in C#.Net or VB.Net I would create a dataset with two tables; one with Name (the parent)(Select distinct; if needed) and the other table with the Message (the children). Then create a relationship between the two tables in your code and use nested repeaters to display the data.

I can't really think of another way.
Does anybody have more experience with SQL to have a more elegant solution?

Does this help Xavier?
KJ.|||thanks for ur solution, I use sql server 2000.|||OK. That was a very high level discussion. Let me know if you want a few code examples. I use nested repeaters, datalists or datagrids. It all depends on the circumstances.

You basically have a typical master-detail scenario. You have to manipulate the data on the .Net side to display it the way you want. SQL server can't really do it for you. (I think!)

I use C# and SQL Server 2000.

HELP ... select statment

Below is the query

It returnes the amount of products, the serial no and quanity

what i need to display in the results is alias column 'qty' only to display items

greater than 0

select name
, serialno
, sum(saleslead_product.quantity) as qty

from company
inner join saleslead on company.id = saleslead.company_id
inner join saleslead_product on saleslead.id = saleslead_product.saleslead_id
inner join product on saleslead_product.product_id = product.id

where name like 'test'

group by name
, serialno

You could add "HAVING sum(saleslead_product.quantity) > 0" at the end.|||

thanks.... been staring at the screen to long!!

Help - user permissions question

Hi,
I have a database user with read-only permissions in the database.
The user would like to be able to display a list of stored procedures
in the database. I would like to grant this user the permission to do
this, but without granting permission to *change* the sp's in any way.
Is there a way to do this?
THANKSRead only permission is enough to display the stored procedures list. Here i
s
the query:
/* It will list the names of all stored proc */
select name from sysobjects
where type = 'P'
order by name
/* It will list names of user stored procs only */
select name from sysobjects
where type = 'P' and status not like '-%'
order by name
Bob
"tootsuite@.gmail.com" wrote:

> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||What version of SQL Server?
In 2000, you can see all objects in the database.
In 2005, you can see the objects that you have permissions to use (execute).
You can grant
permission to see an object even if you cannot execute/select from etc it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1156963477.201608.121260@.74g2000cwt.googlegroups
.com...
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||SQL 2000 or SQL 2005?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||Yes, sorry forgot to mention - SQL Server 2005
I know permissions work differently on 2005 than 2000. Please tell me I
do *not* have to grant execute permission to the user for each and
every user stored procedure in the database'
Is there another way to allow the user to see?
Arnie Rowland wrote:[vbcol=seagreen]
> SQL 2000 or SQL 2005?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <tootsuite@.gmail.com> wrote in message
> news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...|||Yes, in SQL Server 2005 you can grant user "View Definition" permission so
that they can see the stored procedures but not alter or execute it.
For example:
GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
TO User;
GO
"tootsuite@.gmail.com" wrote:

> Yes, sorry forgot to mention - SQL Server 2005
> I know permissions work differently on 2005 than 2000. Please tell me I
> do *not* have to grant execute permission to the user for each and
> every user stored procedure in the database'
> Is there another way to allow the user to see?
>
> Arnie Rowland wrote:
>|||Hi,
Thanks - do I have to run this statement for each and every stored
procedure I wish to grant user permissions on? Or is there a single
stmt I can use?
Thanks
Bob wrote:[vbcol=seagreen]
> Yes, in SQL Server 2005 you can grant user "View Definition" permission so
> that they can see the stored procedures but not alter or execute it.
> For example:
> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> TO User;
> GO
>
> "tootsuite@.gmail.com" wrote:
>|||You can create a list of stored procedures from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'.
You may wish to filter out procedures starting with 'dt'.
Then using dynamic SQL, cycle through the list making the appropriate
changes.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1157052897.903180.182290@.i3g2000cwc.googlegroups.com...
> Hi,
> Thanks - do I have to run this statement for each and every stored
> procedure I wish to grant user permissions on? Or is there a single
> stmt I can use?
> Thanks
>
> Bob wrote:
>|||There is no single statement, you have to run the grant statement for every
single stored proc. Or you can write a query which would print the grant
statements for you for every stored proc.
"tootsuite@.gmail.com" wrote:

> Hi,
> Thanks - do I have to run this statement for each and every stored
> procedure I wish to grant user permissions on? Or is there a single
> stmt I can use?
> Thanks
>
> Bob wrote:
>|||Yes, that is what I thought - thanks
Arnie Rowland wrote:[vbcol=seagreen]
> You can create a list of stored procedures from INFORMATION_SCHEMA.ROUTINE
S
> where ROUTINE_TYPE = 'PROCEDURE'.
> You may wish to filter out procedures starting with 'dt'.
> Then using dynamic SQL, cycle through the list making the appropriate
> changes.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <tootsuite@.gmail.com> wrote in message
> news:1157052897.903180.182290@.i3g2000cwc.googlegroups.com...

Help - user permissions question

Hi,
I have a database user with read-only permissions in the database.
The user would like to be able to display a list of stored procedures
in the database. I would like to grant this user the permission to do
this, but without granting permission to *change* the sp's in any way.
Is there a way to do this?
THANKSRead only permission is enough to display the stored procedures list. Here is
the query:
/* It will list the names of all stored proc */
select name from sysobjects
where type = 'P'
order by name
/* It will list names of user stored procs only */
select name from sysobjects
where type = 'P' and status not like '-%'
order by name
Bob
"tootsuite@.gmail.com" wrote:
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||What version of SQL Server?
In 2000, you can see all objects in the database.
In 2005, you can see the objects that you have permissions to use (execute). You can grant
permission to see an object even if you cannot execute/select from etc it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||SQL 2000 or SQL 2005?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||Yes, sorry forgot to mention - SQL Server 2005
I know permissions work differently on 2005 than 2000. Please tell me I
do *not* have to grant execute permission to the user for each and
every user stored procedure in the database'
Is there another way to allow the user to see?
Arnie Rowland wrote:
> SQL 2000 or SQL 2005?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <tootsuite@.gmail.com> wrote in message
> news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > Hi,
> >
> > I have a database user with read-only permissions in the database.
> >
> > The user would like to be able to display a list of stored procedures
> > in the database. I would like to grant this user the permission to do
> > this, but without granting permission to *change* the sp's in any way.
> >
> > Is there a way to do this?
> >
> > THANKS
> >|||Yes, in SQL Server 2005 you can grant user "View Definition" permission so
that they can see the stored procedures but not alter or execute it.
For example:
GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
TO User;
GO
"tootsuite@.gmail.com" wrote:
> Yes, sorry forgot to mention - SQL Server 2005
> I know permissions work differently on 2005 than 2000. Please tell me I
> do *not* have to grant execute permission to the user for each and
> every user stored procedure in the database'
> Is there another way to allow the user to see?
>
> Arnie Rowland wrote:
> > SQL 2000 or SQL 2005?
> >
> > --
> > Arnie Rowland, Ph.D.
> > Westwood Consulting, Inc
> >
> > Most good judgment comes from experience.
> > Most experience comes from bad judgment.
> > - Anonymous
> >
> >
> > <tootsuite@.gmail.com> wrote in message
> > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > > Hi,
> > >
> > > I have a database user with read-only permissions in the database.
> > >
> > > The user would like to be able to display a list of stored procedures
> > > in the database. I would like to grant this user the permission to do
> > > this, but without granting permission to *change* the sp's in any way.
> > >
> > > Is there a way to do this?
> > >
> > > THANKS
> > >
>|||Hi,
Thanks - do I have to run this statement for each and every stored
procedure I wish to grant user permissions on? Or is there a single
stmt I can use?
Thanks
Bob wrote:
> Yes, in SQL Server 2005 you can grant user "View Definition" permission so
> that they can see the stored procedures but not alter or execute it.
> For example:
> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> TO User;
> GO
>
> "tootsuite@.gmail.com" wrote:
> > Yes, sorry forgot to mention - SQL Server 2005
> >
> > I know permissions work differently on 2005 than 2000. Please tell me I
> > do *not* have to grant execute permission to the user for each and
> > every user stored procedure in the database'
> >
> > Is there another way to allow the user to see?
> >
> >
> >
> > Arnie Rowland wrote:
> > > SQL 2000 or SQL 2005?
> > >
> > > --
> > > Arnie Rowland, Ph.D.
> > > Westwood Consulting, Inc
> > >
> > > Most good judgment comes from experience.
> > > Most experience comes from bad judgment.
> > > - Anonymous
> > >
> > >
> > > <tootsuite@.gmail.com> wrote in message
> > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > > > Hi,
> > > >
> > > > I have a database user with read-only permissions in the database.
> > > >
> > > > The user would like to be able to display a list of stored procedures
> > > > in the database. I would like to grant this user the permission to do
> > > > this, but without granting permission to *change* the sp's in any way.
> > > >
> > > > Is there a way to do this?
> > > >
> > > > THANKS
> > > >
> >
> >|||You can create a list of stored procedures from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'.
You may wish to filter out procedures starting with 'dt'.
Then using dynamic SQL, cycle through the list making the appropriate
changes.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1157052897.903180.182290@.i3g2000cwc.googlegroups.com...
> Hi,
> Thanks - do I have to run this statement for each and every stored
> procedure I wish to grant user permissions on? Or is there a single
> stmt I can use?
> Thanks
>
> Bob wrote:
>> Yes, in SQL Server 2005 you can grant user "View Definition" permission
>> so
>> that they can see the stored procedures but not alter or execute it.
>> For example:
>> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
>> TO User;
>> GO
>>
>> "tootsuite@.gmail.com" wrote:
>> > Yes, sorry forgot to mention - SQL Server 2005
>> >
>> > I know permissions work differently on 2005 than 2000. Please tell me I
>> > do *not* have to grant execute permission to the user for each and
>> > every user stored procedure in the database'
>> >
>> > Is there another way to allow the user to see?
>> >
>> >
>> >
>> > Arnie Rowland wrote:
>> > > SQL 2000 or SQL 2005?
>> > >
>> > > --
>> > > Arnie Rowland, Ph.D.
>> > > Westwood Consulting, Inc
>> > >
>> > > Most good judgment comes from experience.
>> > > Most experience comes from bad judgment.
>> > > - Anonymous
>> > >
>> > >
>> > > <tootsuite@.gmail.com> wrote in message
>> > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
>> > > > Hi,
>> > > >
>> > > > I have a database user with read-only permissions in the database.
>> > > >
>> > > > The user would like to be able to display a list of stored
>> > > > procedures
>> > > > in the database. I would like to grant this user the permission to
>> > > > do
>> > > > this, but without granting permission to *change* the sp's in any
>> > > > way.
>> > > >
>> > > > Is there a way to do this?
>> > > >
>> > > > THANKS
>> > > >
>> >
>> >
>|||There is no single statement, you have to run the grant statement for every
single stored proc. Or you can write a query which would print the grant
statements for you for every stored proc.
"tootsuite@.gmail.com" wrote:
> Hi,
> Thanks - do I have to run this statement for each and every stored
> procedure I wish to grant user permissions on? Or is there a single
> stmt I can use?
> Thanks
>
> Bob wrote:
> > Yes, in SQL Server 2005 you can grant user "View Definition" permission so
> > that they can see the stored procedures but not alter or execute it.
> >
> > For example:
> >
> > GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> > TO User;
> > GO
> >
> >
> >
> > "tootsuite@.gmail.com" wrote:
> >
> > > Yes, sorry forgot to mention - SQL Server 2005
> > >
> > > I know permissions work differently on 2005 than 2000. Please tell me I
> > > do *not* have to grant execute permission to the user for each and
> > > every user stored procedure in the database'
> > >
> > > Is there another way to allow the user to see?
> > >
> > >
> > >
> > > Arnie Rowland wrote:
> > > > SQL 2000 or SQL 2005?
> > > >
> > > > --
> > > > Arnie Rowland, Ph.D.
> > > > Westwood Consulting, Inc
> > > >
> > > > Most good judgment comes from experience.
> > > > Most experience comes from bad judgment.
> > > > - Anonymous
> > > >
> > > >
> > > > <tootsuite@.gmail.com> wrote in message
> > > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > > > > Hi,
> > > > >
> > > > > I have a database user with read-only permissions in the database.
> > > > >
> > > > > The user would like to be able to display a list of stored procedures
> > > > > in the database. I would like to grant this user the permission to do
> > > > > this, but without granting permission to *change* the sp's in any way.
> > > > >
> > > > > Is there a way to do this?
> > > > >
> > > > > THANKS
> > > > >
> > >
> > >
>|||Yes, that is what I thought - thanks
Arnie Rowland wrote:
> You can create a list of stored procedures from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_TYPE = 'PROCEDURE'.
> You may wish to filter out procedures starting with 'dt'.
> Then using dynamic SQL, cycle through the list making the appropriate
> changes.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <tootsuite@.gmail.com> wrote in message
> news:1157052897.903180.182290@.i3g2000cwc.googlegroups.com...
> > Hi,
> >
> > Thanks - do I have to run this statement for each and every stored
> > procedure I wish to grant user permissions on? Or is there a single
> > stmt I can use?
> >
> > Thanks
> >
> >
> > Bob wrote:
> >> Yes, in SQL Server 2005 you can grant user "View Definition" permission
> >> so
> >> that they can see the stored procedures but not alter or execute it.
> >>
> >> For example:
> >>
> >> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> >> TO User;
> >> GO
> >>
> >>
> >>
> >> "tootsuite@.gmail.com" wrote:
> >>
> >> > Yes, sorry forgot to mention - SQL Server 2005
> >> >
> >> > I know permissions work differently on 2005 than 2000. Please tell me I
> >> > do *not* have to grant execute permission to the user for each and
> >> > every user stored procedure in the database'
> >> >
> >> > Is there another way to allow the user to see?
> >> >
> >> >
> >> >
> >> > Arnie Rowland wrote:
> >> > > SQL 2000 or SQL 2005?
> >> > >
> >> > > --
> >> > > Arnie Rowland, Ph.D.
> >> > > Westwood Consulting, Inc
> >> > >
> >> > > Most good judgment comes from experience.
> >> > > Most experience comes from bad judgment.
> >> > > - Anonymous
> >> > >
> >> > >
> >> > > <tootsuite@.gmail.com> wrote in message
> >> > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> >> > > > Hi,
> >> > > >
> >> > > > I have a database user with read-only permissions in the database.
> >> > > >
> >> > > > The user would like to be able to display a list of stored
> >> > > > procedures
> >> > > > in the database. I would like to grant this user the permission to
> >> > > > do
> >> > > > this, but without granting permission to *change* the sp's in any
> >> > > > way.
> >> > > >
> >> > > > Is there a way to do this?
> >> > > >
> >> > > > THANKS
> >> > > >
> >> >
> >> >
> >

Help - Standard vs. Data-Driven Subscriptions

Hello,
We have a number of reports, all of which have from and to dates/times as
parameters. This allows us to display data on the report only for a certain
time period (the parameter values are passed to the stored procedure to
return the subset of data).

Anyway, we also need to schedule these reports to run automatically at
specified times. So, we're setting up subscriptions for these reports. The
issue that we're confused about is whether to use standard or data-driven
subscriptions.

Why is this important? Well, the from and to dates/times (parameter values)
will need to change depending on the scheduled run time, and will need to
change every time the report is executed on the schedule. For example, say
we have a report that needs to run every day at 6:00 am. This report needs
to display data for the last 24 hours (therefore, the from and to
dates/times are know--from = day before at 6:00 am and to = current day at
6:00 am). But, since it's running every day, the from and to dates/times
change every time the report schedule runs. I hope this makes sense.

So, is it possible to use a standard subscription to accomplish our goal?
Or, do we have to use some sort of data-driven subscription? Either way,
how would we set this up?

Thanks.
Either would probably work for you, but I'd probably use a data-driven subscription. The biggest reason why a data-driven would be better for you is the relative ease you can modify your parameter values each time you run the report. Essentially, all you'd have to do is modify the values of a couple columns of data in a particular row inside SQL Server.

You could build an expression to subtract / add X hours from the current date/time and use that as the basis for a parameter's default value, and you would be OK with this technique as long as the time you execute the report was always the same...you would be dead if you ran the report at 6a one day, and 9a the next, however. Using this technique would let you go with a standard subscription

|||Thanks for the info! However, I'm still a little unsure about the data-driven subscription route.

In reading Books Online, the documentation says that a data-driven subscription provides a way to deliver reports to a list of subscribers that is determined at run time or to support the wide distribution of a report with a fluctuating list of subscribers. This is not what we need. We don't have a list of subscribers. We only have one subscriber (I guess).

The documentation also notes that to set up a data-driven subscription you need to supply a command that gets subscriber data and that the query should produce one row for each subscriber. We don't even have a user table in our database to query in setting up the data-driven subscription.

All we need is a way to schedule reports to run at any frequency (i.e., daily, weekly, monthly, etc.) in which the report start and end dates/times are determined at run-time based on current date/time and passed as parameter values to the report for generation.

So, you also mentioned building an expression to subtract/add hours, what does this mean? And, how could we do that?

Or, do you have any other suggestions?

Thanks again.
|||

Data driven subscriptions (in my opinion, anyway) are much more flexible than standard subscriptions because you store the parameter values that you want to use inside a SQL table. When you want to change any of this information (or add a new subscriber), it's simply a matter of modifying the row in question or adding a new one. Data driven subscriptions DO take more effort to set up (creating a sql table to host the subscriber info and writing a simple query to return this info to the wizard) , but they pay off down the road because they are easy to care for and feed. Changing stuff around in a standard subscription requires a lot more clicking around in the UI and will take you more time.

You can use a data driven subscription regardless of whether you have one or many subscribers.

That being said, any parameter can have a default value, and that default value can be set via code (an expression) versus using a real, hardcoded value. So you could write an expression which gets the current date (using the Date() or Today() function) and then add or subtract a particular amount of time using the DataAdd() function. Set your defaults up like this and create a standard subscription, and you'll have a "sliding window" of time based on the current date/time. Again, you're at the mercy at the point in time the subscription is executed, etc. etc.

Hope this clears things up for you.

|||Thanks again for the info!

I think all this subscription stuff is starting to make a little sense to me. However, now I have another question.

Let's say that we go with the data-driven subscription scenario, and we create a table to hold subscriber info and all that. Can a data-drive subscription be created using the web service and our own UI instead of Report Manager? We are creating our own ASP.NET (C#) front-end for our Reporting Services implementation (we can't/don't want to use Report Manager) and we'll need to code a solution that allows end-users to create these data-driven subscriptions. Is this possible?

Thanks!
|||

Yes, you can do this -- no problem at all. Report Manager isn't magic in any form, and uses the same (documented) web service calls that you can.

|||

Hi,

In such case, if you are using a Stored Procedure (SP) to generate a Report. You can even add one extra input varchar parameter to SP to take ReportDateRange - e.g. Today, Y'day, CurrentWeek, etc. And then let that SP use this parameter to calculate the current date range and use it to generate report.

This will not only avoid complexity in subscription but also make your report SP more flexible and pluggable.

Thanks,
Mahesh

|||

Really, it doesn't matter. You could create a data-driven subscription with a query that gets the right date/time for the start and end parameters.

You could also put this logic in the default value expression for your parameter. Essentially, when you run the report, you can use an expression to obtain the current execution time of the report. From this value you can use standard .Net date/time functions to build the appropriate values.

Using this approach has the advantage that any user can run the report interactively or subscribe to it and get the same behavior.

Typically, people use two parameters for each query parameter to make this work well - first they create a parameter with valid values like 'Today', 'Yesterday', 'Last week', etc. Then they create a second parameter that is set to no-prompt. This parameter has a default value expression that is based on the value of the first parameter. This way you decouple the actual date used for the report from the value supplied by the user. Thus subscriptions will continue to work whether or not the user actually specifies 'use default' or a specific value for the one parameter they do see.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:

Procedure dbo.sp_ReportMaster @.myid int=null

When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.

What am I missing here?

Thanks, Iris

|||

Data-driven subscriptions using stored procs are not supported well, though you can make them work if you spend a little time playing with code.

The issue is the Report manager and management studio UI don't handle the case of a stored proc.

You can work around this by calling the createdatadrivensubcription method in code and passing in the prameters explicitly. This isn't entirely easy, but it can be made to work.

Sorry, I don't have an example handy. Take a look for the method definition in MSDN and then you can write a VB.Net script that runs in rs.exe to automate it.

-Lukasz

|||

I have a report that uses a stored procedure that pulls all my data, then I saw where I can create a linked report from my orginial report that will change the parameters. When I tried to use that, I got an error message, 'An interal error occurred on the report server.' I searched for this error when using linked reports, and it said to go to http://support.microsoft.com/kb/918222 and to install the hotfix.

The instructions said something about enabling SMO and SQL-DMO extended stored procedures. It didn't give much instructions but to search the SQL Server 2005 Books online. Then gave a note about making sure a default is set to 1 which mine is.

I installed the packages in order like it said, and still got the error.

Thanks, Iris

Help - Standard vs. Data-Driven Subscriptions

Hello,
We have a number of reports, all of which have from and to dates/times as
parameters. This allows us to display data on the report only for a certain
time period (the parameter values are passed to the stored procedure to
return the subset of data).

Anyway, we also need to schedule these reports to run automatically at
specified times. So, we're setting up subscriptions for these reports. The
issue that we're confused about is whether to use standard or data-driven
subscriptions.

Why is this important? Well, the from and to dates/times (parameter values)
will need to change depending on the scheduled run time, and will need to
change every time the report is executed on the schedule. For example, say
we have a report that needs to run every day at 6:00 am. This report needs
to display data for the last 24 hours (therefore, the from and to
dates/times are know--from = day before at 6:00 am and to = current day at
6:00 am). But, since it's running every day, the from and to dates/times
change every time the report schedule runs. I hope this makes sense.

So, is it possible to use a standard subscription to accomplish our goal?
Or, do we have to use some sort of data-driven subscription? Either way,
how would we set this up?

Thanks.
Either would probably work for you, but I'd probably use a data-driven subscription. The biggest reason why a data-driven would be better for you is the relative ease you can modify your parameter values each time you run the report. Essentially, all you'd have to do is modify the values of a couple columns of data in a particular row inside SQL Server.

You could build an expression to subtract / add X hours from the current date/time and use that as the basis for a parameter's default value, and you would be OK with this technique as long as the time you execute the report was always the same...you would be dead if you ran the report at 6a one day, and 9a the next, however. Using this technique would let you go with a standard subscription

|||Thanks for the info! However, I'm still a little unsure about the data-driven subscription route.

In reading Books Online, the documentation says that a data-driven subscription provides a way to deliver reports to a list of subscribers that is determined at run time or to support the wide distribution of a report with a fluctuating list of subscribers. This is not what we need. We don't have a list of subscribers. We only have one subscriber (I guess).

The documentation also notes that to set up a data-driven subscription you need to supply a command that gets subscriber data and that the query should produce one row for each subscriber. We don't even have a user table in our database to query in setting up the data-driven subscription.

All we need is a way to schedule reports to run at any frequency (i.e., daily, weekly, monthly, etc.) in which the report start and end dates/times are determined at run-time based on current date/time and passed as parameter values to the report for generation.

So, you also mentioned building an expression to subtract/add hours, what does this mean? And, how could we do that?

Or, do you have any other suggestions?

Thanks again.
|||

Data driven subscriptions (in my opinion, anyway) are much more flexible than standard subscriptions because you store the parameter values that you want to use inside a SQL table. When you want to change any of this information (or add a new subscriber), it's simply a matter of modifying the row in question or adding a new one. Data driven subscriptions DO take more effort to set up (creating a sql table to host the subscriber info and writing a simple query to return this info to the wizard) , but they pay off down the road because they are easy to care for and feed. Changing stuff around in a standard subscription requires a lot more clicking around in the UI and will take you more time.

You can use a data driven subscription regardless of whether you have one or many subscribers.

That being said, any parameter can have a default value, and that default value can be set via code (an expression) versus using a real, hardcoded value. So you could write an expression which gets the current date (using the Date() or Today() function) and then add or subtract a particular amount of time using the DataAdd() function. Set your defaults up like this and create a standard subscription, and you'll have a "sliding window" of time based on the current date/time. Again, you're at the mercy at the point in time the subscription is executed, etc. etc.

Hope this clears things up for you.

|||Thanks again for the info!

I think all this subscription stuff is starting to make a little sense to me. However, now I have another question.

Let's say that we go with the data-driven subscription scenario, and we create a table to hold subscriber info and all that. Can a data-drive subscription be created using the web service and our own UI instead of Report Manager? We are creating our own ASP.NET (C#) front-end for our Reporting Services implementation (we can't/don't want to use Report Manager) and we'll need to code a solution that allows end-users to create these data-driven subscriptions. Is this possible?

Thanks!
|||

Yes, you can do this -- no problem at all. Report Manager isn't magic in any form, and uses the same (documented) web service calls that you can.

|||

Hi,

In such case, if you are using a Stored Procedure (SP) to generate a Report. You can even add one extra input varchar parameter to SP to take ReportDateRange - e.g. Today, Y'day, CurrentWeek, etc. And then let that SP use this parameter to calculate the current date range and use it to generate report.

This will not only avoid complexity in subscription but also make your report SP more flexible and pluggable.

Thanks,
Mahesh

|||

Really, it doesn't matter. You could create a data-driven subscription with a query that gets the right date/time for the start and end parameters.

You could also put this logic in the default value expression for your parameter. Essentially, when you run the report, you can use an expression to obtain the current execution time of the report. From this value you can use standard .Net date/time functions to build the appropriate values.

Using this approach has the advantage that any user can run the report interactively or subscribe to it and get the same behavior.

Typically, people use two parameters for each query parameter to make this work well - first they create a parameter with valid values like 'Today', 'Yesterday', 'Last week', etc. Then they create a second parameter that is set to no-prompt. This parameter has a default value expression that is based on the value of the first parameter. This way you decouple the actual date used for the report from the value supplied by the user. Thus subscriptions will continue to work whether or not the user actually specifies 'use default' or a specific value for the one parameter they do see.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:

Procedure dbo.sp_ReportMaster @.myid int=null

When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.

What am I missing here?

Thanks, Iris

|||

Data-driven subscriptions using stored procs are not supported well, though you can make them work if you spend a little time playing with code.

The issue is the Report manager and management studio UI don't handle the case of a stored proc.

You can work around this by calling the createdatadrivensubcription method in code and passing in the prameters explicitly. This isn't entirely easy, but it can be made to work.

Sorry, I don't have an example handy. Take a look for the method definition in MSDN and then you can write a VB.Net script that runs in rs.exe to automate it.

-Lukasz

|||

I have a report that uses a stored procedure that pulls all my data, then I saw where I can create a linked report from my orginial report that will change the parameters. When I tried to use that, I got an error message, 'An interal error occurred on the report server.' I searched for this error when using linked reports, and it said to go to http://support.microsoft.com/kb/918222 and to install the hotfix.

The instructions said something about enabling SMO and SQL-DMO extended stored procedures. It didn't give much instructions but to search the SQL Server 2005 Books online. Then gave a note about making sure a default is set to 1 which mine is.

I installed the packages in order like it said, and still got the error.

Thanks, Iris

Help - Standard vs. Data-Driven Subscriptions

Hello,
We have a number of reports, all of which have from and to dates/times as
parameters. This allows us to display data on the report only for a certain
time period (the parameter values are passed to the stored procedure to
return the subset of data).

Anyway, we also need to schedule these reports to run automatically at
specified times. So, we're setting up subscriptions for these reports. The
issue that we're confused about is whether to use standard or data-driven
subscriptions.

Why is this important? Well, the from and to dates/times (parameter values)
will need to change depending on the scheduled run time, and will need to
change every time the report is executed on the schedule. For example, say
we have a report that needs to run every day at 6:00 am. This report needs
to display data for the last 24 hours (therefore, the from and to
dates/times are know--from = day before at 6:00 am and to = current day at
6:00 am). But, since it's running every day, the from and to dates/times
change every time the report schedule runs. I hope this makes sense.

So, is it possible to use a standard subscription to accomplish our goal?
Or, do we have to use some sort of data-driven subscription? Either way,
how would we set this up?

Thanks.
Either would probably work for you, but I'd probably use a data-driven subscription. The biggest reason why a data-driven would be better for you is the relative ease you can modify your parameter values each time you run the report. Essentially, all you'd have to do is modify the values of a couple columns of data in a particular row inside SQL Server.

You could build an expression to subtract / add X hours from the current date/time and use that as the basis for a parameter's default value, and you would be OK with this technique as long as the time you execute the report was always the same...you would be dead if you ran the report at 6a one day, and 9a the next, however. Using this technique would let you go with a standard subscription

|||Thanks for the info! However, I'm still a little unsure about the data-driven subscription route.

In reading Books Online, the documentation says that a data-driven subscription provides a way to deliver reports to a list of subscribers that is determined at run time or to support the wide distribution of a report with a fluctuating list of subscribers. This is not what we need. We don't have a list of subscribers. We only have one subscriber (I guess).

The documentation also notes that to set up a data-driven subscription you need to supply a command that gets subscriber data and that the query should produce one row for each subscriber. We don't even have a user table in our database to query in setting up the data-driven subscription.

All we need is a way to schedule reports to run at any frequency (i.e., daily, weekly, monthly, etc.) in which the report start and end dates/times are determined at run-time based on current date/time and passed as parameter values to the report for generation.

So, you also mentioned building an expression to subtract/add hours, what does this mean? And, how could we do that?

Or, do you have any other suggestions?

Thanks again.
|||

Data driven subscriptions (in my opinion, anyway) are much more flexible than standard subscriptions because you store the parameter values that you want to use inside a SQL table. When you want to change any of this information (or add a new subscriber), it's simply a matter of modifying the row in question or adding a new one. Data driven subscriptions DO take more effort to set up (creating a sql table to host the subscriber info and writing a simple query to return this info to the wizard) , but they pay off down the road because they are easy to care for and feed. Changing stuff around in a standard subscription requires a lot more clicking around in the UI and will take you more time.

You can use a data driven subscription regardless of whether you have one or many subscribers.

That being said, any parameter can have a default value, and that default value can be set via code (an expression) versus using a real, hardcoded value. So you could write an expression which gets the current date (using the Date() or Today() function) and then add or subtract a particular amount of time using the DataAdd() function. Set your defaults up like this and create a standard subscription, and you'll have a "sliding window" of time based on the current date/time. Again, you're at the mercy at the point in time the subscription is executed, etc. etc.

Hope this clears things up for you.

|||Thanks again for the info!

I think all this subscription stuff is starting to make a little sense to me. However, now I have another question.

Let's say that we go with the data-driven subscription scenario, and we create a table to hold subscriber info and all that. Can a data-drive subscription be created using the web service and our own UI instead of Report Manager? We are creating our own ASP.NET (C#) front-end for our Reporting Services implementation (we can't/don't want to use Report Manager) and we'll need to code a solution that allows end-users to create these data-driven subscriptions. Is this possible?

Thanks!
|||

Yes, you can do this -- no problem at all. Report Manager isn't magic in any form, and uses the same (documented) web service calls that you can.

|||

Hi,

In such case, if you are using a Stored Procedure (SP) to generate a Report. You can even add one extra input varchar parameter to SP to take ReportDateRange - e.g. Today, Y'day, CurrentWeek, etc. And then let that SP use this parameter to calculate the current date range and use it to generate report.

This will not only avoid complexity in subscription but also make your report SP more flexible and pluggable.

Thanks,
Mahesh

|||

Really, it doesn't matter. You could create a data-driven subscription with a query that gets the right date/time for the start and end parameters.

You could also put this logic in the default value expression for your parameter. Essentially, when you run the report, you can use an expression to obtain the current execution time of the report. From this value you can use standard .Net date/time functions to build the appropriate values.

Using this approach has the advantage that any user can run the report interactively or subscribe to it and get the same behavior.

Typically, people use two parameters for each query parameter to make this work well - first they create a parameter with valid values like 'Today', 'Yesterday', 'Last week', etc. Then they create a second parameter that is set to no-prompt. This parameter has a default value expression that is based on the value of the first parameter. This way you decouple the actual date used for the report from the value supplied by the user. Thus subscriptions will continue to work whether or not the user actually specifies 'use default' or a specific value for the one parameter they do see.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:

Procedure dbo.sp_ReportMaster @.myid int=null

When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.

What am I missing here?

Thanks, Iris

|||

Data-driven subscriptions using stored procs are not supported well, though you can make them work if you spend a little time playing with code.

The issue is the Report manager and management studio UI don't handle the case of a stored proc.

You can work around this by calling the createdatadrivensubcription method in code and passing in the prameters explicitly. This isn't entirely easy, but it can be made to work.

Sorry, I don't have an example handy. Take a look for the method definition in MSDN and then you can write a VB.Net script that runs in rs.exe to automate it.

-Lukasz

|||

I have a report that uses a stored procedure that pulls all my data, then I saw where I can create a linked report from my orginial report that will change the parameters. When I tried to use that, I got an error message, 'An interal error occurred on the report server.' I searched for this error when using linked reports, and it said to go to http://support.microsoft.com/kb/918222 and to install the hotfix.

The instructions said something about enabling SMO and SQL-DMO extended stored procedures. It didn't give much instructions but to search the SQL Server 2005 Books online. Then gave a note about making sure a default is set to 1 which mine is.

I installed the packages in order like it said, and still got the error.

Thanks, Iris

Help - Standard vs. Data-Driven Subscriptions

Hello,
We have a number of reports, all of which have from and to dates/times as
parameters. This allows us to display data on the report only for a certain
time period (the parameter values are passed to the stored procedure to
return the subset of data).

Anyway, we also need to schedule these reports to run automatically at
specified times. So, we're setting up subscriptions for these reports. The
issue that we're confused about is whether to use standard or data-driven
subscriptions.

Why is this important? Well, the from and to dates/times (parameter values)
will need to change depending on the scheduled run time, and will need to
change every time the report is executed on the schedule. For example, say
we have a report that needs to run every day at 6:00 am. This report needs
to display data for the last 24 hours (therefore, the from and to
dates/times are know--from = day before at 6:00 am and to = current day at
6:00 am). But, since it's running every day, the from and to dates/times
change every time the report schedule runs. I hope this makes sense.

So, is it possible to use a standard subscription to accomplish our goal?
Or, do we have to use some sort of data-driven subscription? Either way,
how would we set this up?

Thanks.Either would probably work for you, but I'd probably use a data-driven subscription. The biggest reason why a data-driven would be better for you is the relative ease you can modify your parameter values each time you run the report. Essentially, all you'd have to do is modify the values of a couple columns of data in a particular row inside SQL Server.

You could build an expression to subtract / add X hours from the current date/time and use that as the basis for a parameter's default value, and you would be OK with this technique as long as the time you execute the report was always the same...you would be dead if you ran the report at 6a one day, and 9a the next, however. Using this technique would let you go with a standard subscription|||Thanks for the info! However, I'm still a little unsure about the data-driven subscription route.

In reading Books Online, the documentation says that a data-driven subscription provides a way to deliver reports to a list of subscribers that is determined at run time or to support the wide distribution of a report with a fluctuating list of subscribers. This is not what we need. We don't have a list of subscribers. We only have one subscriber (I guess).

The documentation also notes that to set up a data-driven subscription you need to supply a command that gets subscriber data and that the query should produce one row for each subscriber. We don't even have a user table in our database to query in setting up the data-driven subscription.

All we need is a way to schedule reports to run at any frequency (i.e., daily, weekly, monthly, etc.) in which the report start and end dates/times are determined at run-time based on current date/time and passed as parameter values to the report for generation.

So, you also mentioned building an expression to subtract/add hours, what does this mean? And, how could we do that?

Or, do you have any other suggestions?

Thanks again.|||

Data driven subscriptions (in my opinion, anyway) are much more flexible than standard subscriptions because you store the parameter values that you want to use inside a SQL table. When you want to change any of this information (or add a new subscriber), it's simply a matter of modifying the row in question or adding a new one. Data driven subscriptions DO take more effort to set up (creating a sql table to host the subscriber info and writing a simple query to return this info to the wizard) , but they pay off down the road because they are easy to care for and feed. Changing stuff around in a standard subscription requires a lot more clicking around in the UI and will take you more time.

You can use a data driven subscription regardless of whether you have one or many subscribers.

That being said, any parameter can have a default value, and that default value can be set via code (an expression) versus using a real, hardcoded value. So you could write an expression which gets the current date (using the Date() or Today() function) and then add or subtract a particular amount of time using the DataAdd() function. Set your defaults up like this and create a standard subscription, and you'll have a "sliding window" of time based on the current date/time. Again, you're at the mercy at the point in time the subscription is executed, etc. etc.

Hope this clears things up for you.

|||Thanks again for the info!

I think all this subscription stuff is starting to make a little sense to me. However, now I have another question.

Let's say that we go with the data-driven subscription scenario, and we create a table to hold subscriber info and all that. Can a data-drive subscription be created using the web service and our own UI instead of Report Manager? We are creating our own ASP.NET (C#) front-end for our Reporting Services implementation (we can't/don't want to use Report Manager) and we'll need to code a solution that allows end-users to create these data-driven subscriptions. Is this possible?

Thanks!|||

Yes, you can do this -- no problem at all. Report Manager isn't magic in any form, and uses the same (documented) web service calls that you can.

|||

Hi,

In such case, if you are using a Stored Procedure (SP) to generate a Report. You can even add one extra input varchar parameter to SP to take ReportDateRange - e.g. Today, Y'day, CurrentWeek, etc. And then let that SP use this parameter to calculate the current date range and use it to generate report.

This will not only avoid complexity in subscription but also make your report SP more flexible and pluggable.

Thanks,
Mahesh

|||

Really, it doesn't matter. You could create a data-driven subscription with a query that gets the right date/time for the start and end parameters.

You could also put this logic in the default value expression for your parameter. Essentially, when you run the report, you can use an expression to obtain the current execution time of the report. From this value you can use standard .Net date/time functions to build the appropriate values.

Using this approach has the advantage that any user can run the report interactively or subscribe to it and get the same behavior.

Typically, people use two parameters for each query parameter to make this work well - first they create a parameter with valid values like 'Today', 'Yesterday', 'Last week', etc. Then they create a second parameter that is set to no-prompt. This parameter has a default value expression that is based on the value of the first parameter. This way you decouple the actual date used for the report from the value supplied by the user. Thus subscriptions will continue to work whether or not the user actually specifies 'use default' or a specific value for the one parameter they do see.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:

Procedure dbo.sp_ReportMaster @.myid int=null

When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.

What am I missing here?

Thanks, Iris

|||

Data-driven subscriptions using stored procs are not supported well, though you can make them work if you spend a little time playing with code.

The issue is the Report manager and management studio UI don't handle the case of a stored proc.

You can work around this by calling the createdatadrivensubcription method in code and passing in the prameters explicitly. This isn't entirely easy, but it can be made to work.

Sorry, I don't have an example handy. Take a look for the method definition in MSDN and then you can write a VB.Net script that runs in rs.exe to automate it.

-Lukasz

|||

I have a report that uses a stored procedure that pulls all my data, then I saw where I can create a linked report from my orginial report that will change the parameters. When I tried to use that, I got an error message, 'An interal error occurred on the report server.' I searched for this error when using linked reports, and it said to go to http://support.microsoft.com/kb/918222 and to install the hotfix.

The instructions said something about enabling SMO and SQL-DMO extended stored procedures. It didn't give much instructions but to search the SQL Server 2005 Books online. Then gave a note about making sure a default is set to 1 which mine is.

I installed the packages in order like it said, and still got the error.

Thanks, Iris

HELP - Running a Report with Dynamic Query from a web page

Here's the situation. I have a report that uses a dynamic query to select one
or all records from the dataset to display.The query looks like this
="SELECT abunch of fieds" &
"FROM a view" &
iif(Parameters!orgid.value = 0,"","WHERE organization_id = " &
Parameters!orgid.value)
Essentially if the report parameter (orgid) = 0 then get all records, if
it's something other than 0, get the specified record.
The report is called from an ASP web application which builds the required
URL depending on the organization selected. The URL looks like this:
"http://webserver/reporting/pages/report.aspx?ItemPath=pathtoreport/report&rc:Parameters=false&orgid=123
In the browser, the HTML (I guess) viewer opens, displays the Parameter bar,
prompts me for a orgid and sits there.
I don't want the Parameter bar to show, just run the report with the
parameter supplied in the url. What am I doing wrong' Or what am I not doing
right?You have set up the beginning of the url incorrectly.
It should look like this:
http://webserver/reportserver?/pathToReport/ReportName&rc:Parameters=false&orgid=123
See the following msdn article for details:
http://msdn2.microsoft.com/en-us/library/ms152835.aspx
Rowen McDermott|||Ah Rowen, if only it were so easy and straightforward. BTW, thanks for the
reply.
When I use the method described by MS and everybody else who wrote a book on
Reporting Services, this is what happens:
I'm re-directed to the reportserver home page with a URL of
http://<webserver>/<reportserver>/Pages/Folder.aspx. I can see the folder
that contains the report to run, I have an administrative Toolbar and that's
pretty much it. If I browse down to the report, I can click on it, the report
will start and prompt me for an orgid.
Have any other suggestions? I've got the sa who set up Reporting Services
checking on her end to see if she can get the report to run as advertised.
Thanks again.
The only way I've gotten the report to start is the method I first described.
"Rowen" wrote:
> You have set up the beginning of the url incorrectly.
> It should look like this:
> http://webserver/reportserver?/pathToReport/ReportName&rc:Parameters=false&orgid=123
> See the following msdn article for details:
> http://msdn2.microsoft.com/en-us/library/ms152835.aspx
> Rowen McDermott
>|||On Mar 20, 11:15 pm, JoeKoko <JoeK...@.discussions.microsoft.com>
wrote:
> Ah Rowen, if only it were so easy and straightforward. BTW, thanks for the
> reply.
> When I use the method described by MS and everybody else who wrote a book on
> Reporting Services, this is what happens:
> I'm re-directed to the reportserver home page with a URL of
> http://<webserver>/<reportserver>/Pages/Folder.aspx. I can see the folder
> that contains the report to run, I have an administrative Toolbar and that's
> pretty much it. If I browse down to the report, I can click on it, the report
> will start and prompt me for an orgid.
> Have any other suggestions? I've got the sa who set up Reporting Services
> checking on her end to see if she can get the report to run as advertised.
> Thanks again.
> The only way I've gotten the report to start is the method I first described.
>
In that case I'm afraid I cant help you. The method I described above
worked for me, so I can't say what is going on in your case.
I would suggest testing a very basic report with no parameters using
the microsoft documented method and see if it displays the report or
re-directs you to the report server home page.
Regards,
Rowen

Help - How to validate parameters?

I need to validate a parameters Length to make sure it is 8 characters long
and whether it is of numeric value and if it is not to display a validation
error message. Is this possible. I'm using reporting service and SQL Server
2000.
Any help will greatly appreciated.Have you tried placing custom code to validate these, then placing a dummy
textbox at the top of the report to call the validate code?
Eg. =Code.ValidateParameters()
Get this to show a box or something if it fails.
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:D2458D65-E2C3-4C80-951E-D44B389929AA@.microsoft.com...
>I need to validate a parameters Length to make sure it is 8 characters long
> and whether it is of numeric value and if it is not to display a
> validation
> error message. Is this possible. I'm using reporting service and SQL
> Server
> 2000.
> Any help will greatly appreciated.|||Hi, Craig,
my code looks like this:
declare @.lastmonth char(7)
@.lastmonth = code.mycode() <-- will return '2006.02'
select comments from theTable
where yearmonth = @.lastmonth
I execute it and get an error "syntax error near @.lastmonth"
the program of mycode() is in layout, report, report properties, code.
Thanks!
Henry
"Ken" wrote:
> I need to validate a parameters Length to make sure it is 8 characters long
> and whether it is of numeric value and if it is not to display a validation
> error message. Is this possible. I'm using reporting service and SQL Server
> 2000.
> Any help will greatly appreciated.|||Well for starters you can't put multiple lines in the dataset. If you were
doing this you can only do:
="select comments from theTable where yearmonth = "& Code.mycode()
Which will return the SQL statement with the date included.
I thought the original question though was how to validate the parameters?
I was thinking of putting a Testbox on the form which has a value of
=Code.ValidateParameter(Parameters!DateParam.Value)
as an example. Then you can make this visible if the text box has anything
in it.
You can use the same syntax for the SQL statement if you wanted to check the
parameter before you passed it in to the database within the dataset.
Craig
"Henry Chen" <HenryChen@.discussions.microsoft.com> wrote in message
news:7F512BB0-8D5C-4E16-B1E2-6A0270BB0117@.microsoft.com...
> Hi, Craig,
> my code looks like this:
> declare @.lastmonth char(7)
> @.lastmonth = code.mycode() <-- will return '2006.02'
> select comments from theTable
> where yearmonth = @.lastmonth
> I execute it and get an error "syntax error near @.lastmonth"
> the program of mycode() is in layout, report, report properties, code.
> Thanks!
> Henry
>
> "Ken" wrote:
>> I need to validate a parameters Length to make sure it is 8 characters
>> long
>> and whether it is of numeric value and if it is not to display a
>> validation
>> error message. Is this possible. I'm using reporting service and SQL
>> Server
>> 2000.
>> Any help will greatly appreciated.

Help - Creating reports in pdf format

Hi all,

Greetings

I m using Sql Server 2000 for creating Reports. I like to display the ad-hoc generated report in pdf format. Do i have any pdf writter to display the report or i can able to do it in programmatically. If we can then plz give me the source code to write a pdf report.

Advance Thanks & Regards

Guna.

PDF creating is an outofthebox feature of Reporting Service. It is available as a rendering format in the report manager (Web interface) of each report, unless inactivated.

HTH, Jens K. Suessmeyer.

http//www.sqlserver2005.de

help

I am runnig query select count(*) from A
I like to display the count in following format. For ex.
If the count is 45 then it should display 000045
If the count is 145 then it should display 000145
If the count is 5 then it should display 000005
If the count is 0 then it should display 000000
Let know how is this possible.
Thanks in advance,
Pete
CREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000
UNION ALL
SELECT 00000
--Use RIGHT to pad the value
SELECT value, RIGHT('000000' + CONVERT(VARCHAR,value),6) AS
FormattedValue
FROM #ValueTable
drop table #ValueTable
http://sqlservercode.blogspot.com/

help

I am runnig query select count(*) from A
I like to display the count in following format. For ex.
If the count is 45 then it should display 000045
If the count is 145 then it should display 000145
If the count is 5 then it should display 000005
If the count is 0 then it should display 000000
Let know how is this possible.
Thanks in advance,
PeteTry this:
SELECT REPLICATE('0', 6 - LEN(cast(count(*) as varchar(10)))) +
cast(count(*) as varchar(10)) AS [Varchar Column]
from A
"peter" <peter@.discussions.microsoft.com> wrote in message
news:6C9199D5-320A-4064-80E1-3D71F6615EDE@.microsoft.com...
>I am runnig query select count(*) from A
> I like to display the count in following format. For ex.
> If the count is 45 then it should display 000045
> If the count is 145 then it should display 000145
> If the count is 5 then it should display 000005
> If the count is 0 then it should display 000000
> Let know how is this possible.
> Thanks in advance,
> Pete
>|||CREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000
UNION ALL
SELECT 00000
--Use RIGHT to pad the value
SELECT value, RIGHT('000000' + CONVERT(VARCHAR,value),6) AS
FormattedValue
FROM #ValueTable
drop table #ValueTable
http://sqlservercode.blogspot.com/

help

I am runnig query select count(*) from A
I like to display the count in following format. For ex.
If the count is 45 then it should display 000045
If the count is 145 then it should display 000145
If the count is 5 then it should display 000005
If the count is 0 then it should display 000000
Let know how is this possible.
Thanks in advance,
PeteCREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000
UNION ALL
SELECT 00000
--Use RIGHT to pad the value
SELECT value, RIGHT('000000' + CONVERT(VARCHAR,value),6) AS
FormattedValue
FROM #ValueTable
drop table #ValueTable
http://sqlservercode.blogspot.com/

Help

This is my error and I need help figuring out why I am getting this error.
I am trying to get my sql query to display on the pages as I want to request. But it is creating some kind of code conflict.

Microsoft VBScript compilation (0x800A03EA)
Syntax error
/nsex707/common/i_webpart_col_left.do, line 104
sub request_featured_content2

Here is the CODE
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
dim featured_content_sql2

sub request_featured_content2
ContentTypeID = request("ContentTypeID")
FeaturedLabel = request("FeaturedLabel")
Thumbnail = request("Thumbnail")
Display = request("Display")
ContentID = request("ContentID")
ContentType = request("ContentType")
ContentTitle = request("ContentTitle")
iftest = request("iftest")
ShortDesc = request("ShortDesc")
elsetest = request("elsetest")
LongDesc = request("LongDesc")
endtest = request("endtest")
iftest_ads = request("iftest_ads")
AdTitle = request("AdTitle")
AdBanner = request("AdBanner")
endiftest_ads = request("endiftest_ads")
end sub

sub validate_featured_content
''' request and validate data entered from this form
ContentTypeID = trim(request("ContentTypeID"))
FeaturedLabel = trim(request("FeaturedLabel"))
Thumbnail = trim(request("Thumbnail"))
Display = trim(request("Display"))
ContentID = trim(request("ContentID"))
ContentType = trim(request("ContentType"))
ContentTitle = trim(request("ContentTitle"))
iftest = trim(request("iftest"))
ShortDesc = trim(request("ShortDesc"))
elsetest = trim(request("elsetest"))
LongDesc = trim(request("LongDesc"))
endtest = trim(request("endtest"))
iftest_ads = trim(request("iftest_ads"))
AdTitle = trim(request("AdTitle"))
AdBanner = trim(request("AdBanner"))
endiftest_ads = trim(request("endiftest_ads"))
end sub

sub db_select_featured_content2
Featured_Content_Sql2 = "SELECT TOP 3 ContentID, ContentType, Thumbnail, FeaturedLabel, ShortDesc, LongDesc, Title AS ContentTitle " & _
"FROM (Content LEFT JOIN ContentTypes ON Content.ContentTypeId = ContentTypes.ContentTypeId) " & _
"WHERE ContentTypes.ContentTypeId <> 1 AND Enabled=1 AND Feature=1 ORDER BY Priority, DateAdded DESC"
end sub

''' request form keys and inputs
ContentID = request("ContentID")

':: request action
action = lcase(request("action"))

':: handle the action
select case action

case "select_featured_content2"
' select the requested key record from database
if ContentID <> "" then
db_select_featured_content2
else
b_error = true
error_list.add "edit_featured_content", "Specify record to select."
end if

end select

':: handle the default case(s) (ignores value of action parameter)

%>

This seems to not be related to either ASP.NET or ADO.NET.

help

I am runnig query select count(*) from A
I like to display the count in following format. For ex.
If the count is 45 then it should display 000045
If the count is 145 then it should display 000145
If the count is 5 then it should display 000005
If the count is 0 then it should display 000000
Let know how is this possible.
Thanks in advance,
PeteCREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000
UNION ALL
SELECT 00000
--Use RIGHT to pad the value
SELECT value, RIGHT('000000' + CONVERT(VARCHAR,value),6) AS
FormattedValue
FROM #ValueTable
drop table #ValueTable
http://sqlservercode.blogspot.com/