Report Parameters

If I specifiy a parameter in my report dataset like @.status to filter the
report based on a status selected by the user - I can't figure out how to
give them the "ALL" option - so that the report ignores the status and
displays all the records.
--
ArkayTry this
SELECT NULL AS StatusValue, '<All>' AS Status
UNION
SELECT StatusValue, Status
FROM Status_mstr
"Arkay" <Arkay@.discussions.microsoft.com> wrote in message
news:7E0C2DD8-A1E9-407C-BDDD-AD0B110C5775@.microsoft.com...
> If I specifiy a parameter in my report dataset like @.status to filter the
> report based on a status selected by the user - I can't figure out how to
> give them the "ALL" option - so that the report ignores the status and
> displays all the records.
>
> --
> Arkay

Report Parameter Errors

Hello.

I've been trying to figure this out for a day now and am getting frustrated. (ARGH!) As shown below, I have 4 parameters in my sproc. The @.Action is being set as "getAll", the @.FormID is coming from a textbox control, and the other 2 should allow nulls (this is in Local mode in a Web Form). I keep getting the error message below.

I do have these params defined in my .rdlc, then I'm setting the param values in my form's code-behind.

What am I doing wrong?

---

CREATE PROCEDURE dbo.spFormResults
(
@.Action VarChar( 20 ),
@.ResultID INT = NULL,

@.FormID int = NULL,

@.Data text = NULL
)
AS

.....

---

string strAction = "getAll";

ReportParameter actionParam = new ReportParameter("Action", strAction, false);

ReportParameter resultIDParam = new ReportParameter("ResultID", "0", false);

ReportParameter formIDParam = new ReportParameter("FormID", txtFormID.Text, false);

ReportParameter dataParam = new ReportParameter("Data", string.Empty, false);

this.ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { actionParam, resultIDParam,

formIDParam, dataParam });

this.ReportViewer1.LocalReport.Refresh();

---

An error has occurred during report processing.

Exception has been thrown by the target of an invocation.

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

To me it seems you are looking in the wrong place for the error. It looks like the sort of error message that actually comes from the database, not RS. You need to check whether some of the columns you are searching on are set to NOT NULL in the schema.

HTH,

sluggy

|||

Yes, I thought that might be the case, too, but when I looked at it in the debugger, none of the parameters had values in them -- even though I was setting them in my code. So I'm still thinking the values of the params aren't being set somehow.

Report not in Database

I have a Tool Program (Tool Manager from QuickPen) that I need to add a feild that is in another report but I can't figure out how to get that feild in there I have tried to copy and paste but I get this error ( The field RptData.DisplayID does not exist in the database of this report)

Does anyone have any idea how I can fix this?If you copy a database field to another report, you must first make sure that your have the same database table and same field in the report you are pasting to. To be more precise, the table alias must be the same.
You can also copy and paste formulas, but all fields used in formula must also be present in destination report.

Report Model against Cube data source

We have successfully generated a Report Model against a Cube data source. My question is I can't seem to figure out how to modify the model. The wizard organized and named entities in an odd way - and even finding the measures is in some obscure place. I know how to create models in BIDS, but it doesn't seem to give me that same flexibility against a cube source.
unfortunately you can't edit it using VS.
maybe you can manually edit the XML file behind the model... but its a huge job...
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:7FE0118E-8DD0-4D77-B39A-024254A0C10E@.microsoft.com...
We have successfully generated a Report Model against a Cube data source. My question is I can't seem to figure out how to modify the model. The wizard organized and named entities in an odd way - and even finding the measures is in some obscure place. I know how to create models in BIDS, but it doesn't seem to give me that same flexibility against a cube source.

Report Model against Cube data source

We have successfully generated a Report Model against a Cube data source. M
y question is I can't seem to figure out how to modify the model. The wizar
d organized and named entities in an odd way - and even finding the measures
is in some obscure place. I know how to create models in BIDS, but it does
n't seem to give me that same flexibility against a cube source.unfortunately you can't edit it using VS.
maybe you can manually edit the XML file behind the model... but its a huge
job...
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:7FE0118E-8DD0-4D77-B
39A-024254A0C10E@.microsoft.com...
We have successfully generated a Report Model against a Cube data source. M
y question is I can't seem to figure out how to modify the model. The wizar
d organized and named entities in an odd way - and even finding the measures
is in some obscure place. I know how to create models in BIDS, but it does
n't seem to give me that same flexibility against a cube source.

Report Model - where is it stored?

Hello,

I'm trying to figure out where a "report model" is stored in the ReportServer database. Can anyone tell me the tables and views where a report model definition is stored internally?

Thanks,

BCB

The ReportServer's Catalog table contains the information you want (I think). To get the info stored for each report, you can use

use ReportServer;

select * from Catalog

where Type = 2;

HTH.

|||

Thanks for your reply. The query you provided will return the RS reports that are stored in the Catalog table. I modified your query to return the Model data that I wanted. (See below.) The Type column has a value of 6 for models as you can see in the query. The answer to my original question appears to be that report models are stored in their entirety in the Content column. It is an image column that has a capacity of 2 billion bytes. I put the model in the "black box" category because it does not seem to be implemented (in the physical sense) as views or procedures that can be examined with the naked eye - my favorite tool.

USE ReportServer;

SELECT

ItemID,

Path,

Name,

Description,

Content,

DATALENGTH(Content) AS [Content Length],

Type

FROM

Catalog

WHERE

Type = 6;

Report Model - where is it stored?

Hello,

I'm trying to figure out where a "report model" is stored in the ReportServer database. Can anyone tell me the tables and views where a report model definition is stored internally?

Thanks,

BCB

The ReportServer's Catalog table contains the information you want (I think). To get the info stored for each report, you can use

use ReportServer;

select * from Catalog

where Type = 2;

HTH.

|||

Thanks for your reply. The query you provided will return the RS reports that are stored in the Catalog table. I modified your query to return the Model data that I wanted. (See below.) The Type column has a value of 6 for models as you can see in the query. The answer to my original question appears to be that report models are stored in their entirety in the Content column. It is an image column that has a capacity of 2 billion bytes. I put the model in the "black box" category because it does not seem to be implemented (in the physical sense) as views or procedures that can be examined with the naked eye - my favorite tool.

USE ReportServer;

SELECT

ItemID,

Path,

Name,

Description,

Content,

DATALENGTH(Content) AS [Content Length],

Type

FROM

Catalog

WHERE

Type = 6;

Report Hiding Duplicates, not wanted

I created a report which has duplicates on one field. I want the report to display the duplicates, but cannot figure out how. Anywhere I have found where there is a "Hide Duplicates" option I have it turned off (in matrix properties), but the report matrix still hides the duplicates. I don't understand why this would happen since the other fields on the same record are not duplicates. I tried grouping but that was no help.

The query in the dataset DOES return the duplicates as expected.

Anyone know how to fix this, or if it is a bug?

Thanks,

Chris

Hide Duplicates could be set to true in one of your textboxes. Right click on all of your matrix columns (which are textboxes) and go to Properties where you will Hide Duplicates checkbox. Or you can even select the textbox and click on F4 to see the Properties window.

Shyam

Report Headers vs Page Headers

Hi, this is probably a simple question but I just can't figure it out. I'm using SQL Server Reporting Services 2005 sp1.

I'm trying to design a report that displays a particular embedded image on the first page, and then a different one on every subsequent page. I.e full, fancy company logo on the first page, and a trimmed down version on every other. In Crystal, I would have simply used the Report Header and Page Header sections to achieve this - easy.

However, in SQL Reporting Services, there is only a Page Header section - therefore whatever I place in the Page Header shows on every page. Now, I know how to stop it displaying on the first page, but I don't know how to display the other image instead.

I read in a support forum that it is possible to do this by "placing report items above or below your data regions" but I can't get it to work.

Grateful for any assistance.

Matt

Hello Matt,

Here's what I did to accomplish this task.

In your 'Page Header' section, place your trimmed down image (you want to display on all pages but the first), and set the PrintOnFirstPage property to False. Then, in the 'Body' section, move everything down enough to place your fancy company image at the top. Now, your fancy logo will show on the first page and none after, and your trimmed image will display on all other pages.

Hope this helps.

Jarret

|||

Hello Jarret,

Thanks very much, that was helpful. I think I'm nearly there - one last Q though! Now that the Page Header is set not to print on the first page it leaves a white blank space where it will appear on the other pages. Therefore, the image placed in the 'Body' section appears about 1/3 of the way down the first page. Do you know any way to make it appear at the top - at the same level as the Page Header one?

Thanks again

Matt

|||

Here’s another way you could do this:

Remove all the images you created earlier

Change PrintOnFirstPage back to true for the Page Header

In the BackgroudImage.Value property of the Page Header, type this: =IIf(Globals!PageNumber = 1, "Link to image that should show on Page 1", "Link to image that should show on the rest of the pages")

Resize your Page Header section to fit the images.

Unless your fancy and trimmed images are the same height, there will be blank space below the shorter of the two.Since you can’t dynamically change the size of a section, I think that’s as good as you’re going to get.

Hope this helps.

Jarret

|||Thanks - I'll give that a try. SSRS is great, but there are some features which Crystal has had for years which it is sadly lacking |||I'm having the same issue. I've found questions about this dating back to July of 2004, but no answers. Is it simply impossible to make a Page Header not print (and not take up space) on the first page?

Report Headers vs Page Headers

Hi, this is probably a simple question but I just can't figure it out. I'm using SQL Server Reporting Services 2005 sp1.

I'm trying to design a report that displays a particular embedded image on the first page, and then a different one on every subsequent page. I.e full, fancy company logo on the first page, and a trimmed down version on every other. In Crystal, I would have simply used the Report Header and Page Header sections to achieve this - easy.

However, in SQL Reporting Services, there is only a Page Header section - therefore whatever I place in the Page Header shows on every page. Now, I know how to stop it displaying on the first page, but I don't know how to display the other image instead.

I read in a support forum that it is possible to do this by "placing report items above or below your data regions" but I can't get it to work.

Grateful for any assistance.

Matt

Hello Matt,

Here's what I did to accomplish this task.

In your 'Page Header' section, place your trimmed down image (you want to display on all pages but the first), and set the PrintOnFirstPage property to False. Then, in the 'Body' section, move everything down enough to place your fancy company image at the top. Now, your fancy logo will show on the first page and none after, and your trimmed image will display on all other pages.

Hope this helps.

Jarret

|||

Hello Jarret,

Thanks very much, that was helpful. I think I'm nearly there - one last Q though! Now that the Page Header is set not to print on the first page it leaves a white blank space where it will appear on the other pages. Therefore, the image placed in the 'Body' section appears about 1/3 of the way down the first page. Do you know any way to make it appear at the top - at the same level as the Page Header one?

Thanks again

Matt

|||

Here’s another way you could do this:

Remove all the images you created earlier

Change PrintOnFirstPage back to true for the Page Header

In the BackgroudImage.Value property of the Page Header, type this: =IIf(Globals!PageNumber = 1, "Link to image that should show on Page 1", "Link to image that should show on the rest of the pages")

Resize your Page Header section to fit the images.

Unless your fancy and trimmed images are the same height, there will be blank space below the shorter of the two.Since you can’t dynamically change the size of a section, I think that’s as good as you’re going to get.

Hope this helps.

Jarret

|||Thanks - I'll give that a try. SSRS is great, but there are some features which Crystal has had for years which it is sadly lacking |||I'm having the same issue. I've found questions about this dating back to July of 2004, but no answers. Is it simply impossible to make a Page Header not print (and not take up space) on the first page?