Showing posts with label dataset. Show all posts
Showing posts with label dataset. Show all posts

Wednesday, March 28, 2012

Report designer

In a RD, i have 2 dataset, one of them consist of two field (id and name) by which the name will be shown as a parameter. When a user select from the combo box, how do I enable the id of the this dataset to be link up as a 'parameter' to another dataset which is a query to retrieve the result?

In another words instead of using the parameter 'name' i would like to implicitly link up the id instead of the name which is shown to the user.

Thanks.

Regards

Alu

In Report Parameters, you set the

Value Field to be ID

and the

Label Field to be Name

A bit like this

<Prompt>Select Hierarchy Group</Prompt>

<ValidValues>

<DataSetReference>

<DataSetName>H_Groups</DataSetName>

<ValueField>id</ValueField>

<LabelField>List</LabelField>

</DataSetReference>

</ValidValues>

Where id is the parameter passed to the query & List is the values displayed for the user to choose from in the Listbox

|||

thanks!

Regards

Alu

sql

Monday, March 26, 2012

Report Dataset is lost once the Data Tab is clicked

1. Created a New Report Server Wizard Project using OLAP as datasource
2. Gone through the wizard steps to generate a matrix report.
3. All is fine and the report gets previewed. I build and deploy the report.
4. Now , if I need to add a new column I go to data tab of the report. Realize that the query is lost and now the report throws exception like the following when rebuild:

" [rsFieldReference] The Value expression for the textbox ‘Base_Premium’ refers to the field ‘Base_Premium’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
5. No idea why the query given at the query builder stage of the wizard is lost

Steps to Reproduce:

1. Create a new Report Server Wizard Project
2. Create a new datasource using OLAP as datasource
3. Add the report data fields (query builder)
4. Select matrix report format and any style
5. Select the row, col and data fields.
6. complete the wizard.
7. Save, build and deploy the project
8. Preview the report.
9. Now try to add a new column to the report by clicking the data tab.

Actual Results:

The sql prepared from the query builder is lost and all the columns previously selected disappear. The report throws exception in preview mode something like :

" [rsFieldReference] The Value expression for the textbox ‘Base_Premium’ refers to the field ‘Base_Premium’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

Expected Results:

Should be able to modify the report any number of times after it is initially generated.

Actual Results:

The sql prepared from the query builder is lost and all the columns previously selected disappear. The report throws exception in preview mode something like :

" [rsFieldReference] The Value expression for the textbox ‘Base_Premium’ refers to the field ‘Base_Premium’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

Expected Results:

Should be able to modify the report any number of times after it is initially generated.

Please check this KB article: http://support.microsoft.com/kb/909535/en-us

Friday, March 23, 2012

Report code and dataset question

I have a strange (maybe not too strange) problem. I have data populating a report and everything is fine. This table also references another table where there might be zero to several prefixes associated with the name. If I have one row from the main table, but there are three prefixes that have to be concatenated to the beginning of the Name field (like a one to many situation), what would be the easiest way to do this?

I thought of doing a separate Report code and I have the following:

Function addPrefix(byval current as string, newToAdd as string) as string

dim deg as string

deg = current + " " + newToAdd

return deg

End Function

In the text box, I have it referencing it's own value. So the textboxes expression is

=code.addTitles(ReportItems.("textbox1").Value, Fields!NAM.Value)

I hope this makes some sort of sense. If the first record's value in the main table is THISNAME and it pulls the values VAL1 and VAL2 from the second dataset then I want the report to show VAL1 VAL2 THISNAME.

Thanks for the information

have you tried using sql-joins?
left/right-join should fulfill your needs if i understood your problem right|||

Yes, I have used joins. The record in the left table produces the desired results. However the first record pulls three records from the INNER JOIN. I need to attached those three records to the NAME field from the left table to that one record. So if the first record pulls 1, 2, and 3. I need the report to display as 1 2 3 NAME.

I was thinking of doing a for each statement in the code. Is there a way to pass the dataset to the code in a report?

|||

Why don't you sort it out in the select statement in the Dataset:

select min(p1.prefix)+min(p2.prefix)+min(p3.prefix)+p0.name

from name_file p0

inner join prefix_file p1 on p1.prefix > ' ' and p0.name_id = p1.name_id

inner join prefix_file p2 on p2.prefix > p1.prefix and p0.name_id = p2.name_id

inner join prefix_file p3 on p3.prefix > p2.prefix and p0.name_id = p3.name_id

It should give you the desired results.....

|||There can be zero or more prefixes. That is why I would like to pass the dataset onto the Report Code and then do a for each statement, however I don't think that is possible.

Wednesday, March 21, 2012

Report Builder Query Timeout?

Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.

My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.

Unfortunately, no, you cannot set the timeout of a Report Builder query. You can specify a query timeout in Report Designer so you could build the report in Report Builder and then import it into Report Designer. But this doesn't get what you want, which is a limit on end user queries.

This feature is under consideration for the next release.

|||

I'm trying to build a Report Model for a Data Source View that has a table in it with a little over 1 billion records. When I try to create the new Report Model, the process fails while trying to get a row count on the 1billion+ table:

An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Command:
SELECT COUNT(*) FROM [dbo].[Sor1_PageHist]

This timeout is at 5 minutes. Where is the query timeout setting for building the model, or is this like the problem above that there isn't a location to set the query timeout?

Any information would be great!

|||

I am getting the same error message.

"An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

How can I resolve this timeout issue?

|||"You can specify a query timeout in Report Designer"

How?
|||

I am setting the Querytimeout parameter in rsReportServer.config file to 10 seconds and timeout parameter to 10 sec in Dataset for query tab in Report Designer for particular ".rdl" file and expecting to get the query timeout error in 10 seconds from Report server but it takes more time to timeout around 40-50 or more seconds. Can u please tell me where I am going wrong ?

Report Builder Query Timeout?

Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.

My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.

Unfortunately, no, you cannot set the timeout of a Report Builder query. You can specify a query timeout in Report Designer so you could build the report in Report Builder and then import it into Report Designer. But this doesn't get what you want, which is a limit on end user queries.

This feature is under consideration for the next release.

|||

I'm trying to build a Report Model for a Data Source View that has a table in it with a little over 1 billion records. When I try to create the new Report Model, the process fails while trying to get a row count on the 1billion+ table:

An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Command:
SELECT COUNT(*) FROM [dbo].[Sor1_PageHist]

This timeout is at 5 minutes. Where is the query timeout setting for building the model, or is this like the problem above that there isn't a location to set the query timeout?

Any information would be great!

|||

I am getting the same error message.

"An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

How can I resolve this timeout issue?

|||"You can specify a query timeout in Report Designer"

How?
|||I am setting the Querytimeout parameter in rsReportServer.config file to 10 seconds and timeout parameter to 10 sec in Dataset for query tab in Report Designer for particular ".rdl" file and expecting to get the query timeout error in 10 seconds from Report server but it takes more time to timeout around 40-50 or more seconds. Can u please tell me where I am going wrong ?

Report Builder Query Timeout?

Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.

My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.

Unfortunately, no, you cannot set the timeout of a Report Builder query. You can specify a query timeout in Report Designer so you could build the report in Report Builder and then import it into Report Designer. But this doesn't get what you want, which is a limit on end user queries.

This feature is under consideration for the next release.

|||

I'm trying to build a Report Model for a Data Source View that has a table in it with a little over 1 billion records. When I try to create the new Report Model, the process fails while trying to get a row count on the 1billion+ table:

An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Command:
SELECT COUNT(*) FROM [dbo].[Sor1_PageHist]

This timeout is at 5 minutes. Where is the query timeout setting for building the model, or is this like the problem above that there isn't a location to set the query timeout?

Any information would be great!

|||

I am getting the same error message.

"An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

How can I resolve this timeout issue?

|||"You can specify a query timeout in Report Designer"

How?
|||

I am setting the Querytimeout parameter in rsReportServer.config file to 10 seconds and timeout parameter to 10 sec in Dataset for query tab in Report Designer for particular ".rdl" file and expecting to get the query timeout error in 10 seconds from Report server but it takes more time to timeout around 40-50 or more seconds. Can u please tell me where I am going wrong ?

Report Builder Query Timeout?

Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.

My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.

Unfortunately, no, you cannot set the timeout of a Report Builder query. You can specify a query timeout in Report Designer so you could build the report in Report Builder and then import it into Report Designer. But this doesn't get what you want, which is a limit on end user queries.

This feature is under consideration for the next release.

|||

I'm trying to build a Report Model for a Data Source View that has a table in it with a little over 1 billion records. When I try to create the new Report Model, the process fails while trying to get a row count on the 1billion+ table:

An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Command:
SELECT COUNT(*) FROM [dbo].[Sor1_PageHist]

This timeout is at 5 minutes. Where is the query timeout setting for building the model, or is this like the problem above that there isn't a location to set the query timeout?

Any information would be great!

|||

I am getting the same error message.

"An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

How can I resolve this timeout issue?

|||"You can specify a query timeout in Report Designer"

How?
|||

I am setting the Querytimeout parameter in rsReportServer.config file to 10 seconds and timeout parameter to 10 sec in Dataset for query tab in Report Designer for particular ".rdl" file and expecting to get the query timeout error in 10 seconds from Report server but it takes more time to timeout around 40-50 or more seconds. Can u please tell me where I am going wrong ?

Report Builder Query Timeout?

Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.

My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.

Unfortunately, no, you cannot set the timeout of a Report Builder query. You can specify a query timeout in Report Designer so you could build the report in Report Builder and then import it into Report Designer. But this doesn't get what you want, which is a limit on end user queries.

This feature is under consideration for the next release.

|||

I'm trying to build a Report Model for a Data Source View that has a table in it with a little over 1 billion records. When I try to create the new Report Model, the process fails while trying to get a row count on the 1billion+ table:

An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Command:
SELECT COUNT(*) FROM [dbo].[Sor1_PageHist]

This timeout is at 5 minutes. Where is the query timeout setting for building the model, or is this like the problem above that there isn't a location to set the query timeout?

Any information would be great!

|||

I am getting the same error message.

"An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

How can I resolve this timeout issue?

|||"You can specify a query timeout in Report Designer"

How?
|||

I am setting the Querytimeout parameter in rsReportServer.config file to 10 seconds and timeout parameter to 10 sec in Dataset for query tab in Report Designer for particular ".rdl" file and expecting to get the query timeout error in 10 seconds from Report server but it takes more time to timeout around 40-50 or more seconds. Can u please tell me where I am going wrong ?sql

Wednesday, March 7, 2012

Report Blank on Render?

Hello Everyone,
I have a Sql Server report which, when there is no data returned in the
dataset, goes entirelyt blank. Static text is wiped out, no page
header, nothing, just a blank screen. How can I at least get my static
text to stay there?
Thanks.Hi Eric,
Is it working in your preview ? you need to place all your field titles
hardcoded. If it comes from the dataset then you will get the page empty. If
u require it in a tabular form use tables.
e.g assume both in a text box and in the body.
Employee No
=Field!emp_no
Amarnath
"Eric_H" wrote:
> Hello Everyone,
> I have a Sql Server report which, when there is no data returned in the
> dataset, goes entirelyt blank. Static text is wiped out, no page
> header, nothing, just a blank screen. How can I at least get my static
> text to stay there?
> Thanks.
>|||Hi Amarnath,
No, it shows up blank in the preview, text is not contained in the
table, it is just textboxes on the form with static text. The titles
are hardcoded, that's why this is confusing. No error messages, just a
pure white report.