Prompts Help

[Cognosnewbie14]

Hi all,

Hoping someone can help me with a query I have.

I am creating a report pack which consists of a number of pages showing different data and levels of detail.

The report basically summarises the position for whichever region the user selects from the first prompt. There are numerous pages which detail different information for selected region. And there is a page which shows detail for each town in that region; at the moment I have done this via the use of 'sections'. But in this review pack, users will only wish to see a few of the Towns in detail in their region, whereas currently the page shows every town in detail. Is it possible for this page to show only certain towns in detail (as selected by user), while also still showing the correct summary figures for the region on the other pages?

I have thought about setting up a prompt where a user can select multiple towns to filter but then this means the summary pages for the region will only show totals for those towns selected, not the actual regions total.

Is it possible for me to achieve this? Or is easier to leave the report as I have originally set up with towns sectioned and then once it's exported the user can cut out the towns they don't need?

Thanks in advance
;D

[BigChris]

Hi,

It's a bit difficult to answer without knowing the structure of your report, but I'd do it by creating a new query based on your original query. You can do that by effectively referring to your original query. (Apologies if I'm teaching you how to suck eggs - I've attached a screenshot to show you what I mean).

Once you've done that you can base the element of your report for selected towns on the Second Query. In that query you can put the filter for the towns that the user can select...you'll probably want to make that the cascading prompt on your prompt screen.

C

[Cognosnewbie14]

Big Chris... thanks for the rapid response!! ok that makes sense, and no no i am quite new to this so more detail the better!!

will give that a go and hopefully report back success in the morning!

cheers  ;D

[Cognosnewbie14]

Chris thats wicked advice! works exactly how i want it too! followed your steps and now have a prompt which selects the region, followed by a cascade prompt which shwos a list of towns from the region selected first!

But when i try to run the report i get an error 'A record was rejected. An arithmetic overflow occurred. PDS-PPE-0104' , i think basically this is due to some very large numbers that are cropping up in my report (even though i have scaled it!).

I think whats causing it, is that with how I have my columns set up (which cover the years), I have a member with code
'case
   when [Fiscal Year Name] between #$[RelSubToFiscYearName]{  prompt('Submission','token','Current')   } # + '13/14'and
   #$[RelSubToFiscYearName]{  prompt('Submission','token','Current')   } #  + '23/24'
   then [Fiscal Year Name]
end'
this column shows individual years 13/14 to 23 /24

and then i have a column with code
'case
   when [Fiscal Year Name] between #$[RelSubToFiscYearName]{  prompt('Submission','token','Current')   } # + '24/25'and
   #$[RelSubToFiscYearName]{  prompt('Submission','token','Current')   } #  + '24/25'
   then [Fiscal Year Name]
end'
this shows year 24/25

reason i have them split over  2 members is because ideally i want the first 11 years shown individually and then i want a sum of years 2-11 followed by year 12 column.... but i'm still trying to figure out how insert a column to total the necessary years!

what was happening prior to me inserting cascading prompts and the new pages, the table would show 11 columns 13/14 to 23/24 but then have 2 blank headered columns which contained unscaled numbers (i believe this was the amounts attributing to the years i had excluded from the table eg sunk costs and later years after 24/25!) , these are then followed by the 24/25 column.

so i think the error is being caused by the 2 blank headered columns that are appearing when i run the report?

any ideas where i am going wrong?

apologies for the long winded email, but any help would be much appreciated, or happy to give more info if it would help?

thanks in advance  :)

[BigChris]

Hmmm...I'm sure that that's what's causing the error, but I'm sure someone else will correct me if I'm wrong. An arithmetic overflow is usually caused by somehting like a large number being divided by a very small number. For example, 1000 divided by 0.001 would result in 1000000 (stating the obvious perhaps, but you see what I mean). Splitting the data into two columns shouldn't cause any problems...I'd check the data that's going into those columns. Could you filter the query to give a smaller dataset then check the tabular data? That might give you some sort of clue...

[BigChris]

That should obviously have read "I'm not sure that that's ..."

[Cognosnewbie14]

hmm ok just tested it for one of the smaller regions and the report has run,

I do have a % calculation in my tables and i think thats whats causing the error for the bigger regions where its trying to divide a really small number by a much larger number.... any way to rectify this or would i be better off taking the calculation out of there?

Also I dont suppose you have any idea why I'm getting the 2 blank headered columns? and how easy is it to insert a column to total up the selected 10 years?

apologies for all the questions and i do appreciate your help!