Page break

Hi All,

I have created one report. When I click on view report information about different countries will be displayed.

By default, reporting services breaks pages of report on some criteria.

I want to break the pages of report on country basis.

For example: Australia: page should include only rows belonging to Australia.

Please teach me how to do this.

Regards

Abdul

Hi Abdul,

Have a read on my article below

http://www.koffeekoder.com/ArticleDetails.aspx?id=339

|||

Hi,

I hope you already have a group created in your report based on Country field. If yes you just need to select the table, right click properties, select groups tab, from the group list select the desired group in your case it will be country and click edit.

you are now on Grouping & Sorting properties dialog box. Just check Page break at end check box and that should resolve your issue.

Cheers,

...

Dont forget to click "mark as answered" on the post that helped you.

...

sql

Package failure still causing partial load

I have a package that is failing because of a truncation error. Now, by default (and I leave this for ALL my packages) if one row fails processing the entire package should fail and nothing gets loaded into db. But instead I am actually getting a partial db load.

I have confirmed the "Rows per btach" value (blank) and the "Maximum insert commit size" value (0) for the OLE DB Destination Editor so I have no idea what is going on. Are there any other properties I should be checking?

Thanks.

Jason

Why are you surprised that there is a partial load? You'll have to set MICS to equal to or greater than the number of rows coming into the source to have it such that if one row is bad, the whole batch is aborted.

The settings you have now are likely committing each row as they are inserted.|||According to the documentation, a "value of 0 indicates that all data is committed in a single batch after all rows have been processed". This, to me, tells me that all rows will be written or none. Since I am getting a truncation error, should I not be getting zero rows written?|||Well, I'm not so sure that SQL Server can accept an arbitrarily large bulk load. That is, I believe there's a limit to the size of the batch, and if it's exceeded, it will have to issue a commit or fail. I'm not sure.

You could redirect error rows out of the OLE DB command and try to see where that error occurs (row number or something).|||

Thanks for the responses Phil.

I know exactly where the error is happening. Just that in the past (with other packages) the execution loaded everything or nothing. (No matter the number of rows in the source.) It is just that the behavior for this package is not what I am used to and I cannot figure out why it is doing a partial load when the settings (as far as I can tell) are telling it not to.

- Jason

|||

To be honest, I have been playing with those 2 properties in the OLE DB Destination, so I can get bigger batches but I only can get around 9K-10K rows per commit as maximum. Not sure if SSIS or Bulk_insert look at the available resources and decided what value to use. I have no gotten the time to dig into that.

|||

Rafael Salas wrote:

To be honest, I have been playing with those 2 properties in the OLE DB Destination, so I can get bigger batches but I only can get around 9K-10K rows per commit as maximum. Not sure if SSIS or Bulk_insert look at the available resources and decided what value to use. I have no gotten the time to dig into that.

I think that SQL Server can only handle a batch size of 256 MB.

http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Overriding repeating totals from non-referenced slicer dimension

I've run across what appears to be a change in the default behavior of Analysis Services from 2000 to 2005. My question is how to get the default behavior to revert to the AS 2000 method.

If you have a cube with multiple measure groups or multiple linked cubes, queries that try to slice a measure using a dimension that is not related to the measure result in the total amount for any other slicers being repeated across the "invalid" slicer.

For example, the following query slices the reseller amount by calendar year and employee department. The results are as expected. Only the Sales department has any totals for the measure and all other departments return null.

SELECT { [Employee].[Employee Department].[All Employees].CHILDREN } ON COLUMNS ,{ DESCENDANTS( [Date].[Calendar].[All Periods], [Date].[Calendar].[Calendar Year] ) } ON ROWS FROM [Adventure Works] WHERE ( [Measures].[Reseller Sales Amount] );

The next query is identical, except it using the Internet Sales Amount measure, which comes from a measure group that does not have a link to the Employee dimension. The result is the total amount for internet sales is displayed across all employee departments. In AS 2000, the results would have been null or empty.

SELECT { [Employee].[Employee Department].[All Employees].CHILDREN } ON COLUMNS ,{ DESCENDANTS( [Date].[Calendar].[All Periods], [Date].[Calendar].[Calendar Year] ) } ON ROWS FROM [Adventure Works] WHERE ( [Measures].[Internet Sales Amount] );

At one level I can agree with the change, but non-the-less, it is a change in the default behavior, and users hate it. Is ther any way to revert this behavior back to the AS 2000 approach?

Thanks!

What you are running into is the fact that AS2K5 now applies ValidMeasure() automatically. You can revert back to AS2K behavior by setting the "IgnoreUnrelatedDimensions" property associated with your measure group to "False".

HTH,

- Steve

|||

Steve,

Many thanks! I knew it would be something simple, but was getting tired of looking...

Overriding Defaults

I have a stored procedure that has @.BeginDate and @.EndDate as parameters. I created a report with a default for both. They run just fine. After I deployed, I created Linked Reports and wanted to override the defaults. In the defaults, I tried to put in GetDate() for @.BeginDate and GetDate()+10 for the @.EndDate so this can be passed in the where statement of the stored procedure. I get 'Syntax error converting datetime from character string.'

What I assume is that if I override the default, the stored procedure will process what I put in the @.BeginDate and @.EndDate parameters.

The where statement looks like:

and (m.BeginDate >= @.BeginDate) and (m.EndDate <= @.EndDate)

I'm using Reporting Services 2005 and SQL Server 2005.

Thanks, Iris

I have figured out my own problem. In my where statement, in the stored procedure, I put in a case statement that calculates for today, yesterday, etc. for the BeginDate and EndDate parameters. I then created available values for today, yesterday, etc. I made the default for both parameters 'Today'. Then when I created my linked report from my master report, there was a drop down to select my date range options.

Thanks, Iris