Report pagination for charts

Hi,

We have developed few reports displaying data using chart layout. In the Data tab, we have specified MDX query that will return top 10 records.

But now, instead of restricting to just top 10 records, we would like to display all records and go in for pagination.

Is there some setting in the chart properties, where in I can display the first n records in first page, and the next n records( if available) in the next page and so on?

Can I specify the value of n somewhere in the propeties?

I read through many posts regarding pagination but those couldn't help me much.

Please help me in solving this problem.

Any help would be appreciated.

Thanks in advance!

No response yet!:(|||

One approach is the following:

Step 1: Add a table to the report
Step 2: Group by a number of rows
Right-click on the table and select Properties. Add a table group (with a group header, but no group footer)
Enter this for the group expression: =Ceiling(RowNumber(Nothing)/10)
This will cause the table to group on every ten rows. So you'll get a separate table group for every ten rows.
Step 3: Add a chart in the table group header
Design your chart.

Note: this approach is similar to the table inline charts approach discussed in the following whitepaper: http://msdn2.microsoft.com/en-us/library/aa964128.aspx

-- Robert

|||

Thanks a lot Robert!

I got the idea from the solution provided by you and did the following:

Step 1: Add a list to the report

Step 2: Added group expression for the list to group the data

Grouping =Ceiling(RowNumber(Nothing)/10)

Set the following properties for list:

KeepTogether: False

PageBreakAtEnd: True

Step 3: Added chart to the list

Set the property, PageBreakAtEnd: True for the chart.

The above steps solved my problem of report pagination for charts.

Thanks once again!

sql

Report pagination for charts

Hi,

We have developed few reports displaying data using chart layout. In the Data tab, we have specified MDX query that will return top 10 records.

But now, instead of restricting to just top 10 records, we would like to display all records and go in for pagination.

Is there some setting in the chart properties, where in I can display the first n records in first page, and the next n records( if available) in the next page and so on?

Can I specify the value of n somewhere in the propeties?

I read through many posts regarding pagination but those couldn't help me much.

Please help me in solving this problem.

Any help would be appreciated.

Thanks in advance!

No response yet!:(|||

One approach is the following:

Step 1: Add a table to the report
Step 2: Group by a number of rows
Right-click on the table and select Properties. Add a table group (with a group header, but no group footer)
Enter this for the group expression: =Ceiling(RowNumber(Nothing)/10)
This will cause the table to group on every ten rows. So you'll get a separate table group for every ten rows.
Step 3: Add a chart in the table group header
Design your chart.

Note: this approach is similar to the table inline charts approach discussed in the following whitepaper: http://msdn2.microsoft.com/en-us/library/aa964128.aspx

-- Robert

|||

Thanks a lot Robert!

I got the idea from the solution provided by you and did the following:

Step 1: Add a list to the report

Step 2: Added group expression for the list to group the data

Grouping =Ceiling(RowNumber(Nothing)/10)

Set the following properties for list:

KeepTogether: False

PageBreakAtEnd: True

Step 3: Added chart to the list

Set the property, PageBreakAtEnd: True for the chart.

The above steps solved my problem of report pagination for charts.

Thanks once again!

Report only showing a single record

I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
records in Table. However, the report only shows a single record/page. I
placed the fields on the report in the "Body". What am I missing here?Nevermind... Got it. It's the List item.
"Dan" wrote:
> I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
> records in Table. However, the report only shows a single record/page. I
> placed the fields on the report in the "Body". What am I missing here?|||When you place the fields on the blank layout surface you are not using any
of the controls that know about multiple rows. You need to place either a
table control or a list control and then drag and drop onto them.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:7F61EA6A-DB25-43FC-9117-84A2E7E037AA@.microsoft.com...
>I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
> records in Table. However, the report only shows a single record/page. I
> placed the fields on the report in the "Body". What am I missing here?sql

Report not returning all the records

Hello EveryBody,
I am designing report using report designer...I have defined two dataset..
First Dataset is just returning all the Category(i.e select CategoryName
from Category)
and in the second dataset i am passing that CategoryName to get all the
products belongs to that category...
So basically CagegoryName is my dropdown list to select category... Now
whiile is designing the report in the report body i have drag and drop all
the fields of second dataset...with their lebels.. but when i preview the
report i am just able to see first record even though each category has
atleast 10 products...
my sample textbox value is as =First(Fields!ProductName.Value, "Product_Set")
Eventhough i removed First and tried still it is returning one record...
Pls help
thxWhat you want in this case is to have one report with another report
embedded as a subreport. You will want to read up on subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
> Hello EveryBody,
> I am designing report using report designer...I have defined two dataset..
> First Dataset is just returning all the Category(i.e select CategoryName
> from Category)
> and in the second dataset i am passing that CategoryName to get all the
> products belongs to that category...
> So basically CagegoryName is my dropdown list to select category... Now
> whiile is designing the report in the report body i have drag and drop all
> the fields of second dataset...with their lebels.. but when i preview the
> report i am just able to see first record even though each category has
> atleast 10 products...
> my sample textbox value is as =First(Fields!ProductName.Value,
> "Product_Set")
> Eventhough i removed First and tried still it is returning one record...
> Pls help
> thx|||It is not a subreport.. It is just selecting a CategoryName from the dropdown
list on the top bar and passing that value to second dataset..
"Bruce L-C [MVP]" wrote:
> What you want in this case is to have one report with another report
> embedded as a subreport. You will want to read up on subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
> > Hello EveryBody,
> > I am designing report using report designer...I have defined two dataset..
> > First Dataset is just returning all the Category(i.e select CategoryName
> > from Category)
> > and in the second dataset i am passing that CategoryName to get all the
> > products belongs to that category...
> >
> > So basically CagegoryName is my dropdown list to select category... Now
> > whiile is designing the report in the report body i have drag and drop all
> > the fields of second dataset...with their lebels.. but when i preview the
> > report i am just able to see first record even though each category has
> > atleast 10 products...
> >
> > my sample textbox value is as =First(Fields!ProductName.Value,
> > "Product_Set")
> >
> > Eventhough i removed First and tried still it is returning one record...
> >
> > Pls help
> >
> > thx
>
>|||It sounds to me like one dataset is just for the dropdown for the parameter
to list. Then that gets passed as a parameter to the second dataset that you
want to display. My guess is that you are dropping textboxes on the report
design surface. What you want to do is drop a table (it will default to 3
fields). Then drag and drop fields onto it. Add additional columns by doing
a right mouse click on the table.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:1588A17A-D5BE-4325-A5B3-F979E03E0642@.microsoft.com...
> It is not a subreport.. It is just selecting a CategoryName from the
> dropdown
> list on the top bar and passing that value to second dataset..
> "Bruce L-C [MVP]" wrote:
>> What you want in this case is to have one report with another report
>> embedded as a subreport. You will want to read up on subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "mvp" <mvp@.discussions.microsoft.com> wrote in message
>> news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
>> > Hello EveryBody,
>> > I am designing report using report designer...I have defined two
>> > dataset..
>> > First Dataset is just returning all the Category(i.e select
>> > CategoryName
>> > from Category)
>> > and in the second dataset i am passing that CategoryName to get all the
>> > products belongs to that category...
>> >
>> > So basically CagegoryName is my dropdown list to select category... Now
>> > whiile is designing the report in the report body i have drag and drop
>> > all
>> > the fields of second dataset...with their lebels.. but when i preview
>> > the
>> > report i am just able to see first record even though each category has
>> > atleast 10 products...
>> >
>> > my sample textbox value is as =First(Fields!ProductName.Value,
>> > "Product_Set")
>> >
>> > Eventhough i removed First and tried still it is returning one
>> > record...
>> >
>> > Pls help
>> >
>> > thx
>>|||Yes that was the problem.. It is working now... Thanks
"Bruce L-C [MVP]" wrote:
> It sounds to me like one dataset is just for the dropdown for the parameter
> to list. Then that gets passed as a parameter to the second dataset that you
> want to display. My guess is that you are dropping textboxes on the report
> design surface. What you want to do is drop a table (it will default to 3
> fields). Then drag and drop fields onto it. Add additional columns by doing
> a right mouse click on the table.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:1588A17A-D5BE-4325-A5B3-F979E03E0642@.microsoft.com...
> > It is not a subreport.. It is just selecting a CategoryName from the
> > dropdown
> > list on the top bar and passing that value to second dataset..
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What you want in this case is to have one report with another report
> >> embedded as a subreport. You will want to read up on subreports.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> >> news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
> >> > Hello EveryBody,
> >> > I am designing report using report designer...I have defined two
> >> > dataset..
> >> > First Dataset is just returning all the Category(i.e select
> >> > CategoryName
> >> > from Category)
> >> > and in the second dataset i am passing that CategoryName to get all the
> >> > products belongs to that category...
> >> >
> >> > So basically CagegoryName is my dropdown list to select category... Now
> >> > whiile is designing the report in the report body i have drag and drop
> >> > all
> >> > the fields of second dataset...with their lebels.. but when i preview
> >> > the
> >> > report i am just able to see first record even though each category has
> >> > atleast 10 products...
> >> >
> >> > my sample textbox value is as =First(Fields!ProductName.Value,
> >> > "Product_Set")
> >> >
> >> > Eventhough i removed First and tried still it is returning one
> >> > record...
> >> >
> >> > Pls help
> >> >
> >> > thx
> >>
> >>
> >>
>
>sql

Report limited to Top 8 but not in the SQL satement?

I got a report that seems to be limiting its displayed data to the first 8 records but not in the SQL statement itself. Although I have been working with SQL for many years I'm very new to Reporting Services so it may be something very simple, like perhaps a property that can be changed on the report for me to increase the amount of records the report is pulling from the database?

I don't seem to find any property set to 8 though.

Also the report is not pulling the records in the order the query pulls them, but in an apparent random order, I don't need to change this but it may help describe the report a bit better.

In Visual Studio, are all the records returned when you execute your SQL statement while you're in the Data tab?

Check to see if you have any filters on your table. Right click on your table and go to Properties, then check the Filters tab. Filters limit your recordset after it is returned to the report, so your query would return it, but it wouldn't show on your report.

This may sound silly, but are you sure the rest of the records are not on another page?

Jarret

|||Yes, the records are all returned without limits in the Data tab, and no there are no extra pages. I was told by the report user that it is intentionally set to, according to him, "8 random records", but for the records appear to be the same 8 every time, just not sorted in any logical order.

I checked the filters in the dataset as that didn't occur to me at first but there were no filters for this dataset.
|||

Do you have any grouping in your table? If so, are you hiding the detail records and only showing the group header/footer?

Can you try running Profiler when the report runs to make sure the query is being run correctly?

Jarret

|||There is no grouping, The query is a very simple select, although it calls a Stored Procedure it is working perfectly. I tested it inside the Visual Studio Business Intelligence environment and inside SQL Management Studio, plus ran the simple SQL that is inside the Stored Procedure on it's own, all with the same results (all records being returned.)

I was told this report was intentionally designed to show 8 records, I simply was requested to increase it to 10.
|||

Well, I think I will have to defer to someone else to help you. I've never seen RS 'eat' records before without having a filter of some sort. Like a filter on the table, a 'top' in the query, or a 'set rowcount X'.

Can you get in contact with the report creator and see how they got it to only show 8?

You could open the RDL file in notepad and see if you can see an '8' anywhere, and what setting it is on.

Jarret

|||Thank you, i forgot that rpl files where xml. I looked it up and found a filter being applied to a "Table1", oddly it never shows in the interface.

Code Snippet

<Table Name="table1">
<Filters>
<Filter>
<Operator>TopN</Operator>
<FilterValues>
<FilterValue>=8</FilterValue>
</FilterValues>
<FilterExpression>=Fields!Account_Number.Value</FilterExpression>
</Filter>
</Filters>

I changed the 8 to 10 but I'm still lost on why it does not show up in the dataset filters... although also there is no dataset called "table1".
|||

I understand now. Here, do this:

In your properties window (F4), use the drop-down to select 'table1'. This is the name of the table object in your report. Then, in the properties window, find and click on 'Filters'. It should say (Collection) next to it, click on the '...' button in the filters property. This will open a dialog box with an expression, operator, and value. In your case, you will see the following settings:

Expression: =Fields!Account_Number.Value

Operator: =

Value: =8

Here is where you can change the number of records you want returned.

Jarret

|||Thank you, this information is very useful!

Report Header is not displaying

Hi Everybody,

I have a strange problem. I'm using CR 9. My report works fine. The problem is it if there is no records found, then it is not displaying anything including Report Header, Report Footer.

Report Header section contains just a heading, sys date and parameter values.

Have any idea? pls help me?

ThanksGot it! Any way thanks!|||Hi harmonycitra,
How did you get the solution? Did you use suppress event in the Report header?

Madhivanan|||No, Actually I have checked the "Suppress Printing if No Records" in Report Options dialog. (accidentally, and I simply forgot)

After checking all the possibilities, I noticed that one. So I removed it. It's working now.