Need to allow paging and exporting

I've got a .NET web app that has a report, and every time I click the paging or export button, the report refreshes to the parameter prompt. Same thing with clicking on the group tree.

I'm using version XI.

I've got the DB credentials fine, since my two Crystal XI books tell me how to do that...but I haven't found anything about paging and exporting.

Thanks!Duh...had to use:

If not page.ispostback....

thanks!sql

Need to add expression when subreport returns NULL value

Help! @.=)
Using SRS 2000 SP2. I have a subreport which only returns a value to the
main report some of the time. At other times, it returns nothing (usually in
situations where the result would be NULL). In the main report, when it
returns NULL/Blank/Nothing, I want to that space to display "There was no
value for this query" or something similar. Unfortunately, when I
right-click the textbox containing the subreport, it doesn't give me an
expressions field.
Where could I put something like this? Or is this not something that can be
done?
Thanks in advance!
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)Click the table in your report and look for a property value called NoRows.
Add text to that, and it will be displayed in your main report.
Kaisa M. Lindahl Lervik
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:CBF33AD2-95A4-4BDC-951A-D108AB2FE33E@.microsoft.com...
> Help! @.=)
> Using SRS 2000 SP2. I have a subreport which only returns a value to the
> main report some of the time. At other times, it returns nothing (usually
> in
> situations where the result would be NULL). In the main report, when it
> returns NULL/Blank/Nothing, I want to that space to display "There was no
> value for this query" or something similar. Unfortunately, when I
> right-click the textbox containing the subreport, it doesn't give me an
> expressions field.
> Where could I put something like this? Or is this not something that can
> be
> done?
> Thanks in advance!
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the
> back?
> @.=)|||AHA! That's exactly what I was looking for. Thank you ever so much!!!
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)

Need some logic help

Trying to bind some data to a datalist for a report.

User selects an Industry from a dropdown list and then I dump all records for that industry. However in order to parse some of the record field values into names (I.E. from a 1 to the actual company name) for some records I have to read TABLE_ONE and for other records I might have to read TABLE_TWO depending on the value of FIELD_ONE.

If FIELD_ONE = "A" then I get the NAME from TABLE_ONE.
If FIELD_ONE = "B" then I get the NAME from TABLE_TWO.
If FIELD_ONE = "C" then I get the NAME from TABLE_THREE.

I'm lost at how to get started on this. I thought about adding IF statements to my query but these won't work because I'm not passing in the value of FIELD_ONE ahead of time - it's part of the query. So I thought maybe I could do a pre-read and store all FIELD_ONE values in an ArrayList and pass these in as parameters, but the stored proc is only being called once - so that won't work.

Any thoughts on how I can do this?I can think of a couple of ways to accomplish this, but the most elegant involves using right outer joins and the COALESE function. COALESE is a function that returns the first non-null expression from a list of parameters. So the SELECT statement would be something like the following

SELECT COALESE(TABLE_ONE.NAME, TABLE_TWO.NAME, TABLE_THREE.NAME)
FROM TABLE RIGHT OUTER JOIN TABLE_ONE ON (TABLE.FIELD = TABLE_ONE.KEY AND TABLE.FIELD_ONE = 'A')
RIGHT OUTER JOIN TABLE_TWO ON (TABLE.FIELD = TABLE_TWO.KEY AND TABLE.FIELD_ONE = 'B')
RIGHT OUTER JOIN TABLE_THREE ON (TABLE.FIELD = TABLE_THREE.KEY AND TABLE.FIELD_ONE = 'C')

The returned field will contain whichever value is not null, the one that is appropriate depending on the value of FIELD_ONE.

HTH|||Am I understanding that the lookup values are in different tables. If so then you'd be doing conditional joins which I don't think is possible. Assuming you can't change the table structure around, I would create a query that combines all the lookup information from Table_One, Table_two, and Table_three (and others) and then join this table to your original table. Something like:

Select YourMainTable.Field1, B.Name
From YourMainTable INNER JOIN
(
Select Field1,Name
From
(
Select Field1,Name FROM Table1
Union
Select Field1,Name FROM Table2
Union
Select Field1,Name FROM Table3
) A
) B ON(YourMainTable.Field1 = B.Field1)

sql

Need some kind of run sp order

Hi all,
I have a report that uses three stored procs as 3 datasets. Two of these
stored procs further down the report are large select statements drawn over
a table which is populated with data by the first stored proc (i.e.
dataset). The problem is that when I preview the report, I know the first sp
has ran because the table is populated with data but the second and third
run but don't find any data.
Any takes on this one?
Regards
John.There is no guarantee of order. To do this you should use a subreport
instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <a@.b.c> wrote in message
news:OQ6VpFHWFHA.2768@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a report that uses three stored procs as 3 datasets. Two of these
> stored procs further down the report are large select statements drawn
> over a table which is populated with data by the first stored proc (i.e.
> dataset). The problem is that when I preview the report, I know the first
> sp has ran because the table is populated with data but the second and
> third run but don't find any data.
> Any takes on this one?
> Regards
> John.
>|||While it is true that there is no 100% guaranteed order (it may change in
future releases), this is what you can do in RS 2000:
* make all 3 datasets use the same data source
* check "use transaction" on the data source properties dialog
The datasets will then get executed sequentially (within the same
transaction) in the order they are defined in the RDL (which is typically
the order in which they show up in the dataset drop-down list in report
designer).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eBke1wIWFHA.2256@.TK2MSFTNGP14.phx.gbl...
> There is no guarantee of order. To do this you should use a subreport
> instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "John" <a@.b.c> wrote in message
> news:OQ6VpFHWFHA.2768@.tk2msftngp13.phx.gbl...
>> Hi all,
>> I have a report that uses three stored procs as 3 datasets. Two of these
>> stored procs further down the report are large select statements drawn
>> over a table which is populated with data by the first stored proc (i.e.
>> dataset). The problem is that when I preview the report, I know the first
>> sp has ran because the table is populated with data but the second and
>> third run but don't find any data.
>> Any takes on this one?
>> Regards
>> John.
>

Need some help with subtotals in a matrix

Hello all,

This is all being done under SQL2000 and VS2003

I have a matrix report which is showing user information. The Rows are displaying numbers for each user, and the columns show the user info in weekly increments. I have 7 fields of info for each user. My stored procedure already is set up to give me the correct numbers. I dont need to SUM them or anything. Although in the report designer it forced me to SUM them since it was part of an aggregate. This still worked for me anyhow because it was Summing a single value.

However, at the end of the report i want to display totals for all the users combined, per week. So right now the report is showing 21 weeks, so at the end of the report i should have 21 sets of totals.

I right clicked on the users name column and selected subtotal. This gave me some of what i want. But some of the numbers are not correct. Some of the numbers should not just be a simple SUM of the column. Some of the values should be averages etc. I know how to calculate those values myself (its very simple math) but i dont know how to do it using this setup in the report designer. So in the matrix, for each week, how can i calculate the totals for all the users combined and specify the formula used to get the totals for each field?

thanks
I think i might have found a way to fix this. I added a table to the matrix, grouped the records by the weeks, and then displayed what fields i wanted in the table header.

however, i need for all the records returned to be displayed in new columns, not new rows. If i can get it displayed in new colums, it'll look like its just part of the existing matrix.

So instead of new records repeating by adding a new row, i need to know how to get the new records to come up as a new column.

Or is there a better way?

Need some help with picture in Crystal Reports, please.

Hello all!

I use VB.NET and Crystal Reports and I need to create a win report which will display pictures.
The issue is that I don't know what pictures should be printed while I design the report, but my application check this in runtime.
What I want to do is create a report that will get pictures by parameters or by any other way that will allow me to add pictures dynamically during runtime.
I heard about OLE Picture but I didn't find how to use it and I don't even sure that I should use it...

May someone explain me how to create such report?

Thanks in advanced,
Tom.Hi Tom,

Problem with Crystal and pictures is that there's no way (up until 8.5) to dynamically add a picture from a file or folder location.

You can, however, create a field in a table structure within your database to store BLOBs (BinaryLargeObjects) such as pictures or binary streams etc, and then embed that field within your report. That's provided your database supports the storage of binary objects.

So if you have a table for Personnel, and the Fields are Id, LastName, FirstName, Address etc, and you wanted to show a picture of the person, you would add a BLOB field named Pic to this table and then store the picture in it. Then when you show the person's details on the report, you will have the picture field available to select from the field list in Crystal, and pop it on the report in the appropriate location.

Some databases support storage of BLOBs 2 ways - directly storing the picture itself, and storing a file reference to a location within the file system.
You'll have to work out which way is gonna work for you.

Good luck

Dave|||I am having this same problem.

When you say "field list" are you refering to the dataset *.xsd file that is created in VB.NET 2002 that is populated from a SQL query and used and the record source for the crystal report?|||Raven (nevermore!)
I don't use Crystal and .NET, so I can't say absolutely yes to your question, but my theoretical understanding is that the schema is exposed via XML, so that anything in the underlying tables on the server side would be visible to the user as a "field".

The real issue here is that your connection can expoise a BLOB. If it can, you store the pictures in a table on the server and then retrieve thme to client side as what is essentially a binary stream. On the client side you have a container that can display binary stream data (like a picture box), and you load the binary data into that object.

Dave|||Thank You.

I have read the pictures into a SQL table via a binary stream and I have now read that data into Crystal Reports via a binary stream.

I have decided that I will bypass storing the pics in the database and simply store the path to the pics in the database and still perform the binary stream read into Crystal Reports.

thank for the help!

(" 'Tis some visitor," I muttered, "tapping at my chamber door;
Only this, and nothing more.") :p|||Raven,
One less midnight dreary, pondering weak and weary over one of those damned Crystal Reports!
My 11yo daughter's fave poem.

Glad to help - streaming is an often misunderstood feature, and once you master the mechanics of it, it's so useful. I first came across it coding with Intersystems Cache OO database, and it was very useful overcoming Crystal's problems with dynamic pictures.
Don't forget to rate the thread......

Dave
"Nameless here for evermore"

Need scope for RowNumber( ) function

HI ...

I have a detailed report ..with summary lines and detailed lines (drill down).

I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows

when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).

I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.

How do I modify RowNumber(Nothing) to exclude counting the detailed rows....?

Any help will be much appreciated...thanks

To the folks who might face this problem

I found the solution myself .....here it goes...just use this in the row you want to rumber (summary or detailed)

=RunningValue(Fields!FieldNumber1.Value,CountDistinct,Nothing)

|||

This was exactly what I was looking for thank you very much, you have been a great help|||

Excellent!! Thank you, I have looked every where for this......

Need scope for RowNumber( ) function

HI ...

I have a detailed report ..with summary lines and detailed lines (drill down).

I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows

when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).

I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.

How do I modify RowNumber(Nothing) to exclude counting the detailed rows....?

Any help will be much appreciated...thanks

To the folks who might face this problem

I found the solution myself .....here it goes...just use this in the row you want to rumber (summary or detailed)

=RunningValue(Fields!FieldNumber1.Value,CountDistinct,Nothing)

|||

This was exactly what I was looking for thank you very much, you have been a great help|||

Excellent!! Thank you, I have looked every where for this......

Need scope for RowNumber( ) function

HI ...

I have a detailed report ..with summary lines and detailed lines (drill down).

I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows

when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).

I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.

How do I modify RowNumber(Nothing) to exclude counting the detailed rows....?

Any help will be much appreciated...thanks

To the folks who might face this problem

I found the solution myself .....here it goes...just use this in the row you want to rumber (summary or detailed)

=RunningValue(Fields!FieldNumber1.Value,CountDistinct,Nothing)

|||

This was exactly what I was looking for thank you very much, you have been a great help|||

Excellent!! Thank you, I have looked every where for this......|||Thank you|||Excellent solution my friend
Thanks for sharing the code

Need report to NEVER cache

Let me say up front that rs:ClearSession=true didn't work :)

I have a report on RS that displays two blocks of data. The intent is to move items from one list to the other. To do so, there's a link on the detail lines which does a drill-through to another report. That report takes parameters (IDs for the record to be moved) and uses a stored procedure as it's datasource. The stored procedure takes in the parameter, does an INSERT INTO into the table that's driving which list items appear on, then returns the ID that was affected. On the report, there's just a text box stating that the item was added to the table and a "Back" text box. This Back item is a drill through back to the original report.

When we go back to the report (not really *back* since we're actually drilling through to the report anew), I want it to show the two lists with the one item moved from the one list to the other. Problem is, since the report was already run, it's showing the data from the cache. If I hit the refresh button, it updates and the item moves as it should.

I've read a number of posting on this and the only thing anyone said that should work was to use the rs:ClearSession=true option in the URL. So I generated a URL to the report, added this option. Tested that my URL worked and then changed the "Back" text box's navigation so that rather than a simple drill-through, it goes to a URL...the one with the ClearSession.

But it still doesn't work. I click the link on one of my lines, it goes to the drill through saying that it added the line to the table (I can even query the table in QA and see that it did), then I click the Back and I can see in the address box that the ClearSession arguement is there but the results still don't refresh.

I've also tried CTRL-F5 while in this state and the data doesn't refresh. The only thing that makes it re-run the stored procedure and pull in new data is the green refresh button.

Any ideas?

Thanks, Tim

Does the original report have a query parameter? If it does, the original query should be re-executed. You might need to bind it to a complext expression (not just =Parameters!XX.Value).|||

Brian,

The report has two data sets that are based on Stored Procedures. The sproc does have parameters but the only parameters being passed is a @.MODE that allows the sproc to run different blocks of code (let me know if you'd like to see the code of the sproc.

So there's nothing dynamic about what's being passed in, it's always the same. I tried adding a parameter called @.CURR_TIME to test what I thought you were saying. I set the dataset to pass "=Now()" to that parameter. Within the code of the sproc, the parameter is then ignored. In theory, everytime it's called, it would think it needs to send the sproc a new time, right? But still, when I drill through back to the parent report, it doesn't rerun. I hit the refresh and the data changes show up.

Help!!! :(

Tim

|||

Can anyone elaborate on what Brian was saying? I really need to get this report to always rerun the sproc behind it every time it refreshes through any means and Brian sounded like he knew how to do that.

Thanks,

Tim

|||

I'd still love to hear if anyone can elaborate on this. I didn't quite understand how to do what Brian was referring to.

Thanks,

Tim

|||

Hi Tim,

I had the same issue. To get around it, we created a time parameter, similar to what you described on your stored procedure, but on the report itself. Always passing the current time will make the report completely re-execute.

-Jessica

|||

Jessica,

Thanks so much for the response. I was so focused on getting the sproc to rerun, it didn't occur to me to try forcing the report to refresh in that way! I added a parameter to the report called ENSURE_REFRESH with a prompt "Ingore this prompt:", made it a datetime type and gave it a default of =DateAdd("s", 1, Now()). It worked perfectly!!!!

Everytime I drill back to the report, it's realizes it needs to refresh because the default changes and the underlying data changes are reflected!!!!

Thanks for getting me thinking in the right direction.

Tim Graffham

Need report to NEVER cache

Let me say up front that rs:ClearSession=true didn't work :)

I have a report on RS that displays two blocks of data. The intent is to move items from one list to the other. To do so, there's a link on the detail lines which does a drill-through to another report. That report takes parameters (IDs for the record to be moved) and uses a stored procedure as it's datasource. The stored procedure takes in the parameter, does an INSERT INTO into the table that's driving which list items appear on, then returns the ID that was affected. On the report, there's just a text box stating that the item was added to the table and a "Back" text box. This Back item is a drill through back to the original report.

When we go back to the report (not really *back* since we're actually drilling through to the report anew), I want it to show the two lists with the one item moved from the one list to the other. Problem is, since the report was already run, it's showing the data from the cache. If I hit the refresh button, it updates and the item moves as it should.

I've read a number of posting on this and the only thing anyone said that should work was to use the rs:ClearSession=true option in the URL. So I generated a URL to the report, added this option. Tested that my URL worked and then changed the "Back" text box's navigation so that rather than a simple drill-through, it goes to a URL...the one with the ClearSession.

But it still doesn't work. I click the link on one of my lines, it goes to the drill through saying that it added the line to the table (I can even query the table in QA and see that it did), then I click the Back and I can see in the address box that the ClearSession arguement is there but the results still don't refresh.

I've also tried CTRL-F5 while in this state and the data doesn't refresh. The only thing that makes it re-run the stored procedure and pull in new data is the green refresh button.

Any ideas?

Thanks, Tim

Does the original report have a query parameter? If it does, the original query should be re-executed. You might need to bind it to a complext expression (not just =Parameters!XX.Value).|||

Brian,

The report has two data sets that are based on Stored Procedures. The sproc does have parameters but the only parameters being passed is a @.MODE that allows the sproc to run different blocks of code (let me know if you'd like to see the code of the sproc.

So there's nothing dynamic about what's being passed in, it's always the same. I tried adding a parameter called @.CURR_TIME to test what I thought you were saying. I set the dataset to pass "=Now()" to that parameter. Within the code of the sproc, the parameter is then ignored. In theory, everytime it's called, it would think it needs to send the sproc a new time, right? But still, when I drill through back to the parent report, it doesn't rerun. I hit the refresh and the data changes show up.

Help!!! :(

Tim

|||

Can anyone elaborate on what Brian was saying? I really need to get this report to always rerun the sproc behind it every time it refreshes through any means and Brian sounded like he knew how to do that.

Thanks,

Tim

|||

I'd still love to hear if anyone can elaborate on this. I didn't quite understand how to do what Brian was referring to.

Thanks,

Tim

|||

Hi Tim,

I had the same issue. To get around it, we created a time parameter, similar to what you described on your stored procedure, but on the report itself. Always passing the current time will make the report completely re-execute.

-Jessica

|||

Jessica,

Thanks so much for the response. I was so focused on getting the sproc to rerun, it didn't occur to me to try forcing the report to refresh in that way! I added a parameter to the report called ENSURE_REFRESH with a prompt "Ingore this prompt:", made it a datetime type and gave it a default of =DateAdd("s", 1, Now()). It worked perfectly!!!!

Everytime I drill back to the report, it's realizes it needs to refresh because the default changes and the underlying data changes are reflected!!!!

Thanks for getting me thinking in the right direction.

Tim Graffham

Need report to NEVER cache

Let me say up front that rs:ClearSession=true didn't work :)

I have a report on RS that displays two blocks of data. The intent is to move items from one list to the other. To do so, there's a link on the detail lines which does a drill-through to another report. That report takes parameters (IDs for the record to be moved) and uses a stored procedure as it's datasource. The stored procedure takes in the parameter, does an INSERT INTO into the table that's driving which list items appear on, then returns the ID that was affected. On the report, there's just a text box stating that the item was added to the table and a "Back" text box. This Back item is a drill through back to the original report.

When we go back to the report (not really *back* since we're actually drilling through to the report anew), I want it to show the two lists with the one item moved from the one list to the other. Problem is, since the report was already run, it's showing the data from the cache. If I hit the refresh button, it updates and the item moves as it should.

I've read a number of posting on this and the only thing anyone said that should work was to use the rs:ClearSession=true option in the URL. So I generated a URL to the report, added this option. Tested that my URL worked and then changed the "Back" text box's navigation so that rather than a simple drill-through, it goes to a URL...the one with the ClearSession.

But it still doesn't work. I click the link on one of my lines, it goes to the drill through saying that it added the line to the table (I can even query the table in QA and see that it did), then I click the Back and I can see in the address box that the ClearSession arguement is there but the results still don't refresh.

I've also tried CTRL-F5 while in this state and the data doesn't refresh. The only thing that makes it re-run the stored procedure and pull in new data is the green refresh button.

Any ideas?

Thanks, Tim

Does the original report have a query parameter? If it does, the original query should be re-executed. You might need to bind it to a complext expression (not just =Parameters!XX.Value).|||

Brian,

The report has two data sets that are based on Stored Procedures. The sproc does have parameters but the only parameters being passed is a @.MODE that allows the sproc to run different blocks of code (let me know if you'd like to see the code of the sproc.

So there's nothing dynamic about what's being passed in, it's always the same. I tried adding a parameter called @.CURR_TIME to test what I thought you were saying. I set the dataset to pass "=Now()" to that parameter. Within the code of the sproc, the parameter is then ignored. In theory, everytime it's called, it would think it needs to send the sproc a new time, right? But still, when I drill through back to the parent report, it doesn't rerun. I hit the refresh and the data changes show up.

Help!!! :(

Tim

|||

Can anyone elaborate on what Brian was saying? I really need to get this report to always rerun the sproc behind it every time it refreshes through any means and Brian sounded like he knew how to do that.

Thanks,

Tim

|||

I'd still love to hear if anyone can elaborate on this. I didn't quite understand how to do what Brian was referring to.

Thanks,

Tim

|||

Hi Tim,

I had the same issue. To get around it, we created a time parameter, similar to what you described on your stored procedure, but on the report itself. Always passing the current time will make the report completely re-execute.

-Jessica

|||

Jessica,

Thanks so much for the response. I was so focused on getting the sproc to rerun, it didn't occur to me to try forcing the report to refresh in that way! I added a parameter to the report called ENSURE_REFRESH with a prompt "Ingore this prompt:", made it a datetime type and gave it a default of =DateAdd("s", 1, Now()). It worked perfectly!!!!

Everytime I drill back to the report, it's realizes it needs to refresh because the default changes and the underlying data changes are reflected!!!!

Thanks for getting me thinking in the right direction.

Tim Graffham

Need Report select statement help CR9

i need to report for a center (a location #) within a date range where item #1 and item #2's qty's are not the same. The center and date range is fine

"{DS_DATA.DBCTR} = 50 and
{DS_DATA.DBDATE} in DateTime (2006, 02, 20, 0, 0, 0) to DateTime (2006, 03, 19, 0, 0, 0)"

it is the the item's that i am stuck on. How can I select where the center = 50 the date is between x and y, and the qty's for items 1 and 2 are NOT the same on the same day for the same location. I keep envisioning in sql string terms like a select statement within a select statement but you cant do that in crystal. at least I dont think. HELP!It might help if we knew your table / data structure.

I assume that there are multiple rows per DBCTR, DBDATE pair i.e. that there's an item column and a quantity column.
I assume that the DS_DATA table's key is DBCTR, DBDATE, ITEM_NO.
I assume that the DBDATE column is a date (rather than a datetime with a non-zero time).

So, the SQL is something like

Select t1.dbctr, t1.dbdate, t1.qty, t2.qty
from ds_data t1
inner join ds_data t2 on t2.DBCTR = t1.DBCTR and t2.DBDATE = t1.DBDATE
and t1.item_no = 1 and t2.item_no = 2
where t1.qty <> t2.qty
and ...

(Yes, I did mean to put the item_no restrictions on the join - I've no idea how big your table is or how many items there could be and it limits the cartesian product before where clause filtering.)

So you could either put the SQL straight into the 'Add command' or do it via the graphical interface: add the table in the database expert twice (the 2nd time it'll be given a different name), add the inner join (just the column joins here) as links and then add the item_no and qty restrictions to your record selection formula, using the correctly aliased table.|||It didn't even dawn on me to put the table in twice and do the join. It was stairing me right in the face. I actually tried using the repository and used this sql statement:
select d.dscenter, d.DSDATE, d.dsvoid, s.storevoid from
(select DBCTR DSCENTER, DBDATE DSDATE, dbqty DSVOID from ds_user.ds_data where dbitem = 11200 )D ,
(select DBCTR STORECTR, DBDATE STOREDATE, dbqty STOREVOID from ds_user.ds_data where dbitem = 21200 )S
where
d.dsdate = s.storedate
and
d.dscenter = s.storectr
and
d.dsvoid <> s.storevoid

it worked too and the speed was no different. Thanks for the help

Need report for current heaviest queries

What tools can I use to get an immediate report of which queries are the
heavist on my sql server? Sometimes it can max for 30-40 seconds and I need
to troubleshoot where the bottleneck it.
All tips and links would be appreciated!
Thanks,
Moshe
You can use a server side trace or Profiler to capture
information on queries such as duration, cpu, reads, writes,
etc. You can use the tool or tracing in different ways but
I'm not sure what you mean by an immediate report - if you
need to see what's hardest on your server you'd need to
monitor this over some period of time. You can save the
trace results to a file and from there you can import them
to a table using fn_trace_gettable and do some analysis,
reporting, etc.
-Sue
On Mon, 6 Mar 2006 14:07:26 -0800, Moshe Rosenberg
<MosheRosenberg@.discussions.microsoft.com> wrote:

>What tools can I use to get an immediate report of which queries are the
>heavist on my sql server? Sometimes it can max for 30-40 seconds and I need
>to troubleshoot where the bottleneck it.
>All tips and links would be appreciated!
>Thanks,
>Moshe

Need report for current heaviest queries

What tools can I use to get an immediate report of which queries are the
heavist on my sql server? Sometimes it can max for 30-40 seconds and I need
to troubleshoot where the bottleneck it.
All tips and links would be appreciated!
Thanks,
MosheYou can use a server side trace or Profiler to capture
information on queries such as duration, cpu, reads, writes,
etc. You can use the tool or tracing in different ways but
I'm not sure what you mean by an immediate report - if you
need to see what's hardest on your server you'd need to
monitor this over some period of time. You can save the
trace results to a file and from there you can import them
to a table using fn_trace_gettable and do some analysis,
reporting, etc.
-Sue
On Mon, 6 Mar 2006 14:07:26 -0800, Moshe Rosenberg
<MosheRosenberg@.discussions.microsoft.com> wrote:

>What tools can I use to get an immediate report of which queries are the
>heavist on my sql server? Sometimes it can max for 30-40 seconds and I need
>to troubleshoot where the bottleneck it.
>All tips and links would be appreciated!
>Thanks,
>Moshe

Need report for current heaviest queries

What tools can I use to get an immediate report of which queries are the
heavist on my sql server? Sometimes it can max for 30-40 seconds and I need
to troubleshoot where the bottleneck it.
All tips and links would be appreciated!
Thanks,
MosheYou can use a server side trace or Profiler to capture
information on queries such as duration, cpu, reads, writes,
etc. You can use the tool or tracing in different ways but
I'm not sure what you mean by an immediate report - if you
need to see what's hardest on your server you'd need to
monitor this over some period of time. You can save the
trace results to a file and from there you can import them
to a table using fn_trace_gettable and do some analysis,
reporting, etc.
-Sue
On Mon, 6 Mar 2006 14:07:26 -0800, Moshe Rosenberg
<MosheRosenberg@.discussions.microsoft.com> wrote:
>What tools can I use to get an immediate report of which queries are the
>heavist on my sql server? Sometimes it can max for 30-40 seconds and I need
>to troubleshoot where the bottleneck it.
>All tips and links would be appreciated!
>Thanks,
>Moshe

Need Reporing Server Options in VS 2005

I installed VS 2005 for one of our better users so he can start using VS 2005 for Report Designer. Because I'm a programmer, I installed Reporting Services with SQL 2005 so I automatically see the option in my VS 2005 to create a new Reporting Services project but he doesn't. What do I need to install to give him the Reporting Services Project options in VS 2005 ?

You need to install the client tools of SQL Server 2005, particularly the Business Intelligence Development Studio.

-- Robert

Need rdl help

Can you launch a sql report rdl for a menu form you default page if so I know how to launch a crystal.aspx but do not know how to launch the rdl files do what do I need to to to make this work.

You have to deploy the report to your report server. You can upload the .rdl file through Report Manager but I've always had problems with this concerning the data sources. (I'm new at this too). I usually create the report in VS and then deploy it from there.

After it is on the report server you can access it usually from this urlhttp://localhost/reportserver or you can use the ReportViewer control in your .aspx page if you are using Visual Studio 2.0. Or you can link directly to the report server from your .aspx page.

If you need more help make sure you read the tutorials on Microsoft's site to get you started.

|||

I am inheriting a web site (whose original author is currently unavailable) that runs an winform app behind the scene to send emails or faxes to certain recipients based on what users enter onto the site. The win app calls a SSRS web service to generate the text contents for email/fax. Recently I have received new requirements and need to modify the SSRS text contents but have not been able to find the related rdl files. Could the SRSS reports be deployed,without the rdl files being copied to the server? What can I do now? Thanks.

Need Parameter Optionally Omitted

Is it possible to have a parameter ignored? I have a report that I am
web-deploying with a large set of parameters, but a user may not wish to
include some for a given execution of the report. For example, I have a
Boolean checkbox that I cannot get the report to ignore. I have tried
toggling the following settings: allow null, allow blank, setting defaults,
not setting defaults. I then recast as char and used a true/false drop-down
but still could not get the report to optionally use it.
Thanks for any helpMike,
It sounds like you're using the Report Parameter to make a Query
Parameter to use in your SQL query. If that's the case, the problem may
be that when the parameter is null, your SQL query fails because it's
expecting a query parameter that doesn't exist.
If this is the case (and I can't be sure since you didn't give the
exact error) what you can do is write a function that checks all of
your parameters, performs whatever logic you need, and returns a sql
statement as a string. In your dataset you will have something like
=Code.GetSQL()
instead of the sql statement you have now.
Just make sure you're using the Generic Query Designer instead of the
Query Builder, or it'll have a fit.|||I am not getting an error, just incorrect results from the query when I
don't want the param used. If I delete the param, I get the results I would
expect if the param were ignored. In the grid colum 'Criteria' I add
'@.param'. Then from the menu Report\Report Parameters I add the addtional
attributes for the param as I mentioned earlier.
I am not a SQL power-user so I am tring for a modest report, accepting some
of the known limitations of the tool esp regarding use of params. I was
hoping to at least get basic function though.
Also, Is there a distinction between a report vs query paramter?
Many thanks!

Need NT & SQL loginNames for Server & DB roles

I'm creating a SOX compliance report in Reporting Services 2000 via SQL
Server 2000. We will be upgrading in the next 3 months to 2005 on both, so I
need this question answered for both databases.
How do I pull permissions & fixed roles for all mapped NT accounts?
I can find the many of the names in Syslogins mapped to their default
databases, but it doesn't give me a list of ALL the dbs the Login has access
to. Also, when I go to <dbname>.dbo.SysUsers, it will give me the list of
people who specifically have access to that database, but not what DB roles
that might have.
What am I missing? I need a comprehensive list of these permissions on our
database.
The report format we're going with is:
<ServerName>
<DatabaseName>
<ServerLogin> <Server or App Roles belonging to Login>
<Database logins>
<Database Roles>
<Table/View/SP/UDF permissions>
Thanks for any suggestions or help anyone can give me on this!
Catadmin
--
MCDBA, MCSA
Random Thoughts: "First things first, but not necessarily in that order."
-- Doctor WhoI'd ask this in one of the other SQL Server newsgroups.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:9F07D7A9-F2A0-4956-9E78-699143248F2C@.microsoft.com...
> I'm creating a SOX compliance report in Reporting Services 2000 via SQL
> Server 2000. We will be upgrading in the next 3 months to 2005 on both,
> so I
> need this question answered for both databases.
> How do I pull permissions & fixed roles for all mapped NT accounts?
> I can find the many of the names in Syslogins mapped to their default
> databases, but it doesn't give me a list of ALL the dbs the Login has
> access
> to. Also, when I go to <dbname>.dbo.SysUsers, it will give me the list of
> people who specifically have access to that database, but not what DB
> roles
> that might have.
> What am I missing? I need a comprehensive list of these permissions on
> our
> database.
> The report format we're going with is:
> <ServerName>
> <DatabaseName>
> <ServerLogin> <Server or App Roles belonging to Login>
> <Database logins>
> <Database Roles>
> <Table/View/SP/UDF permissions>
> Thanks for any suggestions or help anyone can give me on this!
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: "First things first, but not necessarily in that order."
> -- Doctor Who
>