Jump to report value for parameter, cube data

Have a problem with passing information between reports using Jump to report
facility.
User clicking on field value in report to pass to second report. This works
with the second report showing the value of field user has selected in the
parameter box (Available Values set to non-queried) but report shows an error
of:
restrictions imposed by the constrained flag in the STRTOSET function were
violated
Have tried many settings to correct this but with no luck. Does anybody
know how to correct this problem.
Thanks
IanIn case this is helpful to others I have found that the field parameter was
causing the problem. I was using Fields!FieldName.Value and should have used
Fields!FieldName.UniqueName
Ian
"IanP" wrote:
> Have a problem with passing information between reports using Jump to report
> facility.
> User clicking on field value in report to pass to second report. This works
> with the second report showing the value of field user has selected in the
> parameter box (Available Values set to non-queried) but report shows an error
> of:
> restrictions imposed by the constrained flag in the STRTOSET function were
> violated
> Have tried many settings to correct this but with no luck. Does anybody
> know how to correct this problem.
> Thanks
> Ian

Joining cubes

I have three cubes in AS. How can I have a separate cube which can retrieve
data from these three cubes and then (similar to join function in T-SQL)
can join the results?
Thanks,
Gilgamesh
there is no join option for cubes.
but you can use virtual cubes (in AS2000) or link the cubes (SSAS2005)
when you link 1 cube in another, the user is able to access the measures of
the 2 cubes at the same time.
you have to use shared/common dimensions to be able to analyze the measures
of the 2 cubes at the same time. (like 1 common time dimension)
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:ukwtRNGYHHA.1240@.TK2MSFTNGP04.phx.gbl...
>I have three cubes in AS. How can I have a separate cube which can retrieve
> data from these three cubes and then (similar to join function in T-SQL)
> can join the results?
> Thanks,
> Gilgamesh
>
|||How do you share dimensions? I tried nameing them the same in all thecubes,
but when I drag and drop the dimensions to the browser it creates separate
columns even though they're called with the same name.
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:5E0F1674-B392-4739-82CE-2ED8657CF469@.microsoft.com...[vbcol=seagreen]
> there is no join option for cubes.
> but you can use virtual cubes (in AS2000) or link the cubes (SSAS2005)
> when you link 1 cube in another, the user is able to access the measures
> of the 2 cubes at the same time.
> you have to use shared/common dimensions to be able to analyze the
> measures of the 2 cubes at the same time. (like 1 common time dimension)
>
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:ukwtRNGYHHA.1240@.TK2MSFTNGP04.phx.gbl...
|||do you use AS2000 or 2005?
is AS2000 shared dimensions are created in the shared dimension tree of the
management console.
use the SAME dimension in both cube to be able to analyze the measures of
the 2 cubes at the same time.
in 2005 all the dimensions are shared by default, so use the same dimension
in both cubes.
having the same name is not enough.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:eH3aUcdYHHA.2316@.TK2MSFTNGP04.phx.gbl...
> How do you share dimensions? I tried nameing them the same in all
> thecubes, but when I drag and drop the dimensions to the browser it
> creates separate columns even though they're called with the same name.
> -G
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:5E0F1674-B392-4739-82CE-2ED8657CF469@.microsoft.com...
>
|||Ok. I'm getting very close. How can I combine three fact tables into one? In
other words, how can I JOIN them?
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:9234EA65-4AA3-4983-86E1-360F679BA035@.microsoft.com...[vbcol=seagreen]
> do you use AS2000 or 2005?
> is AS2000 shared dimensions are created in the shared dimension tree of
> the management console.
> use the SAME dimension in both cube to be able to analyze the measures of
> the 2 cubes at the same time.
> in 2005 all the dimensions are shared by default, so use the same
> dimension in both cubes.
> having the same name is not enough.
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:eH3aUcdYHHA.2316@.TK2MSFTNGP04.phx.gbl...
|||well... joining 3 fact tables its different.
can you explain what are the 3 tables and the 3 associated cubes you have.
and the version of the tools (SQL2000 or 2005)
take a look at the foodmart sample or the adventure works sample to see how
different fact tables are combined into 1 cube.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:O4gUZDfYHHA.3628@.TK2MSFTNGP02.phx.gbl...
> Ok. I'm getting very close. How can I combine three fact tables into one?
> In other words, how can I JOIN them?
> -G
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:9234EA65-4AA3-4983-86E1-360F679BA035@.microsoft.com...
>
|||I'm using AS2005. I started by creating three differentd data source views
and then created one cube for each view. i.e. one cube for Orders, one for
Invoices, and one for Receipts. That part works fine. Now I need to either
create a fourth cube, or maybe somethings else, whcih allows me to comine
the records of three fact tables into one view. I'll need specifcally six
columns of each fat tables, which have been named the same, to be included
in the new fourth cube.
The issue is not to combine multiple fact tables into one cube. The issue,
maybe, is to combine multiple fact tables into one fact table and then use
that table in a cube.
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:AB50D69A-F1BB-455D-88EB-63B8B4EE9A4E@.microsoft.com...[vbcol=seagreen]
> well... joining 3 fact tables its different.
> can you explain what are the 3 tables and the 3 associated cubes you have.
> and the version of the tools (SQL2000 or 2005)
> take a look at the foodmart sample or the adventure works sample to see
> how different fact tables are combined into 1 cube.
>
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:O4gUZDfYHHA.3628@.TK2MSFTNGP02.phx.gbl...
|||ok, now I see
first use only 1 DSV and add all the tales used in this unique DSV; add the
correct relationship between fact tables and dimension tables.
after, create only 1 cube and 3 measure groups each one linked to the 3
tables;
now you have 1 cube with the measures from the 3 source tables.
the cube wizard will create everything for you if the DSV contains enough
information (ie good links).
again, take a look at the adv. works sample to see how this works.
if your tables come from 3 different database, and if you are not familiar
with SSAS, try to copy your tables into the same database to simplify your
project.
or use the linked servers + views, but try to have only 1 source database
for your cubes.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:enT0ScoYHHA.4008@.TK2MSFTNGP05.phx.gbl...
> I'm using AS2005. I started by creating three differentd data source views
> and then created one cube for each view. i.e. one cube for Orders, one for
> Invoices, and one for Receipts. That part works fine. Now I need to either
> create a fourth cube, or maybe somethings else, whcih allows me to comine
> the records of three fact tables into one view. I'll need specifcally six
> columns of each fat tables, which have been named the same, to be included
> in the new fourth cube.
> The issue is not to combine multiple fact tables into one cube. The issue,
> maybe, is to combine multiple fact tables into one fact table and then use
> that table in a cube.
> -G
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:AB50D69A-F1BB-455D-88EB-63B8B4EE9A4E@.microsoft.com...
>

Joining cubes

I have three cubes in AS. How can I have a separate cube which can retrieve
data from these three cubes and then (similar to join function in T-SQL)
can join the results?
Thanks,
Gilgameshthere is no join option for cubes.
but you can use virtual cubes (in AS2000) or link the cubes (SSAS2005)
when you link 1 cube in another, the user is able to access the measures of
the 2 cubes at the same time.
you have to use shared/common dimensions to be able to analyze the measures
of the 2 cubes at the same time. (like 1 common time dimension)
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:ukwtRNGYHHA.1240@.TK2MSFTNGP04.phx.gbl...
>I have three cubes in AS. How can I have a separate cube which can retrieve
> data from these three cubes and then (similar to join function in T-SQL)
> can join the results?
> Thanks,
> Gilgamesh
>|||How do you share dimensions? I tried nameing them the same in all thecubes,
but when I drag and drop the dimensions to the browser it creates separate
columns even though they're called with the same name.
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:5E0F1674-B392-4739-82CE-2ED8657CF469@.microsoft.com...[vbcol=seagreen]
> there is no join option for cubes.
> but you can use virtual cubes (in AS2000) or link the cubes (SSAS2005)
> when you link 1 cube in another, the user is able to access the measures
> of the 2 cubes at the same time.
> you have to use shared/common dimensions to be able to analyze the
> measures of the 2 cubes at the same time. (like 1 common time dimension)
>
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:ukwtRNGYHHA.1240@.TK2MSFTNGP04.phx.gbl...|||do you use AS2000 or 2005?
is AS2000 shared dimensions are created in the shared dimension tree of the
management console.
use the SAME dimension in both cube to be able to analyze the measures of
the 2 cubes at the same time.
in 2005 all the dimensions are shared by default, so use the same dimension
in both cubes.
having the same name is not enough.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:eH3aUcdYHHA.2316@.TK2MSFTNGP04.phx.gbl...
> How do you share dimensions? I tried nameing them the same in all
> thecubes, but when I drag and drop the dimensions to the browser it
> creates separate columns even though they're called with the same name.
> -G
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:5E0F1674-B392-4739-82CE-2ED8657CF469@.microsoft.com...
>|||Ok. I'm getting very close. How can I combine three fact tables into one? In
other words, how can I JOIN them?
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:9234EA65-4AA3-4983-86E1-360F679BA035@.microsoft.com...[vbcol=seagreen]
> do you use AS2000 or 2005?
> is AS2000 shared dimensions are created in the shared dimension tree of
> the management console.
> use the SAME dimension in both cube to be able to analyze the measures of
> the 2 cubes at the same time.
> in 2005 all the dimensions are shared by default, so use the same
> dimension in both cubes.
> having the same name is not enough.
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:eH3aUcdYHHA.2316@.TK2MSFTNGP04.phx.gbl...|||well... joining 3 fact tables its different.
can you explain what are the 3 tables and the 3 associated cubes you have.
and the version of the tools (SQL2000 or 2005)
take a look at the foodmart sample or the adventure works sample to see how
different fact tables are combined into 1 cube.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:O4gUZDfYHHA.3628@.TK2MSFTNGP02.phx.gbl...
> Ok. I'm getting very close. How can I combine three fact tables into one?
> In other words, how can I JOIN them?
> -G
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:9234EA65-4AA3-4983-86E1-360F679BA035@.microsoft.com...
>|||I'm using AS2005. I started by creating three differentd data source views
and then created one cube for each view. i.e. one cube for Orders, one for
Invoices, and one for Receipts. That part works fine. Now I need to either
create a fourth cube, or maybe somethings else, whcih allows me to comine
the records of three fact tables into one view. I'll need specifcally six
columns of each fat tables, which have been named the same, to be included
in the new fourth cube.
The issue is not to combine multiple fact tables into one cube. The issue,
maybe, is to combine multiple fact tables into one fact table and then use
that table in a cube.
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:AB50D69A-F1BB-455D-88EB-63B8B4EE9A4E@.microsoft.com...[vbcol=seagreen]
> well... joining 3 fact tables its different.
> can you explain what are the 3 tables and the 3 associated cubes you have.
> and the version of the tools (SQL2000 or 2005)
> take a look at the foodmart sample or the adventure works sample to see
> how different fact tables are combined into 1 cube.
>
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:O4gUZDfYHHA.3628@.TK2MSFTNGP02.phx.gbl...|||ok, now I see
first use only 1 DSV and add all the tales used in this unique DSV; add the
correct relationship between fact tables and dimension tables.
after, create only 1 cube and 3 measure groups each one linked to the 3
tables;
now you have 1 cube with the measures from the 3 source tables.
the cube wizard will create everything for you if the DSV contains enough
information (ie good links).
again, take a look at the adv. works sample to see how this works.
if your tables come from 3 different database, and if you are not familiar
with SSAS, try to copy your tables into the same database to simplify your
project.
or use the linked servers + views, but try to have only 1 source database
for your cubes.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:enT0ScoYHHA.4008@.TK2MSFTNGP05.phx.gbl...
> I'm using AS2005. I started by creating three differentd data source views
> and then created one cube for each view. i.e. one cube for Orders, one for
> Invoices, and one for Receipts. That part works fine. Now I need to either
> create a fourth cube, or maybe somethings else, whcih allows me to comine
> the records of three fact tables into one view. I'll need specifcally six
> columns of each fat tables, which have been named the same, to be included
> in the new fourth cube.
> The issue is not to combine multiple fact tables into one cube. The issue,
> maybe, is to combine multiple fact tables into one fact table and then use
> that table in a cube.
> -G
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:AB50D69A-F1BB-455D-88EB-63B8B4EE9A4E@.microsoft.com...
>

JOIN outside of WHERE clause ? ? ? ?

view 1

I have a view that is drawing from two tables. Each table contains fields representing cube coordinates. The view is filtering the results based on some simple logic (where the defference between two values in the tables are greater than x) this part works fine.

view 2

notes field

I want to include a note field in my view. This field will contain the contents of a note field from another view. This second view also contains coordinates that I can use to map the notes to the appropriate rows in view 1. However, if I join the views in my FROM clause, I will end up filtering my resultset down to rows that correspond to view 2's contents.

I want to have the full contents of view 1, displayed with a note field containing the note field content from view 2 only in the rows that have corresponding notes. (some rows will have notes, some will not)

eg.

VIEW 1

row1 row2 row3 note_row (from view 2)

fsdfs sdfsdf sdfsdf <no note>

sdfs sdfsd sdfsd "note"

sdfsdf sdfsdf ssdfsd <no note>

so... my question: is there any way that I can include this field without joining the views in my FROM clause (meking my resultset exclusive)..... possibly somehow in fields list of the select statement?

THANKS!

Sounds like a UNION or UNION ALL would do the trick because you could avoid joining the two recordsets. Create that as your inner query and query it as a virtual table, maybe?

Lee Everest

www.texastoo.com/sqlblog

|||

You're looking at left join.

e.g.

Code Snippet

select v1.*, v2.notes

from v1 left join v2 on v1.id=v2.id

|||Thanks. I realized this in the middle of the night last night and tried it out... the LEFT JOIN works. Thanks.