If you are unable to create a new account, please email bspsoftware@techdata.com

Author Topic: Active Report - Select all drop down list items  (Read 961 times)

Offline Vanaeph

  • Full Member
  • ***
  • Join Date: Jun 2022
  • Posts: 7
  • Forum Citizenship: +0/-0
Active Report - Select all drop down list items
« on: 24 Jun 2022 11:12:54 am »
Hello Everyone,

Super new to cognos analytics, and I hear this is the place to be!  I was wondering if someone could help me out?

I am trying to add a new item to my drop down list that is a summary of all the items in that list.  There are 8 product portfolios, I would like one of the items in the list to be a summary of all 8.

I was able to create a category in that dropdown list called All Product Portfolios by going into the data dropdown list object, changing the no value item list to show, and then changing the label to All Product Portfolios and changing the position in list to first item.

I have a data deck that uses this drop down list to populate data across two cross tabs and pie charts. 

Any help is appreciated.  This is my first attempt at authoring a report so please speak to me as if I was 5 and am easily distracted by bright shiny objects.  (I did use the search function but couldn't find/understand to be an answer to my problem - apologies if this has been asked recently).
 

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,529
  • Forum Citizenship: +672/-10
  • Cognos Software Muppet
Re: Active Report - Select all drop down list items
« Reply #1 on: 24 Jun 2022 02:28:28 pm »
Hello Everyone,

Super new to cognos analytics, and I hear this is the place to be!  I was wondering if someone could help me out?

I am trying to add a new item to my drop down list that is a summary of all the items in that list.  There are 8 product portfolios, I would like one of the items in the list to be a summary of all 8.

I was able to create a category in that dropdown list called All Product Portfolios by going into the data dropdown list object, changing the no value item list to show, and then changing the label to All Product Portfolios and changing the position in list to first item.

I have a data deck that uses this drop down list to populate data across two cross tabs and pie charts. 

Any help is appreciated.  This is my first attempt at authoring a report so please speak to me as if I was 5 and am easily distracted by bright shiny objects.  (I did use the search function but couldn't find/understand to be an answer to my problem - apologies if this has been asked recently).

Hi,

As you're using a data deck, you will need to make use of the Default Card. This is normally hidden, but you can enable it in the properties of the Data Deck. It only appears when the variable driving the data deck is empty. You will need to enable it, then drag into it and build the same crosstabs and pie charts that you are using in the regular cards of the data deck. The difference with these is that you will not master-detail them to the data deck.
Then when you unset the variable from your dropdown list (using the no value item), the default card of the data deck should appear, showing the unfiltered crosstabs and charts.

Hope this makes sense. If you need more details, please ask.

Cheers!

MF.
Meep!

Offline Vanaeph

  • Full Member
  • ***
  • Join Date: Jun 2022
  • Posts: 7
  • Forum Citizenship: +0/-0
Re: Active Report - Select all drop down list items
« Reply #2 on: 27 Jun 2022 08:20:28 am »
Thank you! I was able to figure out based on your guidance it was much appreciated!

I am on to my next set of issues. From what I read it doesn't seem like I can create a downloadable excel file that has the underlying data that was used to populate the data containers in the data deck with an active report. 

So I thought maybe I could just do a list on a separate page called detail but I can only view the list if I  change the order of the summary page to be on the bottom and it seems like there is no way to navigate between pages when I run the report.  I can only view the list on the detail page or the Summary report with the data deck on the summary page.  Is there some configuring I can do to make it easier to jump between the pages.

In any event thanks again for your help!  I'll research some basic page functionality as I have no idea what Page set and Report references are and it's probably something simple that I've just missed.

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,529
  • Forum Citizenship: +672/-10
  • Cognos Software Muppet
Re: Active Report - Select all drop down list items
« Reply #3 on: 27 Jun 2022 08:36:45 am »
Thank you! I was able to figure out based on your guidance it was much appreciated!

I am on to my next set of issues. From what I read it doesn't seem like I can create a downloadable excel file that has the underlying data that was used to populate the data containers in the data deck with an active report. 

So I thought maybe I could just do a list on a separate page called detail but I can only view the list if I  change the order of the summary page to be on the bottom and it seems like there is no way to navigate between pages when I run the report.  I can only view the list on the detail page or the Summary report with the data deck on the summary page.  Is there some configuring I can do to make it easier to jump between the pages.

In any event thanks again for your help!  I'll research some basic page functionality as I have no idea what Page set and Report references are and it's probably something simple that I've just missed.

Hi,

An important piece of information to know when authoring Active Reports is that they only ever render one page. This means you should avoid adding multiple pages and/or page sets to an active report. That doesn't mean you can't achieve the result you are looking for, though.

In an active report, you can use decks and/or data decks to simulate what pages do in a regular report. In this case, if you want a "page" containing a list of details, you could use a Deck for this. Define the deck with two cards (you can give each card a name, so maybe call the first card Summary and the second card Detail). Move all your current content (data deck etc) into the first (Summary) card of the deck, and add your List to the second card of the deck. Then all you need is a control and a variable to allow a user to switch between the cards.

Again, if you need more details, please ask.

Cheers!

MF.
Meep!

Offline Vanaeph

  • Full Member
  • ***
  • Join Date: Jun 2022
  • Posts: 7
  • Forum Citizenship: +0/-0
Re: Active Report - Select all drop down list items
« Reply #4 on: 30 Jun 2022 01:46:40 pm »
In order to get around the multi-page and detail list requirements I have been playing around with Drill-through definitions.   I created a report that is a basic list of items (target report) and I was able to export a csv file on the report when the hyperlink was clicked on Product portfolio (in the source report).  Success!  Popped a bottle of champagne gave a congratulations speech to my dog.  All is well in the world!
 
Next  I tried to  produce an output of only the items that made up the summarized count of the crosstab intersection.  This is where I am coming into some issues.  I did the following:

1.   Unlocked Page designed objects
2.   Checked the query and default measure that was being used
3.   Ih the Properties pane, set the Define contents property to Yes
4.   From the crosstab query, drag the measure that disappeared to the empty crosstab intersection.
5.   Click the measure in the crosstab intersection and then, in the Properties pane, click Drill-through definitions.
6.   Create the drill through definition

I was expecting to just have the 42 items that were summarized in the cross tab intersection populate in the CSV file, however what I got was the same data in the csv file that was in the link for the product portfolio. 

Should I create a cross tab in the target report?  Does every item in my crosstab query from the source report need to be in my target report as well?  The measure I used in the Crosstab was # of accounts.  I created a new data item by taking the expression of account number and changed the detail aggregation to count distinct and named that account number (Count) and then used that as my measure in my Crosstab in my source report.     

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,529
  • Forum Citizenship: +672/-10
  • Cognos Software Muppet
Re: Active Report - Select all drop down list items
« Reply #5 on: 30 Jun 2022 02:37:55 pm »
In order to get around the multi-page and detail list requirements I have been playing around with Drill-through definitions.   I created a report that is a basic list of items (target report) and I was able to export a csv file on the report when the hyperlink was clicked on Product portfolio (in the source report).  Success!  Popped a bottle of champagne gave a congratulations speech to my dog.  All is well in the world!
 
Next  I tried to  produce an output of only the items that made up the summarized count of the crosstab intersection.  This is where I am coming into some issues.  I did the following:

1.   Unlocked Page designed objects
2.   Checked the query and default measure that was being used
3.   Ih the Properties pane, set the Define contents property to Yes
4.   From the crosstab query, drag the measure that disappeared to the empty crosstab intersection.
5.   Click the measure in the crosstab intersection and then, in the Properties pane, click Drill-through definitions.
6.   Create the drill through definition

I was expecting to just have the 42 items that were summarized in the cross tab intersection populate in the CSV file, however what I got was the same data in the csv file that was in the link for the product portfolio. 

Should I create a cross tab in the target report?  Does every item in my crosstab query from the source report need to be in my target report as well?  The measure I used in the Crosstab was # of accounts.  I created a new data item by taking the expression of account number and changed the detail aggregation to count distinct and named that account number (Count) and then used that as my measure in my Crosstab in my source report.   

Hi,

I'm assuming the target report of the drill-through is a "regular" report (vs an active report)? Did you create prompts or filters with parameters in this report to receive the relevant items from the rows and columns in the source report?

If not, add those prompts (or filters that use parameters), then save the report. Then go back to the drill-through definition in your source report and make sure the definition is passing the correct items to fulfill the parameters in the target report.

Cheers!

MF.
Meep!

Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 790
  • Forum Citizenship: +29/-1
Re: Active Report - Select all drop down list items
« Reply #6 on: 01 Jul 2022 10:02:12 am »
Maybe I missed something.  Couldn't you do the same thing by making the parameter optional and not selecting anything in the list?  If it's a matter of aggregating and showing a project name or "all projects", couldn't that be done by way of a macro?

Code: [Select]
case
  when # prompt('project','string','ALL') # = 'ALL'
    then 'All Projects'
  else [Project Name]
end

Offline Vanaeph

  • Full Member
  • ***
  • Join Date: Jun 2022
  • Posts: 7
  • Forum Citizenship: +0/-0
Re: Active Report - Select all drop down list items
« Reply #7 on: 05 Jul 2022 10:21:23 am »
Hi,

I'm assuming the target report of the drill-through is a "regular" report (vs an active report)? Did you create prompts or filters with parameters in this report to receive the relevant items from the rows and columns in the source report?

If not, add those prompts (or filters that use parameters), then save the report. Then go back to the drill-through definition in your source report and make sure the definition is passing the correct items to fulfill the parameters in the target report.

Cheers!

MF.

Thanks! adding the parameters on the normal target report did the trick, now when you click on the number in the crosstab intersection a csv file downloads with that subset of data.

However, now when the total is clicked I would like the target report  to run wide open and download to a CSV. 

The issue I have is  it goes to a prompt page for all the parameters.  I thought - ok if I just don't add the parameters when I set up the drill through definitions that it would default to displaying the whole report like when before I had created the parameters.  I'll play around with it and see if I can't figure it out.

Maybe I missed something.  Couldn't you do the same thing by making the parameter optional and not selecting anything in the list?  If it's a matter of aggregating and showing a project name or "all projects", couldn't that be done by way of a macro?

Code: [Select]
case
  when # prompt('project','string','ALL') # = 'ALL'
    then 'All Projects'
  else [Project Name]
end

Dougp, Thanks for the suggestion!  I have a couple more of these static reports that need to be converted to active reports with similar concepts,  so I'll try that on the next one!         

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,529
  • Forum Citizenship: +672/-10
  • Cognos Software Muppet
Re: Active Report - Select all drop down list items
« Reply #8 on: 06 Jul 2022 08:32:54 am »
Maybe I missed something.  Couldn't you do the same thing by making the parameter optional and not selecting anything in the list?  If it's a matter of aggregating and showing a project name or "all projects", couldn't that be done by way of a macro?

Code: [Select]
case
  when # prompt('project','string','ALL') # = 'ALL'
    then 'All Projects'
  else [Project Name]
end

Hi Doug,

This won't work in an Active Report. The dropdown list is used to set an active report variable which then drives which card is visible in a data deck. There are no prompts or parameters involved. All the interaction is done within the pre-rendered output.

Cheers!

MF.
« Last Edit: 08 Jul 2022 10:50:40 am by MFGF »
Meep!

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,529
  • Forum Citizenship: +672/-10
  • Cognos Software Muppet
Re: Active Report - Select all drop down list items
« Reply #9 on: 06 Jul 2022 08:39:56 am »
Thanks! adding the parameters on the normal target report did the trick, now when you click on the number in the crosstab intersection a csv file downloads with that subset of data.

However, now when the total is clicked I would like the target report  to run wide open and download to a CSV. 

The issue I have is  it goes to a prompt page for all the parameters.  I thought - ok if I just don't add the parameters when I set up the drill through definitions that it would default to displaying the whole report like when before I had created the parameters.  I'll play around with it and see if I can't figure it out.

Dougp, Thanks for the suggestion!  I have a couple more of these static reports that need to be converted to active reports with similar concepts,  so I'll try that on the next one!         

Hi,

The rendering type you pick for the target report, and the way your browser deals with the rendered output will determine what you see when you drill through. It sounds like you have chosen the csv output type - your browser will usually just present this as a download. If you pick HTML instead (for example), you will see the results rendered in your browser.

I'm going to suggest we take a breath here and consider why you are going down the path of authoring an Active Report (which is designed for offline use) then drilling through to a regular report (which is designed to run live in a browser)? What is the end goal you are trying to achieve? Would a Dashboard (for example) deliver this in a simpler and more intuitive manner? You can open up the data tray at the bottom to see the data, plus you can filter to your heart's content without having to do lots of authoring. Just a thought.

Cheers!

MF.
Meep!

Offline Vanaeph

  • Full Member
  • ***
  • Join Date: Jun 2022
  • Posts: 7
  • Forum Citizenship: +0/-0
Re: Active Report - Select all drop down list items
« Reply #10 on: 08 Jul 2022 10:29:56 am »
Hi,

Thanks again for all your help,  I appreciate your guidance.  This boils down to management requests and company capabilities.  Boss person wants an active report with drill throughs, this is based on a report template created by someone who is not available to complete the work, and the company I work for doesn't support dashboards (which is unfortunate because every tutorial and resource I see based on dashboards look pretty slick and are easy to configure). 

Hi,

The rendering type you pick for the target report, and the way your browser deals with the rendered output will determine what you see when you drill through. It sounds like you have chosen the csv output type - your browser will usually just present this as a download. If you pick HTML instead (for example), you will see the results rendered in your browser.

I'm going to suggest we take a breath here and consider why you are going down the path of authoring an Active Report (which is designed for offline use) then drilling through to a regular report (which is designed to run live in a browser)? What is the end goal you are trying to achieve? Would a Dashboard (for example) deliver this in a simpler and more intuitive manner? You can open up the data tray at the bottom to see the data, plus you can filter to your heart's content without having to do lots of authoring. Just a thought.

Cheers!

MF.
     

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,529
  • Forum Citizenship: +672/-10
  • Cognos Software Muppet
Re: Active Report - Select all drop down list items
« Reply #11 on: 08 Jul 2022 10:57:29 am »
Hi,

Thanks again for all your help,  I appreciate your guidance.  This boils down to management requests and company capabilities.  Boss person wants an active report with drill throughs, this is based on a report template created by someone who is not available to complete the work, and the company I work for doesn't support dashboards (which is unfortunate because every tutorial and resource I see based on dashboards look pretty slick and are easy to configure). 
     

Hi,

One thing you might want to clarify is what your boss actually means when referring to an "active report". If he or she isn't really familiar with how reports work, they might just mean they want a report that can be interacted with. This could be a "regular" report with prompts on the page, possibly. The "Active Report" type you are using at the moment is quite old technology, developed back in the early days of Cognos 10, and it was/is really intended to deliver interactive report outputs that can be used in an offline situation (ie when not logged in to the Cognos portal).

I just don't want you to develop your way into a non-optimal solution without investigating the options and understanding whether this really is the best fit for the requirements.

Cheers!

MF.
Meep!

Offline Vanaeph

  • Full Member
  • ***
  • Join Date: Jun 2022
  • Posts: 7
  • Forum Citizenship: +0/-0
Re: Active Report - Select all drop down list items
« Reply #12 on: 11 Jul 2022 10:08:20 am »
Hi this has been discussed and shot down, the funny thing is (I have a sick sense of humor so I find it funny at least),  IE is at end of life, the MHT file for the active reports can only function using IE at my firm.  The current work around to use the active reports is to log on to VM ware and run them through IBM active report viewer.  So essentially the whole benefit of active reports is gone, but the marching orders are take these reports and convert them to active reports. I will note that a couple of folks have pointed this out -  myself and my boss included but this was the path that was taken and we are tethered to it. I also think there is a secondary driver of getting me competent at cognos and this is how they are approaching that with the limited resources and lack of a training program for report authoring.

Good Times and Happy Monday!  I do actually enjoy my firm and the role, I don't want to come off as pessimistic or as disenchanted worker as that it not the intent.  Check with me in a couple of years and that statement might change  ;D

Looks Like We can use dashboard......I was miss informed.  Now I have to figure out how to do that look foward to my next surrly of posts in the dashboard area.  I'm sure you can't wait!
« Last Edit: 11 Jul 2022 12:46:34 pm by Vanaeph »