distinct select statement of households

I'm trying to match households based on last name, house no., street
and apt no. and select only 1 record so that I get something like 'the
DOE Family' when all criteria match. I still want to keep Frank Black
results.
Here is how the data is structured:
+--+--+--+--+
| Last Name | First Name | Initial | House No | Street | Apt |
+--+--+--+--+
| DOE | JOHN | | 100 | MAIN STREET | 3 |
| DOE | JANE | | 100 | MAIN STREET | 3 |
| BLACK | FRANK | M | 104 | CHESTNUT DR | |
I initially tried doing a subselect statement but I'm still not
getting just one result for 'Doe':
SELECT DISTINCT
[Last_Name],[First_Name],[Initial],
CONVERT(INT, [House_Number]) AS 'House',[Street_Name],[Apt_Number]
FROM address_tbl T
WHERE
freq >= 3 AND
Street_Name = (
SELECT DISTINCT TOP 1 Street_Name FROM address_tbl T1 WHERE
T.House_Number = T1.House_Number AND
T.Street_Name = T1.Street_Name AND
T.Apt_Number = T1.Apt_Number AND
T.Zip_Code = T1.Zip_Code
)
ORDER BY Street_Name, House
What am I doing wrong?
How do you define what First Name/Middle Initial to select?
Try:
SELECT Last_Name,
MAX(First_Name) AS First_Name,
MAX(Initial) AS Initial,
CONVERT(INT, House_Number) AS House,
Street_Name,
Apt_Number
FROM address_tbl T
WHERE freq >= 3
GROUP BY Last_Name, House_Number, Street_Name, Apt_Number
ORDER BY Street_Name, House
HTH,
Plamen Ratchev
http://www.SQLStudio.com

distinct select statement of households

I'm trying to match households based on last name, house no., street
and apt no. and select only 1 record so that I get something like 'the
DOE Family' when all criteria match. I still want to keep Frank Black
results.
Here is how the data is structured:
+--+--+--+--+
| Last Name | First Name | Initial | House No | Street | Apt |
+--+--+--+--+
| DOE | JOHN | | 100 | MAIN STREET | 3 |
| DOE | JANE | | 100 | MAIN STREET | 3 |
| BLACK | FRANK | M | 104 | CHESTNUT DR | |
I initially tried doing a subselect statement but I'm still not
getting just one result for 'Doe':
SELECT DISTINCT
[Last_Name],[First_Name],[Initial],
CONVERT(INT, [House_Number]) AS 'House',[Street_Name],[Apt_Number]
FROM address_tbl T
WHERE
freq >= 3 AND
Street_Name = (
SELECT DISTINCT TOP 1 Street_Name FROM address_tbl T1 WHERE
T.House_Number = T1.House_Number AND
T.Street_Name = T1.Street_Name AND
T.Apt_Number = T1.Apt_Number AND
T.Zip_Code = T1.Zip_Code
)
ORDER BY Street_Name, House
What am I doing wrong?How do you define what First Name/Middle Initial to select?
Try:
SELECT Last_Name,
MAX(First_Name) AS First_Name,
MAX(Initial) AS Initial,
CONVERT(INT, House_Number) AS House,
Street_Name,
Apt_Number
FROM address_tbl T
WHERE freq >= 3
GROUP BY Last_Name, House_Number, Street_Name, Apt_Number
ORDER BY Street_Name, House
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Distinct Count with Junk Dimension Attribute

Hi,

Suppose I have following Model Dimension which is formed based on Junk Dimension definition.

JunkID

ModelID

ModelCode

ModelTypeID

ModelTypeCode

ModelSpecificationID

ModelSpecificationCode

50

50

BAO

2

FFCVT

2

F1C1

53

53

BGO

1

FFAT

1

FW4A4

55

55

NBO

3

FRAT

9

V4A5

60

60

NCO

3

FRAT

9

V5A5

97

55

NBO

3

FRAT

11

V4A5

99

53

BGO

1

FFAT

13

F4A4

And In Fact Table Suppose there is data as below...

JunkID

Amount

50

5210983

53

9576

55

7994

60

7872

97

4306

99

6519

Now my requirement is to make Distinct Count on ModelID. If I makes Distinct Count on DimModel.ModelID then it gives me 5 on every combination in Cube. If I make Distinct Count on FactTable.JunkID then it gives me 6 in places of 5 as above seen data.

Please help, how can I make Distinct Count on ModelID in this situation?

Thanks in Advance

Bhudev

Are you excluding the ALL member? Take a look at the code sample below? It calculates a count 3 ways. In the first, I count the members of the dimension's attribute hierarchy. It includes the members from all levels including the ALL member because I didn't specify a level. In the next one, I specify the leaf level of the hierarchy which does not include the ALL member. The third example uses DISTINCTCOUNT.

Code Snippet

with

member measures.a as

COUNT([Geography].[Country].Members)

member measures.b as

COUNT([Geography].[Country].[Country].Members)

member measures.c as

DISTINCTCOUNT([Geography].[Country].Country.Members)

select

{[a],[b],[c]} on 0

from [Adventure Works]

;

Good luck,
Bryan

|||

Hi,

It works fine when we are making Calculated Members, but when Analysis Services 2005 has introduced Junk Dimension Concept. So I feel it should give facility to add Distinct Count Cube Measure based on its [Junk Dimension] associated Attribute Dimensions. As per my understanding if there is Junk Dimension and we have very important Attribute Dimension, in Junk Dimension, On which I have to add Distinct Count Measure which can be used across all combination, In that situation we can not, because I can add Distinct Count only on JunkID which will be wrong or not of my use, As I have explained above.

But we can add Several Calculated members as per our need.

Thanks Bryan C. Smith for your suggestion overall it was helpfull.

Bhudev

|||

Just to be clear, SSAS 2005 didn't add support for Junk dimensions. Junk dimensions are a concept from the Kimball group that are documented in their "The Data Warehouse Toolkit" and other books. They can be implemented in SSAS 2005 (and just about any other technology that supports regular old dimensions). :-)

B

Distinct Count with Junk Dimension Attribute

Hi,

Suppose I have following Model Dimension which is formed based on Junk Dimension definition.

JunkID

ModelID

ModelCode

ModelTypeID

ModelTypeCode

ModelSpecificationID

ModelSpecificationCode

50

50

BAO

2

FFCVT

2

F1C1

53

53

BGO

1

FFAT

1

FW4A4

55

55

NBO

3

FRAT

9

V4A5

60

60

NCO

3

FRAT

9

V5A5

97

55

NBO

3

FRAT

11

V4A5

99

53

BGO

1

FFAT

13

F4A4

And In Fact Table Suppose there is data as below...

JunkID

Amount

50

5210983

53

9576

55

7994

60

7872

97

4306

99

6519

Now my requirement is to make Distinct Count on ModelID. If I makes Distinct Count on DimModel.ModelID then it gives me 5 on every combination in Cube. If I make Distinct Count on FactTable.JunkID then it gives me 6 in places of 5 as above seen data.

Please help, how can I make Distinct Count on ModelID in this situation?

Thanks in Advance

Bhudev

Are you excluding the ALL member? Take a look at the code sample below? It calculates a count 3 ways. In the first, I count the members of the dimension's attribute hierarchy. It includes the members from all levels including the ALL member because I didn't specify a level. In the next one, I specify the leaf level of the hierarchy which does not include the ALL member. The third example uses DISTINCTCOUNT.

Code Snippet

with

member measures.a as

COUNT([Geography].[Country].Members)

member measures.b as

COUNT([Geography].[Country].[Country].Members)

member measures.c as

DISTINCTCOUNT([Geography].[Country].Country.Members)

select

{[a],[b],[c]} on 0

from [Adventure Works]

;

Good luck,
Bryan

|||

Hi,

It works fine when we are making Calculated Members, but when Analysis Services 2005 has introduced Junk Dimension Concept. So I feel it should give facility to add Distinct Count Cube Measure based on its [Junk Dimension] associated Attribute Dimensions. As per my understanding if there is Junk Dimension and we have very important Attribute Dimension, in Junk Dimension, On which I have to add Distinct Count Measure which can be used across all combination, In that situation we can not, because I can add Distinct Count only on JunkID which will be wrong or not of my use, As I have explained above.

But we can add Several Calculated members as per our need.

Thanks Bryan C. Smith for your suggestion overall it was helpfull.

Bhudev

|||

Just to be clear, SSAS 2005 didn't add support for Junk dimensions. Junk dimensions are a concept from the Kimball group that are documented in their "The Data Warehouse Toolkit" and other books. They can be implemented in SSAS 2005 (and just about any other technology that supports regular old dimensions). :-)

B

Distinct Count with Junk Dimension Attribute

Hi,

Suppose I have following Model Dimension which is formed based on Junk Dimension definition.

JunkID

ModelID

ModelCode

ModelTypeID

ModelTypeCode

ModelSpecificationID

ModelSpecificationCode

50

50

BAO

2

FFCVT

2

F1C1

53

53

BGO

1

FFAT

1

FW4A4

55

55

NBO

3

FRAT

9

V4A5

60

60

NCO

3

FRAT

9

V5A5

97

55

NBO

3

FRAT

11

V4A5

99

53

BGO

1

FFAT

13

F4A4

And In Fact Table Suppose there is data as below...

JunkID

Amount

50

5210983

53

9576

55

7994

60

7872

97

4306

99

6519

Now my requirement is to make Distinct Count on ModelID. If I makes Distinct Count on DimModel.ModelID then it gives me 5 on every combination in Cube. If I make Distinct Count on FactTable.JunkID then it gives me 6 in places of 5 as above seen data.

Please help, how can I make Distinct Count on ModelID in this situation?

Thanks in Advance

Bhudev

Are you excluding the ALL member? Take a look at the code sample below? It calculates a count 3 ways. In the first, I count the members of the dimension's attribute hierarchy. It includes the members from all levels including the ALL member because I didn't specify a level. In the next one, I specify the leaf level of the hierarchy which does not include the ALL member. The third example uses DISTINCTCOUNT.

Code Snippet

with

member measures.a as

COUNT([Geography].[Country].Members)

member measures.b as

COUNT([Geography].[Country].[Country].Members)

member measures.c as

DISTINCTCOUNT([Geography].[Country].Country.Members)

select

{[a],[b],[c]} on 0

from [Adventure Works]

;

Good luck,
Bryan

|||

Hi,

It works fine when we are making Calculated Members, but when Analysis Services 2005 has introduced Junk Dimension Concept. So I feel it should give facility to add Distinct Count Cube Measure based on its [Junk Dimension] associated Attribute Dimensions. As per my understanding if there is Junk Dimension and we have very important Attribute Dimension, in Junk Dimension, On which I have to add Distinct Count Measure which can be used across all combination, In that situation we can not, because I can add Distinct Count only on JunkID which will be wrong or not of my use, As I have explained above.

But we can add Several Calculated members as per our need.

Thanks Bryan C. Smith for your suggestion overall it was helpfull.

Bhudev

|||

Just to be clear, SSAS 2005 didn't add support for Junk dimensions. Junk dimensions are a concept from the Kimball group that are documented in their "The Data Warehouse Toolkit" and other books. They can be implemented in SSAS 2005 (and just about any other technology that supports regular old dimensions). :-)

B

Dissolve based on common value

I am looking for help with a SQL statement to do the following:
Compare each value in [FIELD2] with every other value in [FIELD2]. Then, for all exact matches, perform % calculations on corresponding values in [FIELD3] and [FIELD4] to update [FIELD5]. ex:

[FIELD1] - [FIELD2] - [FIELD3] - [FIELD4] - [FIELD5]
North - China - 20 - 40 - 50.0%
Central - Japan - 30 - 90 - 33.3%
South - China - 10 - 10 - 100.0%
East - Japan - 50 - 200 - 25.0%

The desired output would be like:
Overall China has [100*(20+10)/(40+10)]=60.0% apples
and
Overall Japan has [100*(30+50)/(90+200)]=27.5% apples

...maybe by creating an array for all values in [FIELD2] using PHP for the comparison??

Regards
Accessorselect t1.f1, t1.f2, t1.f3, t1.f4, 100 * ((t1.f3 + t2.f3)/(t1.f4+t2.f4)) As "%"
from table t1
INNER JOIN
table t2 ON
t1.f2 = t2.f2 AND
NOT t1.f1 = t2.f1;

Returns,

F1 F2 F3 F4 %
---- ---- ---- ---- ----
South China 10 10 60
North China 20 40 60
East Japan 50 200 27.5862069
Central Japan 30 90 27.5862069

Displaying Totals in matrix-control based upon a RunningValue-expression

Hi,
I have a Matrix-control which has to "summarize" a lot of data in the end,
based upon boolean values. (the column-grouping is a static group).
Customer1
Test1 0 1 0 Value 1
Test 1.1 Value 2
Test 1.2 Value 3
Test 2 1 0 1 Value 4
Test 2.1 Value 5
Test 3 0 1 0 Value 6
______________________________________
Totals: 1 2 1
Customer 2
Test 1 ...
The values are bool-values AND not always displayed. I want to summarize
the values as they appear on the screen, not how they are in the dataset.
Because, now the runningValues-function always summarizes to much, which is
also al the "invisible" data between the main groups. I have written
something to display 0 in the subgroups, but it doesn't change my result.
Is it possible to do this and, if so, how?
Thanks a lot for your answers!
IveAre you giving a Scope'
"Ive" wrote:
> Hi,
> I have a Matrix-control which has to "summarize" a lot of data in the end,
> based upon boolean values. (the column-grouping is a static group).
> Customer1
> Test1 0 1 0 Value 1
> Test 1.1 Value 2
> Test 1.2 Value 3
> Test 2 1 0 1 Value 4
> Test 2.1 Value 5
> Test 3 0 1 0 Value 6
> ______________________________________
> Totals: 1 2 1
> Customer 2
> Test 1 ...
> The values are bool-values AND not always displayed. I want to summarize
> the values as they appear on the screen, not how they are in the dataset.
> Because, now the runningValues-function always summarizes to much, which is
> also al the "invisible" data between the main groups. I have written
> something to display 0 in the subgroups, but it doesn't change my result.
> Is it possible to do this and, if so, how?
> Thanks a lot for your answers!
> Ive
>
>|||Yes,
I am!
Problem is that the Scope doesn't seem to work correctly: it refers to the
dataset in stead of the matrix itself.
I have tried something like this:
=RunningValue(Abs(cInt(Fields!PlannedChecked.Value)),Sum,"matrix1_SubGroup")
I again refer to the fact that Fields!PlannedChecked.Value is a boolean
value and that he doesn't seem to summarize the values as in the control,
but in the dataset. He doesn't summarize correctly.
The runningValue function was inserted with an IIF -statement in combination
with the InScope-function to determine whether I'm actually in a
summarize-column.
A count would also do the job, but how to do that if I can't put a
limitation on the scope?
A dSum would do the trick, but that isn't supported.
I'm really stuck on this one, but as long as someone hasn't officially told
me how the RunningValue-function works exactly in a matrix-control, I keep
searching for a solution and reason.
Regards,
Ive
"Soan" <Soan@.discussions.microsoft.com> wrote in message
news:EF2FBEA3-C751-4C63-84C5-C319251D5F7C@.microsoft.com...
> Are you giving a Scope'
> "Ive" wrote:
>> Hi,
>> I have a Matrix-control which has to "summarize" a lot of data in the
>> end,
>> based upon boolean values. (the column-grouping is a static group).
>> Customer1
>> Test1 0 1 0 Value 1
>> Test 1.1 Value 2
>> Test 1.2 Value 3
>> Test 2 1 0 1 Value 4
>> Test 2.1 Value 5
>> Test 3 0 1 0 Value 6
>> ______________________________________
>> Totals: 1 2 1
>> Customer 2
>> Test 1 ...
>> The values are bool-values AND not always displayed. I want to summarize
>> the values as they appear on the screen, not how they are in the dataset.
>> Because, now the runningValues-function always summarizes to much, which
>> is
>> also al the "invisible" data between the main groups. I have written
>> something to display 0 in the subgroups, but it doesn't change my result.
>> Is it possible to do this and, if so, how?
>> Thanks a lot for your answers!
>> Ive
>>

Displaying Number of Table Fields Based on Database Values

Hi guys,

I have this problem here. I have this database with fields LB1, LB2, LB3, LB4 and LB5. Each of these fields have their own respective values. I have another database field called ANSCODE to control the number of fields being displayed.

If the value of ANSCODE is 4, I will have to display fields LB1 to LB4. If the value of ANSCODE is 5, i will have to display fields LB1 to LB5. Can this be achieved with a table? Can someone enlighten me on this? Thanks.

Cheers,

Do you have one value for ANSCODE for the entire report, or different values for each detail row? If it's the former, you can define a table with five columns, then based on the value of ANSCODE, hide the column which shows LB5, i.e. set the hidden property on the table column to "=IIF(Fields!ANSCODE.Value=4, true, false)".

If it's the latter, you might have different number of fields shown in different table rows? In that case, you can hide the textbox for LB5 using the same expression above.

|||

look at this link perhaps it' will help you

http://www.codeproject.com/dotnet/DynamicReport.asp?df=100&forumid=205933&exp=0&select=1195355

Displaying Number of Table Fields Based on Database Values

Hi guys,

I have this problem here. I have this database with fields LB1, LB2, LB3, LB4 and LB5. Each of these fields have their own respective values. I have another database field called ANSCODE to control the number of fields being displayed.

If the value of ANSCODE is 4, I will have to display fields LB1 to LB4. If the value of ANSCODE is 5, i will have to display fields LB1 to LB5. Can this be achieved with a table? Can someone enlighten me on this? Thanks.

Cheers,

Do you have one value for ANSCODE for the entire report, or different values for each detail row? If it's the former, you can define a table with five columns, then based on the value of ANSCODE, hide the column which shows LB5, i.e. set the hidden property on the table column to "=IIF(Fields!ANSCODE.Value=4, true, false)".

If it's the latter, you might have different number of fields shown in different table rows? In that case, you can hide the textbox for LB5 using the same expression above.

|||

look at this link perhaps it' will help you

http://www.codeproject.com/dotnet/DynamicReport.asp?df=100&forumid=205933&exp=0&select=1195355

Displaying multiple records from one record

Hi there,

I've a complex stored procedure, which I'm trying to get a list of one or more users based the list of records that are being display. If I mentioned the overall store procedure here it'll turn you off, so I'll put the questions in bitesize chunks.

Example would be if Bob and Luke work for Acrm Corp and John for Maxwellarms Ltd, and there was an intimediate table which linked both. With my stored procedure I want to display both names in the same row as Acrm Corp. Is there a way to do this?

User table
Userid Username

1 Bob
2 Luke
3 John

Store procedure result
Companyname Username

Acrm Corp Null
Maxwellarm Ltd Null

Regards,

Since my last post, I found that the CMS is storing individual ids.

Still it would be nice to know how others are doing this.

An addition to my post above, I believe if I wanted more than one firstname and surname to appear in the columns, that I would have to create a different stored procedure retrieving the unique id from the first stored procedure.

Displaying information based on months

I've got to display information based on months. The user provides
The report with a year and then I provide the information for each
Month of that year. I have no idea where to start. Any suggestions on
How to pass the month either in number format or text to a stored
Procedure so that I can retrieve the needed information?
I don't want somebody to do it for me I just want some suggestions on
How to go about doing it.
Thanks in advance for any help.
MathiasCascading Parameters and datepart?
"mathias" <mathias.helbach@.gmail.com> wrote in message
news:1137378113.994027.129370@.g14g2000cwa.googlegroups.com...
> I've got to display information based on months. The user provides
> The report with a year and then I provide the information for each
> Month of that year. I have no idea where to start. Any suggestions on
> How to pass the month either in number format or text to a stored
> Procedure so that I can retrieve the needed information?
> I don't want somebody to do it for me I just want some suggestions on
> How to go about doing it.
> Thanks in advance for any help.
> Mathias
>|||Hi mathias
What i get from you query, you are having year as input, and you want
to display month wise details regarding that year. If it is the case
than i think you should develop ur logic arround group by clause over
month.|||Thanks Techotsav
That works like a charm I had to modify my query slightly but it works.
Thanks
Mathias

Displaying images in a report

Hi all

I have a report and I display specific images based on the parameter selected. i.e. If parameter value = "1" then I display "image1.jpg". The images were added as a part of the project so I was able to display the images by setting the value property on the images to the image file name. So if I want to display image1.jpg then I would set the value property of the image to "image1.jpg".

Now I have decided to create a folder called "images" in which I want to keep all the images. So I have removed the images from the project. In the value property of the image when I put "/images/image1.jpg" it doesnt work. I have also tried "images/image1.jpg" When I run the report in the Preview mode, the image is not shown. How do i fix this?

The directory struncture is so:

Reports folder

--images folder

-image1.jpg

-image2.jpg

--report1.rdl

--report2.rdl

Thanks guys!!

One thing you could do is embed the images so they're available without being published separately. But if you have a very large number of alternative images, I guess this might be cumbersome.

For dynamic images, you can create a parameter with the location/path of these reports, such as file://.... or /. finishing up with the final path slash. Now your values are something like

Code Snippet

IIF(paramvalue = "1",paramlocation & "image1.jpg", paramlocation & "image2.jpg")

... or maybe if the "1" represents a part of the image filename, it's really just something like the following (no IIF):

Code Snippet

paramlocation & "image" & paramvalue & ".jpg"

... IOW, you can fully-qualify the path, and this is one way I know for sure it works.

I'm not entirely clear on where you put the images and whether, therefore, the relative path you're using is going to work as you think it is. If it's on the Report Server you can try using Globals!ReportFolderand Globals!ReportServer to build up the image path but I haven't personally had much luck doing it that way.

You may also be using ReportViewer and trying to set the folder according to where your EXE is (or your webapp if it's a webform ReportViewer)? I think you still need to fully-qualify that path.

>L<

|||I have several images that I need to change based upon a parameter being passed in. I put the images in my images folder on my website and changed the image names to each parameter being passed. I then placed a web image on the report and changed the value of the image in properties to something like the following. "=http://server/images/" & Fields!parameter.Value & ".jpg" This way it changes the image based upon a parameter. Basically changing the image property to a string. Hope this might help.

Jason

displaying different reports based on a parameter

Hi,

I have bunch of reports that take same set of parameters. I am trying parametrize the report type so that depending on the report type selected, body should display that report when user hits "View report" button. How can I do this? Pardon me if there is an obvious solution as I am pretty new to the joys of MS Reporting Services.

Thanks a bunch.

add a subreport control and then set the sub report name using an expression

Displaying data in Columns based on Criteria

I have data in rows that I want to display in columns based on a value in
each row.
Here's an example of the data:
Employee ID Type Code Amount
123 PAY BONUS 1,000
123 PAY SALARY 5,000
123 DED INS 500
123 DED DENTAL 100
123 DED FLEX 50
Here's how I'd like to display the data:
EMPLOYEE CODE AMOUNT CODE AMOUNT
123 BONUS 1,000 INS 500
123 SALARY 5,000 DENTAL 100
123 FLEX 50
My problem is understanding how to display the items in each column starting
at the top.
Thanks for any help.
--
Charles Allen, MVPOn Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> I have data in rows that I want to display in columns based on a value in
> each row.
> Here's an example of the data:
> Employee ID Type Code Amount
> 123 PAY BONUS 1,000
> 123 PAY SALARY 5,000
> 123 DED INS 500
> 123 DED DENTAL 100
> 123 DED FLEX 50
> Here's how I'd like to display the data:
> EMPLOYEE CODE AMOUNT CODE AMOUNT
> 123 BONUS 1,000 INS 500
> 123 SALARY 5,000 DENTAL 100
> 123 FLEX 50
> My problem is understanding how to display the items in each column starting
> at the top.
> Thanks for any help.
> --
> Charles Allen, MVP
The best way to produce this type of layout is to create a matrix
report where your pivot column (the column that gets split into
multiple columns based on distinct values) is Type. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I'll give it a shot. Thanks
--
Charles Allen, MVP
"EMartinez" wrote:
> On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> > I have data in rows that I want to display in columns based on a value in
> > each row.
> >
> > Here's an example of the data:
> > Employee ID Type Code Amount
> > 123 PAY BONUS 1,000
> > 123 PAY SALARY 5,000
> > 123 DED INS 500
> > 123 DED DENTAL 100
> > 123 DED FLEX 50
> >
> > Here's how I'd like to display the data:
> >
> > EMPLOYEE CODE AMOUNT CODE AMOUNT
> > 123 BONUS 1,000 INS 500
> > 123 SALARY 5,000 DENTAL 100
> > 123 FLEX 50
> >
> > My problem is understanding how to display the items in each column starting
> > at the top.
> >
> > Thanks for any help.
> > --
> > Charles Allen, MVP
>
> The best way to produce this type of layout is to create a matrix
> report where your pivot column (the column that gets split into
> multiple columns based on distinct values) is Type. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Please do suggest me on the below requirement in addition to what Charles
Allen has asked for:
How can I add another column that shows the difference between first CODE
column value and second CODE column value?
Thanks in advance!
"Charles Allen" <callen@.nospam-bkd.com> wrote in message
news:AD136641-F3A8-42B3-A900-92E78174D443@.microsoft.com...
> I'll give it a shot. Thanks
> --
> Charles Allen, MVP
>
> "EMartinez" wrote:
>> On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
>> > I have data in rows that I want to display in columns based on a value
>> > in
>> > each row.
>> >
>> > Here's an example of the data:
>> > Employee ID Type Code Amount
>> > 123 PAY BONUS 1,000
>> > 123 PAY SALARY 5,000
>> > 123 DED INS 500
>> > 123 DED DENTAL 100
>> > 123 DED FLEX 50
>> >
>> > Here's how I'd like to display the data:
>> >
>> > EMPLOYEE CODE AMOUNT CODE AMOUNT
>> > 123 BONUS 1,000 INS 500
>> > 123 SALARY 5,000 DENTAL 100
>> > 123 FLEX
>> > 50
>> >
>> > My problem is understanding how to display the items in each column
>> > starting
>> > at the top.
>> >
>> > Thanks for any help.
>> > --
>> > Charles Allen, MVP
>>
>> The best way to produce this type of layout is to create a matrix
>> report where your pivot column (the column that gets split into
>> multiple columns based on distinct values) is Type. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||On Sep 30, 11:20 pm, Charles Allen <cal...@.nospam-bkd.com> wrote:
> I'll give it a shot. Thanks
> --
> Charles Allen, MVP
> "EMartinez" wrote:
> > On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> > > I have data in rows that I want to display in columns based on a value in
> > > each row.
> > > Here's an example of the data:
> > > Employee ID Type Code Amount
> > > 123 PAY BONUS 1,000
> > > 123 PAY SALARY 5,000
> > > 123 DED INS 500
> > > 123 DED DENTAL 100
> > > 123 DED FLEX 50
> > > Here's how I'd like to display the data:
> > > EMPLOYEE CODE AMOUNT CODE AMOUNT
> > > 123 BONUS 1,000 INS 500
> > > 123 SALARY 5,000 DENTAL 100
> > > 123 FLEX 50
> > > My problem is understanding how to display the items in each column starting
> > > at the top.
> > > Thanks for any help.
> > > --
> > > Charles Allen, MVP
> > The best way to produce this type of layout is to create a matrix
> > report where your pivot column (the column that gets split into
> > multiple columns based on distinct values) is Type. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Displaying data from multiple datasets in the same table

I have two datasets, the first one attached to a table, and I would like
to display a field from the second dataset based on a criteria. Think as an
analogy to joins, but for various reasons I cannot merge the queries to get
a single dataset (data is coming from different databases). So, I have a
common key and I want to lookup into the second dataset? How can I achieve
that?
Practical example: first dataset retrieves a list of people, with their
address and a column the contains the country code. In a different DB I have
a table that has associations for a country code a country name. How can I
display a list of people and the country name? No join at query level is
acceptable.
Regards,
MariusA data region can only be bound to one dataset. Why is a join in a query
unacceptable? Can you create a view in your database with a join in it and
create a dataset off of that view? Would that be an acceptable alternative?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marius Cristian CONSTANTIN" <mconstantin.nos.pam@.bigfoot.com> wrote in
message news:ewqG%23aGrEHA.348@.TK2MSFTNGP15.phx.gbl...
> I have two datasets, the first one attached to a table, and I would
like
> to display a field from the second dataset based on a criteria. Think as
an
> analogy to joins, but for various reasons I cannot merge the queries to
get
> a single dataset (data is coming from different databases). So, I have a
> common key and I want to lookup into the second dataset? How can I achieve
> that?
> Practical example: first dataset retrieves a list of people, with
their
> address and a column the contains the country code. In a different DB I
have
> a table that has associations for a country code a country name. How can I
> display a list of people and the country name? No join at query level is
> acceptable.
> Regards,
> Marius
>|||It's not acceptable because we are not using a database, but instead a
custom data extensions that gets data from our business layer objects. In
business layer we do complex calculations, so writing the reporting as pure
SQL queries wouldn't be feasible. So we have business layer object that
retrieves a list of persons, and a business layer that retrieves a table
with mapping from country codes to names. We would prefer not to implement
joins in our custom data extensions if it would be possible. Also, I guess
that subreports would do it, but is seems to be that it would be an
overkill, and would raise problems when going to numeric (orders with items,
and items with prices in a different dataset), because for example we could
have two different tables, and show the difference between two prices.
Regards,
Marius Cristian CONSTANTIN
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:Oc03VBHrEHA.1964@.TK2MSFTNGP12.phx.gbl...
>A data region can only be bound to one dataset. Why is a join in a query
> unacceptable? Can you create a view in your database with a join in it and
> create a dataset off of that view? Would that be an acceptable
> alternative?
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Marius Cristian CONSTANTIN" <mconstantin.nos.pam@.bigfoot.com> wrote in
> message news:ewqG%23aGrEHA.348@.TK2MSFTNGP15.phx.gbl...
>> I have two datasets, the first one attached to a table, and I would
> like
>> to display a field from the second dataset based on a criteria. Think as
> an
>> analogy to joins, but for various reasons I cannot merge the queries to
> get
>> a single dataset (data is coming from different databases). So, I have a
>> common key and I want to lookup into the second dataset? How can I
>> achieve
>> that?
>> Practical example: first dataset retrieves a list of people, with
> their
>> address and a column the contains the country code. In a different DB I
> have
>> a table that has associations for a country code a country name. How can
>> I
>> display a list of people and the country name? No join at query level is
>> acceptable.
>> Regards,
>> Marius
>>
>

Displaying Character Format in Numeric Field

I have a Calculated Member that displays values of 999.0 when the calculation cannot be computed based on certain criteria. I would like to be able to format the numeric field so that is displays a character such as "X" when the value of the Calculated Member is 999.0. Does anyone know how to do this?

If this is in Analysis Services 2005, you should be able to use the IIF() MDX function to return either the value or "x".|||

Thanks. That worked!

Displaying Character Format in Numeric Field

I have a Calculated Member that displays values of 999.0 when the calculation cannot be computed based on certain criteria. I would like to be able to format the numeric field so that is displays a character such as "X" when the value of the Calculated Member is 999.0. Does anyone know how to do this?

If this is in Analysis Services 2005, you should be able to use the IIF() MDX function to return either the value or "x".|||

Thanks. That worked!

Displaying Arrows in a report field

Hi

I need to display

1. >

2. up arrow

3. down arrow

for a field called Levels based on SCORE1 and SCORE2

I am planning on using IIF statements

but I need to Use WINGDINGS font for the arrow symbols

but how do i use it in value property of text box (start typing in arial then cannot swith to WINGDINGS)

THNXYou probably will not find a way to do two different fonts in the same textbox or table cell at the same time, however, I'm pretty sure I can help if you tell me how you want this to look. Where are the arrows in relation to your Score fields? When do you have Winddings? Which objects will have what fonts? Those are a couple questions that might help you get your question answered.|||

Another option is to use the embedded image. Upload the image and then you can use it within the IIF statement to display the appropriate image.

|||

Hi

I have a report fields like below

Level current Level Previous Trend

4 3 has decreased

6 7 has increased

3 3 remains same

I need arrows to indicate the Trend field going UP ,Down and >>

|||

Using MS Paint create three images -- Up(Up.JPG) , Down (Down.JPG), --> (Right.JPG)

Upload these three images as Embedded images into the report.

Now in the field of trend add one of these images. write the below code.

=iif((Fields!LevelCurrent-Fields!LevelPrevious)=0,"Right",IIF((Fields!LevelCurrent-Fields!LevelPrevious)>0,"UP",iif((Fields!LevelCurrent-Fields!LevelPrevious)<0,"Down","None")))

Now, depending on the trending difference appropriate image will be selected and displayed. Once you develop this you might have to slighlty play with the images and allignment to beautify the report. Hope this is clear and helps...

Raj

|||

The above property needs to go in "Data" property of the field

Source= Embedded

Value= Above given condition

|||Hi,
You can try this also:
Take a different text box for Trend column.write the below mentioned expression:
=IIf(Fields!LevelPrevious.Value < Fields!LevelCurrent.Value,6,iif(Fields!LevelPrevious.Value > Fields!LevelCurrent.Value,5,4))
And give the font style of that textbox as Wingding 3.
Then it will work the desired way.

Cheers,
Shri

Display text based on the value of a field

In my reports i have a field called mydataset.fruittype

In the details section when I display the data, currently the value is being displayed as 1, 2 etc...

I need to display for eg:

if the value of mydataset.fruittype = 1, then display apple,
if 2 then display mango etc...

How can i do that ? Do I need a formula editor for that.

I am using Crystal Reports 8.0

Can anyone please suggest me a solution.

Thanks.Create a formula @.fruits_name:

if {mydataset.fruittype} = 1 then 'Apple' else
if {mydataset.fruittype} = 2 then 'Mango' else
.
.
.
if {mydataset.fruittype} =n then 'xxxxxx'|||In the details section when I display the data, currently the value is being displayed as 1, 2 etc...

I need to display for eg:

if the value of mydataset.fruittype = 1, then display apple,
if 2 then display mango etc...

I think that you can create a view in your database set values you want to show it diretly in your report.


create view myview as
select
case when mydataset.fruittype=1 then
'apple'
case when mydataset.fruittype=2 then
'mango'
case when mydataset.fruittype=3 then
'orange'
end as fruit
from my table

using this, your client has less works to process the data and
server would make the work.