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 reports

We have a designed a table report to show the hierarchy like "Locations -> Station -> Prod. category -> Product Name -> Product Model" and the report have some measures like Quantity in stock, Quantity sold.......

By default the report shows all the Locations with sum of all the measures, on expanding any location all the Stations are shown and on expanding Stations all the product names are shown and on expanding any product name all the product models are shown.

Now the requirement is, the same report should show the details of all the Product names in a single group, from all the locations and all the stations. And when i click on All stations link i should be shown the same report with all the stations under all locations.

i.e. the same report should have one row with the static text in columns like All Locations, All Stations, All Prod. Category, All Product Names and All Product Models. When the user click on All Product Names column the report should expand to show all the products in all locations and all stations.

I have looked for many examples but couldn't find such type of report, Is it possible to create such type of grouping using SQL server reporting services (MSSQL 2005). Pl. provide me a example to implement this type of requirement.

Thanks,
Sri

Hi,

Can you explain why you can't use the grouping features of a normal table or matrix? In your query make sure every row has a column indicating the location, station, prod. cat. etc. In your table, make different groups on these columns.

Regards, Jeroen

|||

With grouping i cannot get all the product names under all the locations and stations. With grouping, i need to select specific location and station to see the product names under that location and station. Also i need to show all the product names under all the stations in any specific location and i also need to show the product names under selected station this is the requirement.

I think this is the basic feature any OLAP reporting engine should provide for analysis of data. The report should show how many Products are there in stock in the country and the same report should have options to expand and see which locations in the country have what stock and further drill-down show which station has what products in stock without navigation to other screens.

I am trying to use sub-reports for achiving this requirement, if there are any other simple solutions for this type of reports Pl. suggest with a example.

Thanks & Regards
Sri

|||

Hi Sri,

There have been a few cases in which I managed to built a more dynamic report using multiple tables in a report and using the navigation property of a cell to run the same report again using parameters which get their value based on the cell which was clicked on. So lets say you have a table with a dataset of locations and a table with a dataset of stations. Now clicking a particular location runs the same report again, with the location parameter set to the clicked location. The station dataset is filled with all stations of this location and displayed in the table with stations. But I don't think this approach will cover all your requirements though.

Regards, Jeroen

|||

Sri,

i am thinking that maybe the reason why you are not getting a list of all products when grouping by station is because empty rows are being returned from the cube, these get eliminated from the dataset. To fix this, use a COALESCEEMPTY in the MDX, that way you can return a zero for empty rows, and should get a complete listing of products.

|||

I tried using COALESCEEMPTY in the MDX but still not getting all the products in all the stations as a single group. I know this is becaue all the products are defined under some station and the there are no products with section empty.

In this report i should be able to see all the products in the country with the measures defined (Quanity in stock and quantity sold) and i should be able to select some location and should able to see all the products in that location with measures showing the measures within that location and if i select any station i should be able to show all the products and measures within that location and station.

Looks it is not possible to create such a report using MS SQL 2005 reporting services. If it is possible or if there are any alternative ways (to achive this without navigationg to other report screen) Pl. guide me through some example.

Thanks and regards,
Sri

Grouping question...

Hi,

I am migrating some reports from MS Access2003 to SQL 2005 Reporting Services.

I have a dataset which contains columns for Sex, Age, Name etc... I firstly display the contents of this dataset in a table and this is fine. I also need to display a table of the breakdown of the age and sex. E.G:-

< 16yrs 16yrs-24yrs 25yrs-64yrs 65yrs-74yrs 75yrs-84yrs >=85yrs
Male x x x x x x

Female x x x x x x

Does anyone know if this is possible. I was going to use a DCount function (As found in access) but I can not find it in SRS. What is thet best way to produce this result?

Thanks in advance for your time

Peter Tewkesbury

BlueFlower Limited

Conditional aggregation can be achieved as follows:

=Sum(iif(Fields!Age.Value >= 25 AND Fields!Age.Value < 65, 1, 0))

-- Robert

sql

grouping on date without time

Hello,

I have the following problem.

I a making reports based on a database that i do not control.

In that database i have a table with statistical data including a field with datetime informtion.

The format of the data I receive is "5/04/2007 7:43:27".

In my report i want to create a group which groups my event by date : "05/04/2007"

In my output i always get subgroups by date & time so "5/04/2007 7:43:27", "5/04/2007 7:43:28", ....

How can i group only on the date.

Vincent

Hello Vincent,

Right click on your group row for the dates, and select 'Edit Group...' In the 'Group on:' section, change your expression from =Fields!DateField.Value to =Format(Fields!DateField.Value, "MM/dd/yyyy")

Jarret

|||

Use this expression for grouping:

DateValue(Fields!DateField.Value)

This will set the time part of the datetime field to 00:00:00 and use only the date part. This gives better performance than formatting or any other solution.

Shyam

|||

Vincent,

I forgot to mention this in my last post...

You will probably want to show the date without the time as well, so just put the format statement as your textbox's expression in the group row. You could use Shyam's suggestion for the DateValue function (I didn't know it is more efficient, but I haven't noticed any performance degredation from using format), I just use the format for simplicity; both the group on expression and the textbox expression being shown will be the same.

Jarret

|||

Jarrett,

As you know, any operation (be it grouping or sorting or whatever) based on string is going to be more costlier (if not much more) than other datatypes and unfortunately Format function returns a string though we can still convert it using CDate and use that expression for grouping (which again becomes a 2 level conversion). Maybe there wont be a significant difference in performance unless there are millions of records.

Shyam

|||this is helpful
|||hi Lifesavers

I am getting this default format
3/6/2007 12:00:00 AM|||somehow I am using this following format and it seems to work.

=FormatDateTime(Fields!LOGINDTTIME.Value, 3)

now i am not able to sort it properly ...when i do sort it give following output

03/01/07 01:51:09 pm 03/01/07 11:46:35 am 03/01/07 04:42:53 pm 03/02/07 12:40:08 pm 03/01/07 03:56:04 pm

Please notice 03/01/07 11:46:35 am on second line, it is sorting it on numeric value not in am /pm...any ideas?
|||

Hello Anand,

If you want to display as 03/06/07 12:00:00 AM, you could use this: =Format(Fields!LOGINDTTIME.Value, "MM/dd/yy hh:mms tt"). The FormatDateTime uses your computer's regional settings to display the date/time.

As for the sorting on the table, you need to go to the Properties of the table, then click on the Sorting tab. Instead of using the Format in here, you should sort by the value in the field. You should be sorting by Fields!LOGINDTTIME.Value.

Jarret

|||

The best way to do it is to format the text box where you keep your date. (If it is a table it still will be a text box whithin a table)

So :

-> Right click -> Properties -> Format (tab) -> Format code: -> Ellipsis button [...] ->

... and here you have all kind of Standard formating e.g. date,time, currency ... Good Luck

|||

Hello Bernardo,

One of Anand's questions was how to format it like this 03/06/07 12:00:00 AM. Since there is no standard format that matches this, a custom format had to be used.

I'm not so sure that either way is a 'best' way (putting in a format code through the properties or using the format function), aren't they both doing the same thing? At least with the Format function, you can see the format directly in the textbox without navigating through the dialog box to find it or looking in the properties window.

Jarret

|||thanks Jarret,

now formating and sorting on date works as I wanted

thanks,
anand

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 Data into Periods for Reporting

Hi there.

I am working on a set of reports where I am summing/averaging data elements based on what period they are in. For example, the report output should look something like this:

Period Sum May '07 41 April '07 14 Q2 '07 55 March '07 36 February '07 28 January '07 22 Q1 '07 86 June '07 N/A YTD '07 141 December '06 33 November '06 27 October '06 42 Q4 '06 102 September '06 58 August '06 84 July '06 52 Q3 '06 194 June '06 40 May '06 41 April '06 14 Q2 '06 95 March '06 67 February '06 38 January '06 N/A Q1 '06 105 YTD '06 496

For each of the items I am summing, all I have is a datetime of when the event happened. This is a relational database (not a cube), so I am struggling with how to create the 'buckets' based on period. I think the best way is to dynamically create the buckets based on a given date. Is there a way in RS that it can do this bucketing for you?

Thanks, Mike

There are several ways to "create buckets". I recently posted something here http://spacefold.com/lisa/post/Partition-Magic.aspx having discovered some of the SQL 2005 syntax that I never knew existed, which may help you in your explorations of non-cube data -- and there is also a PIVOT clause which is really neat.

When you look at it, it looks as though you can't dynamically figure out how many buckets you have and go for it, but you actually can, if you write a bit of very easy dynamic SQL. I was actually planning on posting about that today, having helped a co-worker do it!

But in RS, you can do this using a matrix layout control, which basically does the thing for you, albeit with (from my POV) some frustrating and counter-intuitive ways of thinking.

Look into the matrix data region first, if you need to display the buckets across, and if you don't like it look into the PIVOT clause to do this in SQL Server (NB: if your data source isn't SQL Server you don't have this T-SQL syntax but there are ways of getting around that if you need to <g>.)

If you need to display the buckets down, I think you have even easier ways to do it. What you seem to be showing (as I read your example table) is a table that has grouping and you've suppressed the detail rows that might ordinarily appear with each line (the dates for each event). OK so far?

The very simplest way to get what you want is to write your query like this:

SELECT MONTH(eventdate) AS M, Datepart(quarter,eventdate) AS Q, YEAR(eventdate) AS Y, eventtype, eventdate from YourEventTable

... now you can summarize by having appropriate groups on the first three calculated values that you see in this query.

The wrinkle that most people have when doing this particular thing is that they actually want the fiscal month, the fiscal quarter, and the fiscal year rather than the base values that you see here. So you generally want to write a couple of UDFs that pass in your first month of fiscal year to handle this properly. These can be a PITA but once you've written them appropriately for your situation, you are usually okay forever. Give a shout if you find you need help with this part. (I may be offline for about a week, but if it is urgent I'm sure many other people can help you with this).

So that's how you do it if your "buckets" are rows, as they seem to be from your example layout. If they are really columns, again, look into matrix and pivot.

>L<

|||

Hi Lisa.

Thanks so much for the great response. I was working on doing something similar, but there are a few more wrinkles (aren't there always?). As you mentioned, the buckets are rows, so that is good, but in this case, my columns can vary, so I need to use a matrix control.

1. The data set I need is for the current quarter (based on getdate()) and the previous 5 full quarters. I think this can be easily handled in the where clause, so that should be ok.

2. The matrix control is strange in that when you add row groups, it actually adds them as columns on the report itself (I posted a question on this recently). My customer doesn't want it that way, so I have to get the dataset to match the control - meaning, I will need to have my dataset return the summed/averaged bucket rows and NOT have the control handle the buckets. So, I think I am stuck.

Can you see any way around that?

Thanks, Mike

|||

Hi again, Mike,

I can't actually think closely about what you're stuck on here, because (I think I said) I'm leaving on a trip and 'way late on preparing <s>. But, in fact, a PIVOT clause might be just the ticket here -- for this reason among others I did end up posting a blog entry about that. http://spacefold.com/lisa/post/Matrix-Rebuilt-More-non-standard-fun-with-T-SQL.aspx

It's discussing some aspects of the clause that you may or may not be interested it but it will give you some idea of the scope of what it can help you accomplish. In your case -- since you actually know how many buckets there are (6 quarters) -- it may be especially apt.

I'll be back in a week, if you haven't got what you need by then I'll do my best to help <s>. Look forward to reading whatever else has been posted on this thread by then!

>L<

|||

Thanks Lisa.

I'll check into it. Enjoy your time 'away'.

- Mike

Group Headers Not Showing In Excel

Hi,
I have created some reports that I need to export to Excel. I have realised
that when they are exported, the group headers do not appear in Excel, just
the table header and footer, group footers and details group.
Does anyone know why the group headers are ommitted from the Excel files and
how I can get around this problem?
ThanksAre the group headers hidden, or are they missing completely?
"Tabby Cool via SQLMonster.com" wrote:
> Hi,
> I have created some reports that I need to export to Excel. I have realised
> that when they are exported, the group headers do not appear in Excel, just
> the table header and footer, group footers and details group.
> Does anyone know why the group headers are ommitted from the Excel files and
> how I can get around this problem?
> Thanks
>|||They are missing completely.
I hope they fix this in the 2005 version of RS!
daw wrote:
>Are the group headers hidden, or are they missing completely?
>> Hi,
>[quoted text clipped - 6 lines]
>> Thanks
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1

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 Filter Not Working

I'm developing a simple client application, using local reports populated from a local dataset, which is in turn saved and read from xml.
I am trying to set up a VERY simple group filter expression and I'm running into problems. What I want to do is display all rows where a date column matches a parameter passed to the report. I'm setting the filter up using something like this:
Expression Operator Value
=Fields!SampleDate = =Parameters!DateParam
I keep getting the following error:

Processing Errors
Value cannot be null.
Parameter name: value
OK

I know I'm probably doing something wrong. The only filter that I could get to not give the error is 1=1. LOL. Please help!You may want to try this approach:

Filter expression:
=(Fields!SampleDate.Value = Parameters!DateParam.Value)

Operator:
=

Filter Value:
=True
-- Robert

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.

Gridlines in excel

We currently using reporting services to generate excel reports. I was unable
to switch on gridlines for my excel reports and reverted to the method where
I gave each cell a border to simulate gridlines.
These 'styles' prove to be problematic, especially with large spreadsheats.
I have a large 47MB spreadsheet which takes a few minutes to load. By
replacing the cell borders with gridlines and resizing the cells the size
drops to 36MB and it loads instantaniously.
How can I generate excel reports with gridlines 'switched on' by default.Not possible to enable grid lines from RS for excel, may be after excel is
created you can enable it by going to tools ->options->view click on check
box for gridlines. But I dont think you require like this to do that too each
and every users?
Amarnath, MCTS
"JRMalherbe" wrote:
> We currently using reporting services to generate excel reports. I was unable
> to switch on gridlines for my excel reports and reverted to the method where
> I gave each cell a border to simulate gridlines.
> These 'styles' prove to be problematic, especially with large spreadsheats.
> I have a large 47MB spreadsheet which takes a few minutes to load. By
> replacing the cell borders with gridlines and resizing the cells the size
> drops to 36MB and it loads instantaniously.
> How can I generate excel reports with gridlines 'switched on' by default.