how to simplify what i'm doing (AS the solution?)

i need to do create reports that perform queries where the results are
percentages of total count divided by counts based on different
subquery where values.
a la ...
select 100 * (totalCount/ (select count where columnA value is x) as
partialCount1) as perc1,
100 * (totalCount/ (select count where columnA value is y) as
partialCount2) as perc2
etc
from myTable where (..bunch of where stuff..)
I have simplified the above example just for brevity here.
So, I hate these kinds of nested queries - and they get incredibly
complex to read. I could break them up into longer scripts to simplify
- but I am wondering if there's another way.
The data I am using is from a warehouse on SQL 2005. I have very little
experience with warehouses - and know little about analysis services
and OLAP.
My question is whether it is possible to use Analysis services to
create new tables that make this data far easier to query? That's the
path I am considering venturing down, but I don't want to hit a dead
end... and need just to know if this makes sense. That is to use
Analysis services to crunch out some new tables from the tables I am
using which would have the results neatly packaged in a table or 2 that
is fast and simple to query. Is that the idea of AS?
thx for helping a learner here. let me know if i am making enough sense
in the question.
Hi
This looks like you are trying to do a pivot, in which case the best
solutions will be at the front end although with SQL 2005 you have the new
pivot function, check out the topic "Using PIVOT and UNPIVOT " in books
online.
John
<rsphere@.gmail.com> wrote in message
news:1137856740.675188.233050@.g49g2000cwa.googlegr oups.com...
>i need to do create reports that perform queries where the results are
> percentages of total count divided by counts based on different
> subquery where values.
> a la ...
> select 100 * (totalCount/ (select count where columnA value is x) as
> partialCount1) as perc1,
> 100 * (totalCount/ (select count where columnA value is y) as
> partialCount2) as perc2
> etc
> from myTable where (..bunch of where stuff..)
> I have simplified the above example just for brevity here.
> So, I hate these kinds of nested queries - and they get incredibly
> complex to read. I could break them up into longer scripts to simplify
> - but I am wondering if there's another way.
> The data I am using is from a warehouse on SQL 2005. I have very little
> experience with warehouses - and know little about analysis services
> and OLAP.
> My question is whether it is possible to use Analysis services to
> create new tables that make this data far easier to query? That's the
> path I am considering venturing down, but I don't want to hit a dead
> end... and need just to know if this makes sense. That is to use
> Analysis services to crunch out some new tables from the tables I am
> using which would have the results neatly packaged in a table or 2 that
> is fast and simple to query. Is that the idea of AS?
> thx for helping a learner here. let me know if i am making enough sense
> in the question.
>

how to simplify what i'm doing (AS the solution?)

i need to do create reports that perform queries where the results are
percentages of total count divided by counts based on different
subquery where values.
a la ...
select 100 * (totalCount/ (select count where columnA value is x) as
partialCount1) as perc1,
100 * (totalCount/ (select count where columnA value is y) as
partialCount2) as perc2
etc
from myTable where (..bunch of where stuff..)
I have simplified the above example just for brevity here.
So, I hate these kinds of nested queries - and they get incredibly
complex to read. I could break them up into longer scripts to simplify
- but I am wondering if there's another way.
The data I am using is from a warehouse on SQL 2005. I have very little
experience with warehouses - and know little about analysis services
and OLAP.
My question is whether it is possible to use Analysis services to
create new tables that make this data far easier to query? That's the
path I am considering venturing down, but I don't want to hit a dead
end... and need just to know if this makes sense. That is to use
Analysis services to crunch out some new tables from the tables I am
using which would have the results neatly packaged in a table or 2 that
is fast and simple to query. Is that the idea of AS?
thx for helping a learner here. let me know if i am making enough sense
in the question.Hi
This looks like you are trying to do a pivot, in which case the best
solutions will be at the front end although with SQL 2005 you have the new
pivot function, check out the topic "Using PIVOT and UNPIVOT " in books
online.
John
<rsphere@.gmail.com> wrote in message
news:1137856740.675188.233050@.g49g2000cwa.googlegroups.com...
>i need to do create reports that perform queries where the results are
> percentages of total count divided by counts based on different
> subquery where values.
> a la ...
> select 100 * (totalCount/ (select count where columnA value is x) as
> partialCount1) as perc1,
> 100 * (totalCount/ (select count where columnA value is y) as
> partialCount2) as perc2
> etc
> from myTable where (..bunch of where stuff..)
> I have simplified the above example just for brevity here.
> So, I hate these kinds of nested queries - and they get incredibly
> complex to read. I could break them up into longer scripts to simplify
> - but I am wondering if there's another way.
> The data I am using is from a warehouse on SQL 2005. I have very little
> experience with warehouses - and know little about analysis services
> and OLAP.
> My question is whether it is possible to use Analysis services to
> create new tables that make this data far easier to query? That's the
> path I am considering venturing down, but I don't want to hit a dead
> end... and need just to know if this makes sense. That is to use
> Analysis services to crunch out some new tables from the tables I am
> using which would have the results neatly packaged in a table or 2 that
> is fast and simple to query. Is that the idea of AS?
> thx for helping a learner here. let me know if i am making enough sense
> in the question.
>

how to simplify what i'm doing (AS the solution?)

i need to do create reports that perform queries where the results are
percentages of total count divided by counts based on different
subquery where values.
a la ...
select 100 * (totalCount/ (select count where columnA value is x) as
partialCount1) as perc1,
100 * (totalCount/ (select count where columnA value is y) as
partialCount2) as perc2
etc
from myTable where (..bunch of where stuff..)
I have simplified the above example just for brevity here.
So, I hate these kinds of nested queries - and they get incredibly
complex to read. I could break them up into longer scripts to simplify
- but I am wondering if there's another way.
The data I am using is from a warehouse on SQL 2005. I have very little
experience with warehouses - and know little about analysis services
and OLAP.
My question is whether it is possible to use Analysis services to
create new tables that make this data far easier to query? That's the
path I am considering venturing down, but I don't want to hit a dead
end... and need just to know if this makes sense. That is to use
Analysis services to crunch out some new tables from the tables I am
using which would have the results neatly packaged in a table or 2 that
is fast and simple to query. Is that the idea of AS?
thx for helping a learner here. let me know if i am making enough sense
in the question.Hi
This looks like you are trying to do a pivot, in which case the best
solutions will be at the front end although with SQL 2005 you have the new
pivot function, check out the topic "Using PIVOT and UNPIVOT " in books
online.
John
<rsphere@.gmail.com> wrote in message
news:1137856740.675188.233050@.g49g2000cwa.googlegroups.com...
>i need to do create reports that perform queries where the results are
> percentages of total count divided by counts based on different
> subquery where values.
> a la ...
> select 100 * (totalCount/ (select count where columnA value is x) as
> partialCount1) as perc1,
> 100 * (totalCount/ (select count where columnA value is y) as
> partialCount2) as perc2
> etc
> from myTable where (..bunch of where stuff..)
> I have simplified the above example just for brevity here.
> So, I hate these kinds of nested queries - and they get incredibly
> complex to read. I could break them up into longer scripts to simplify
> - but I am wondering if there's another way.
> The data I am using is from a warehouse on SQL 2005. I have very little
> experience with warehouses - and know little about analysis services
> and OLAP.
> My question is whether it is possible to use Analysis services to
> create new tables that make this data far easier to query? That's the
> path I am considering venturing down, but I don't want to hit a dead
> end... and need just to know if this makes sense. That is to use
> Analysis services to crunch out some new tables from the tables I am
> using which would have the results neatly packaged in a table or 2 that
> is fast and simple to query. Is that the idea of AS?
> thx for helping a learner here. let me know if i am making enough sense
> in the question.
>

How to show multiple main reports with their subreports one set by

Hi,
I have made a SQL Reporting Services report with the subreport. I need
my report to show multiple records with their subreport at the same time.
At now, I can generate multiple main reports altogether and then they are
followed by the subreport with multiple subreport details. It's just like
the following:
1st Page: 1st record in the Main report
2nd Page: 2nd record in the Main report
3rd Page: 1st Subreport detail (use Table object)
2nd Subreport detail (use Table object)
Inside the table of the 3rd page, it shows the 1st
Subreport details and then 2nd Subreport details.
What I actually need is I want my report to show one record in the main
report and then followed by the corresponding subreport details, then next
page for the next record in the main report and then followed by the
corresponding subreport details.
1st Page: 1st record in the Main report
2nd Page: 1st Subreport detail
3rd Page: 2nd record in the Main report
4th Page: 2nd Subreport detail
Is there anyone who can help me to solve this problem ?
Thanks in advance.
RogerHave you tried putting your subreport inside a table cell? The table could
contain your main rows and your subreports inside the same table as
repeating elements.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:17AC53EB-D966-476E-8B86-30B0864DE28B@.microsoft.com...
> Hi,
> I have made a SQL Reporting Services report with the subreport. I
> need
> my report to show multiple records with their subreport at the same time.
> At now, I can generate multiple main reports altogether and then they are
> followed by the subreport with multiple subreport details. It's just
> like
> the following:
> 1st Page: 1st record in the Main report
> 2nd Page: 2nd record in the Main report
> 3rd Page: 1st Subreport detail (use Table object)
> 2nd Subreport detail (use Table object)
> Inside the table of the 3rd page, it shows the 1st
> Subreport details and then 2nd Subreport details.
> What I actually need is I want my report to show one record in the
> main
> report and then followed by the corresponding subreport details, then next
> page for the next record in the main report and then followed by the
> corresponding subreport details.
> 1st Page: 1st record in the Main report
> 2nd Page: 1st Subreport detail
> 3rd Page: 2nd record in the Main report
> 4th Page: 2nd Subreport detail
> Is there anyone who can help me to solve this problem ?
> Thanks in advance.
> Roger
>

How to show duplicates in reports

I'm trying to create a report in Sql Server 2005 Reporting Services where it will display duplicates rows in the reports. I'm unable to find the property that enables this. I would need something like HideDuplicates = false, but that is not the way it is intended to be used. If HideDuplicates is left blank, it seems to still hide duplicate values if the preceding row matches.

The report is a crosstab type, so we are using a Matrix for the report item. I'll illustrate with a simple example:

This is what we currently get:

Catagory	Class	Detail
Type 1	A	Item 1
	B	Item 2
		Item 3
Type 2	A	Item 4
	B	Item 5

This is what we want:

Catagory	Class	Detail
Type 1	A	Item 1
Type 1	B	Item 2
Type 1	B	Item 3
Type 2	A	Item 4
Type 2	B	Item 5

I'm sure its something simple, but we just cant seem to find the solution,

Thanks

Darin

Rather than using a matrix report, I think a simple list report would do what you are looking for (if you don't do any grouping). You can still use aggreated fields in the simple list report.

|||

Unfortunately we need to use a matrix as the actual report is a crosstab, I just didn't illustrate that in the example for simplicity sake.

I think the report looks nicer with the blanks and is easier to read; however, if it is dumped to Excel for further analysis ( which in our actual report will happen often), the auto filter function will not work as the blank rows are not included if a specifiec catagory is selected. Only the first row that is allgned with the merged cells is shown.

|||

Open the code view of the report and search for the XML node called "HideDuplicates". See http://msdn2.microsoft.com/en-us/library/ms152916.aspx for on how to set. Removing the element entirely will cause the values to appear in each row on the report, like you'd want to have for use in Excel.

My problem is related. I have business analysts using the Report Builder tool basically as a querying tool. 90% of the time, they export to Excel and then crunch there. While I know this is not intended usage of Reporting Services, I am still disappointed by the fact that, in order to get duplicates to appear, you must go code-side, something I can't push off on a BA.

Any ideas for editting a Report Builder generated report file on the fly, to remove "HideDuplicates" elements?

|||

Try setting the "HideDuplicate" property on the "Type 1" textbox in your report.

Here are the defintion of the Textbox properties:

=========================================

Textbox

The Textbox element has the following attributes/elements in addition to what it inherits from ReportItem:

Attributes/Elements

Name

Card

Type

Description

Value

1

Expression

(Variant)

An expression, the value of which is displayed in the text-box.

This can be a constant expression for constant labels.

CanGrow

0-1

Boolean

Indicates the Textbox size can increase to accommodate the contents

CanShrink

0-1

Boolean

Indicates the Textbox size can decrease to match the contents

HideDuplicates

0-1

String

Indicates the text should not be displayed when the value of the expression associated with the report item is the same as the preceding instance.The value of HideDuplicates is the name of a containing grouping (other than the current grouping) or data set over which to apply the hiding.Each time a new instance of that group is encountered, the first instance of this report item will not be hidden. Rows on a previous page are ignored for the purposes of hiding duplicates.If the textbox is in a table or matrix cell, only the text will be omitted.The textbox will remain to provide background and border for the cell.Outside of a table/matrix cell, the background and borders are omitted as well.

Ignored in matrix subtotals.

ToggleImage

0-1

Element

Indicates the initial state of a toggling image should one be displayed as a part of the textbox.

UserSort

0-1

Element

Indicates an end-user sort control should be displayed as a part of this textbox in the UI.

DataElementStyle

0-1

Enum

Indicates whether textbox value should render as an element or attribute: Auto (Default) | AttributeNormal | ElementNormal. Auto uses the setting on the Report element.

|||Sorry for the late reply, but the 'HideDuplicates' element is nowhere to be found in the report definition.|||I've tried setting the HideDuplicates to a number of the values available in the dropdown list and it still does not solve my problem. Other than that, I really dont know what to set it to.|||You have to change the DiscourageGrouping Property on the fields you don't want the report to be hidden when grouped. Do it on the Report Model Designer.

Regards

How to show duplicates in reports

I'm trying to create a report in Sql Server 2005 Reporting Services where it will display duplicates rows in the reports. I'm unable to find the property that enables this. I would need something like HideDuplicates = false, but that is not the way it is intended to be used. If HideDuplicates is left blank, it seems to still hide duplicate values if the preceding row matches.

The report is a crosstab type, so we are using a Matrix for the report item. I'll illustrate with a simple example:

This is what we currently get:

Catagory	Class	Detail
Type 1	A	Item 1
	B	Item 2
		Item 3
Type 2	A	Item 4
	B	Item 5

This is what we want:

Catagory	Class	Detail
Type 1	A	Item 1
Type 1	B	Item 2
Type 1	B	Item 3
Type 2	A	Item 4
Type 2	B	Item 5

I'm sure its something simple, but we just cant seem to find the solution,

Thanks

Darin

Rather than using a matrix report, I think a simple list report would do what you are looking for (if you don't do any grouping). You can still use aggreated fields in the simple list report.

|||

Unfortunately we need to use a matrix as the actual report is a crosstab, I just didn't illustrate that in the example for simplicity sake.

I think the report looks nicer with the blanks and is easier to read; however, if it is dumped to Excel for further analysis ( which in our actual report will happen often), the auto filter function will not work as the blank rows are not included if a specifiec catagory is selected. Only the first row that is allgned with the merged cells is shown.

|||

Open the code view of the report and search for the XML node called "HideDuplicates". See http://msdn2.microsoft.com/en-us/library/ms152916.aspx for on how to set. Removing the element entirely will cause the values to appear in each row on the report, like you'd want to have for use in Excel.

My problem is related. I have business analysts using the Report Builder tool basically as a querying tool. 90% of the time, they export to Excel and then crunch there. While I know this is not intended usage of Reporting Services, I am still disappointed by the fact that, in order to get duplicates to appear, you must go code-side, something I can't push off on a BA.

Any ideas for editting a Report Builder generated report file on the fly, to remove "HideDuplicates" elements?

|||

Try setting the "HideDuplicate" property on the "Type 1" textbox in your report.

Here are the defintion of the Textbox properties:

=========================================

Textbox

The Textbox element has the following attributes/elements in addition to what it inherits from ReportItem:

Attributes/Elements

Name

Card

Type

Description

Value

1

Expression

(Variant)

An expression, the value of which is displayed in the text-box.

This can be a constant expression for constant labels.

CanGrow

0-1

Boolean

Indicates the Textbox size can increase to accommodate the contents

CanShrink

0-1

Boolean

Indicates the Textbox size can decrease to match the contents

HideDuplicates

0-1

String

Indicates the text should not be displayed when the value of the expression associated with the report item is the same as the preceding instance.The value of HideDuplicates is the name of a containing grouping (other than the current grouping) or data set over which to apply the hiding.Each time a new instance of that group is encountered, the first instance of this report item will not be hidden. Rows on a previous page are ignored for the purposes of hiding duplicates.If the textbox is in a table or matrix cell, only the text will be omitted.The textbox will remain to provide background and border for the cell.Outside of a table/matrix cell, the background and borders are omitted as well.

Ignored in matrix subtotals.

ToggleImage

0-1

Element

Indicates the initial state of a toggling image should one be displayed as a part of the textbox.

UserSort

0-1

Element

Indicates an end-user sort control should be displayed as a part of this textbox in the UI.

DataElementStyle

0-1

Enum

Indicates whether textbox value should render as an element or attribute: Auto (Default) | AttributeNormal | ElementNormal. Auto uses the setting on the Report element.

|||Sorry for the late reply, but the 'HideDuplicates' element is nowhere to be found in the report definition.|||I've tried setting the HideDuplicates to a number of the values available in the dropdown list and it still does not solve my problem. Other than that, I really dont know what to set it to.sql

How to show duplicates in reports

I'm trying to create a report in Sql Server 2005 Reporting Services where it will display duplicates rows in the reports. I'm unable to find the property that enables this. I would need something like HideDuplicates = false, but that is not the way it is intended to be used. If HideDuplicates is left blank, it seems to still hide duplicate values if the preceding row matches.

The report is a crosstab type, so we are using a Matrix for the report item. I'll illustrate with a simple example:

This is what we currently get:

Catagory	Class	Detail
Type 1	A	Item 1
	B	Item 2
		Item 3
Type 2	A	Item 4
	B	Item 5

This is what we want:

Catagory	Class	Detail
Type 1	A	Item 1
Type 1	B	Item 2
Type 1	B	Item 3
Type 2	A	Item 4
Type 2	B	Item 5

I'm sure its something simple, but we just cant seem to find the solution,

Thanks

Darin

Rather than using a matrix report, I think a simple list report would do what you are looking for (if you don't do any grouping). You can still use aggreated fields in the simple list report.

|||

Unfortunately we need to use a matrix as the actual report is a crosstab, I just didn't illustrate that in the example for simplicity sake.

I think the report looks nicer with the blanks and is easier to read; however, if it is dumped to Excel for further analysis ( which in our actual report will happen often), the auto filter function will not work as the blank rows are not included if a specifiec catagory is selected. Only the first row that is allgned with the merged cells is shown.

|||

Open the code view of the report and search for the XML node called "HideDuplicates". See http://msdn2.microsoft.com/en-us/library/ms152916.aspx for on how to set. Removing the element entirely will cause the values to appear in each row on the report, like you'd want to have for use in Excel.

My problem is related. I have business analysts using the Report Builder tool basically as a querying tool. 90% of the time, they export to Excel and then crunch there. While I know this is not intended usage of Reporting Services, I am still disappointed by the fact that, in order to get duplicates to appear, you must go code-side, something I can't push off on a BA.

Any ideas for editting a Report Builder generated report file on the fly, to remove "HideDuplicates" elements?

|||

Try setting the "HideDuplicate" property on the "Type 1" textbox in your report.

Here are the defintion of the Textbox properties:

=========================================

Textbox

The Textbox element has the following attributes/elements in addition to what it inherits from ReportItem:

Attributes/Elements

Name

Card

Type

Description

Value

1

Expression

(Variant)

An expression, the value of which is displayed in the text-box.

This can be a constant expression for constant labels.

CanGrow

0-1

Boolean

Indicates the Textbox size can increase to accommodate the contents

CanShrink

0-1

Boolean

Indicates the Textbox size can decrease to match the contents

HideDuplicates

0-1

String

Indicates the text should not be displayed when the value of the expression associated with the report item is the same as the preceding instance.The value of HideDuplicates is the name of a containing grouping (other than the current grouping) or data set over which to apply the hiding.Each time a new instance of that group is encountered, the first instance of this report item will not be hidden. Rows on a previous page are ignored for the purposes of hiding duplicates.If the textbox is in a table or matrix cell, only the text will be omitted.The textbox will remain to provide background and border for the cell.Outside of a table/matrix cell, the background and borders are omitted as well.

Ignored in matrix subtotals.

ToggleImage

0-1

Element

Indicates the initial state of a toggling image should one be displayed as a part of the textbox.

UserSort

0-1

Element

Indicates an end-user sort control should be displayed as a part of this textbox in the UI.

DataElementStyle

0-1

Enum

Indicates whether textbox value should render as an element or attribute: Auto (Default) | AttributeNormal | ElementNormal. Auto uses the setting on the Report element.

|||Sorry for the late reply, but the 'HideDuplicates' element is nowhere to be found in the report definition.|||I've tried setting the HideDuplicates to a number of the values available in the dropdown list and it still does not solve my problem. Other than that, I really dont know what to set it to.

How to show a linked report in a report container?

I have a report container (reportviewer.dll) in a .net web page to display
the reports using the reporting services. Some of the reportds are linked
report and I am not sure how to display it in the report container.
Currently it opens on a new page. Thanks.I have the same problem...
Any luck with that?
--
http://dotnet.org.za/stanley
"Paul" wrote:
> I have a report container (reportviewer.dll) in a .net web page to display
> the reports using the reporting services. Some of the reportds are linked
> report and I am not sure how to display it in the report container.
> Currently it opens on a new page. Thanks.
>
>

How to show 2 reports in 1 viewer right after each other

I am using vb.net 2005 and have created 2 reports with Crystal. I have the reports showing fine by themselves, but what I want to happen is as follows:

User generates a detail report > user views/prints report > user closes report > summary report opens > user views/prints summary.

I want both reports to use the same CrystalReportViewer/Form, so once the first report is closed, I want the 2nd one to open. Is this possible?

I am not sure how to accomplish this. Any help?Are you pulling directly from a database or are you populating the data programatically?

This is how we do something similar in vb6 so if you use that you can put one in the close event of the form you are loading... As long as you don't allow them to close throug the view and you close on a button on the screen.

Hope this helps..
Rico

'Defines the CRxdrt object as the application
Set mobjApplication = CreateObject("CrystalRuntime.Application.11")

'Opens the defined report
Set mobjReport = mobjApplication.OpenReport(mcReportName, iOPEN_MULTIUSE)

'Sets the datasource of the report
mobjReport.Database.Tables(1).SetDataSource mrsReportData, 3

'Sets the crview object to the report we just opened
crptViewer.ReportSource = mobjReport

'Makes it viewable on the screen
crptViewer.ViewReport|||Is this the right close event?

Private Sub frmDetailSumm_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed

'my code to load 2nd report

End Sub

How to share parameter value between main and subreport?

Hi All,

I am really lousy when it comes to creating reports. So I am seeking your expert advise/experience.

I have a report that uses a stored-procedure as its data source. This procedure has a parameter. Within this report, I created a subreport which also sources to yet another stored-procedure that also has a parameter which expects the same value as the param of the main report.

When I run the report, say in preview mode, I do not want to be prompted with the parameter twice (i.e. once for the main report, and another for the subreport). How can I edit/modify subreport so that in its select or formula it will say something like:

{?@.ParamOfSubReport} = {?@.ParamOfMainReport}

Many thanks in advance.

_msd_You need to use shared variable and you can use it anywhere in the report|||Right click on your sub-report and choose "Change sub-report links".

Example:

"?parameter1" in the main report links to "?Pm-?parameter1" in the sub-report, then choose the field in the datasource that the parameter is tested against.

When you enter the parameter in your main report it will be passed to the sub-report preview also.|||How do I pass a value returned from the stored proc in the main report to the sub report. I'm getting an error "cannot link from stored procedure" when I pass the value from main report to subreport using subreport links.

Immediate response would be appreciated. Thanks.|||Assign value to shared variable and use that in Sub report|||Hi, I saw this old thread and I am working on the same issue. I'm working on a simple dumb shell of a report in Crystal 8.5 as a test for how to pass parameters into a sql server 2000 stored procedure via a subreport. I'd like to deliver this as straight Crystal without using vb or other shells to call the report from.

Eventually I need to modify a copy of a main report with data and about 8 subreports, all pulling from stored procedures using one parameter called ID. I am getting many requests for this ID parameter, for each subreport, even though I am linking the parameters via subreport links.

The subreport wants to link into the stored procedure using the parameter name from the stored procedure, which is @.ID. The main report only wants to give the subreport a subordinate link such as ?Pm-@.ID. I cannot name the stored procedure parameter with a name like ?Pm-@.ID because it violates the rules of transact-sql. It sees the hyphen as a subtraction. I can see the logic of using the same variable names, but how do I get rid of that bothersome hyphen?

I have also tried using shared variables and they connect to the subreport ok, but I'm still not closing the loop by linking into the stored procedure based on the shared variable.

I am trying to use the same information in multiple places without making the user enter the same information over and over again.

Has anyone had this problem before?sql

How to share dataset after it was created within the report

Is there a way to share the dataset with the other reports once it was
created in once of the reports in the same project? Right now, we are
copying the same dataset from one to the others...
BrianI'm not sure if you want to pull out data from a created report, then
use that in the others.
Or, if you already have that data, and want to feed it into 5 reports?
I'm new to RS, and the only way I have used it sofar is setting up the
connections, parameters, then querying it through IIS. I myself am
wondering if I can take a Dataset (received through MSMQ), then tell a
Report 'use this data', then send it off to a printer (pdf etc).|||It looks like the solution to my problem will be to use the RS viewer
controls in VS.net 2005. Basically if it works the way I believe I can
get a dataset, then apply that to a number of reports, save them off to
PDF, then I am done.

How to setup reporting database

Hi!
Our client is using sql2000 sp3a OLTP database. They are also using a lot of
reports that generate high utilization on the server.
We would like to move that load to secondary server. What is the easiest way
to do that without modifying the current database or application?
First thing that comes to my mind is log shipping but as far as I know, in
this case we would get at least 1 min delay between primary and secondary
server.
I think that's great for reports but our clients don't think so...They
request seconds...
Alternative would be transactional replication. Can I replicate the entire
database with all views, stored procedures...?
I also found out that it requires that all the tables have at least one
primary key. Off course NOT all tables have them. How to solve this? Can
PK's be added easy or does it mean that they would have to modify the
database and application? The goal is not to modify the database and
application if posible.
Any sugestions?
tomLog Shipping wouldnt work anyways because the db is in a load state until
you apply the final TLog since you are constantly restoring. Transactional
Replication is pretty common for this. Not having a PK on a table is pretty
rare. Put an Identity column on the tables that don't have one.
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:OQTJVlHqEHA.1644@.tk2msftngp13.phx.gbl...
> Hi!
> Our client is using sql2000 sp3a OLTP database. They are also using a lot
of
> reports that generate high utilization on the server.
> We would like to move that load to secondary server. What is the easiest
way
> to do that without modifying the current database or application?
> First thing that comes to my mind is log shipping but as far as I know, in
> this case we would get at least 1 min delay between primary and secondary
> server.
> I think that's great for reports but our clients don't think so...They
> request seconds...
> Alternative would be transactional replication. Can I replicate the entire
> database with all views, stored procedures...?
> I also found out that it requires that all the tables have at least one
> primary key. Off course NOT all tables have them. How to solve this? Can
> PK's be added easy or does it mean that they would have to modify the
> database and application? The goal is not to modify the database and
> application if posible.
>
> Any sugestions?
>
> tom
>|||P.S.
Yes, you can replicate View, Procs, etc. but then you need a new snapshot
whenever they are modified. I prefer to just copy and paste them personally.
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:OQTJVlHqEHA.1644@.tk2msftngp13.phx.gbl...
> Hi!
> Our client is using sql2000 sp3a OLTP database. They are also using a lot
of
> reports that generate high utilization on the server.
> We would like to move that load to secondary server. What is the easiest
way
> to do that without modifying the current database or application?
> First thing that comes to my mind is log shipping but as far as I know, in
> this case we would get at least 1 min delay between primary and secondary
> server.
> I think that's great for reports but our clients don't think so...They
> request seconds...
> Alternative would be transactional replication. Can I replicate the entire
> database with all views, stored procedures...?
> I also found out that it requires that all the tables have at least one
> primary key. Off course NOT all tables have them. How to solve this? Can
> PK's be added easy or does it mean that they would have to modify the
> database and application? The goal is not to modify the database and
> application if posible.
>
> Any sugestions?
>
> tom
>|||"ChrisR" <chris@.noemail.com> wrote in message
news:ODv4F0JqEHA.376@.TK2MSFTNGP14.phx.gbl...
> Log Shipping wouldnt work anyways because the db is in a load state until
> you apply the final TLog since you are constantly restoring. Transactional
> Replication is pretty common for this. Not having a PK on a table is
pretty
> rare. Put an Identity column on the tables that don't have one.
Well, for reporting you can put the receiver into read-only mode, except
when the log is being applied.
Of course if you do this every minute, you spend most of your time NOT in
read-only mode. ;-)
And yes, this is a case where Joe Celko not withstanding an Identity key can
be a useful PK if you don't have one already.

How to set up tools so that reports can be designed on client machine

Sorry if this is really simple.

We will be having a SQL 2005 DB set up which we need developers to be able to design & deploy reports on. The developers will only have remote access to this server across a network. What (&how) is the simplest way to set up the relevant tools (and which specific tools are needed) on the developers machines so that they can design, build & deploy reports to the main server ?

Thanks for any help, Will

You need to have Business Developement Studio on the clients installed, thats all.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Cool.

Thanks

How to set this up?

I need to set up the security on the Reporting Services such that user can
view the reports ONLY through a ReportViewer control on a asp.net web page
ie not allow to navigate all other folders or areas in the Report Manager.
Reporting services is installed on a stand alone server (not belong to any
domain, no AD set up) running on Windows 2003 server. Currently I configured
the "Report Manager" and "ReportServer" web site to allow annonyous access.
However some users know how to bypass the web application and directly enter
the IP address/reports of the stand alone server to view all the reports.
The asp.net page has built-in security to filter out the reports listed on
the page based on the user login and password. This web application is also
hosted at the stand alone server and is connected to a remote Sql server.
The web application is using the sql security.I have a similar problem and cannot find a solution provided by Microsoft.
However, I decidee to solve it by a trick. I develop an ASP.net web
application and the application have some report printing function. Whenever
a report is printed, a random key will be inserted into a database table
like this:
Key User
KE#n-asdfjk-ad33klj-kasjd WBush
The key and user are also passed in the query string. When the report is
run, the key and user will be checked again in the stored procedure to
verify that this user can print this report.
I am not sure whether this is a good solution, but I think that's what we
can do before Microsoft can provide a better solution.
"Paul" <paul_mak@.hotmail.com> wrote in message
news:eWZLuvhdGHA.2456@.TK2MSFTNGP04.phx.gbl...
> I need to set up the security on the Reporting Services such that user can
> view the reports ONLY through a ReportViewer control on a asp.net web page
> ie not allow to navigate all other folders or areas in the Report Manager.
> Reporting services is installed on a stand alone server (not belong to any
> domain, no AD set up) running on Windows 2003 server. Currently I
configured
> the "Report Manager" and "ReportServer" web site to allow annonyous
access.
> However some users know how to bypass the web application and directly
enter
> the IP address/reports of the stand alone server to view all the reports.
> The asp.net page has built-in security to filter out the reports listed on
> the page based on the user login and password. This web application is
also
> hosted at the stand alone server and is connected to a remote Sql server.
> The web application is using the sql security.
>|||Hi Paul.
The only way I know of is to have your web site's app pool use an identity
that is the only identity that is allowed to hit the reports.
For example, you might set up a service account called "WebApp1SvcAcct".
give that account and administrators group privileges in reporting services,
but not the rest of your users. This might be only half the solution. It's
been a while since we've had to deal with this.
-Tim
"Paul" <paul_mak@.hotmail.com> wrote in message
news:eWZLuvhdGHA.2456@.TK2MSFTNGP04.phx.gbl...
>I need to set up the security on the Reporting Services such that user can
>view the reports ONLY through a ReportViewer control on a asp.net web page
>ie not allow to navigate all other folders or areas in the Report Manager.
>Reporting services is installed on a stand alone server (not belong to any
>domain, no AD set up) running on Windows 2003 server. Currently I
>configured the "Report Manager" and "ReportServer" web site to allow
>annonyous access. However some users know how to bypass the web application
>and directly enter the IP address/reports of the stand alone server to view
>all the reports. The asp.net page has built-in security to filter out the
>reports listed on the page based on the user login and password. This web
>application is also hosted at the stand alone server and is connected to a
>remote Sql server. The web application is using the sql security.
>

How to set the path to the reports snapshots folder?

We have a statutory requirement to keep 5 years of history, so I've checked the option in the Site Settings page in the Report Manager to "Keep an unlimited number of snapshots in report history". The drives are partitioned as follows:

C: -- OS; 20GB

D: -- Apps; 20GB

E: --Data: 160GB

I want to force the report history snapshots onto the data (E:) drive, but I can't figure out what setting will force them there. Can anyone tell me how to set the path to the reports snapshots folder?

Hello,

The snapshots are stored in the database, not as seperate files on the server. You'll just need to put the data file for the ReportServer database on a drive with enough space allocated.

Jarret

How to set PDF margins when rendering via WS

I'm rendering reports in PDF format in aspx pages by calling the ssrs
webservice. Is there a way to adjust the side margins in the PDF?
Currently the margins are a default of 1 inch on each side and I want to
reduce it to 1/2 inch on each side.
Thanks.
--
moondaddy@.nospam.nospamHello,
I suggest that you refer to the following topics in Reporting Services
Books Online:
PDF Device Information Settings
Device Information Settings
Render Method
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

How to set password through CRviewer to DSN less connection in crystal report

Hi,please help.
I have crystal reports bearing DSN less(direct) connection with password protected Access database.But i dont know how to set password through CRviewer in VB 6.0 .Moreover my reports have no. of subreports too.How are you calling Reports? If you use front end application, then you need to supply the password informations

How to set Grow column and row height in crosstab reports

hello guys,
i am working with crystal report 9.0, i have one report that contains multiple columns in crosstab. but in this report column and row headings are not growing according to content length and overlapping on other column and row values. also can grow option is disabled for row and column headings in crosstab. can anybody help me to solve this problem.you need to manually increase the width as much as possible

How to set execution priority for certain Stored procedures ?

On our heavily used data warehouse server where some reports running for tens of minutes and sometimes for hours, we have some processes that we want to execute within seconds . Ideally this stored procedure executes within 1 sec on empty development server where no heavy processes are running. But on our production data warehouse it may take over 30 sec because it has to wait in queue to obtain server resources such as CPU and I/O.

I am wondering whether SQL Server 2005 has such feature to set high priority for certain stored procedures to by-pass other processes in queue and thereby to reduce wait time ?

Thanks.

There isn't such a setting in sqlserver.|||

Unfortunately, not. There is no hidden "Turbo" button.

Instead, I would recommend running the stored proc in Management Studio with SET STATISTICS IO ON, and with Display Actual Execution Plan enabled. If you are unfamiliar with how to tune a query, I would try running the SP in the Database Tuning Advisor to see what it recommends. In a DW type of database, you can be much more aggressive with indexes than in an OLTP database. You would also want to make sure your statistics are up to date.

You would also want to do some analysis of wait states at the instance and server level to get a feel as to whether you are seeing memory pressure, CPU pressure, or IO pressure.

How to set execution priority for certain Stored procedures ?

On our heavily used data warehouse server where some reports running for tens of minutes and sometimes for hours, we have some processes that we want to execute within seconds . Ideally this stored procedure executes within 1 sec on empty development server where no heavy processes are running. But on our production data warehouse it may take over 30 sec because it has to wait in queue to obtain server resources such as CPU and I/O.

I am wondering whether SQL Server 2005 has such feature to set high priority for certain stored procedures to by-pass other processes in queue and thereby to reduce wait time ?

Thanks.

There isn't such a setting in sqlserver.|||

Unfortunately, not. There is no hidden "Turbo" button.

Instead, I would recommend running the stored proc in Management Studio with SET STATISTICS IO ON, and with Display Actual Execution Plan enabled. If you are unfamiliar with how to tune a query, I would try running the SP in the Database Tuning Advisor to see what it recommends. In a DW type of database, you can be much more aggressive with indexes than in an OLTP database. You would also want to make sure your statistics are up to date.

You would also want to do some analysis of wait states at the instance and server level to get a feel as to whether you are seeing memory pressure, CPU pressure, or IO pressure.