Dimension with two names for each Key

Hi!

I need to build a dimension that offers two names for each entry.

For example, I have an electronics sparepart, a simple resistor that is called "abc-xy-12345" as company internal part number and also maybe "Resistor 1.0 kOhm 0.5 W" for a human being. Finally, for me as database guy, this part is a 4 byte integer number, I understand this is preferred to using the 15 byte part number. The dimension will have around 10,000 members when fully loaded, if that is important for the decision how to do it.

So I need to have a dimension that allows some of the users need to see the part as part number since they need to do a VLOOKUP in Excel or similar with the data, while the other usergroups needs to build a report upon the data, and they want to see a "self speaking name".

I do not want to build two nearly identical dimensions, what other way can I use to accomplish this?

If I add the order number to the text property of the key attribute and have another attribute on the long clear name, should this attribute be based on the integer for key as well and use name for the text?

Hi Ralf,

maybe the fastest way is create two attribute both with Key = your integer code.

One with Name = Company Part Number Name and the other with Name = Self Speaking Name

Francesco

|||

I was thinking that too, but where do you set the Key attribute to (the one that is displayed with the little golden key) for the dimension onto?

Anyone of the two, does not matter? Or have the ID alone (invisible) as the key as "anchor" and add the two names each as attribute?

|||

Hi Ralf,

the attribute with the little golden key is the attribute that has Set Attribute Usage = Key (right click on the attribute to check). It's used by AS as "unique key" in the dimension and in your case it has KeyColumn = your integer key code column .

Because both your attribute are at the lowest granularity in your dimension (1 member for every integer key code), you can:

use the one with Set Attribute Usage = Key (the one with the little golden key) with NameColumn set to the column you prefer (Company Part Number Name or Self Speaking Name)

then add another attribute (with Set Attribute Usage = Regular) with KeyColumn = your integer key code column and NameColumn = the other description you have.

Francesco

Dimension Security

Hi,
is it possible to secure specific dimension? I'd like to deny selected roles using certain dim. For example, pricing role shouldn't use Customer dim, Basic role should use just Date and Location dim. Could perspectives be used for that (specify dims/measures and grant access just to that perspective) or is there other way? Obviously, I must be overlooking something, this is really basic requirement, isn't it...

TIA,
Radim

Hi! Have a look at roles in the solution explorer. Right click on roles and explore the features.

Perspectives are not a security feature. They are more about easier browsing/navigation.

Regards

Thomas Ivarsson

|||Hi! Thanks, but of course I have tried that :) If you go there, you'll be able to see just two options (at least I do) allowed for dimension security: Read and Read/Write (or to inherit settings). I know that (unfortunatelly) perspectives are more for convenient browsing then for anything else.
Has someone really tried that with success?

Radim|||

On the following tab you have dimension data where you can give access to or deny members in dimensions.

Regards

Thomas Ivarsson

|||That's right. We've implemented dynamic member security system (clr callback function) already, but what I'm asking is how to deny the whole dimension for certain role. I don't want go down to members.

Why this cannot be implemented, is there any internal limitation?

Radim|||

OK. I understand. Can this link be of any help: http://sqljunkies.com/WebLog/mosha/archive/2005/04/10/10599.aspx ?

Regards

Thomas Ivarsson

|||Thomas, I really appreciate your effort :) But yes, I've read all post from Mosha (it's a must for everyone who wants to get deeper into AS :) ). I know that I could solve it by granting access just to Allmember in some dim, but that's not good. My thinking is - why should I unveil dimensions structure if I don't want to? If Customer dim (for example) is not interesting for certain role, why I just can't hide it. Or should I create new cube for that... ridiculous. I'm really missing this functionality and it seems strange to me, that nobody else is asking same question...

Thanks,
Radim|||

Sorry about consumed information. If you are interested in hiding a dimension by code I have not done that. I have implemented dynamic security with a fact table and a virtual cube in AS2000.

What I do not understand is why you cannot use the tab where you allow/deny access to dimensions and point that combinations to separate roles.

I have not tried this by code.

I do not know how big your cubes are but sometimes building a new cube can be the easiest solution, even if it not a good solution. We have done that depending on that we need to deny access to some measures and dependent calculations. Without separate cubes this will mean a large administrative burden. As long as they are in the same project they share dimensions and it is easy to copy scripts.

If you have large cubes I understand that this is impossible.

We have also the problem that ProClarity do not recommend dynamic security with their Analytics Platform.

Regards

Thomas Ivarsson

|||Thomas, in ideal world I'd like to have three choices on Dimensions tab: None, Read and Read/Write. I can see only Read and Read/Write. So that's useless. On Dimension Data tab I can deny all members from certain dim and that does the trick, but dimension is still visible (even though it cannot be used for analysis). Imagine that you have 20 dimensions in a cube (just as an example) and you deny all members for 18 of them. Now, in a browser (Proclarity or anyhing) you would still see all 20 dims, where 18 is completely useless. And that's what I'd like to overcome.

Could you please publish that recommendation about Proclarity? Dynamic security is heart of our cubes and Proclarity is the default browser.

And our solution is still growing, dwh is over 100GB and major cube around 10GB. So don't fancy idea about bulding and processing multiple cubes..

Radim|||

Here is a link to a ProClarity Blog: http://blogs.proclarity.com/blogs/dgustafson/default.aspx, regarding dynamic security.

Edit: I will have to test a little more time but I think you are correct. The dimensions will show but you cannot use them. If you construct perspectives after applying security that can help filtering denied dimensions.

Regards

Thomas Ivarsson

sql

Dimension formula

In a sales cube I have added dimension formulas to an account dimension. Example: NetRevenuePer100kg. The formula is:

NetRevenue / Quantity * 100

When I use this in a MDX query like

select

{[DIM_ACCOUNT].[NetRevenuePer100kg]} on columns,

{[DIM_PRODUCT].[WOOD]} on rows

from cube

where ([DIM_TIME].[2005].[Q1])

everything is fine.

But using the following calculated member in the where clause:

... member [DIM_TIME].[MY_TIME] as 'Sum([DIM_TIME].[2005].[Q1].[Jan]:[DIM_TIME].[2005].[Q1].[Mar]) ...

... where ([DIM_TIME].[MY_TIME])

I get a value which is about 3 times greater than the correct one.

I think it is a problem about the calculation order. It seems that first the value for each month is calculated and then they are summed up. Using the AVG function is not the solution. It gives only a almost correct value.

When I use a calculated member instead of a dimension formula everything is ok.

But I wanna get it right with a dimension formula.

Thanks for any help

Cornelius,

You are correct about your issue being related to solve order. In the example you gave you should be able to get the result you are looking for by replacing the SUM() function with AGGREGATE().

Try:

... member [DIM_TIME].[MY_TIME] as 'AGGREGATE([DIM_TIME].[2005].[Q1].[Jan]:[DIM_TIME].[2005].[Q1].[Mar]) ...

HTH,

- Steve

|||

Thanks Steve for your reply!

I tried it. But there is no difference between Sum and Aggregate.

|||

Cornelius,

I should have confirmed this to begin with, but is your "NetRevenuePer100kg" calculation in the cube? Can you provide the MDX for both calulations and the query?

- Steve

|||

Hi Steve,

formula in the account dimension [NetRevPer100kg]:

iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)

The complete MDX Query (please copy and enlarge in your editor):

with
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)'
member [TIME_CalendarYear].[BasisZeitraum] as 'SUM({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})'
--member [TIME_CalendarYear].[BasisZeitraum] as '[TIME_CalendarYear].[2005].[1. Halbjahr].[1. Quartal]'
member [PRODUCT_GROUP].[sy_Slicer] as 'AGGREGATE({[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044125)],[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044115)]})'
select
{
([ACCOUNT_Sales].[Quantity]),
([ACCOUNT_Sales].[ContributionMargin].[NetRev]),
([ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]),
([ACCOUNT_Sales].[Test_FINE])
} properties [ACCOUNT_Sales].[DISPLAY_CAPTION], [ACCOUNT_Sales].[DISPLAY_FORMAT], [ACCOUNT_Sales].[DISPLAY_STYLE] on columns, non empty
{ [SALES_MANAGER].[Alle Bereichsleiter].Children
} on rows
from Sales
where ([TIME_CalendarYear].[BasisZeitraum], [CATEGORY].[Rechnung],[PRODUCT_GROUP].[sy_Slicer])

The calculated member [Test_FINE] gives the correct result. The member based on the dimension formula [NetRevPer100kg] only when I use the time member [Quartal] (uncommented).

It is a strange thing for me, that a calculated member and a member based on a dimension formula returning different results allthough using the identical mdx expression. And it only happens when I use a Sum (or Aggragate function) on an other member in the query.

Thanks for a reply

|||

Cornelius,

I noticed in your MDX you still have a background member defined using SUM:

member [TIME_CalendarYear].[BasisZeitraum] as 'SUM({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})'

The reason I think you are running into this issue is related to solve order. The AGGREGATE function will correctly sum up the numerator and denominator before performing the division. SUM will simply add up the percentages. In AS2K5 all calculations in the cube script are evaluated before any query or session scoped calculated members. This is why you are probably seeing a difference in the cube based calculated member results versus your query scoped calculated member. It is important that any aggregated member in the WHERE clause be calculated using AGGREGATE rather than SUM.

HTH,

- Steve

|||

Hi Steve,

with
member [ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg] as '[ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]'
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)'
member [TIME_CalendarYear].[BasisZeitraum] as 'AGGREGATE({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})'
--member [TIME_CalendarYear].[BasisZeitraum] as '[TIME_CalendarYear].[2005].[1. Halbjahr].[1. Quartal]'
member [PRODUCT_GROUP].[sy_Slicer] as 'AGGREGATE({[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044125)],[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044115)]})'
member [SALES_MANAGER].[Alle B] as 'AGGREGATE({[SALES_MANAGER].[Alle Bereichsleiter]})'
member [CATEGORY].[Rechn.] as 'AGGREGATE({[CATEGORY].[Rechnung]})'
select
{
([ACCOUNT_Sales].[Quantity]),
([ACCOUNT_Sales].[ContributionMargin].[NetRev]),
([ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg]),
([ACCOUNT_Sales].[Test_FINE])
} properties [ACCOUNT_Sales].[DISPLAY_CAPTION], [ACCOUNT_Sales].[DISPLAY_FORMAT], [ACCOUNT_Sales].[DISPLAY_STYLE] on columns, non empty
{ [SALES_MANAGER].[Alle B]
} on rows
from Sales
where ([TIME_CalendarYear].[BasisZeitraum], [CATEGORY].[Rechn.],[PRODUCT_GROUP].[sy_Slicer])

In my case there is no difference using the SUM or AGGREGATE function.

I played with the solve_order.

with
member [ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg] as '[ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]', solve_order=1
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)', solve_order=1
member [TIME_CalendarYear].[BasisZeitraum] as 'AGGREGATE({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})', solve_order=0
--member [TIME_CalendarYear].[BasisZeitraum] as '[TIME_CalendarYear].[2005].[1. Halbjahr].[1. Quartal]', solve_order=0
member [PRODUCT_GROUP].[sy_Slicer] as 'AGGREGATE({[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044125)],[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044115)]})', solve_order=0
member [SALES_MANAGER].[Alle B] as 'AGGREGATE({[SALES_MANAGER].[Alle Bereichsleiter]})', solve_order=0
member [CATEGORY].[Rechn.] as 'AGGREGATE({[CATEGORY].[Rechnung]})', solve_order=0
select
{
([ACCOUNT_Sales].[Quantity]),
([ACCOUNT_Sales].[ContributionMargin].[NetRev]),
([ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg]),
([ACCOUNT_Sales].[Test_FINE])
} properties [ACCOUNT_Sales].[DISPLAY_CAPTION], [ACCOUNT_Sales].[DISPLAY_FORMAT], [ACCOUNT_Sales].[DISPLAY_STYLE] on columns, non empty
{ [SALES_MANAGER].[Alle B]
} on rows
from Sales
where ([TIME_CalendarYear].[BasisZeitraum], [CATEGORY].[Rechn.],[PRODUCT_GROUP].[sy_Slicer])

Applying the solve_order 1 to the Account members and the solve_order 0 to the other dimension members I receive the same results ([Test_FINE] is correct, [CM_NetRevPer100kg] not).

member [ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg] as '[ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]', solve_order=0
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)', solve_order=0

Setting the solve_order of the Account members to 0 and the solve_order of the other dimension members to 1 also the calculated member [Test_FINE] returns a incorrect value. This behaviour I understand: in this case first the calculation is performed and then the calculation results are aggregated.

But I still see no way to influence the aggregation of the member based on the dimension formula.

|||

Cornelius,

I am sorry that this is still not working. When you say you are using a dimension formula, can you be more specific. Are you using a "custom rollup column" or are you using a MDX statement in the cube script? I am curious as to the source of this problem and if possible it would be helpful if you could email me a copy of the project files.

spontello@.proclarity.com

- Steve

|||

Thanks to Steve there is a solution.

When using custom member formulas (what I called dimension formulas) there is no way to override the default aggregation behaviour of the cube. That issue is discussed in a previous post listed here:

http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_thread/thread/885149d85c381cb0/a0bc768a4066d4fe?q=custom+rollup+division&rnum=2#a0bc768a4066d4fe

The solution here is the ussage of calculated members.

But my calculations are regarding to members in an account dimension. In this account dimension I had declard properties like custom_caption, custom_style or custom_format I use in my client application.

On calculated members I cannot declare these properties.

So the next issue was to join an accurate calculation to the members in the account dimension. For this issue Steve proposed the ussage of calculated cells.

Using the Calculated Cells Wizard you can define a MDX Expression for the calculation and specify a specific member (in my case from the account dimension) to apply the calculation to.

It works wonderful.

Differential Backup disproportionately large

Hello,
Just recently I noticed that on one of our databases, the diff backups
are HUGE compared to the full backups.
For example, the regular full backup is only 80 MB, however the diff
backups are 800 MB to 1.2 GB in size. The activity on this database is
low. Our other databases are not exhibiting this anomaly, whereas they
have much higher activiity.
Is there any logical explanation for this? How I can see what's going
on with the diff backups?
Thanks muchAre you sure you don't have several backups on the same file? Use RESTORE HEADERONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1161877716.372685.197900@.h48g2000cwc.googlegroups.com...
> Hello,
> Just recently I noticed that on one of our databases, the diff backups
> are HUGE compared to the full backups.
> For example, the regular full backup is only 80 MB, however the diff
> backups are 800 MB to 1.2 GB in size. The activity on this database is
> low. Our other databases are not exhibiting this anomaly, whereas they
> have much higher activiity.
> Is there any logical explanation for this? How I can see what's going
> on with the diff backups?
> Thanks much
>|||No, it's not possible. For example, I just now deleted all the DIFF
backups and started from zero. I ran the differential backup again. It
ran for about 5 minutes and created a diff backup that is 1.6 GB in
size! The db itself is only 90 MB in size, and the log is 137 MB in
size.
? Not sure how to go about troubleshooting this one... never seen
this problem before.
Tibor Karaszi wrote:
> Are you sure you don't have several backups on the same file? Use RESTORE HEADERONLY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1161877716.372685.197900@.h48g2000cwc.googlegroups.com...
> > Hello,
> >
> > Just recently I noticed that on one of our databases, the diff backups
> > are HUGE compared to the full backups.
> >
> > For example, the regular full backup is only 80 MB, however the diff
> > backups are 800 MB to 1.2 GB in size. The activity on this database is
> > low. Our other databases are not exhibiting this anomaly, whereas they
> > have much higher activiity.
> >
> > Is there any logical explanation for this? How I can see what's going
> > on with the diff backups?
> >
> > Thanks much
> >|||Strange... What does RESTORE HEADERONLY and RESTORE FILELISTONLY say? Anything out of the ordinary?
Does the size go down after doing a database backup?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1161878879.100901.263140@.k70g2000cwa.googlegroups.com...
> No, it's not possible. For example, I just now deleted all the DIFF
> backups and started from zero. I ran the differential backup again. It
> ran for about 5 minutes and created a diff backup that is 1.6 GB in
> size! The db itself is only 90 MB in size, and the log is 137 MB in
> size.
> ? Not sure how to go about troubleshooting this one... never seen
> this problem before.
>
>
> Tibor Karaszi wrote:
>> Are you sure you don't have several backups on the same file? Use RESTORE HEADERONLY.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <tootsuite@.gmail.com> wrote in message
>> news:1161877716.372685.197900@.h48g2000cwc.googlegroups.com...
>> > Hello,
>> >
>> > Just recently I noticed that on one of our databases, the diff backups
>> > are HUGE compared to the full backups.
>> >
>> > For example, the regular full backup is only 80 MB, however the diff
>> > backups are 800 MB to 1.2 GB in size. The activity on this database is
>> > low. Our other databases are not exhibiting this anomaly, whereas they
>> > have much higher activiity.
>> >
>> > Is there any logical explanation for this? How I can see what's going
>> > on with the diff backups?
>> >
>> > Thanks much
>> >
>|||Hi Tibor, thanks for the suggestion, RESTORE FILELISTONLY revealed the
problem.
Tibor Karaszi wrote:
> Strange... What does RESTORE HEADERONLY and RESTORE FILELISTONLY say? Anything out of the ordinary?
> Does the size go down after doing a database backup?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1161878879.100901.263140@.k70g2000cwa.googlegroups.com...
> > No, it's not possible. For example, I just now deleted all the DIFF
> > backups and started from zero. I ran the differential backup again. It
> > ran for about 5 minutes and created a diff backup that is 1.6 GB in
> > size! The db itself is only 90 MB in size, and the log is 137 MB in
> > size.
> >
> > ? Not sure how to go about troubleshooting this one... never seen
> > this problem before.
> >
> >
> >
> >
> > Tibor Karaszi wrote:
> >> Are you sure you don't have several backups on the same file? Use RESTORE HEADERONLY.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> <tootsuite@.gmail.com> wrote in message
> >> news:1161877716.372685.197900@.h48g2000cwc.googlegroups.com...
> >> > Hello,
> >> >
> >> > Just recently I noticed that on one of our databases, the diff backups
> >> > are HUGE compared to the full backups.
> >> >
> >> > For example, the regular full backup is only 80 MB, however the diff
> >> > backups are 800 MB to 1.2 GB in size. The activity on this database is
> >> > low. Our other databases are not exhibiting this anomaly, whereas they
> >> > have much higher activiity.
> >> >
> >> > Is there any logical explanation for this? How I can see what's going
> >> > on with the diff backups?
> >> >
> >> > Thanks much
> >> >
> >|||tootsuite@.gmail.com wrote:
> Hi Tibor, thanks for the suggestion, RESTORE FILELISTONLY revealed the
> problem.
>
Don't leave us hanging! What was the problem?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It's embarassingly stupid, I'm afraid - was backing up the wrong
database :-)
Tracy McKibben wrote:
> tootsuite@.gmail.com wrote:
> > Hi Tibor, thanks for the suggestion, RESTORE FILELISTONLY revealed the
> > problem.
> >
> Don't leave us hanging! What was the problem?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||<tootsuite@.gmail.com> wrote in message
news:1161888872.392346.209340@.e3g2000cwe.googlegroups.com...
> It's embarassingly stupid, I'm afraid - was backing up the wrong
> database :-)
>
LOL!
If it's any consolation, you're not the only person who has done stuff like
that. :-)
> Tracy McKibben wrote:
>> tootsuite@.gmail.com wrote:
>> > Hi Tibor, thanks for the suggestion, RESTORE FILELISTONLY revealed the
>> > problem.
>> >
>> Don't leave us hanging! What was the problem?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>

Differential Backup disproportionately large

Hello,
Just recently I noticed that on one of our databases, the diff backups
are HUGE compared to the full backups.
For example, the regular full backup is only 80 MB, however the diff
backups are 800 MB to 1.2 GB in size. The activity on this database is
low. Our other databases are not exhibiting this anomaly, whereas they
have much higher activiity.
Is there any logical explanation for this? How I can see what's going
on with the diff backups?
Thanks muchAre you sure you don't have several backups on the same file? Use RESTORE HE
ADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1161877716.372685.197900@.h48g2000cwc.googlegroup
s.com...
> Hello,
> Just recently I noticed that on one of our databases, the diff backups
> are HUGE compared to the full backups.
> For example, the regular full backup is only 80 MB, however the diff
> backups are 800 MB to 1.2 GB in size. The activity on this database is
> low. Our other databases are not exhibiting this anomaly, whereas they
> have much higher activiity.
> Is there any logical explanation for this? How I can see what's going
> on with the diff backups?
> Thanks much
>|||No, it's not possible. For example, I just now deleted all the DIFF
backups and started from zero. I ran the differential backup again. It
ran for about 5 minutes and created a diff backup that is 1.6 GB in
size! The db itself is only 90 MB in size, and the log is 137 MB in
size.
? Not sure how to go about troubleshooting this one... never seen
this problem before.
Tibor Karaszi wrote:[vbcol=seagreen]
> Are you sure you don't have several backups on the same file? Use RESTORE
HEADERONLY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1161877716.372685.197900@.h48g2
000cwc.googlegroups.com...|||Strange... What does RESTORE HEADERONLY and RESTORE FILELISTONLY say? Anythi
ng out of the ordinary?
Does the size go down after doing a database backup?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1161878879.100901.263140@.k70g2000cwa.googlegroup
s.com...
> No, it's not possible. For example, I just now deleted all the DIFF
> backups and started from zero. I ran the differential backup again. It
> ran for about 5 minutes and created a diff backup that is 1.6 GB in
> size! The db itself is only 90 MB in size, and the log is 137 MB in
> size.
> ? Not sure how to go about troubleshooting this one... never seen
> this problem before.
>
>
> Tibor Karaszi wrote:
>|||Hi Tibor, thanks for the suggestion, RESTORE FILELISTONLY revealed the
problem.
Tibor Karaszi wrote:[vbcol=seagreen]
> Strange... What does RESTORE HEADERONLY and RESTORE FILELISTONLY say? Anyt
hing out of the ordinary?
> Does the size go down after doing a database backup?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1161878879.100901.263140@.k70g2
000cwa.googlegroups.com...|||tootsuite@.gmail.com wrote:
> Hi Tibor, thanks for the suggestion, RESTORE FILELISTONLY revealed the
> problem.
>
Don't leave us hanging! What was the problem?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It's embarassingly stupid, I'm afraid - was backing up the wrong
database :-)
Tracy McKibben wrote:
> tootsuite@.gmail.com wrote:
> Don't leave us hanging! What was the problem?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||<tootsuite@.gmail.com> wrote in message
news:1161888872.392346.209340@.e3g2000cwe.googlegroups.com...
> It's embarassingly stupid, I'm afraid - was backing up the wrong
> database :-)
>
LOL!
If it's any consolation, you're not the only person who has done stuff like
that. :-)

> Tracy McKibben wrote:
>

Different results - Not Exists Vs Not In

The following example results to differnet results depending upon whether I'
m
using Not exists or Not in. I'm sdoing the same thing in 2 different ways.
Is there an explanation for this or is this a bug in SQL Server?
Current Version SQL 2000 SP3a
Example:
Declare @.temp1
table (
id int
)
Insert into @.temp1 (id) values (1)
Insert into @.temp1 (id) values (2)
Insert into @.temp1 (id) values (3)
Insert into @.temp1 (id) values (4)
-- Expected anawer 101,102,103,104
-- returns correct
Select
*
From
@.temp1 t1x
Where (100+t1x.id) NOT IN ( Select
t1y.id
from
@.temp1 t1y )
-- returns "NOTHING"
Select
*
From
@.temp1 t1x
Where
NOT EXISTS (Select
t1y.id
from
@.temp1 t1y
Where
t1y.id != (100+t1x.id) )Change the comparison expression from second query.

> Where
> t1y.id != (100+t1x.id) )
Where
t1y.id = (100+t1x.id) )
AMB
"Core" wrote:

> The following example results to differnet results depending upon whether
I'm
> using Not exists or Not in. I'm sdoing the same thing in 2 different ways.
> Is there an explanation for this or is this a bug in SQL Server?
> Current Version SQL 2000 SP3a
> Example:
> Declare @.temp1
> table (
> id int
> )
> Insert into @.temp1 (id) values (1)
> Insert into @.temp1 (id) values (2)
> Insert into @.temp1 (id) values (3)
> Insert into @.temp1 (id) values (4)
> -- Expected anawer 101,102,103,104
> -- returns correct
> Select
> *
> From
> @.temp1 t1x
> Where (100+t1x.id) NOT IN ( Select
> t1y.id
> from
> @.temp1 t1y )
> -- returns "NOTHING"
> Select
> *
> From
> @.temp1 t1x
> Where
> NOT EXISTS (Select
> t1y.id
> from
> @.temp1 t1y
> Where
> t1y.id != (100+t1x.id) )
>|||I think you intended your second example to be:
Select
*
From
@.temp1 t1x
Where
NOT EXISTS (Select
t1y.id
from
@.temp1 t1y
Where
t1y.id = (100+t1x.id) )
Result:
id
1
2
3
4
(4 row(s) affected)
However, the two queries are still not logically equivalent. Insert a
NULL in the table and you'll see what I mean.
David Portas
SQL Server MVP
--|||Thanks Alejandro,
I feel stupid. I had starred at this problem for 30 minutes.
"Alejandro Mesa" wrote:
> Change the comparison expression from second query.
>
> Where
> t1y.id = (100+t1x.id) )
>
> AMB
> "Core" wrote:
>|||I have been there too.
AMB
"Core" wrote:
> Thanks Alejandro,
> I feel stupid. I had starred at this problem for 30 minutes.
>
> "Alejandro Mesa" wrote:
>

Different images based on condition...

I was wondering if there is a way to display a particular image into a table
record based on a condition. For example, I have a list of questions that
come in 3 different types, each type has a different image associated with
it, so it would look like this:
<Image1.bmp> First Question (type is 1)
<Image3.bmp> Second Question (type is 3)
<Image3.bmp> Third Question (type is 3)
<Image2.bmp> Forth Question (type is 2)
<Image1.bmp> Fifth Question (type is 1)
...and so forth.
So basically what I am wanting to do is (at the record level) display the
different images based on what type of question I want. But, I can't seem
to figure out how to insert three different images into one cell and supress
two of them based on the 'question_type'. Which was how I accomplished it
in crystal reports.
I think I could probably do this by storing the images in a column in the
database, then call that with my stored procedure. Although, I am afraid
that this would take up tons of space in the database and take forever for
the report to run.
Any thoughts? Thanks!
LisaThe sample report at the end of this posting shows how to conditionally
display an image.
This report conditionally displays an image based on a detail row value. In
this case Germany = Green, Purple = USA, and Canada = Red. The technique is
to place a rectangle in the table detail row and place all images at the
same location in the rectangle. The images should be the same size. Next is
to use an expression to set the image visibility:
=iif(Fields!<FieldName>.Value = "SomeValue", false, true). False = Not
hidden and True = Hidden.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:O4L73T9eEHA.3916@.TK2MSFTNGP11.phx.gbl...
> I was wondering if there is a way to display a particular image into a
table
> record based on a condition. For example, I have a list of questions that
> come in 3 different types, each type has a different image associated with
> it, so it would look like this:
> <Image1.bmp> First Question (type is 1)
> <Image3.bmp> Second Question (type is 3)
> <Image3.bmp> Third Question (type is 3)
> <Image2.bmp> Forth Question (type is 2)
> <Image1.bmp> Fifth Question (type is 1)
> ...and so forth.
> So basically what I am wanting to do is (at the record level) display the
> different images based on what type of question I want. But, I can't seem
> to figure out how to insert three different images into one cell and
supress
> two of them based on the 'question_type'. Which was how I accomplished it
> in crystal reports.
> I think I could probably do this by storing the images in a column in the
> database, then call that with my stored procedure. Although, I am afraid
> that this would take up tons of space in the database and take forever for
> the report to run.
> Any thoughts? Thanks!
> Lisa
>
>
ConditionallyDisplayAnImage.RDL
----
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<Height>0.625in</Height>
<Width>6.375in</Width>
<CanGrow>true</CanGrow>
<Value>This report conditionally displays an image based on a detail
row value. In this case Germany = Green, USA = Purple, and Canada = Red. The
technique is to all images in a rectangle at the using the same origin and
size. Next is to place the rectangle in the table detail row cell. Finally,
is to set each images initial visibility using an expression:
=iif(Fields!<FieldName>.Value = "SomeValue", false, true).</Value>
</Textbox>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>country</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Rectangle Name="rectangle1">
<ReportItems>
<Image Name="image1">
<ZIndex>2</ZIndex>
<Height>0.15625in</Height>
<Visibility>
<Hidden>=iif(Fields!country.Value = "Germany",
false, true)</Hidden>
</Visibility>
<Source>Embedded</Source>
<Style />
<Value>greenbullet</Value>
<Sizing>AutoSize</Sizing>
</Image>
<Image Name="image2">
<ZIndex>1</ZIndex>
<Height>0.15625in</Height>
<Visibility>
<Hidden>=iif(Fields!country.Value = "USA",
false, true)</Hidden>
</Visibility>
<Source>Embedded</Source>
<Style />
<Value>greybullet</Value>
<Sizing>AutoSize</Sizing>
</Image>
<Image Name="image3">
<Height>0.15625in</Height>
<Visibility>
<Hidden>=iif(Fields!country.Value = "Canada",
false, true)</Hidden>
</Visibility>
<Source>Embedded</Source>
<Style />
<Value>redbullet</Value>
<Sizing>AutoSize</Sizing>
</Image>
</ReportItems>
<Style />
</Rectangle>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Top>0.75in</Top>
<Width>2.375in</Width>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>0.20833in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.625in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>69fa60f9-f434-44ed-a70d-2fc41b5d0cb3</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.50001in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Country
FROM Customers
WHERE (Country = N'Germany') OR
(Country = N'USA') OR
(Country = N'Canada')</CommandText>
</Query>
</DataSet>
</DataSets>
<EmbeddedImages>
<EmbeddedImage Name="greenbullet">
<MIMEType>image/png</MIMEType>
<ImageData>iVBORw0KGgoAAAANSUhEUgAAABQAAAAPCAMAAADTRh9nAAAAAXNSR0IArs4c6QAAA
ARnQU1BAACxjwv8YQUAAAAgY0hSTQAAeiYAAICEAAD6AAAAgOgAAHUwAADqYAAAOpgAABdwnLpRP
AAAAwBQTFRFISoIKDMKLjoLLzUbMT0MMzscNUMNO0oPO0UePUIvRFURU101TWAVU2gVW3EYYHcdZ
XskT1NDYGNYcnlcdXhtbogeaoIidY0te5UufIZfgZ0shZ82h6cojawuh6Mzi6c4lLUyiJpSlK5En
r1EkqFlmKN2nqp4pcc+psJTrMlVsc5YuNtPvt9ZwONYyetfyOZv0O9y1vR12vd33vt05P19hoiBj
5ODoKKYpamZrrSbrK2rs7Wstrmt9v+U//+Z////AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAadF2SAAAAMFJREFUKFNNj9cagjAUgxFQtLZQlSWIRVCR4QQXMt7/
rURo1XOTk//LRcLV9J6r7SanP9dpQRzXWWr40boOPhdBvI8DoqHXFxYkOGRZuvctDO4suXaStKrK
c0h0OGPQcJOsgZeI6GhM4Ut3o0tZZsfQxlBkScXyk9P5GHmGDHoMYm3ph+HOMzGS+gzOZc20bcvA
CPDqtydACtaxDIEofFZ15W8D2ByQeOH6W1TnQ3Eg8tyoW0+313WuTqZt7B9S38ob8DI2JkkuGrgA
AAAASUVORK5CYII=</ImageData>
</EmbeddedImage>
<EmbeddedImage Name="greybullet">
<MIMEType>image/png</MIMEType>
<ImageData>iVBORw0KGgoAAAANSUhEUgAAABQAAAAPCAMAAADTRh9nAAAAAXNSR0IArs4c6QAAA
ARnQU1BAACxjwv8YQUAAAAgY0hSTQAAeiYAAICEAAD6AAAAgOgAAHUwAADqYAAAOpgAABdwnLpRP
AAAAwBQTFRFOC04XkxeemV6pYmlyazJ9tn2//X/////AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAABATwmQAAAHRJREFUKFNNz1kSwCAIA1CWqPe/cROgrflR3yCinQky
E7O3XkWZEc2NSGABGV5aSFtMab7IbmvvvYTxYgC0Rh9EXJXqVz2JasrC8B+lfIa37cLoMd3te+i4
h0IzDdrDp7liZhpz8IBn5f6mfsXLVfZXzmmWB4hABapTVcWWAAAAAElFTkSuQmCC</ImageData>
</EmbeddedImage>
<EmbeddedImage Name="redbullet">
<MIMEType>image/png</MIMEType>
<ImageData>iVBORw0KGgoAAAANSUhEUgAAABQAAAAPCAMAAADTRh9nAAAAAXNSR0IArs4c6QAAA
ARnQU1BAACxjwv8YQUAAAAgY0hSTQAAeiYAAICEAAD6AAAAgOgAAHUwAADqYAAAOpgAABdwnLpRP
AAAAwBQTFRFAAAAKgAIMwAKOgALPQAMNRUbNhUcOxUcAEBAQQANRgAOSgAPTAAPRRUeUwESVQARW
QESXQMVQiovXSo1YQIVYgUXZQIVZAQXaQEWaAYZcQIYdwYdcQgdeAIZfAQcegwiew4kfQsifg0jf
A0kdxYpexYqQABAQEAAQEBAU0BDY1VYeVVceGptAAD/AP8AAICAAMDAAP//gQMciAQeggoihwoji
gYhjRcunw8skBYulhQunBEtkBgwlhs0mR01nhw2ogwqpwgorAsrrA8uoxczpBs3px05qBMxsRc2t
RIytRY2oCA5pS9GripEvSVEuChFhlVfmkBSoVZlo2t2qmx4xxs+/wAAyR5AxSVFxDdTwjhTzDVTy
ThVyTtYzjtY2yxP0TFR2DBR3DBS3zdZ4zZY6ztfzkBd3EBf5EFh5lJv6FJw71Ny9VV28ll49lt6+
lF0/FJ2/Vt9/lx/gACAwADA/wD//2qJ/2yN/26S/3OU/3SV/3aZ/3+hgIAAwMAA//8AgICAiICBk
4CDopWYqZWZrpWatJWbraqrtaqsuaqtvKuu////////wMDA///D////////wMDA////AAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAeTG9CwAAAM1JREFUKFNj6IaCtsCQ4FYomwFCd/k4eTo7GKu2gHkQ
wTbH0PiUxAh3M4kOuGCna3hqQVF+hp+1HH8zTGWQc1J+RXV5TrSbiYg2TNDGK7mwuqYqL87NREwY
Kthh5JGQU15ZlhVpryjCAlMpa+GXnp2bmeZrKSPABBNUMXPxj4mN8rVVFudhhwkGSJla2XvbmStI
8jHrwN3JLyGtqCQvI8rHwtYOF2ziEhAREeTnZmZtRPiou52XhZOTmVEIpA7mTSCjXUdTC6wMWRDK
B1MA/Td3eObvA7wAAAAASUVORK5CYII=</ImageData>
</EmbeddedImage>
</EmbeddedImages>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>e66e2b26-0660-4802-920d-211083d62c86</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Different format in flat file for header and data

Hello all,

Is it possible to have two different formats for the header and the data in a flat file connection?

An example text file would look like this:

Col1,Col2,Col3

abcdefghi12345testtesttesttest

abcdeeeee12333setsetsetsetsets

where the header is delimited and the data is ragged right.

It looks like you should be able to accomplish this from the Flat File Connection Manager Editor interface, but perhaps having different delimiter dropdown boxes for the header and columns can only be used if you are using the Delimited format?

Thanks for any info you can provide!

Jessica

Do you even need the header?|||

No, but it would save a lot of time if we didn't have to type in hundreds of column names

I was more interested in knowing if this functionality is supposed to work and I'm missing some check box... or if it isn't supposed to work.

|||I don't think this is supported currently.

Different font size and style in the same table cell

I have a table cell that contains two things:

well code and well type (two different data fields concatenated)

for example:

4WA53102 Verticall Well

I want the code to be 12 pt arial regular

I want the well type to be 8 point italic arial

4WA53102 Vertical Well

How do I do this? Thanks

Why not put well code and well type in adjacent table cells and then you can change the formatting on the cell well code is in and the formatting on the cell well type is in to be different from each other. I don't see how it would be different from what you want to do.

It isn't possible to do what you want the way you want to do it.

Differences between two Columns

Has anyone computed a difference between two columns in a matrix agianst
the group by column?
Example
YEAR Diff
2003 2004
Company A 100 200 100
Company B 50 200 150
I posted this question earlier but I don't think I was clear about what
I was asking about. If anyone has any ideas please let me know. This
is a pretty common problem for our reporting efforts so a solution would
be greatly appreciated.
Thanks Ahead of Time
Steve
sfibich@.pfgc.comOne way to compute the difference between two columns is to write a function
(either embedded in the .rdl or as an assembly) and call the function from
the cell that is going to hold the return value.
Using your example and using an embedded function:
In the Tab=Layout of your design, click the menu item 'Report' then click
'Report
Properties'. On the resulting dialog box, click Tab=Code. Type in the
function:
Public Function ColumnDiff (YearAmt1 as integer, YearAmt2 as integer)
RETURN YearAmt2 - YearAmt1
End Function
Then in the Layout of your form: put the following expression in your 'Diff'
cell.
=Code.ColumnDiff(Fields!YearAmt1.Value, Fields!YearAmt2.Value)
Good Luck
Dawn|||Dawn wrote:
> One way to compute the difference between two columns is to write a function
> (either embedded in the .rdl or as an assembly) and call the function from
> the cell that is going to hold the return value.
> Using your example and using an embedded function:
> In the Tab=Layout of your design, click the menu item 'Report' then click
> 'Report
> Properties'. On the resulting dialog box, click Tab=Code. Type in the
> function:
> Public Function ColumnDiff (YearAmt1 as integer, YearAmt2 as integer)
> RETURN YearAmt2 - YearAmt1
> End Function
> Then in the Layout of your form: put the following expression in your 'Diff'
> cell.
> =Code.ColumnDiff(Fields!YearAmt1.Value, Fields!YearAmt2.Value)
> Good Luck
> Dawn
>
>
>
>
>
Thats not exactly what I'm looking for. My data is not structure in a
way that I have two columns of data already split on years, it is one
column depicting dollar values, another column depicting year.
Example Data Result Set:
Year Sales Dollars
2004 1000
2003 1020
2002 900
2001 50
2000 1000
What I would want is to put the data into a Matrix
Example:
Year 2004 2003 diff2004/2003 2002 diff
Dollar Value 1000 1020 -20 900 +30
I am wondering if anyone else is running into any year over year
comparison reports and how they are handling it. If anyone has any
suggestions please let me know.
Thanks

differences between RPC and SP in profiler

I am trying to capture sprocs being called in profiler. Why do I need
RPC:Completed as an example and not SP:Completed ? Wouldnt SP:completed
capture all the stored procedures ? What does RPC:completed do ? Please
provide any and all info you can about the differences..
Bring up the profiler trace that was captured during the time the System Monitor log was also captured, group it by CPU, and look at the large CPU values for the RPC:Completed or the SQL:BatchCompleted events. This will indicate the queries that are CPU i
ntensive.
When a SQL statement calls a stored procedure using the ODBC CALL escape clause, the SQL driver sends the procedure to SQL Server using the remote stored procedure call (RPC) mechanism. RPC requests bypass much of the statement parsing and parameter proce
ssing in SQL Server and are faster than using the Transact-SQL EXECUTE statement--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Performance topic.
"Hassan" wrote:

> I am trying to capture sprocs being called in profiler. Why do I need
> RPC:Completed as an example and not SP:Completed ? Wouldnt SP:completed
> capture all the stored procedures ? What does RPC:completed do ? Please
> provide any and all info you can about the differences..
>
>

differences between RPC and SP in profiler

I am trying to capture sprocs being called in profiler. Why do I need
RPC:Completed as an example and not SP:Completed ? Wouldnt SP:completed
capture all the stored procedures ? What does RPC:completed do ? Please
provide any and all info you can about the differences..Bring up the profiler trace that was captured during the time the System Mon
itor log was also captured, group it by CPU, and look at the large CPU value
s for the RPC:Completed or the SQL:BatchCompleted events. This will indicate
the queries that are CPU i
ntensive.
When a SQL statement calls a stored procedure using the ODBC CALL escape cla
use, the SQL driver sends the procedure to SQL Server using the remote store
d procedure call (RPC) mechanism. RPC requests bypass much of the statement
parsing and parameter proce
ssing in SQL Server and are faster than using the Transact-SQL EXECUTE state
ment--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Perform
ance topic.
"Hassan" wrote:

> I am trying to capture sprocs being called in profiler. Why do I need
> RPC:Completed as an example and not SP:Completed ? Wouldnt SP:completed
> capture all the stored procedures ? What does RPC:completed do ? Please
> provide any and all info you can about the differences..
>
>

differences between RPC and SP in profiler

I am trying to capture sprocs being called in profiler. Why do I need
RPC:Completed as an example and not SP:Completed ? Wouldnt SP:completed
capture all the stored procedures ? What does RPC:completed do ? Please
provide any and all info you can about the differences..Bring up the profiler trace that was captured during the time the System Monitor log was also captured, group it by CPU, and look at the large CPU values for the RPC:Completed or the SQL:BatchCompleted events. This will indicate the queries that are CPU intensive.
When a SQL statement calls a stored procedure using the ODBC CALL escape clause, the SQL driver sends the procedure to SQL Server using the remote stored procedure call (RPC) mechanism. RPC requests bypass much of the statement parsing and parameter processing in SQL Server and are faster than using the Transact-SQL EXECUTE statement--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Performance topic.
"Hassan" wrote:
> I am trying to capture sprocs being called in profiler. Why do I need
> RPC:Completed as an example and not SP:Completed ? Wouldnt SP:completed
> capture all the stored procedures ? What does RPC:completed do ? Please
> provide any and all info you can about the differences..
>
>