Report Two Data Subsets in the same Grid

This is likely very easy, but I'm not sure which direction is the best
way to go...
Dimension:
Time: Date and Hour the record occurred
Cube:
Has a field that counts the number of times an event occurred in a
given hour
I want to show a grid that has for each day of the month how many times
an event occured between 8:00 and 16:00 and from 16:00 to 20:00 hours.
What is the best way to role up the records in the report?
I've seen two possible ways to do it...
1) Use calculated fields with IIF statements to try to role up the
records.
Problem: IIF statement doesn't seem to work correctly.
2) Use a Matrix control to Filter the records correctly.
Problem: I can't seem to have two Filters on the Hour Group in the same
Matrix. One for 08:00 to 16:00 and one for 16:00 to 20:00.
Clearly I'm missing some key concept, because what I'm asking has to be
commonplace. Can someone point me in the right direction?
Thanks,
Paul WI might have missed something, but this is my suggestion:
Create a dataset that returns the number of events in two section, one for
8-16 and the other for 16-20. You should be able to do this with a
calculated member and filter in your MDX query. (No good examples spring to
mind, but you can always ask in the microsoft.public.sqlserver.olap news
group.)
Then create a matrix, and have two coloums (one for each section of the day)
and group on date.
Kaisa M. Lindahl Lervik
"Paul W" <pbwedz@.yahoo.com> wrote in message
news:1155143250.949345.213080@.n13g2000cwa.googlegroups.com...
> This is likely very easy, but I'm not sure which direction is the best
> way to go...
> Dimension:
> Time: Date and Hour the record occurred
> Cube:
> Has a field that counts the number of times an event occurred in a
> given hour
> I want to show a grid that has for each day of the month how many times
> an event occured between 8:00 and 16:00 and from 16:00 to 20:00 hours.
> What is the best way to role up the records in the report?
> I've seen two possible ways to do it...
> 1) Use calculated fields with IIF statements to try to role up the
> records.
> Problem: IIF statement doesn't seem to work correctly.
> 2) Use a Matrix control to Filter the records correctly.
> Problem: I can't seem to have two Filters on the Hour Group in the same
> Matrix. One for 08:00 to 16:00 and one for 16:00 to 20:00.
> Clearly I'm missing some key concept, because what I'm asking has to be
> commonplace. Can someone point me in the right direction?
> Thanks,
> Paul W
>

Report Record Count in Header Section

Do anyone know how to do a count on the number of records in a data set and
display the information in the header section of the report? I can easily
perform this operation in the data section using the Count() method against
the data set, but RS does not allow datasets/fields to be referenced in the
header section. By required design, this record count textbox is above a
page count textbox (which must also be in the header since it's a global
variable), so I can't cheat by moving the field to the data section - it must
live in the header. Any help would be appreciated.
The only alternatives that I can think of are:
-Somehow reference a textbox that is using the Count() method in the data
section, and display the same text in the header section. (I have no idea
how I would do this.)
-Determine the record count and page count in SQL to move both fields to the
data section. I didn't want to do this because if the report layout changes,
the page count might not be correct.You can achieve this by using scope. Get the count of records for the table
level as follows:
In the header section add an expression as follows:
=Count(<field>, "<table name>")
Regards,
SaraS|||Unfortunately, you can't use Count() or CountRows() in headers or footers.
"saras" wrote:
> You can achieve this by using scope. Get the count of records for the table
> level as follows:
> In the header section add an expression as follows:
> =Count(<field>, "<table name>")
> Regards,
> SaraS
>|||Have you tried to use Report!Items.whatever.Value?
These fields are used in headers and footers. If you manage to get this data
in data section you can copy it to header section by
Report!Items.fieldname.Value.
"Bryan" wrote:
> Do anyone know how to do a count on the number of records in a data set and
> display the information in the header section of the report? I can easily
> perform this operation in the data section using the Count() method against
> the data set, but RS does not allow datasets/fields to be referenced in the
> header section. By required design, this record count textbox is above a
> page count textbox (which must also be in the header since it's a global
> variable), so I can't cheat by moving the field to the data section - it must
> live in the header. Any help would be appreciated.
> The only alternatives that I can think of are:
> -Somehow reference a textbox that is using the Count() method in the data
> section, and display the same text in the header section. (I have no idea
> how I would do this.)
> -Determine the record count and page count in SQL to move both fields to the
> data section. I didn't want to do this because if the report layout changes,
> the page count might not be correct.
>

Report pulls only first record

Hello,

First I'd like to thank Michel Smit for providing the instructions on how to create/recreate a report using VWD then converting it to C# Express. It was a tremendous help.

My report is generating, however, it is only pulling the first record in the database (there are four records in all). My report uses a stored procedure shown below:

ALTER PROCEDURE dbo.StoredProcedure1

AS

SET NOCOUNT ON;

SELECT inventory.*, Feeding.*

FROM Feeding CROSS JOIN inventory

Is there something wrong with this stored procedure? Or is there some other step I need to include. I was experiencing the "A data source instance has not been supplied for the data source" error before and I corrected that issue when I discover the report was looking at the wrong data source. However, on the information I found to the correct the problem it said that when the correct source is located, the relevant components should be created on the design surface which they are not, could this be contributing to my issue?

walkswan

if you want to pull all records regardless of whether or not they have related records in another table, then you must research the use of LEFT JOIN and RIGHT JOIN.

Hope that helps. Without seeing your data i'm just guessing that only 1 of your records has a related record in the joined table. (i believe cross join, like inner join, only shows records that have related records in BOTH joined tables.)

|||

Thank you, unfortunately I can't get it to work at the moment. I ran three more types of queries based on the suggestion and they all returned the information I wanted but when I run the report I still only get the first record. I did double check and I have three records in the second table with id's that match records in the first table. Any other ideas?

walkswan