Dynamic measures

Hi,

I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.

The parameter values has, Actual, Budget, Target

For the one field, base on the above parameter, will select,

if the value for the parameter is Actual, then we will have only a column of Actual values

if it's Budget, then we will have only a column of Budget values

if it's Target, then we will have only a column of Target values

How should I write the mdx query for this?

I'm really desperate for the answer.

Thanks a lot for your help.

The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.sql

Dynamic measures

Hi,

I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.

The parameter values has, Actual, Budget, Target

For the one field, base on the above parameter, will select,

if the value for the parameter is Actual, then we will have only a column of Actual values

if it's Budget, then we will have only a column of Budget values

if it's Target, then we will have only a column of Target values

How should I write the mdx query for this?

I'm really desperate for the answer.

Thanks a lot for your help.

The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.

Dynamic Images

Hi Gurus,

here's a tricky problem. I have a requirement for a report where in for certain specific customers, the logo on the page header ( which is inserted as an image on the crystal report ) should not appear. I am novice in Crystal reporting and finding it hard to come up with a solution. Currently we are on Crystal Version 9.0. Any help on this issue is dearly appreciated.

Thank you.I would say if you insert the images dynamically. you can easily add a parameter to the company table let's say addpicture. and then if this field is set to 1 then you display picture otherwise you don't

dynamic creation of select statement

hello,

My basic requirement is i have five drop down list where data is selected and based on the selection the no. of colums to be selected have to be designed. ex : say i have following drop down lists:
1. community
2. gender
3. level
4. term
5. year

at runtime is it possible to design the crystal reports columns.

Pl. help out in directing to url or given sample source to dynamic creation of columns in the crystal reports.

thanks in advance
karunau need to add the formulas and the field object formatting.
u can do this by declaring an array -->
IFormulaFieldDefinitionPtr formulas[10];

then when adding formulas-->
ISectionPtr pSection = GetReportSection(sectionNum);
IFormulaFieldDefinitionsPtr pFormulaFields = 0;
m_Report->get_FormulaFields((IFormulaFieldDefinitions**) &pFormulaFields);

CString Recur = CString(L"WhileReadingRecords;" +
(CString)__toascii(13) +
(CString)__toascii(10));

// Add the formulas to the formula fields collection
CString CStrText = Recur + CString(L"Space(10)");
BSTR strText = CStrText.AllocSysString();
BSTR objName;
int i=0;
////assume u have stored the field names somewhere like a list or an array.

for(iter=lstRecs.begin();iter!=lstRecs.end();iter++)
{

CString str= *iter;
if(i==0)
{
objName = str.AllocSysString();
formulas[i]=pFormulaFields->Add(objName, strText);


}
else
{
SysReAllocString(&objName, str.AllocSysString());
formulas[i]=pFormulaFields->Add(objName, strText);

}

i++;
}

////////////////////////////////////////////////////////////////////////////////
then add the field objects-->

ISectionPtr pSection = GetReportSection(3);
IFieldObjectPtr pFieldObj = 0;

LocX = 10; // Horizontal offset
VARIANT var;
VariantInit (&var);
var.vt = VT_DISPATCH;
BSTR objName = NULL;

int i=0;

for(iter=lstRecs.begin();iter!=lstRecs.end();iter++)
{

CString str= *iter;
var.pdispVal = formulas[i];
pFieldObj = pSection->AddFieldObject(var, LocX, 0);
SysReAllocString(&objName,str.AllocSysString());
pFieldObj->put_Name(objName);
pFieldObj->put_Width(1600);
LocX += 1500;
}

////////////////////////////////////////////////////////////////////////////////////////////
thats it! hope this helps...

Dynamic Connectionstring and Email delivery

Hi,
Im new to reporting services.
My requirement is to develop only one set of reports and to pass the
connectionstring dynamically based on the user logs in.I used the
setdatasourcecontents API as it overwrites the connectionstring and Im
passing it just before the retrieval of the reports.Can anyone suggest me
whether it is the best way or is there any other way to acheive this.
Also the user will have an option of subscribing to a report and he can
specify a scheduled time for that.As the report runs just before the delivery
and Im Overwriting the datasource the mail will be sent with wrong report
data.So,please help me in solving this.
Im using standard edition.
Thanks..What are you trying to accomplish with this? Is it a matter of several
databases on the same server and based on the user you are allowing them to
see data from that database?
What you are doing is very problematic (as you have seen) but there might be
some other solution for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GK" <GK@.discussions.microsoft.com> wrote in message
news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> Hi,
> Im new to reporting services.
> My requirement is to develop only one set of reports and to pass the
> connectionstring dynamically based on the user logs in.I used the
> setdatasourcecontents API as it overwrites the connectionstring and Im
> passing it just before the retrieval of the reports.Can anyone suggest me
> whether it is the best way or is there any other way to acheive this.
> Also the user will have an option of subscribing to a report and he can
> specify a scheduled time for that.As the report runs just before the
> delivery
> and Im Overwriting the datasource the mail will be sent with wrong report
> data.So,please help me in solving this.
>
> Im using standard edition.
> Thanks..
>|||Hello Bruce,
Thanks for your relply,I have seen some articles and custom data extension
might be a solution for this,and I don't know how to implement this and
whether it solves our email issue.
Definitely "Setdatasourcecontents" is not a solution as it applies changes
globally.
Our client have different companies wherein the database structure is
same.So I can create the reports for one company and pass the connection
string based on the employee login,so that he can view the reports specific
to his company.
Also when an employee subscribes for a report,how to pass the connection
string at the time of delivery of the report so that he receives his
company's report data.
"Bruce L-C [MVP]" wrote:
> What are you trying to accomplish with this? Is it a matter of several
> databases on the same server and based on the user you are allowing them to
> see data from that database?
> What you are doing is very problematic (as you have seen) but there might be
> some other solution for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "GK" <GK@.discussions.microsoft.com> wrote in message
> news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> > Hi,
> > Im new to reporting services.
> > My requirement is to develop only one set of reports and to pass the
> > connectionstring dynamically based on the user logs in.I used the
> > setdatasourcecontents API as it overwrites the connectionstring and Im
> > passing it just before the retrieval of the reports.Can anyone suggest me
> > whether it is the best way or is there any other way to acheive this.
> > Also the user will have an option of subscribing to a report and he can
> > specify a scheduled time for that.As the report runs just before the
> > delivery
> > and Im Overwriting the datasource the mail will be sent with wrong report
> > data.So,please help me in solving this.
> >
> >
> > Im using standard edition.
> >
> > Thanks..
> >
> >
>
>|||There are several solutions for this with one exception. The problem is with
subscriptions. There is the User!userid global variable which will work for
everything but a subscription. The subscription will not know who to run as.
I have trouble seeing how to get around that even for a custom data
extension.
Leaving aside subscriptions you can solve the problem without a custom data
extension. Have a table that maps userid to database. Could also use dynamic
sql in Reporting Services but it would be cleaner with Stored Procedures.
Pass the User!Userid to the SP. Look up the database to use and then
assemble your SQL string to use it and execute it. This is clean and cannot
be modified by the user. It does not, however deal with subscriptions. I
don't have a good solution for you for that.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GK" <GK@.discussions.microsoft.com> wrote in message
news:F76DFED5-42A9-4351-9284-E82370257FCB@.microsoft.com...
> Hello Bruce,
> Thanks for your relply,I have seen some articles and custom data extension
> might be a solution for this,and I don't know how to implement this and
> whether it solves our email issue.
> Definitely "Setdatasourcecontents" is not a solution as it applies changes
> globally.
> Our client have different companies wherein the database structure is
> same.So I can create the reports for one company and pass the connection
> string based on the employee login,so that he can view the reports
specific
> to his company.
> Also when an employee subscribes for a report,how to pass the connection
> string at the time of delivery of the report so that he receives his
> company's report data.
>
> "Bruce L-C [MVP]" wrote:
> > What are you trying to accomplish with this? Is it a matter of several
> > databases on the same server and based on the user you are allowing them
to
> > see data from that database?
> >
> > What you are doing is very problematic (as you have seen) but there
might be
> > some other solution for you.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> >
> > "GK" <GK@.discussions.microsoft.com> wrote in message
> > news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> > > Hi,
> > > Im new to reporting services.
> > > My requirement is to develop only one set of reports and to pass the
> > > connectionstring dynamically based on the user logs in.I used the
> > > setdatasourcecontents API as it overwrites the connectionstring and Im
> > > passing it just before the retrieval of the reports.Can anyone suggest
me
> > > whether it is the best way or is there any other way to acheive this.
> > > Also the user will have an option of subscribing to a report and he
can
> > > specify a scheduled time for that.As the report runs just before the
> > > delivery
> > > and Im Overwriting the datasource the mail will be sent with wrong
report
> > > data.So,please help me in solving this.
> > >
> > >
> > > Im using standard edition.
> > >
> > > Thanks..
> > >
> > >
> >
> >
> >

dynamic change of graphs type

We have a requirement where we show a particular simple graph in type columns. we wanted to give the user a parameter like Type: which has some or different charts in it. i would like to give a user a feature of dynamically changing the chart type.

hope you understand me. let me know which is the property i need to assign the parametere value and where i should write that

there is no such property available. For this you need to writhe the function and make it DLL and refer in your report or u can write the code in code window and refer that in your report|||

I have the same problem, I tried the following but did not work.

<Type>=Parameters!ChartType.Value</Type>

|||

Expression-based chart types are not supported at this point. The closest you can get in RS is to define multiple charts with different types. Then through the Visibility.Hidden property you always hide all but the one chart you want to see for a particular parameter value.

-- Robert

|||

OK, just figured out a work around:

1. Create report a report parameter for chart type, say ChartType, add in all the types you want.

2. Use your current chart as a template, make a few copy of it, and paste them in the same report.

3. Change the type property of each chart to a type corresponding to a item in ChartType parameter.

4. Also change the visibility/Hidden property of each chart from False to expression: e.g. for Pie chart, =iif(Parameters!ChartType.Value="Pie",false,true)

Drawback: I guess the Reporting Server may need to create all the charts and Browser may need to load all the charts' images each time when user sees one chart, the other charts are hidden but has been generated/loaded.

Hope this will help before MS fixes the bug in its next release.

|||

I have just had a user ask for this feature, wondering if it is in RS 2005 or planned for the near future?

99

|||

I have the same problem.

I created 5 charts on one report. but i dont want to execute the report for every graph change.

all the charts are using the same dataset.

Can i show /Hide all the charts except the choosen one by clicking on a textbox?

thanks

|||

I have the same problem too,

I wonder how did you solve problem lastly?

And ...

In last 3 months microsoft deliver SP2 of SQL Server 2005. Does it bring any new property to solve this problem?

I will be glad taking your answers about problem...

|||

Hi,

I didnt find a way to choose the graph type by clicking a text box.

i created all the graphs in the report, all connected to the same dataset.

i also created a list control and the user can choose which type he wants.

and by the graphs visibilty propery, i show only the wanted one.

regarding the sp2, i dont familar with it yet. soory.

dynamic change of graphs type

We have a requirement where we show a particular simple graph in type columns. we wanted to give the user a parameter like Type: which has some or different charts in it. i would like to give a user a feature of dynamically changing the chart type.

hope you understand me. let me know which is the property i need to assign the parametere value and where i should write that

there is no such property available. For this you need to writhe the function and make it DLL and refer in your report or u can write the code in code window and refer that in your report|||

I have the same problem, I tried the following but did not work.

<Type>=Parameters!ChartType.Value</Type>

|||

Expression-based chart types are not supported at this point. The closest you can get in RS is to define multiple charts with different types. Then through the Visibility.Hidden property you always hide all but the one chart you want to see for a particular parameter value.

-- Robert

|||

OK, just figured out a work around:

1. Create report a report parameter for chart type, say ChartType, add in all the types you want.

2. Use your current chart as a template, make a few copy of it, and paste them in the same report.

3. Change the type property of each chart to a type corresponding to a item in ChartType parameter.

4. Also change the visibility/Hidden property of each chart from False to expression: e.g. for Pie chart, =iif(Parameters!ChartType.Value="Pie",false,true)

Drawback: I guess the Reporting Server may need to create all the charts and Browser may need to load all the charts' images each time when user sees one chart, the other charts are hidden but has been generated/loaded.

Hope this will help before MS fixes the bug in its next release.

|||

I have just had a user ask for this feature, wondering if it is in RS 2005 or planned for the near future?

99

|||

I have the same problem.

I created 5 charts on one report. but i dont want to execute the report for every graph change.

all the charts are using the same dataset.

Can i show /Hide all the charts except the choosen one by clicking on a textbox?

thanks

|||

I have the same problem too,

I wonder how did you solve problem lastly?

And ...

In last 3 months microsoft deliver SP2 of SQL Server 2005. Does it bring any new property to solve this problem?

I will be glad taking your answers about problem...

|||

Hi,

I didnt find a way to choose the graph type by clicking a text box.

i created all the graphs in the report, all connected to the same dataset.

i also created a list control and the user can choose which type he wants.

and by the graphs visibilty propery, i show only the wanted one.

regarding the sp2, i dont familar with it yet. soory.

dynamic change of graphs type

We have a requirement where we show a particular simple graph in type columns. we wanted to give the user a parameter like Type: which has some or different charts in it. i would like to give a user a feature of dynamically changing the chart type.

hope you understand me. let me know which is the property i need to assign the parametere value and where i should write that

there is no such property available. For this you need to writhe the function and make it DLL and refer in your report or u can write the code in code window and refer that in your report|||

I have the same problem, I tried the following but did not work.

<Type>=Parameters!ChartType.Value</Type>

|||

Expression-based chart types are not supported at this point. The closest you can get in RS is to define multiple charts with different types. Then through the Visibility.Hidden property you always hide all but the one chart you want to see for a particular parameter value.

-- Robert

|||

OK, just figured out a work around:

1. Create report a report parameter for chart type, say ChartType, add in all the types you want.

2. Use your current chart as a template, make a few copy of it, and paste them in the same report.

3. Change the type property of each chart to a type corresponding to a item in ChartType parameter.

4. Also change the visibility/Hidden property of each chart from False to expression: e.g. for Pie chart, =iif(Parameters!ChartType.Value="Pie",false,true)

Drawback: I guess the Reporting Server may need to create all the charts and Browser may need to load all the charts' images each time when user sees one chart, the other charts are hidden but has been generated/loaded.

Hope this will help before MS fixes the bug in its next release.

|||

I have just had a user ask for this feature, wondering if it is in RS 2005 or planned for the near future?

99

|||

I have the same problem.

I created 5 charts on one report. but i dont want to execute the report for every graph change.

all the charts are using the same dataset.

Can i show /Hide all the charts except the choosen one by clicking on a textbox?

thanks

|||

I have the same problem too,

I wonder how did you solve problem lastly?

And ...

In last 3 months microsoft deliver SP2 of SQL Server 2005. Does it bring any new property to solve this problem?

I will be glad taking your answers about problem...

|||

Hi,

I didnt find a way to choose the graph type by clicking a text box.

i created all the graphs in the report, all connected to the same dataset.

i also created a list control and the user can choose which type he wants.

and by the graphs visibilty propery, i show only the wanted one.

regarding the sp2, i dont familar with it yet. soory.

dynamic change of graphs type

We have a requirement where we show a particular simple graph in type columns. we wanted to give the user a parameter like Type: which has some or different charts in it. i would like to give a user a feature of dynamically changing the chart type.

hope you understand me. let me know which is the property i need to assign the parametere value and where i should write that

there is no such property available. For this you need to writhe the function and make it DLL and refer in your report or u can write the code in code window and refer that in your report|||

I have the same problem, I tried the following but did not work.

<Type>=Parameters!ChartType.Value</Type>

|||

Expression-based chart types are not supported at this point. The closest you can get in RS is to define multiple charts with different types. Then through the Visibility.Hidden property you always hide all but the one chart you want to see for a particular parameter value.

-- Robert

|||

OK, just figured out a work around:

1. Create report a report parameter for chart type, say ChartType, add in all the types you want.

2. Use your current chart as a template, make a few copy of it, and paste them in the same report.

3. Change the type property of each chart to a type corresponding to a item in ChartType parameter.

4. Also change the visibility/Hidden property of each chart from False to expression: e.g. for Pie chart, =iif(Parameters!ChartType.Value="Pie",false,true)

Drawback: I guess the Reporting Server may need to create all the charts and Browser may need to load all the charts' images each time when user sees one chart, the other charts are hidden but has been generated/loaded.

Hope this will help before MS fixes the bug in its next release.

|||

I have just had a user ask for this feature, wondering if it is in RS 2005 or planned for the near future?

99

|||

I have the same problem.

I created 5 charts on one report. but i dont want to execute the report for every graph change.

all the charts are using the same dataset.

Can i show /Hide all the charts except the choosen one by clicking on a textbox?

thanks

|||

I have the same problem too,

I wonder how did you solve problem lastly?

And ...

In last 3 months microsoft deliver SP2 of SQL Server 2005. Does it bring any new property to solve this problem?

I will be glad taking your answers about problem...

|||

Hi,

I didnt find a way to choose the graph type by clicking a text box.

i created all the graphs in the report, all connected to the same dataset.

i also created a list control and the user can choose which type he wants.

and by the graphs visibilty propery, i show only the wanted one.

regarding the sp2, i dont familar with it yet. soory.