Grouping with Reporting services

Can anyone help me with grouping in Reporting services. I am more used to crystal reports drill-down method

For example i have a simple table that has timestamp and three other columns. I want to drill down by (after Grouping) for Day-Then- Hour and then show the details for three columns. And also group by one of the columns, if i get above working.

All i could do with Reporting services was stepped down model, but i have same dates repeated more than once. i would like them to be grouped under day and then show time stamps for times of day .

-Thanks all

You can do this by creating a Table and adding a grouping, which is grouped by the date portion of the timestamp field, and then place the detail fields in the detail section, as you are now. The expressions should be something like the following:

Grouping expression:
=Fields!TimeStamp.Value.Date

Grouping header textbox for the date:

=Fields!TimeStamp.Value.ToString("D")

Detail textbox for the time:
=Fields!TimeStamp.Value.ToString("T")

Ian

Grouping using two stored procedures

Hi,

I am creating a set of reports in Crystal showing emails sent and delivered from users within our organisation.

Each report uses a different stored procedure.

Report 1 shows emails sent:

Sender: Person in our org
Recipient: Person outside our org
Number: number of emails sent

Report 2 shows emails recived:

Sender: person outside our org
Recipient: Person in our org
Number: Number of emails received

In crystal, the reports are grouped around the sender for report 1, and the recipient for group 2 (therefore the reports are grouped around the person in our organisation).

Now I need to create a report showing the details of the two reports combined into one, but this creates a problem when i try to group. i need to distinguish between who is in our organisation and who is it, and then to group by them.

does anyone have any idea how this may be done?

i want the report to look like this:

Person in our organisation:
----------
Sent: bob@.yahoo.co.uk 26
sally@.hotmail.com 4
peter@.msn.com 12
Subtotal: 42

Received: fred@.company.co.uk 45
vicky@.hotmail.com 10
Subtotal 55

Total 97

and so on for each person.

Many thanks if you can helpEither create it as two subreports in Crystal, or use a UNION query to create a single dataset from both SQL statements. With the UNION query, you will probably want to add a dummy values that indicates "SENT" or "RECEIVED".|||With the UNION query, you will probably want to add a dummy values that indicates "SENT" or "RECEIVED".

how do i do this?

at the moment, the most i can come up with is

select * from vw_sent
union
select * from vw_received

and that's where my question comes from really, how to determine what addresses in each result are @.mydomain.co.uk and then to group by those. Becuase the resultset of this query is:

Sender Recipient Number
person@.mydomain.co.uk person@.hotmail.com 5
otherperson@.mydomain.co.uk person@.hotmail.com 2
otherperson@.hotmail.com person@.mydomain.co.uk 10

so how do i add an extra field in my resultset to show which email address is in my domain?|||select 'SENT' as Direction, * from vw_sent
union
select 'RECEIVED' as Direction, * from vw_received

...though you really should enumerate your field names instead of using *, especially in an UNION query.

Grouping problem

Ok, I need to create an invoice for different plans that customers are subscribed to but I can't seem to group it in crystal properly. The invoice needs to list the invoice # at the top, followed by all of the different plans the customer is subscribed to, then a quick one time sentence that reads: "Fees for services in connection with:", and finally I need to display the services.

So basically, I may get data from my database that looks like the following:
1 | 51271 (inv #) | Pension Plan (plan name) | Yearly Admin Fee (service desc.)
2 | 51271 (inv #) | Cafeteria Plan(plan name)| Government Forms (service desc.)

The report would then look something like this:
----------------
Invoice #
---------------
Plan(s):
Pension Plan
Cafeteria Plan
--------------
Fees for services in connection with:
--------------

-Yearly Admin Fee
-Government Forms
----------------

Does anyone know how I could group my report this way or can you point me to a tutorial that can help me out?

Thanks in advance.

-Goalie35Ok...try grouping by invoice number, list plans in the details section. In group footer, put a subreport with the fees for services linked to the main report based on invoice number.

Let me know if this helps :)

Grouping Issue

i am grouping one field(CustomerID) in Crystal Report 10 even though value(111) of that field(CustomerID) is repeating in entire row. for example

Output is coming this way

customer ID Product name
111 Key board
111 Mouse
111 Mother Board

Output is suppose to come this way

customer ID Product name
111 Key board
Mouse
Mother Board

Can anyone help me to figure out why the value of field is repeating in row even though i have put that field in Group Section???

Thank in AdvacneIs your output
Preview View
<GH>111
<D>111 Key board
<D>111 Mouse
<D>111 Mother Board

Design View
Group Header: Group #1 name
Details: <Customer_id><Product_name>

<GH> = Group Header <D> = Details

If so you left the <customer_ID> field in the Details section

Let me know if Im off.

Thanks

Grouping in crystal reports

Hi

I m using crystal reports ver 8.0.

I have a report which is grouped on a field called "states".

My requirement is that the data for each state has to begin from a fresh page. i.e, each group item has to start from the next page.

I m not able to do this in crystal reports. Can anyone tell me if this is possible in crystal reports. and if possible, how it can be done ?

Thanking in advancethis works for 8.5 I don't know about version 8.0

Set "New Page Before" in the Group Header

Grouping feilds

This should be fairly easy by my reckoning but i just can't seem to figure it out.

I'm fairly new on crystal reports so i'm learning on the fly as i do this and i have my boss asking me if it's ready hehe

On the screenshot attached all i want to do is group if their are multiple records and have the services display for that particular record.

I've attached the actual report as well if anyone get's chance to look at it. If i put the feilds in the details section and try and group the way i want it just all goes haywire.

Any ideas?

thanksthe report even with group will still show the name... this is because your Add on Services is different...|||I could suggest you create groups to records are shown again,
so they just would be shown one time.

sample
group for

File# Surname Fisrtname AsignStatus
------------
15498 Peter Jorn Active
Add on Services
-----
Global Temo Living
Visa & Inmigration

File# Surname Fisrtname AsignStatus
------------
1998 Name 1 Name2 Active
Add on Services
-----
Service1
Service2|||The other thing you can do is compare the File, SurName, FirstName, Asgn Status by checking all them with their previous values. If all are same then suppress them.|||Many thanks for the tips, i managed to get it working sort of similar to hensa22's example. The tricky bit with with crystal reports i'm finding is displaying the d@.mn data in a decent way hehe

--edit--
here is the final form if anyone does want to see itsql

Grouping based on multiple fields

I am using crystal report version 7.
I am linking the stored procedure to crystal report and display it's fields. I want to create the group having 2 fields and sum the amount field. At present, I can create group with only one field and sum the amount field based on this field.
How can I have the group defined by 2 fields?Create a formula joining the two fields:
{field1}+{field2}

and then group on that formula|||Thanks Anonymous2,
That resolved my problem!

GROUPING at runtime

All...

I am using vb.net 2003. i am trying to set report groupings of a crystal report at runtime based on user defined options. MSDN says this:

Dim FieldDef As FieldDefinition
FieldDef =
Report.Database.Tables.Item(0).Fields.Item(comboBox1().Text)
Report.DataDefinition.Groups.Item(0).ConditionField = FieldDef

However, the .ConditionField is Read Only, you cannot assign anything to it. I have searched the object browser at each level of this object model, and cannot figure out how to assign a group to a report.

Heres the link to MSDN... the code for SORTING cr at runrime works perfectly, its just the GROUPING code im having trouble with...

http://msdn.microsoft.com/library/d...resentation.asp

Thanks.The following Example is specific to Crystal Reports 8.5 using RDC and VB6, but you may be able to tweak it a bit to work for you...

The following code can be used to Change the Group through VB Code:

1. In Crystal, create a Formula Field and add 1 column (a String column works best).
2. Insert a Group and choose your Formula for the GroupBy.
3. Now, you can comment out the Column you entered, or you can leave it there, your choice.
4. In VB, add this line of code, substituting {ttxFileName.ColumnName} for the column you want to sort by: Report.FormulaFields(1).Text = "{ttxFileName.ColumnName} ". The FormulaFields can only take a long for the index, so you need to know what number your Formula is so you don't replace the wrong one. The numbers start at 1 and are incremented based on the order they were created (they are listed in chronological order).

Group Total Summary Help

I hope someone can help me with this one. I can't seem to find a way to solve my problem. I am converting a report from Crystal to RS. In Crystal I am using global variables to keep track of group totals for a final summary. I need a similar result from RS. Data example

Group A PK Field Summary Data Field 1 250 2 300 Group A Total 550 Group B 3 100 4 50 Group B Total 150 Grand Total 700

The underlying query contains detail data and I am using a table with two group levels. All details are hidden.

To calculate the totals at the detail level I need to know what the total value for the entire group is. This leads me to my problem, it is not possible (as far as I can tell) to summarize a summary (I get an error). I have tried using the code window to store variables but the value returns a 0. I found a suggestion here http://msdn2.microsoft.com/en-us/library/bb395166.aspx under Distinct Sum, but I can't call the function using the Sum command given that the formula to calculate the value is already using the sum command. I hope this makes sense.

Thanks,

Simone

Hello Simone,

In your example, I assume that the rows with 1, 2, 3, & 4 are the detail rows?

If so, In your Group A & B footer, put the expression =Sum(Fields!Data.Value)

Then place the same in your Grand Total row, summary column.

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your response. The rows 1, 2, 3, & 4 are actually summary rows of the details. All details are hidden. This is where my problem lies. The details are in the dataset but in the report I have them rolled up. To obtain each summary I have a formula that needs to know the summary for each group to determine the outcome. For example:

Group Summary 1 > 0 Use Formula A

Group Summary 1 <= 0 User Formula B

I can get the correct summary at this level, but I then need to total all of the summary records and obtain a second group total. Finally I need to sum all secondary group totals to obtain a grand total.

Thanks,

Simone

|||

I'm not sure I understand what you are trying to do with the formula, but...

Try using the same expression in your Grand Total summary that you are using in your Group Footer 1 and 2.

Example data:

Fruit Count Date

Apples 35 4/12/07

Apples 10 4/12/07

Apples 15 4/13/07

Apples 10 4/13/07

Pears 5 4/12/07

Pears 16 4/13/07

Pears 4 4/13/07

Plums 30 4/13/07

Here's how I am picturing your table:

GH1

GH2

Details =Fields!Count.Value

GF2 (by Fruit) =Sum(Fields!Count.Value)

GF1 (by Date) =Sum(Fields!Count.Value)

Grand Total =Sum(Fields!Count.Value)

In this case, your report would look like (with the details hidden):

4/12/07

Apples 45

Pears 5

Date Total: 50

4/13/07

Apples 25

Pears 20

Plums 30

Date Total: 75

Grand Total 125

Hope this helps.

Jarret

|||

Thanks again for your detailed answer. You have the right idea with the data, but because the detail data needs the summary for the total group I am not able to carry the expression down. I get an error (can't sum expression with aggregates.). Using your example, we would need to know the summary of total fruit for the day to determine the percentage of daily fruit represented by pears (or apples or oranges). I would then need to summarize all percentages for an overall total. For now I overcame my issue by bringing back group totals in my query. I was just hoping I wouldn't need to do this. In crystal I was able to create a global variable that could be set with each group and displayed at the end. I can't find a way to do this in RS.

|||

Have you try this : RunningValue(Expression, Function, Scope)

|||

I get the same error message:

Aggregate functions cannot be nested inside other aggregate functions.

|||

Maybe this will help. Here is the expression located at the first group level:

=iif(sum(FieldA) < 0
,sum(FieldA)*FieldB
,iif(sum(FieldC) = 0
,0
,iif(sum(FieldD * FieldC)/sum(FieldC) < 0
,(sum(FieldC)-sum(FieldE))*5.25
,(sum(FieldC)-sum(FieldE))*(sum(FieldD * FieldC)/sum(FieldC))
)
)
)

I get the correct result at this level. Now I need to sum all of these values for a grand total. There are no values at the detail level.

|||

Try putting that same exact expression in your other group level, and your report footer (for your grand total).

=iif(sum(FieldA) < 0
,sum(FieldA)*FieldB
,iif(sum(FieldC) = 0
,0
,iif(sum(FieldD * FieldC)/sum(FieldC) < 0
,(sum(FieldC)-sum(FieldE))*5.25
,(sum(FieldC)-sum(FieldE))*(sum(FieldD * FieldC)/sum(FieldC))
)
)
)

Jarret

|||I don't get the right total because the formula depends on the summary of each individual group total and not the overall total. The expression above evaluates each detail record for the first group level and produces a result based on that group's total. I did try to add the scope to the expression in the second group level total, but this resulted in a scope error. Unfortunately, there doesn't seem to be an easy solution but I am extremely grateful for the advice.

Group summary totals Help please

Hi,

I am pretty new to Crystal 8.5 and am having problems using formulas on a group summary field.

The database is > 1 million records and I am trying to group a field (for quicker report generation). The field is the time (in seconds) numeric and I want to be able to manipulate this field and get it into hhhh:mm:ss format.

In this application, I want to add up all the seconds for each person in a department, then give a total for each person however in hh:mm:ss format.

Eg Person Table

Name Duration(in seconds)

Peter 50
John 10
Peter 45
John 22
John 80
Peter 100

The Crystal generated SQL will generate

Peter 195
John 112

This is straight forward, however, I now need to convert this to hh:mm:ss format to give me

Peter 00:03:15
John 00:01:52

I can do the above if I look at each individual record (ie no grouping) and use formulas on the Duratin field, however, because of the size of the database, I want to group the records and then manipulate the SUM (Person."Duration") field.

Does anybody have a solution?

(I hope my explanation of my problem is clear ...)

Thanking you in advance.

TheodoreOK Guys,

Solved the problem.

Thanks anyway.

Theodore|||Theo,

I have similar problem, any chance of posting the solution

Regards

Steve Dyson|||Why do you not just use the same formula you used for individual records, to convert the summary? The field would be sum of duration.|||My problem is that i have the following:

Planned and unplanned items.

I can group and Sum them that is the easy bit.

Because this is going to be a sub report, i need these totals to appear in the Page header as i only want to see the Totals

Steve

Group Header to next page

Hi,

In my Crystal Report generated with VB6.0, I have added a Group Header on to the Report. This is followed by another group in the Report, all done with coding. I would like to know if I can print the report in such a way, that the 2 two groups should always be together. At times it so happens that the first group will be in one page and the next group will be in the next page. It would be easier for the user to understand the report if the groups are together. Can this be accomplished?

ThanksHi,

Explain your requirements with diagrams (examples), so that developers can understand your requirement properly.

Thanks.

Hi,

In my Crystal Report generated with VB6.0, I have added a Group Header on to the Report. This is followed by another group in the Report, all done with coding. I would like to know if I can print the report in such a way, that the 2 two groups should always be together. At times it so happens that the first group will be in one page and the next group will be in the next page. It would be easier for the user to understand the report if the groups are together. Can this be accomplished?

Thanks

Group Footer Overflow problem

Using Crystal Reports 7

Have a report that displays items and their amounts in the Details section

The report includes a Group section where in the Group Footer it displays the total amount of all the items from the Details section above.

We print on custom paper that lays out all the sections i.e Titles on the Page Header section, a box for the Details etc...

Problem arises when the Details section takes the entire page or most of it, leaving not enough room for the Group Footer section to print. Instead of generating a following page (i.e another page header etc) it dumps the footer on to the top of the next page - so we are left with the Group Footer (the totals) being displayed on our customs titles etc.

Therefore we end up with the subtotals being unreadable because they are printed on top of the titles of the custom page.

For some reason I can't get Crystal to regonise that this particular record flows on to another page.

I've tried using a variable to force a page break, but this either leaves big gaps in the report or some parts of the footer still overflow into the top on the next page.

Ideally I need to tell Crystal that the footer section should always print from this point on the page downwards (i.e. that start of the details section) - never above this point

I hope this is clear

Can anybody offer any other suggestions I could look into ??

CheersIn Crystal Reports 8.5, there's a property in Group Footers called PrintAtBottomOfPage. Maybe there's something similar in version 7?|||Yes Print at Bottom of Page is available in Crystal 7, however this would leave quite a significant bit of white space if say I had only a couple of detail lines.

Any other suggestions ?|||FYI

Seem to have come up with a solution that works so far:

Have created another Page Header section - so now have Page Header a and b
Page Header b is the same size as my Group Header section. Under Supress format for Page Header b have inserted:

pagenumber =1 or
InRepeatedGroupHeader = true;

Meaning that Page Header b will appear when there is no Group header section on subsequent pages

Sweet as!

Group by Month/Week/Day in DateTime Field?

Crystal Reports has the ability to group by datetime based on
month/week/daily basis in DateTime field. Can Reporting Server do this?I figured it out myself by using DataName and DatePart in SQL query:
SELECT DATENAME(mm, DateTime) + ', ' + CAST(DATENAME(yyyy, DateTime) AS
varchar(4)) AS MonthYearName, DATEPART(yyyy, DateTime) AS Year,
DATEPART(mm, DateTime) AS Month, *
FROM CorporateSales
Then, in Reporting Server, add the GROUP and then group the data by Month,
and by Year.
Add "MonthYearName" in header.
Add "Subtotal" in footer.
Bingo!!!
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:w_qdnf4Sq4V0-gneRVn-rw@.rogers.com...
> Crystal Reports has the ability to group by datetime based on
> month/week/daily basis in DateTime field. Can Reporting Server do this?
>
>

Group by Date

Hi all,

Firstly: I'm a newbie when it comes to reporting services (previously used to Crystal reports).

This is probably going to appear as a stupid question but here goes.

How do i group on a date time field. I have set up a report with a table and as my group i have selected my datetime field

[code]

=Fields!WeekBegin.Value

[/code]

When i group on this field each record still seems to have its own grouping even though all entries in my test database have the same date. I have noticed that the seconds don't match up on some entries and believe that this may have something to do with it. There are however still duplicates that should be grouped on. I really only want to group on the date part of the Datetime field.

Anyone got any ideas where i am going wrong.

Thanks in advance

Grant

If you only want to group on the date part, you can use the datevalue() function:

=datevalue(Fields!WeekBegin.Value)

or use the Format function to get the date part.