Grouping record based on a condtion

TechnologyTypeSize

XYZA200

XYZ1A200

XYZ2A300

XYZ3A300

ABC1X238

ABC2X238

PQRB320

MNOC330

I have written a query on a table whose output will look like the above. I need to know if i should store this in a record set or create a temp table to get the following fuctionality.

Now I need to concatenate the Technology based on Type and size.

As you can see in Type A we have two sizes 200 and 300.

We need to group the Technology of type A with same size together.

So the output of the procedure should be

XYZ + XYZ1

XYZ2+ XYZ3

ABC1 + ABC2etc.

We need to concatenate the Technology string with the next technology if they have the same type and size.

Can somebody please help or send any sample code.

Any help is greatly appreciated

Thanks

Swapna

CTE solution for SQL Server 2005:

With MyCTE(Size, Type, col1, col2, myNum) AS

(

SELECT a.Size, a.Type, CONVERT(varchar(50), MIN(RTRIM(a.Technology))) as col1, CONVERT(varchar(50),RTRIM((a.Technology))) as col2, 1 as myNum

FROM techTable AS a GROUP BY a.Size, a.Type, CONVERT(varchar(50),RTRIM(a.Technology))

UNION ALL

SELECT b.Size, b.Type, CONVERT(varchar(50), RTRIM(b.Technology)) as col1, CONVERT(varchar(50), (c.col2 + '+' + RTRIM(b.Technology))) as col2, c.myNum+1 as myNum

FROM techTable AS b INNER JOIN MyCTE c ON b.Size=c.Size AND b.Type= c.Type

WHERE b.Technology>c.col1

)

SELECT a.col2 As Technology_combined, a.Size, a.Type FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Size, a1.Type FROM MyCTE a1

GROUP BY a1.Size, a1.Type) b on b.Size=a.Size AND b.Type= a.Type AND a.myNum= b.myNumMax

|||

you I am new to stored procedures...and working with the databse...so could you please explain the above code...I could not get much from it...Will the loop through the sample table I mentioned and return a set of concatenated Technology values....Please get back.

Thanks for your reply

Swapna

|||

and more over the data in the table is just an example...we are in no way concerned with the data in Technology Column...all we need to do is group the technology column data which have the same Type and Size

TechnologyTypeSize

XYZA200

ABCA200

ABC1A300

XYZ3A300

MNO1X238

ABC2X238

PQRB320

MNOC330

so the output should be XYZ+ABC

ABC1+XYZ3

MNO1+ABC2.... I hope I am clear now.

Please reply...Can we use cursors to do this...can someone explain how to use cursors for the above functionality

Thanks

|||

Hello:

The "techTable" would be the name of your table which holds your data.

The CTE code I posted will work in a recursive fasion.

If you are using SQL Server 2005, you can give the code a try run (remember to change the "techTable" to your table name).

|||

--CREATE TABLE MyTable(Technology VARCHAR(MAX), Type char(10), Size int)

--Enter the values suggested

--Run the following code

DECLARE @.Type CHAR(1)

DECLARE @.Size INT

DECLARE @.MyNewString CHAR(11)

DECLARE @.MyNewString2 VARCHAR(MAX)

SET @.MyNewString2 = ''

--Replace MyTable with your tablename

--Replace Technology, Type, Size with your field names

CREATE TABLE #Temp(MyNewString VARCHAR(MAX))

DECLARE c1 CURSOR FOR

SELECT mt.Type, mt.Size

FROM MyTable mt

OPEN c1

FETCH NEXT FROM c1

INTO @.Type, @.Size

WHILE @.@.FETCH_STATUS = 0

BEGIN

DECLARE c2 CURSOR FOR

SELECT Technology from MyTable Where size = @.Size and type = @.Type

OPEN c2

FETCH NEXT FROM c2

INTO @.MyNewString

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.MyNewString2 = LTRIM(RTRIM(@.MyNewString2)) + LTRIM(RTRIM(@.MyNewString))

FETCH NEXT FROM c2

INTO @.MyNewString

END

CLOSE c2

DEALLOCATE c2

INSERT INTO #Temp(MyNewString) VALUES(@.MyNewString2)

SET @.MyNewString2 = ''

FETCH NEXT FROM c1

INTO @.Type, @.Size

END

CLOSE c1

DEALLOCATE c1

SELECT * from #Temp

GROUP BY MyNewString

DROP TABLE #temp

|||

limno wrote:

CTE solution for SQL Server 2005:

With MyCTE(Size, Type, col1, col2, myNum) AS

(

SELECT a.Size, a.Type, CONVERT(varchar(50), MIN(RTRIM(a.Technology))) as col1, CONVERT(varchar(50),RTRIM((a.Technology))) as col2, 1 as myNum

FROM techTable AS a GROUP BY a.Size, a.Type, CONVERT(varchar(50),RTRIM(a.Technology))

UNION ALL

SELECT b.Size, b.Type, CONVERT(varchar(50), RTRIM(b.Technology)) as col1, CONVERT(varchar(50), (c.col2 + '+' + RTRIM(b.Technology))) as col2, c.myNum+1 as myNum

FROM techTable AS b INNER JOIN MyCTE c ON b.Size=c.Size AND b.Type= c.Type

WHERE b.Technology>c.col1

)

SELECT a.col2 As Technology_combined, a.Size, a.Type FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Size, a1.Type FROM MyCTE a1

GROUP BY a1.Size, a1.Type) b on b.Size=a.Size AND b.Type= a.Type AND a.myNum= b.myNumMax

This code is equivalent to my nested cursor approach and works, but I agree is a tad bit confusing...but nice work all the same.|||

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, max(case t2.seq when 1 then t1.Technology end)

+ max(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

Below working query uses pubs authors table and you can do the same based on your table schema.

select a2.city, a2.state
, max(case a2.seq when 1 then a2.au_id else '' end)
+ max(case a2.seq when 2 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 3 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 4 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 5 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 6 then ', ' + a2.au_id else '' end) as au_ids
from (
select a1.city, a1.state, a1.au_id, row_number() over(partition by a1.city, a1.state order by a1.au_id) as seq
from authors as a1
) as a2
group by a2.city, a2.state
order by a2.state, a2.city;

|||

Umachandar Jayachandran - MS wrote:

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, min(case t2.seq when 1 then t1.Technology end)

+ min(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

Not knowing cursors is a good thing? Can we go a step further with your logic and say not knowing SQL is a good thing? Use ADO?

...and could you post some working code. I'm interested in this approach but getting errors.

Thanks,

Adamus

|||

Umachandar Jayachandran - MS wrote:

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, min(case t2.seq when 1 then t1.Technology end)

+ min(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

I unmarked this as the answer because the poster requested a cursor approach.|||

Not using procedural logic when dealing with SQL is a good thing. Yes, you can use ADO/client-side code to do this but it will be very slow and inefficient. If you have a table that contains say millions of rows you will be moving those rows from client to server for each user and performing the logic on the client side. Moreover, you have to implement lot of specific logic on the client side whereas the SQL language has built-in functionality / primitives to solve complex problems easily.

Anyway, here is a query that uses pubs authors table:

select a2.city, a2.state
, max(case a2.seq when 1 then a2.au_id else '' end)
+ max(case a2.seq when 2 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 3 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 4 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 5 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 6 then ', ' + a2.au_id else '' end) as au_ids
from (
select a1.city, a1.state, a1.au_id, row_number() over(partition by a1.city, a1.state order by a1.au_id) as seq
from authors as a1
) as a2
group by a2.city, a2.state
order by a2.state, a2.city;

The query produces a comma-separated list of author ids for each state and city combination similar to the problem.

Grouping Question

HI there all

Here is my question for the day.

I am creating a report based of a table where i have employee information and hours that he has worked, I need to have the total of all hours, and also the total of Billable hours. there is a field called Billable hours.

Anyways, In my layout section, I get the correct total hours, due to the groupin g which is easy. I also added a new field to my Query for the field hours with an alias of billable hours. I need to put some sort of filter for this field. so that This will add up the hours that are billable, (bullable =true.). I tried to do this with the query and I had no success. I tried thought the report layout, on the properties of that field something along the lines of

=Fields!Billable_Hours.Value where Fields!billable.Value = 1,

but it does not like my where clause and it errors out so I can not run the report.

I I remove the where clause it does not error out, but it prints error on the field on the report.

Any ideas anyone ?

Thanks

Armela,

Try

Sum(iif(Fields!Billable.value>1,Fields!Billable_hours.value,nothing))

I think this should work.

Ham

|||That worked.
Thank you very much

Grouping on first 2 characters of a number

Well, here I am again needing help. :(

My report shows a long list of numbers and I want to separate them into groups based only on the first two characters of each number. For example:

3102
3106
3103

3201
3203
3204

3506
3504
3508
3509

How do I tell Crystal to look at only the first 2 characters and sort accordingly, keeping all the "31" "32" and "35" together? My report is based on a stored procedure so I cannot change the number into a string.

Thank you!!!make a formula in crystal report with Left(cstr(Number field) ,2) and in groupby instead of number use this formula|||Thank you so much! That was it!!!

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 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

Grouping Data Based On Return From Stored Procedure

I'm having some difficulty getting the appropriate results for my scenerio. I have two different datasets that I'm using. One is consisting of two joined tables and the other consisting of one sp. The sp's parameters rely on two things- one is the companyNum (inputed when the user runs the report) and two is the ContactNumType. The ContactTypeNum comes from the dataset of tables. I need to have a table consisting of this format:

ContactNumType1 (From the Tables)
File_Name1 (From the sp)
File_Name4 (From the sp)
File_Name3 (From the sp)

ContactNumType2 (From the Tables)
File_Name2 (From the sp)
File_Name7(From the sp)

ContactNumType3 (From the Tables)
File_Name5 (From the sp)

ContactNumType4 (From the Tables)
File_Name6 (From the sp)

File_Name10 (From the sp)
File_Name8(From the sp)
File_Name9 (From the sp)

So essentially what is going on is that every returned File_Name is grouped based upon the type of ContactNumType. My table returns the appropriate ContactNumTypes and the appropriate number of File_Names but returns only the first File_Name for each row. The File_Names should only grouped by the ContactTypeNums and each be unique. Is there any way to do that?

-
Edited: I still am trying to work this out. I've tried a few run-arounds but none have worked. Adding custom code apparently is too risky at this point because of the security precautions that I've been instructed to take. Any help would be greatly appreciated as this project has been going on for days now....

If I understand you correctly, the problem here is that the argument to the procedure needs come from the results of your two-table join. Question: Are you running SQL Server 2005 or SQL Server 2000? If you are running SQL Server 2005, I would suggest converting your stored procedure into a table function (if possible). If you can do this, then you will be able to use a CROSS APPLY join and pass the arguments to the newly written function based on the results of your two table join.|||The solution involved creating a new stored procedure combining the table dataset and the stored procedure. The one dataset made it much easier to directly throw each dynamic field into a row.

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 based on input parameters

I'm not even sure if this is possible. Our requirement is that the user can input whether to view hourly, daily, or monthly data. Originally I had created three separate reports, but I've been asked to try again.

Is it possible to specify when grouping to group by the whole operation date, or group by days, or group by weeks?

Thanks.Its possible if the grouping is done in logical way say days->weeks->months->years.
Trying to say that if you want to group by Monday or tuesday then the answer is no.
Now what you do is in one report you start from inside grouping by days, then by weeks, then by months and then by years.
Make a parameter through which you can suppress the section accordingly.
Hope you can understand my theory.|||Thank you for your explanation. That was a lot easier than I thought!

:)|||I've been re-doing one of my other reports and noticed that my solution still has one problem with it. I have grouped by (from outer level) : Year, Month, Product Type.

When I view the product type by month, all are listed as one would expect. However, when viewing by year they are not grouped correctly, ie :

Jan 2005
prod x - 5
prod y - 3
prod z - 4

Feb 2005
prod x - 1
prod y - 7

So when viewing by year, I would like to see Prod x - 6. Instead, I get the two individual listings of prod x, etc.

Is there a way around this?

Thanks.|||You should expect only one entry per year if you have done gouping by year->month->type.
Right click on Group Producttype and click on change group and make sure that Order is in ascending and not Original order.

B.thakkar|||Thanks, though everything is grouped with order ascending. I think the problem is that they are essentially still grouped by month (we just choose not to acknowledge/display this by suppressing the header).

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 members of a derived dimension

Hi, Have created a dimension based on a column in the FACT, called Age as given in the post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=199100&SiteID=1) . Now, I need to group these members in custom buckets. The 'aggregate*' property is not allowing for custom buckets. Since this is a derived dimension, it is not appearing in the list of dimensions under the 'Create Custom Member formula' under 'Add BI' feature. How to accomplish this?

Secondly, If I try to create a calculated member on this dimension, why should it be attached under an existing attribute? So, even this route didnt work.

Thanks ina dvance,

If you followed the steps in that post, what you created in the fact table with your case statement is a set of derived surrogate keys. You would then link these keys to a dimension created from a table/view in your dsv. In this dimension table you could then add an extra column for a grouping and specify the group for each member.

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 messes up results of view based on UDF !?!

Hi there,
we have a rather strange effect here were a group by on a view does not
return the expected results. We managed to nail it down to the fact that the
view is based on multiple fields being the result of the same User Defined
Function, but with different arguments. The error seems consistent and in
the example below you can easily see how it fails on the simpelest udf/view
on the pubs db.
Anyone can explain this ' Or better, tell us how to work around it ' (we
may have several situations in our application where this goes wrong, ly
we only just found out)
Thanks.
Cu
Roby
An example : (Pubs db)
DROP FUNCTION dbo.fn_to_upper_or_lower
GO
CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
@.int1 int) -- 0 = UPPER, 1 =
lower
RETURNS varchar(1024)
AS
BEGIN
DECLARE @.result varchar(1024)
IF @.int1 = 0
BEGIN
SELECT @.result = Upper(@.string1)
END
ELSE
BEGIN
SELECT @.result = Lower(@.string1)
END
Return(@.result)
END
GO
-- SELECT dbo.fn_to_upper_or_lower('hello', 0),
-- dbo.fn_to_upper_or_lower('hello', 1)
-- GO
DROP VIEW test
GO
CREATE VIEW test
AS
SELECT title_id,
pub_id,
title,
notes,
upper_case = dbo.fn_to_upper_or_lower (title, 0),
lower_case = dbo.fn_to_upper_or_lower (title, 1)
FROM titles
GO
SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
notes
FROM test
SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
notes
FROM test
GROUP BY title_id, title, upper_case, lower_case, notesLooks like a bug. I reported it and will be back when I have any info.
Tested on SQL2K Dev/SP3.
Bug seems to be fixed in Yukon (tested on CTP2).
BG, SQL Server MVP
www.SolidQualityLearning.com
"deroby" <deroby@.discussions.microsoft.com> wrote in message
news:3C4C4B2B-0646-4D24-9388-F96613CC3A1B@.microsoft.com...
> Hi there,
> we have a rather strange effect here were a group by on a view does not
> return the expected results. We managed to nail it down to the fact that
> the
> view is based on multiple fields being the result of the same User Defined
> Function, but with different arguments. The error seems consistent and in
> the example below you can easily see how it fails on the simpelest
> udf/view
> on the pubs db.
> Anyone can explain this ' Or better, tell us how to work around it ' (we
> may have several situations in our application where this goes wrong,
> ly
> we only just found out)
> Thanks.
> Cu
> Roby
> --
> An example : (Pubs db)
> DROP FUNCTION dbo.fn_to_upper_or_lower
> GO
> CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
> @.int1 int) -- 0 = UPPER, 1 =
> lower
> RETURNS varchar(1024)
> AS
> BEGIN
> DECLARE @.result varchar(1024)
> IF @.int1 = 0
> BEGIN
> SELECT @.result = Upper(@.string1)
> END
> ELSE
> BEGIN
> SELECT @.result = Lower(@.string1)
> END
> Return(@.result)
> END
> GO
> -- SELECT dbo.fn_to_upper_or_lower('hello', 0),
> -- dbo.fn_to_upper_or_lower('hello', 1)
> -- GO
> DROP VIEW test
> GO
> CREATE VIEW test
> AS
> SELECT title_id,
> pub_id,
> title,
> notes,
> upper_case = dbo.fn_to_upper_or_lower (title, 0),
> lower_case = dbo.fn_to_upper_or_lower (title, 1)
> FROM titles
> GO
>
> SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
> notes
> FROM test
> SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
> notes
> FROM test
> GROUP BY title_id, title, upper_case, lower_case, notes
>
>
>|||Roby,
This is a known bug. See this thread for details and some suggested
workarounds.
http://groups.google.co.uk/groups?h...&q=kryszak+kass
The bug occurs only in very restricted situations, so a workaround is
usually possible.
Steve Kass
Drew University
deroby wrote:

>Hi there,
>we have a rather strange effect here were a group by on a view does not
>return the expected results. We managed to nail it down to the fact that th
e
>view is based on multiple fields being the result of the same User Defined
>Function, but with different arguments. The error seems consistent and in
>the example below you can easily see how it fails on the simpelest udf/view
>on the pubs db.
>Anyone can explain this ' Or better, tell us how to work around it ' (we
>may have several situations in our application where this goes wrong, ly
>we only just found out)
>Thanks.
>Cu
>Roby
>--
>An example : (Pubs db)
>DROP FUNCTION dbo.fn_to_upper_or_lower
>GO
>CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
> @.int1 int) -- 0 = UPPER, 1 =
>lower
>RETURNS varchar(1024)
>AS
>BEGIN
> DECLARE @.result varchar(1024)
> IF @.int1 = 0
> BEGIN
> SELECT @.result = Upper(@.string1)
> END
> ELSE
> BEGIN
> SELECT @.result = Lower(@.string1)
> END
> Return(@.result)
>END
>GO
>-- SELECT dbo.fn_to_upper_or_lower('hello', 0),
>-- dbo.fn_to_upper_or_lower('hello', 1)
>-- GO
>DROP VIEW test
>GO
>CREATE VIEW test
>AS
>SELECT title_id,
> pub_id,
> title,
> notes,
> upper_case = dbo.fn_to_upper_or_lower (title, 0),
> lower_case = dbo.fn_to_upper_or_lower (title, 1)
> FROM titles
>GO
>
>SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
>SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
> GROUP BY title_id, title, upper_case, lower_case, notes
>
>
>
>|||Roby,
Also see http://support.microsoft.com/kb/883415.
SK
deroby wrote:

>Hi there,
>we have a rather strange effect here were a group by on a view does not
>return the expected results. We managed to nail it down to the fact that th
e
>view is based on multiple fields being the result of the same User Defined
>Function, but with different arguments. The error seems consistent and in
>the example below you can easily see how it fails on the simpelest udf/view
>on the pubs db.
>Anyone can explain this ' Or better, tell us how to work around it ' (we
>may have several situations in our application where this goes wrong, ly
>we only just found out)
>Thanks.
>Cu
>Roby
>--
>An example : (Pubs db)
>DROP FUNCTION dbo.fn_to_upper_or_lower
>GO
>CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
> @.int1 int) -- 0 = UPPER, 1 =
>lower
>RETURNS varchar(1024)
>AS
>BEGIN
> DECLARE @.result varchar(1024)
> IF @.int1 = 0
> BEGIN
> SELECT @.result = Upper(@.string1)
> END
> ELSE
> BEGIN
> SELECT @.result = Lower(@.string1)
> END
> Return(@.result)
>END
>GO
>-- SELECT dbo.fn_to_upper_or_lower('hello', 0),
>-- dbo.fn_to_upper_or_lower('hello', 1)
>-- GO
>DROP VIEW test
>GO
>CREATE VIEW test
>AS
>SELECT title_id,
> pub_id,
> title,
> notes,
> upper_case = dbo.fn_to_upper_or_lower (title, 0),
> lower_case = dbo.fn_to_upper_or_lower (title, 1)
> FROM titles
>GO
>
>SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
>SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
> GROUP BY title_id, title, upper_case, lower_case, notes
>
>
>
>|||Thx for the replies guys.
Bit strange to find this has been solved quite a while ago but still is in a
hotfix that only the happy few can get. And then still they make it sound as
if you'd rather prefer not to insall it all...
We'll have a look at the workarounds, they seem do-able, but it sure is a
pain =( I guess optimizers aren't always a developpers best friend...
Cu
Roby
"Steve Kass" wrote:

> Roby,
> Also see http://support.microsoft.com/kb/883415.
> SK
> deroby wrote:
>
>

Group based on hourly datetime

I have a report field that shows clock in and out for an employee. For example,

Date Classification Name
8/1/2006 6:30:26am IN A
8/1/2006 3:04:15PM OUT A
8/1/2006 7:30:26am IN B
8/1/2006 3:04:15PM OUT B

and so on...

I would like to have my report to show employees that were here from 7:00am -8:00am, 8:00am-9:00am, etc...

So my report would look like:
6:00AM-7:00AM
A
7:00AM-8:00AM
A
B

8:00AM-9:00AM
A
B
9:00AM-10:00AM
A
B

I'm not sure how to create time/hourly group and how i would achieve this. Please help!! Thanks,create a formula to extract hour
and then group by that formula

datepart('h',datetimefield)|||I created the formula:

datepart('h',{EmployeeClocking.WhenCreated})

and then grouped it on that forumula but it says:

The formula result must be a string!!

Thanks,|||That formula worked. But my results are not what I expected. For example, table:

Date Classification Name
8/1/2006 6:30:26am IN A
8/1/2006 3:04:15PM OUT A
8/1/2006 7:30:26am IN B
8/1/2006 3:04:15PM OUT B

gives me report:

6:00AM
A
7:00AM
B (I should get A because A worked until 3pm, but I get the ones that clocked in or out)
3PM
A
B|||You've not replied to my post on the other forum where you posted this question. :)

It was a simple request for what database you are running the report against, but I'll expand on why here.

If you use the clock in/out times to drive the report then you will only get hour intervals reported when someone actually clocks in/out within that hour, which is why you only got

6:00AM
A
7:00AM
B (I should get A because A worked until 3pm, but I get the ones that clocked in or out)
3PM
A
B

As you wrote '7:00am -8:00am, 8:00am-9:00am, etc...' I think you want output something like this instead:

6AM - 7AM
A
7AM - 8AM
A
B
9AM - 10AM
A
B
10AM - 11AM
A
B

...

3PM - 4PM
A
B

where you display all hours from the first clock in to the last clock out.

In which case I think you are going to need to generate a report with at least 24 rows (24 hours per day!) and then run a subreport for each hour interval to display those employees who clocked in before/during that hour and clocked out during/after it, on any given day.
There will be a slight complication if the clock in / out crosses the midnight boundary, but this can be overcome if necessary.

The 'problem' here is generating a report with at least 24 rows, preferrably exactly 24 rows, but not too many more than 24 rows. Which is why I asked what database you are running on.|||I'm running on SQL Server. I really need helplwith this coz I tried several things and it's not working. How would I do:

"In which case I think you are going to need to generate a report with at least 24 rows (24 hours per day!) and then run a subreport for each hour interval to display those employees who clocked in before/during that hour and clocked out during/after it, on any given day.
There will be a slight complication if the clock in / out crosses the midnight boundary, but this can be overcome if necessary.

The 'problem' here is generating a report with at least 24 rows, preferrably exactly 24 rows, but not too many more than 24 rows. Which is why I asked what database you are running on."

Thank you so much for your help.|||A report with 24 rows:

a) In Oracle, I might have written an 'Add Command' query in CR to get the first 24 rows from all_objects, using rownum. Can a similar thing be done in SQL Server?
b) create a specific table for this purpose with 24 rows of anything in it.
c) Use a data table that you know will always have at least 24 rows of data.

c's not the best one 'cos you'd need to suppress all records after the 24th one, and I think the subreport would still be run for all the extra records even though the detail is suppressed.

In the main report, create a formula to make a datetime out of the record number, something like
dateadd('h', recordnumber -1, today)

Add something from your main query to the supressed header section. (Anywhere really, it's just got to be used for the report to do anything.)
Create a subreport in the details and pass the formula to it as a parameter.
In the subreport, select the day's data from the employee clocking table with a formula like

// restrict to one day
date({EmployeeClocking.WhenCreated}) = date({?Pm-@.hour})
and
// clocked in before/during the hour
( {EmployeeClocking.Classification = 'IN'
and {EmployeeClocking.WhenCreated} < dateadd('h', 1, {?Pm-@.hour})
)
and
// clocked out during/after the hour
( {EmployeeClocking.Classification = 'OUT'
and {EmployeeClocking.WhenCreated} >= {?Pm-@.hour}
)

Then you should just need to fiddle with the format of the subreport and (back in the main report) the suppress blank sections / suppress blank subreport options etc. to get the format you want.

Note that the logic of your record selection might need tweeking. For instance, what if they clock in/out twice in a day or over a midnight boundary? Are your clock in/out records linked to pair them together? Can someone forget to clock in or out?

GridView based on SQLServerDataSource using a Select Union statement, impacts on Update an

I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):

SELECT
FirstName,
LastName
FROM
Master
UNION ALL
SELECT
FirstName,
LastName
FROM
Custom
ORDER BY
LastName,
FirstName

I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated!

Thanks,

Randy

SELECT
FirstName,
LastName,0 AS Editable
FROM
Master
UNION ALL
SELECT
FirstName,
LastName,1 AS Editable
FROM
Custom
ORDER BY
LastName,
FirstName

Only allow rows that editable is 1 to be edited, then use an update statement directly on custom for the rows that get editted.

|||

Thank you for the direction. I am unclear on your last sentence...can you provide a code snippet that illustrates what you are explaning?

I appreciate the help

Randy

|||

UPDATE Custom SETFirstName=@.FirstName,LastName=@.LastName WHEREFirstname=@.original_FirstName ANDLastName=@.original_LastName

|||

Thanks, the Update statement makes sense.

Last question: when you say "Only allow rows that editable is 1 to be edited", can you provide direction on what the code would be such that the EditTemplate never appears for Editable = 0? (i.e. so that Rows where Editable = 1 can go into edit mode but rows where Editable = 0 cannot).

Sorry for what may be basic questions...

|||

I really can't without knowing more about what it is you are trying to do, or how you've implemented your edit functionality.