Distinct list

Let’s say we have two tables with the following values:
Table p1: Product Table p2: Product
-- --
A B
B C
C D
C D
Task #1 - In a single query, generate a unique list of values from both
tables (i.e. A, B, C, D).Hi Danlin,
Select Distinct Product From P1
Union
Select Distinct Product From P2
<Throw in an order by here if you want>
Order By
Product
That should do it.
Richard|||SELECT product
FROM P1
UNION
SELECT product
FROM P2
David Portas
SQL Server MVP
--|||Someone throw me this problem and he wants a single query solution not using
union. Sorry about the confusion. Thanks.
"danlin" wrote:

> Let’s say we have two tables with the following values:
>
> Table p1: Product Table p2: Product
> -- --
> A B
> B C
> C D
> C D
>
> Task #1 - In a single query, generate a unique list of values from both
> tables (i.e. A, B, C, D).
>|||SELECT DISTINCT
COALESCE(P1.product, P2.product)
FROM P1
FULL JOIN P2
ON P1.product = P2.product
--
David Portas
SQL Server MVP
--|||Thanks, you're graet David.
"David Portas" wrote:

> SELECT DISTINCT
> COALESCE(P1.product, P2.product)
> FROM P1
> FULL JOIN P2
> ON P1.product = P2.product
> --
> David Portas
> SQL Server MVP
> --
>
>sql

Distinct document map labels

Here is what my document map tree looks like know...
Plant 1
Product Type 1
Product Code 1
Plant 1
Product Type 2
Product Code 1
Plant 1
Product Type 2
Product Code 2
Plant 1
Product Type 2
Product Code 3
What I would like the tree to look like is...
Plant 1
Product Type 1
Product Code 1
Product Type 2
Product Code 1
Product Code 2
Product Code 3
My table is grouped by the plant, product type and product code with no
details group.
Thanks for your helpyou have to make the groups to display like you want the doc map...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Heather M" <HeatherM@.discussions.microsoft.com> wrote in message
news:13BCFDAD-96C4-44FB-8CB1-2A47D2441B45@.microsoft.com...
> Here is what my document map tree looks like know...
> Plant 1
> Product Type 1
> Product Code 1
> Plant 1
> Product Type 2
> Product Code 1
> Plant 1
> Product Type 2
> Product Code 2
> Plant 1
> Product Type 2
> Product Code 3
> What I would like the tree to look like is...
> Plant 1
> Product Type 1
> Product Code 1
> Product Type 2
> Product Code 1
> Product Code 2
> Product Code 3
> My table is grouped by the plant, product type and product code with no
> details group.
> Thanks for your help|||Thanks for your reply. I thought I had my groups setup correctly. The list
I had my tables in was the culprit.

Distinct count problem

Hi,

I'm trying to create a measure (distinct count) for counting the number of stores which sell product. I used to fact table to count store_key but I've just realized this measure returns a count including stores which do not sell the product.

Daily Sales fact : store_key, net sales dollars .....

How should I define the calc in this case? I'm thinking conditional statements but not clear yet.

Please give me some comments. Thanks.

We do something similar - a count of invoices that have a coupon applied to them. There are two ways (that I know of) to do this:

1) at the fact level. Have a measure in your fact table that contains a 1 or 0 for each store that sells the product. Not a very efficient way, but it will get the job done. Do a sum on this measure. Depending on how you want to count your products, you could have a measure group that is store_key, product_key, sells_product (int). This will work, but definitely not efficient.

2) The way we do it is to make the item you are measuring an attribute of the dimension itself. In our case we have an invoice dimension which contains a "has_coupon" attribute. As part of the ETL we determine if the value of has_coupon shoud be "Yes" or "No". You then can query according to the values of this attribute. We then can do [Invoice].[Has Coupon].Children.Count, or any other derivation of it.

I would recommend option #2, but it all depends on your situation. If anyone else knows of other ways to model, I'm curious too.

Good luck,

John Hennesey

|||Not totally clear, but is it counting a NULL value when you don't want it to?

Just create another fact table in the DSV based off of the fact table where the key is not null:

select *
from fact
where
key is not null

And create the DISTINCT COUNT measure based of this new new table.
|||

thanks.

it's easy and it works fine

sql

Distinct Count Problem

I am using a standard star schema structure with a sales fact table and dimension tables joined by surrogate foreign keys. My product's dimension contains a hierarchy for SKU (the lowest level) and product class. I created a distinct count measure for "SKU Count" using the product key in the sales fact table. This works fine with no problem. Now I need to create a second distinct count measure for product class. I cannot get this to work. Product class exists as an attribute in the product dimension. I have tried several ways to get this to work but each time it results in an error. For example, I created an MDX calculation using both COUNT and DISTINCT COUNT and none of these worked. I also added the product class attribute into the fact table to see if this could work by creating a new distinct count against this column. This resulted in an error message.

Can anyone help with this? Basically, I am looking to create a distinct count measure against the product key in the fact table (which works fine) and than I want to create a second distinct count measure against the product class attribute that comes from the product dimension but is based off of the unique occurences of each product class in the fact table (referenced through the product class key).

David

Both these approaches should work, the MDX Calc would not perform as well as adding the product class attribute to the fact table. Can you tell us exact what errors you were getting.

With the fact table approach I think you will need to create a second measure group (based on the same fact table) as you can only have one distinct count measure in each measure group.

|||

Darren,

In order to create a second fact group based on the same table, would I need to create an alias table in the dsv first. This approach seems like it would be more resource intensive since I'm technically adding another 700,000 records with the "new" fact table.

David

|||

Yes, that's right you would have to scan through the fact table again, but each distinct count measure you create means another scan through the fact table. It depends how often you are processing your cube as this sort of volume should only take minutes. Technically you don't need to create an alias table, but I think it is the only way to do this through the UI, through an XMLA script you can create multiple measure groups off the one dsv object.

The alternative is to use the DistinctCount() function in a calculated measure, you would have to test it and see, but it can be quite slow depending on the size of your queries.

Displaying Aggregate Values in Header on every page

Hi all.
Product: SQL Server 2000 Reporting Services Service Pack
I am trying to display a calculated field in the Page Header for every page.
I have tried the suggestions written, but the textbox only appears on the
Page Header on the last page of the report.
For example, textbox29 in the body of my report is:
=First(Fields!Next_2_Years.Value, "AnalysisCngEECSalary")
In the Page Header, I just display the textbox:
=ReportItems!textbox29.value
The result only appears on the last page of the report. How can this result
appear on every page of the report? I tried using RepeatWith, but that did
nothing. Please help. Thanks.
--
-RB
:)Can someone please respond to this question? I appreciate it. Thanks.
--
-RB
:)
"capricorn" wrote:
> Hi all.
> Product: SQL Server 2000 Reporting Services Service Pack
> I am trying to display a calculated field in the Page Header for every page.
> I have tried the suggestions written, but the textbox only appears on the
> Page Header on the last page of the report.
> For example, textbox29 in the body of my report is:
> =First(Fields!Next_2_Years.Value, "AnalysisCngEECSalary")
> In the Page Header, I just display the textbox:
> =ReportItems!textbox29.value
> The result only appears on the last page of the report. How can this result
> appear on every page of the report? I tried using RepeatWith, but that did
> nothing. Please help. Thanks.
> --
> -RB
> :)|||It sounds like you have a data region (matrix, table, list) above textbox29
in the report layout that will grow to multiple pages at runtime. You could
set the RepeatWith value of textbox29 to the name of that particular data
region that expands over many pages - also try putting textbox29 right next
to that data region. The value should then repeat on all these pages and
will be available in the page header/footer also. BTW: you could set the
textbox29 Visibility to be always hidden - the value will still show up in
the page header/footer, but it will be hidden in the report body.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"capricorn" <capricorn@.discussions.microsoft.com> wrote in message
news:7A28CB6A-4E54-4A42-8C35-9D2894E11F3C@.microsoft.com...
> Can someone please respond to this question? I appreciate it. Thanks.
> --
> -RB
> :)
>
> "capricorn" wrote:
>> Hi all.
>> Product: SQL Server 2000 Reporting Services Service Pack
>> I am trying to display a calculated field in the Page Header for every
>> page.
>> I have tried the suggestions written, but the textbox only appears on the
>> Page Header on the last page of the report.
>> For example, textbox29 in the body of my report is:
>> =First(Fields!Next_2_Years.Value, "AnalysisCngEECSalary")
>> In the Page Header, I just display the textbox:
>> =ReportItems!textbox29.value
>> The result only appears on the last page of the report. How can this
>> result
>> appear on every page of the report? I tried using RepeatWith, but that
>> did
>> nothing. Please help. Thanks.
>> --
>> -RB
>> :)|||Robert,
I tried putting textbox29 next to table1 with the repeatwith option pointing
to table1 and this time the value of textbox29 only appears on this first
page. I tried putting textbox29 before table1 and the value still only
appears on the first page. The textbox value is not repeating with the table
that is spanning multiple pages.
--
-RB
:)
"Robert Bruckner [MSFT]" wrote:
> It sounds like you have a data region (matrix, table, list) above textbox29
> in the report layout that will grow to multiple pages at runtime. You could
> set the RepeatWith value of textbox29 to the name of that particular data
> region that expands over many pages - also try putting textbox29 right next
> to that data region. The value should then repeat on all these pages and
> will be available in the page header/footer also. BTW: you could set the
> textbox29 Visibility to be always hidden - the value will still show up in
> the page header/footer, but it will be hidden in the report body.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "capricorn" <capricorn@.discussions.microsoft.com> wrote in message
> news:7A28CB6A-4E54-4A42-8C35-9D2894E11F3C@.microsoft.com...
> > Can someone please respond to this question? I appreciate it. Thanks.
> > --
> > -RB
> > :)
> >
> >
> > "capricorn" wrote:
> >
> >> Hi all.
> >> Product: SQL Server 2000 Reporting Services Service Pack
> >> I am trying to display a calculated field in the Page Header for every
> >> page.
> >> I have tried the suggestions written, but the textbox only appears on the
> >> Page Header on the last page of the report.
> >>
> >> For example, textbox29 in the body of my report is:
> >> =First(Fields!Next_2_Years.Value, "AnalysisCngEECSalary")
> >>
> >> In the Page Header, I just display the textbox:
> >> =ReportItems!textbox29.value
> >>
> >> The result only appears on the last page of the report. How can this
> >> result
> >> appear on every page of the report? I tried using RepeatWith, but that
> >> did
> >> nothing. Please help. Thanks.
> >> --
> >> -RB
> >> :)
>
>|||Which output format are you using? Note: RepeatWith for duplicating items in
the report body is only supported for physical page oriented renderers (such
as PDF), but at this point not for interactive renderers such as HTML.
I assume the textbox is sitting parallel to the table (not above and not
below). The correct value of that textbox should still be available in the
page header/footer.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"capricorn" <capricorn@.discussions.microsoft.com> wrote in message
news:1750AF6B-7CBE-4F21-9E98-26A60B93FF03@.microsoft.com...
> Robert,
> I tried putting textbox29 next to table1 with the repeatwith option
> pointing
> to table1 and this time the value of textbox29 only appears on this first
> page. I tried putting textbox29 before table1 and the value still only
> appears on the first page. The textbox value is not repeating with the
> table
> that is spanning multiple pages.
> --
> -RB
> :)
>
> "Robert Bruckner [MSFT]" wrote:
>> It sounds like you have a data region (matrix, table, list) above
>> textbox29
>> in the report layout that will grow to multiple pages at runtime. You
>> could
>> set the RepeatWith value of textbox29 to the name of that particular data
>> region that expands over many pages - also try putting textbox29 right
>> next
>> to that data region. The value should then repeat on all these pages and
>> will be available in the page header/footer also. BTW: you could set the
>> textbox29 Visibility to be always hidden - the value will still show up
>> in
>> the page header/footer, but it will be hidden in the report body.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "capricorn" <capricorn@.discussions.microsoft.com> wrote in message
>> news:7A28CB6A-4E54-4A42-8C35-9D2894E11F3C@.microsoft.com...
>> > Can someone please respond to this question? I appreciate it. Thanks.
>> > --
>> > -RB
>> > :)
>> >
>> >
>> > "capricorn" wrote:
>> >
>> >> Hi all.
>> >> Product: SQL Server 2000 Reporting Services Service Pack
>> >> I am trying to display a calculated field in the Page Header for every
>> >> page.
>> >> I have tried the suggestions written, but the textbox only appears on
>> >> the
>> >> Page Header on the last page of the report.
>> >>
>> >> For example, textbox29 in the body of my report is:
>> >> =First(Fields!Next_2_Years.Value, "AnalysisCngEECSalary")
>> >>
>> >> In the Page Header, I just display the textbox:
>> >> =ReportItems!textbox29.value
>> >>
>> >> The result only appears on the last page of the report. How can this
>> >> result
>> >> appear on every page of the report? I tried using RepeatWith, but that
>> >> did
>> >> nothing. Please help. Thanks.
>> >> --
>> >> -RB
>> >> :)
>>|||Robert,
I am looking at the Preview page within the Designer. As you suggested, I
put the textbox next to the table and the textbox value is available on the
Page Header of the first page. The textbox value does not repeat on
subsequent page headers.
--
-RB
:)
"Robert Bruckner [MSFT]" wrote:
> Which output format are you using? Note: RepeatWith for duplicating items in
> the report body is only supported for physical page oriented renderers (such
> as PDF), but at this point not for interactive renderers such as HTML.
> I assume the textbox is sitting parallel to the table (not above and not
> below). The correct value of that textbox should still be available in the
> page header/footer.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "capricorn" <capricorn@.discussions.microsoft.com> wrote in message
> news:1750AF6B-7CBE-4F21-9E98-26A60B93FF03@.microsoft.com...
> > Robert,
> > I tried putting textbox29 next to table1 with the repeatwith option
> > pointing
> > to table1 and this time the value of textbox29 only appears on this first
> > page. I tried putting textbox29 before table1 and the value still only
> > appears on the first page. The textbox value is not repeating with the
> > table
> > that is spanning multiple pages.
> > --
> > -RB
> > :)
> >
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> It sounds like you have a data region (matrix, table, list) above
> >> textbox29
> >> in the report layout that will grow to multiple pages at runtime. You
> >> could
> >> set the RepeatWith value of textbox29 to the name of that particular data
> >> region that expands over many pages - also try putting textbox29 right
> >> next
> >> to that data region. The value should then repeat on all these pages and
> >> will be available in the page header/footer also. BTW: you could set the
> >> textbox29 Visibility to be always hidden - the value will still show up
> >> in
> >> the page header/footer, but it will be hidden in the report body.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "capricorn" <capricorn@.discussions.microsoft.com> wrote in message
> >> news:7A28CB6A-4E54-4A42-8C35-9D2894E11F3C@.microsoft.com...
> >> > Can someone please respond to this question? I appreciate it. Thanks.
> >> > --
> >> > -RB
> >> > :)
> >> >
> >> >
> >> > "capricorn" wrote:
> >> >
> >> >> Hi all.
> >> >> Product: SQL Server 2000 Reporting Services Service Pack
> >> >> I am trying to display a calculated field in the Page Header for every
> >> >> page.
> >> >> I have tried the suggestions written, but the textbox only appears on
> >> >> the
> >> >> Page Header on the last page of the report.
> >> >>
> >> >> For example, textbox29 in the body of my report is:
> >> >> =First(Fields!Next_2_Years.Value, "AnalysisCngEECSalary")
> >> >>
> >> >> In the Page Header, I just display the textbox:
> >> >> =ReportItems!textbox29.value
> >> >>
> >> >> The result only appears on the last page of the report. How can this
> >> >> result
> >> >> appear on every page of the report? I tried using RepeatWith, but that
> >> >> did
> >> >> nothing. Please help. Thanks.
> >> >> --
> >> >> -RB
> >> >> :)
> >>
> >>
> >>
>
>

Display image in Crystal Report 10

I have to generate a report containing data and images in the database, e.g. product information + product image (kept as "image" type in MSSQL database). The information and images are kept in one table named "ProductInfo". The no. of images may vary depending on user input, e.g. on-shelf date. The expected output of the report is similar to below:

<<image1>>
Item: ABC Shampoo
Product No.: 1111

<<image2>>
Item: XXX Toothpaste
Product No.: 2222

<<image3>>
Item: YYY Shaver
Product No.: 3333

User will input the date range in a VB form and a Crystal Report Preview will show the output. I am currently using VB.NET 2003, Crystal Report 10, MSSQL 2000. Would you please help? Thanks very much.

Regards,Store image path in database field, then use picture box in detail section to display pictures.

U can use graphic location property in format editor of picture box...